Filter Data Lookup with real time data from another control or variable

1) Need to be able to add a condition or multiple conditions to a Data Lookup control that is connected to a MS SQL Server On-Prem Data Source, in order to filter returned results.

For example: I have two MS SQL Server on-premises data sources; each connected to separate tables in the same database. The two tables have a common key (ID). Unfortunately, the data sources do not give me the ability to filter using real time data at the data source(s) itself.

I want to use data lookup 1 to find the desired item by its ID in Tbl1 and then use that ID as a filter on data lookup 2 to just return those items in Tbl2 that have the same ID selected in data lookup 1.

I attempted to add a condition to my Data Lookup2, however, I am unable to select either a Form Control or Form Variable as a Value for the condition. They are all Greyed Out.


2) A data lookup should also be able to return multi-value fields, not just one. For example: If I lookup a person by Last Name, I may want to get multiple attributes for that person such as First Name, Address, City, State, Zip, Phone number, Age, Gender, etc. instead of having to choose only one of those attributes, which is the only option currently. I should be able to display the data in a Label, or Text field, or create a string variable containing all desired row columns.

3) And additionally, if a data lookup returns multiple rows, I would like the ability to display the additional rows. Perhaps using a repeating section. For example: I lookup a Last Name, and I want to see all items/rows with that Last Name (plus the additional fields such as first name, age, gender, etc) and display all of them on the form.