Hello, I have a multi-search form that uses 2 dropdown lists. Once, the items have been chosen from the list I want a trigger that checks for the chosen items from my table.
The columns from the select are 2 fields of type REF.
Any help would be appreciated.
Hi @MRiley
This is a common question that have been answered.
You may want to look for dependent drop-down expression in the search bar.
You may also want to look for it here in order to understand the mechanism:
Hi thanks for the quick response. However, I am able to create the dropdowns which is not my issue. My issue is after the selections have been made, I want the results of the selections to show in a row below the selected choices. This is the method used in the template Multi-Search Demo. However, while that template uses 2 date fields, a status and a text name field, I am using 2 dropdown lists only. My hope is to bring back a list of records which have been chosen from the dropdown list items.
Which template are you talking about ? Can you provide the url link ?
Is this this one?
I’m afraid I’m having a language barrier issue at the moment, could you provide a screenshot of what you currently have and of what you would like to get ?
Sure no problem.
Here is the url for the template:
I have attempted to send a screenshot in this email however, email gets rejected because it doesn’t seem to want to accept it.
Here I use 2 dropdown lists which is working fine.
Once I choose the parameters, I want to see a list of all of the records that are chosen based on the parameters.
Melvin
“App not found” with the url you provided.
Is it an AppSheet sample app ?
You can copy-paste screenshot directly from the community, you may want to try it this way.
It is one of the provided samples from the templates. It seems everytime I
paste a screenshot, the email seems to reject it.
[image: image.png]
Melvin Riley, DBA
IAD Data Management
National Archives
*Washington D.C. *
O: 301-837-0715
try it from here:
I sent 2 screenshots with samples from the template and the one from my
revision of it.
Please let me know if received them.
Thanks alot
–
Melvin Riley, DBA
IAD Data Management
National Archives
*Washington D.C. *
O: 301-837-0715
Ok here is the screenshot from the demo app that I’m using:
Except I am using 2 dropdown lists. I want to be able to use the value chosen to look at the referenced detials.
That’s a FILTER() expression.
Type List, base type Ref, sourcetable: your Children table.
What about that in the expression of your virtual column ?
FILTER("ChildrenTable",
AND(
[YourColumn1InChildrenTable]=[_THISROW].[YourColumn1InCurrentTable],
[YourColumn2InChildrenTable]=[_THISROW].[YourColumn2InCurrentTable]
)
)
Upon trying the solution, I am now getting this error:
Column ‘Results’ in Table ‘Search2_Schema’ of Column Type ‘Ref’ has an invalid expression in the Formula field.‘=FILTER(“Test_Prog_Lst”, AND( [File_Series]=[_THISROW].[Series_File_numb], [File_No]=[_THISROW].[Series_Item_numb] ) )’. Cannot compare List with Ref in ([File_Series] = [_THISROW].[Series_File_Numb])
Hi @MRiley
This is explicit: “Cannot compare List with Ref in ([File_Series] = [_THISROW].[Series_File_Numb])”
That means one of your columns is a list, and you are comparing a list and a single item.
You need to test if the single item is included in the list.
I don’t know which one is which one, here are my suggestions:
FILTER("Test_Prog_Lst",
AND(
IN([File_Series],[_THISROW].[Series_File_numb]),
[File_No]=[_THISROW].[Series_Item_numb]
)
)
or
FILTER("Test_Prog_Lst",
AND(
IN([_THISROW].[Series_File_numb],[File_Series]),
[File_No]=[_THISROW].[Series_Item_numb]
)
)
For reference:
I tried that solution and received this error:
Column ‘Results’ in Table ‘Search2_Schema’ of Column Type ‘Ref’ has an invalid expression in the Formula field.‘=FILTER(“Test_Prog_Lst”, AND( IN([File_Series],[_THISROW].[Series_File_numb]), [File_No]=[_THISROW].[Series_Item_numb] ) ) or FILTER(“Test_Prog_Lst”, AND( IN([_THISROW].[Series_File_numb],[File_Series]), [File_No]=[_THISROW].[Series_Item_numb] ) )’. Parameter 2 of function IN is of the wrong type
So I guess it seems that I am having issues assigning the proper type to the 2 fields that I am searching on.
Did you just copy-pasted the whole suggestion ? 
It’s the first part OR the second part.
No, when I copy paste each one individually, and test them, I am getting the same exact error.
Did you have a look to the IN() documentation link I provided ?
You may want to have a look to the type of the parameters you have in your table, and see if it matches.
If you still don’t succeed, can you please provide:
screenshot of your table structure (columns, from the app editor)
screenshot of the expression you use and message error you are getting with
Ok those are the columns for the tables and one of the filter statements that you suggested.
I hope that it’s not too confusing.
Your initial expression was correct.
FILTER("Test_Prog_Lst",
AND(
[_THISROW].[Series_File_numb]=[File_Series],
[_THISROW].[Series_Item_numb]=[File_No]
)
)
However, the [Result] column should be a List of Ref, with source table: Test_Prog_Lst
