Multi-selection to filter a different table

Is there a way to use the ‘multi-select’ feature to select multiple ‘terms’ to use as a filter on a second table? I have a Glossary of terms’. I would like to select terms and then filter a Product table that has all the terms selected, not just any term (as happens when using an IN statement).

Glossary to Products is a many -to- many relationship.

I have an action that attempts to capture the glossary terms selected, by copying them to a Search table. Although this is generating an error.

Any ideas greatly appreciated.

This would be the correct approach.

What error?

Your expression probably isn’t going to get you what you want. Even with multi-select, the action will be applied once for each selected row. Your expression will set the GlosseryTerms column value to the GlosseryUUID column value of each selected row. Each row will overwrite the previous one, leaving you with only the last value.

I suspect the error you’re getting is something about incorrect types or something must be a list. The error is probably because GlosseryTerms is an EnumList but GlosseryUUID is a singular value. To fix the error and get you the list you want, change this:

[GlossaryUUID]

to this:

[GlosseryTerms] + LIST([GlossaryUUID])
2 Likes

Many thanks Steve,

I did think that would be an issue, it not being a list, but I wasn’t sure how the ‘multi-select’ function passes back the selected values. I did try what you suggested but it generates the same error, mentioned earlier. See screen-shot below.

Screenshot 2020-04-08 at 15.15.40

In the Search table, which is where I am trying to capture the selected values, [GlossaryTerms] is defined to be EnumList. I have also tried defining it as Enum and Text, both generate the same error on triggering the action.

The Results of using the selected values is given by the formula:

FILTER(“ProductFeature”,
OR( ISBLANK([_THISROW].[GlossaryTerms]) , IN([GlossaryUUID], [_THISROW].[GlossaryTerms]) )
)

Make sure the EnumList column allows other values:

Does the column have a Valid If expression?

Hi Steve,

That worked. I was trying to sort the list of glossary terms in the ‘valid if formula’, which was causing the error message. Replaced this with OrderBy and it now works. All this does is store the selected terms in a search table.

The idea is then use these UUIDs to filter a Product table that has the same terms (features). Not sure how best to accomplish this in AppSheet as it means comparing a list with the one held in the Search table, representing the the user’s selection. The Search table has a virtual column called Results

For the time being I have hard-coded the selected glossary terms IDs… Normally, this would be a straight-forward SQL query using a subquery to pull in the selected terms.

ProductFeatures is an intermediary table representing the many-to-many relationship Product m:n Glossary Terms (features). Again not sure if this is the most efficient way to handle many-to-many relationships in AppSheet, as I have seen two versions.

Version 1
Screenshot 2020-04-15 at 16.52.04

Version 2
Screenshot 2020-04-15 at 16.53.53

Many thanks once again.

1 Like