You are viewing limited content. For full access, please sign in.

Question

Question

Does Forms Lookup filter results?

Forms Version 10
Updated November 25, 2019
asked on September 4, 2018

This is maybe more of a curiosity than a problem.

I'm return a SQL column to populate a dropdown list. Oddly, I was expecting to need to filter the returned results since the table contains two records with the same value. However, I ran the lookup just to make sure I got results at all, and I did, but it only returned the unique values.

Does Forms filter results to unique records by default?

0 0

Answer

SELECTED ANSWER
replied on September 4, 2018

My experience has been that Forms only displays unique values when you're looking at one column.

1 0
View 1 previous reply
replied on September 4, 2018 • Show version history

On one hand it seems okay. Off the top of my head, I can't think of a reason why I would want to return other than distinct values. On the other hand, I'm not sure like the idea that the choice is not mine to make.

0 0
replied on September 4, 2018

I've never seen a problem with it myself, but I do think some advanced customization on the lookup rules would be nice (Hint Hint: custom SQL like in Workflow).

The behavior is different between a dropdown list and a table. I can't envision any scenario in which you would want duplicate items in a select because choosing one or the other makes no difference.

For example, if you pull that same column into a table it will return everything including duplicates and that's where I think the option would be more useful. My solution is usually to build custom views in my data source.

1 0
replied on September 5, 2018

Sorry if this belongs in its own thread, but I thought I'd add my own observations about odd behavior with lookup rules...

Not only does Forms filter unique results, but it also does some string trimming/cleanup. For instance if you happen to have leading or trailing spaces, or multiple spaces next to one another, or if certain characters are present such as double quote ("). This can lead to unexpected results if you have dependent lookup rules such as:

1. Fill Field1 with Col1

2. When Field1 matches Col1, Fill Field2 with Col2

I've seen many cases where the second lookup rule breaks and we discover that the data source has some value in Col1 that is not getting pulled in accuractely.

1 0
replied on November 25, 2019

I could use this for a forms process I'm trying to build now. Basically, the customer wants a search functionality. So when you enter Building Name, they want it to return 10 or so instances of that building name, and they want the inspector column to then only return the inspectors associated with one of those entries... Then when you select an inspector, they want that to further filter the list.

Right now I can only get one value at a time.

0 0

Replies

You are not allowed to reply in this post.
You are not allowed to follow up in this post.

Sign in to reply to this post.

Details
Related Posts
Loading ...