Multi-search form using 2 drop down list

[MRiley]

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.

[Aurelien]

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:

https://help.appsheet.com/en/?q=dependent+drop+down

[MRiley]

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.

[Aurelien]

Which template are you talking about ? Can you provide the url link ?

Is this this one?

https://www.appsheet.com/templates/How-to-search-with-a-Slice--Dashboard?appGuidString=7e3e1bed-6576...

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 ?

[MRiley]

Sure no problem.

Here is the url for the template:

https://www.appsheet.com/Template/AppDef?appName=Multi-SearchDemo-orig-5059142&appId=Multi-SearchDem...

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

[Aurelien]

"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.

[MRiley]

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]

Hopefully, you'll get this one.
--
*Melvin Riley, DBA*
*IAD Data Management*
*National Archives*
*Washington D.C. *
*O: 301-837-0715*

[Aurelien]

try it from here:

https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Multi-search-form-using-2-drop-down-list/m-p/40...

[MRiley]

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*

[MRiley]

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.

[Aurelien]

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]
  )
)

[MRiley]

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])

[Aurelien]

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:

https://help.appsheet.com/en/articles/2357277-in

[MRiley]

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.

[MRiley]

No, when I copy paste each one individually,  and test them, I am getting the same exact error.

[Aurelien]

Did you just copy-pasted the whole suggestion ? 🤣

It's the first part OR the second part.

[Aurelien]

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

[MRiley]

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.

[Aurelien]

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

[MRiley]

Ok I moved the results field from the Series_Disp_List table to the Test_Pro_Lst  table. I created the Result field in the Test_Prog_Lst table as well using this code:

But it's also giving me an error 

[MRiley]

Sorry I misunderstood your directions. I kept the Results field in the Search2 data and used the Test_Prog_Lst table with the formula that you suggested. I am not getting an error anymore but the data isn't pulling in.

[MRiley]

Ok I had to change the File_Series and File_No from text to enumlist and now I'm getting an error

[MRiley]

Here is the latest error:

[MRiley]

Here is a bigger view of the error:

[Aurelien]

Change your column type: this is NOT a Ref, this is a List of Ref.

I said it 2 times yet, please make it.

[MRiley]

Thank you for the clarification.

I was unfamiliar with List of Ref until you sent the picture. I have changed it and now the formula is no longer complaining.

Now the search fields are complaining about the type when I make them List, Text or Ref.....What should the type be for them and in which table(s)>

[Aurelien]

screenshot please 🙂

[MRiley]

Sure sorry about that:

[Aurelien]

Hi @MRiley 

This will be my last contribution to this thread.

Please find below my suggestion according to your last screenshot.

Please note I probably made a confusion about your various table: I just noticed you are currently dealing with 3 tables (Search2, Test_Prog_List, Series_Disp_List), I didn't get that before, it's my mistake.

If you are expecting to have in the column "result" a list of elements from two different tables, that won't happen.

Basically, please have in mind that you need to keep things simple :

- table1 with items to be filtered

- table2 with criteria for filtering purpose ==> column "Result" that will show items from table1, with a FILTER() expression

Table Search2

1) Column "Series_File_Numb"

==> type Text

OR

==> Type Enum

==> Data validation/valid if ==> expression: Test_Prog_Lst[File_Series]

2) Column "Series_Item_Numb"

==> type Text

OR

==> Type Enum

==> Data validation/valid if ==> expression: Test_Prog_Lst[File_No]

3) Column "Result" looks fine.

Reminder of what it is supposed to be:

==> Type List

==>Base type Ref

==> datasource table: Test_Prog_List

Table Test_Prog_List

The columns that will be taken into account for filtering purpose should match the types of the filtering columns in your table Search2.

[MRiley]

Thank you for all of your continued help and you patience. 

I will work with your suggestions but please keep in mind that I am using the exact number of tables from the Mult-Search example used from the templates. In this example, they are using 3 tables. I thought I made that clear when we first began. If not, it's my mistake.  This example does use 3 tables however, it doesn't use drop down lists which I am. That is the only difference in my real world example and the template. If I have any further questions, I will start a new thread and hopefully find another tech that will help. I do again appreciate your time and attention to what you have helped me with.