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.
Thank you very much for posting your feedback.
- You can use the result of one lookup to filter another, datalookups return text however, so if you are trying to filter an id field you will need to convert to a integer(see attached).
- Data source variables are designed for when you need to return multiple properties(https://help.nintex.com/en-US/nwc/Content/Designer/DataSourceVariables.htm). This should allow you return the information needed. We are looking at objects for data lookups as well.
- Data source variables can also deal with mutliple results, while not ideal it's possible to modify the collections and display the data in the fields using variable functions.
At this time, we will not be adding showing DSV's in repeating section to the near-term backlog, as we are focused on adding additional connector support. However, we reevaluate requests every quarter and will reach out if priorities change. Please keep the feedback coming as it is critical for our longer-term planning.