Additional filtering options for Lookup Element
I was working with a group this week that was looking to pass in an array of values as a lookup filter. The equivalent of a SQL "IN" statement seemed to make the most sense, but that is not a currently supported option.
The best option at the moment is to parse the array into separate fields and use up to 5 values to search the table. Any more than 5 options is not allowed due to the filter constraints.
Anyone else run into other conditions they couldn't account for with the existing lookup element?
-
I think it would be helpful to be able to access the contents of a lookup table as a JSON object (my impression is that this is not possible, I've put in tickets in the past)....so then more advanced lookup operations could be supported via page level JavaScript.
Example - It is very common in forms to have a lookup table of cities/towns/ports etc. Provided this lookup table had the lat lon of each location and the entire table could be passed to a page level function, the nearest location to the user's present location could be found to speed up data entry. The closest location could then be used to set the SmartTable search form field, retaining the full functionality of the original SmartTable search field, but allowing for an option to use nearest.
At present, I have only found this to be possible if the contents of the lookup table are stored within the page as a JSON object, which means when the lookup table data changes it has to be updated in the page as well.
-
Hey Bill, thank you for your feedback. In reference to your example, you should be able to configure your form to show records from the lookup based on your current location. You can take a look at following quick video https://drive.google.com/file/d/1tcXKrQ-qegfH-rKI8DhR7x3RRl2IhOoL/view?usp=sharing where we utilizing 100 meters radius to show list of locations from the lookup table based on device current location. If you have any questions how to set it up, I would be more than happy to schedule a quick call and walk through this example.
-
Ah thanks - this clarifies a few things. The biggest being I had no idea there was a new "lookup" element and had assumed Tony was talking about SmartTable searches. Seeing the filter option in the lookup element this makes more sense - are you filtering the lookup based on greater than/less than a bounding box or an actual radius? I can see how the bounding box example would work, filtering based on lookup lat/lons between lat lon values 100m N/S/E/W of the users location.
-
I would love to see the ability of an SQL "IN" statement in the Lookup Element capabilities.
Strongly agree. Currently hitting a roadblock without this functionality.
My scenario is that I have a list of IDs stored in a lookup table. Users need to create a subform for each ID that appears in the lookup element. What I need is the ability to show users the list of IDs in the lookup table that they have not yet recorded as a subform on the current survey. Having the "IN" operator would allow me to filter the lookup element where "id NOT IN comma_deliminated_string_of_ids_already_recorded"
Unfortunately there are potentially hundreds of IDs that it will need to filter through, so I am not able to accomplish this using individual != filters.
Any ideas on workarounds?
Please sign in to leave a comment.
Comments
6 comments