Filter/select depends on a particular list of values

[Stefano_Martell]

Hi guys, I have a problem with creating a dropdown or a list filtered considering multi values.
My app is for quick search equipment can match with compatibles accessories.
I have 2 main dataset:
EQtab where I record all equipment
ACCtab where I record all accessories

All records in both tables has 3 column type YES/NO indicating the compatibility category:
[Cat1], [Cat2], [Cat3].

So I’m creating a table called SEEK to record all research for each user.
This tables has [IDcolumn], [UserID] type ref, [EQ_ID] type ref with Valid_if constrict dropdown and [ACC_ID] type ref .

The goal would be to obtain a compatibility list based on the selected equipment:
Ex.
“EQ_1” has [Cat1] and [Cat3] set on TRUE and [Cat2] set on FALSE
“AC_1” has [Cat1] set on TRUE and [Cat2], [Cat3] set on FALSE
“AC_2” has [Cat2] set on TRUE and [Cat1], [Cat3] set on FALSE
AC_3 has [Cat2], [Cat3] set on TRUE and [Cat1] set on FALSE

In this example when i search for EQ_1’s compatibles accessory the list of selection should includes only AC_1 and AC_3.
I tried several expression but with no right result…
Hope some one could help for fixing.
Thx

[Suvrutt_Gurjar]

Hi @Stefano_Martella,

Could you please mention where you would like the expression for the mentioned conditions.

Presuming you would like it in the valid_if of [ACC_ID] column in SEEK table, does an expression something like below help?

UNIQUE(IFS([EQ_ID].[Category 1]=TRUE, SELECT( ACCtab[ID], [Category 1]=TRUE))+IFS([EQ_ID].[Category 2]=TRUE, SELECT( ACCtab[ID], [Category 2]=TRUE))+IFS([EQ_ID].[Category 3]=TRUE, SELECT( ACCtab[ID], [Category 3]=TRUE)))

Edit: Here [ID] is the key column of table Acctab.

[Stefano_Martell]

Suvrutt you are the man!
I’m a noob and totally ignored UNIQUE to avoid duplicated values and the SELECT as second term in IFS so my expression was very complicated.
THANK YOU SO MUCH for teach me this!
Wish you all the best in such difficult moment.

[Suvrutt_Gurjar]

Hi @Stefano_Martella,

Thank you for the update and you are welcome. Good to know it works the way you want. Please test it for all possible conditions, as I did minimal testing.

Well, we all keep learning from each other in this great community.

All the very best.

[Stefano_Martell]

Dear Suvrutt, I’m sorry to bother you again but I’m trying in several different way to insert another condition in your suggested expression for filtering the list of value before to verify the Category compatibility… but without success.
This is the [color], every row in EQtab and in ACCtab has a type of color selected. I should check the Acc matching category values only in the list of Acc matching the same color of EQ chosen first in the form.
Thanks for your interest.

[Suvrutt_Gurjar]

Hi @Stefano_Martella,

Please try if the following expression helps

UNIQUE(IFS([EQ_ID].[Category 1]=TRUE, SELECT( ACCtab [ID], AND( [Category 1]=TRUE , [EQ_ID].[EQ_Color] =[ACC_Color])))+

IFS([EQ_ID].[Category 2]=TRUE, SELECT( ACCtab [ID], AND( [Category 2]=TRUE , [EQ_ID].[EQ_Color] =[ACC_Color])))+

IFS([EQ_ID].[Category 3]=TRUE, SELECT( ACCtab [ID], AND( [Category 3]=TRUE , [EQ_ID].[EQ_Color] =[ACC_Color]))))

Here [EQ_Color] is the [Color] column name in the EQtabe table and [ACC_Color] is the color column name in the ACCtab table.

[Stefano_Martell]

Thank you Suvrutt, this works at best… I’d like to understand why is not possible to insert the color matching condition as first in IFS like this: IFS( condition1, value1, condition2, value1)

IFS( [EQ_ID].[Category 1]=TRUE, SELECT( ACCtab [ID], [Category 1]=TRUE), [EQ_ID].[EQ_Color] =[ACC_Color], SELECT( ACCtab [ID], [Category 1]=TRUE))+…

[Suvrutt_Gurjar]

Hi @Stefano_Martella,

I believe we need to include the part [EQ_ID].[EQ_Color] =[ACC_Color] inside the SELECT() expression because there can be many records in the ACCtab table that match the color selected in the SEEK form for the EQtab table. If we take the part [EQ_ID].[EQ_Color] =[ACC_Color], outside the SELECT() statement, the expression will not be able to search multiple records in the Acctab table matching the color criteria.

Hope this helps.

[Stefano_Martell]

Hi Suvrutt, some new Equipment [EQ_ID] belong to new categories and it is the same for the respective Accessories [Acc_ID], so I’m happy to simplify the Valid_If expression in the SEEK dropdown to avoid long updating each time there is a new Category type to insert.

I’m thinking to delete the 3 [Category n] columns YES/NO format in both tables EQtab and ACCtab, replacing it with an easier Enumlist column base type Text named [Cat] with the follow list of value: Category 1, Category 2,…, Category n.

Then I’m supposing to use the INTERSECT function to select the [ACC_ID] matching the category of the [EQ_ID]. I guess should use also SPLIT for checking any single Category value inside the list ACCtab[Cat] and EQ[Cat].

Do you think is possible to proceed in that way? Can you help me with the syntax?

Thanks a lot!

[Suvrutt_Gurjar]

Hi @Stefano_Martella ,

In general using INTERSECT() sounds a good idea in making the written expressions less complex. However I am unsure if using the INTERSECT() is really a more efficient expression in terms of compute time. You may need to test it to come to conclusion.

My understanding is as follows

A) You may still need to use SELECT() function in Valid_if of [ACC_ID]. This is so because, I believe you still need to select multiple [ACC_ID] s across many rows in the ACCTab table matching the condition.

B) Your INTERSECT() function will become a conditional sub expression in that SELECT() expression. I believe you will have [EQ_Category] and [Acc_Cat] as two Enumlist type columns in respective tables to capture all applicable categories for that table. I am sure you are aware that INTERSECT() also returns a list, so you may need to further convert it to TRU/FALSE contition by wrapping COUNT() around it.

C) I believe you may not need SPLIT() function because you already have lists constructed in [EQ_Category] and [Acc_Cat] columns.

[Stefano_Martell]

Hi Suvrutt, I’m trying one step at time because I’m not confident with INTERSECT.
I prefer to work on general app avoiding changes on the deployed one.
For make this test and make easier the computing.
TAB.A dataset

and TAB.B dataset,

SEARCH dataset is the tab for search records.

First I tried with a Virtual Column in SEARCH like this
=INTERSECT(TAB.A[Match], TAB.B[Match])
error occur because INTERSECT doesn’t accept list of list

So I’m using two Virtual Column:
in TAB.A [TA MATCH]= [Match] text format
in TAB.B [TB MATCH]= [Match] text format

in SEARCH a VC for obtain a list of matching COUNT TB_ID MATCH TA_ID
= COUNT(INTERSECT(SPLIT(TAB.A[TA MATCH], “,”), SPLIT(TAB.B[TB MATCH], “,”)))
Without SPLIT I miss some matching with multi values (a, b, c) but in anyway seems is not COUNT the single values

Then in SEARCH[TB_ID] ref to TAB.B I’d like to obtain the list of TB_ID matching all TA_ID I’m trying a simple Valid If constrict:

=SELECT(TAB.B[TB_ID], INTERSECT(TAB.A[TA MATCH], TAB.B[TB MATCH]))
Doesn’t work. Any suggestions?

Here find the link of this general app: https://www.appsheet.com/start/2bc4222f-cd45-458a-8d6e-0324131d843d

Thanks for any help.

[Suvrutt_Gurjar]

Hi @Stefano_Martella,

Thank you for all the details. I was unable to access the general app, the link for which you have given. I got access denied message.

I am also sorry that I am unable to understand the expected end result (goal) to achieve based on this latest description you have given.

May I request you to mention

1. Does the end goal or expected result remain same? Could you please elaborate? Especially where the expressions you have mentioned are to be used?

2. In the test data I find that Tab.A and Tab.B are exactly identical in terms of [Match] column values and A,B, C column values. Any specific reason for that?

3. I am sorry that I did not understand the below statement

Stefano_Martella:

Then in SEARCH[TB_ID] ref to TAB.B I’d like to obtain the list of TB_ID matching all TA_ID I’m trying a simple Valid If constrict:

4. What is the type of column [Match] in Tab.A and Tab.B

5. I also did not understand objective of columns Color , TA_ID and TB_ID in the table SEARCH

My one request will also be to avoid table or variable names with certain special characters like (. or $ etc for example Tab.A) and words for tables or variable such as SEARCH etc. as these could be reserved words. Of course AppSheet is in general very good at even resolving and accepting such words for variables but I suggest better to avoid.

[Stefano_Martell]

Hi Suvrutt,

Suvrutt_Gurjar:

Does the end goal or expected result remain same? Could you please elaborate? Especially where the expressions you have mentioned are to be used?

I’m trying to workout an expression “Valid If” able to find the records in TabB matching any values in column [Match] with the [TA_ID] selected first.

Suvrutt_Gurjar:

In the test data I find that Tab.A and Tab.B are exactly identical in terms of [Match] column values and A,B, C column values. Any specific reason for that?

No that was because I clone the second TAB from the first.

Suvrutt_Gurjar:

What is the type of column [Match] in Tab.A and Tab.B

EnumList (X,Y,Z) text format

I’ve changed all name avoiding special characters and variable “ambiguous” names.

[Suvrutt_Gurjar]

Hi @Stefano_Martella,
Thank you. Could you please repost photos of test data and tables so that we could look into expressions with new table names?

[Suvrutt_Gurjar]

Hi @Stefano_Martella

If I have understood your requirement , as per the new table set up please try an expression like something below in the Valid_if of [TB_ID] column in SEARCH table

SELECT(TabB[TB_ID], COUNT(INTERSECT ([TA_ID].[TA_Match], [TB_Match]))>0)

Here [TA_Match], [TB_Match] are Enumlist columns in TabA and TabB respectively.

[Stefano_Martell]

[Suvrutt_Gurjar]

@Stefano_Martella,

Thank you. Does the above suggested expression work?

[Stefano_Martell]

[Suvrutt_Gurjar]

Please try

SELECT(TabB[TB_ID], COUNT(INTERSECT ([_THISROW].[TA_Match], [Match]))>0)

OR

SELECT(TabB[TB_ID], COUNT(INTERSECT ([TA_Match], [Match]))>0)

[Stefano_Martell]

Hi tried both but this is the error: INTERSECT takes two arguments of type List.

So I try with 2 Virtual Column in text format and the expression is:
SELECT(TabB[TB_ID], COUNT(INTERSECT (TabA[V_MatchA], TabB[V_MatchB]))>0)

This syntax works but the result is all values [TA_ID]

[Suvrutt_Gurjar · Accepted solution]

Please make [TA_MAtch] in SeekRes table as Enumlist type column, with expression as
[TA_ID].[Match]

Please retest with the expression I shared earlier.

SELECT(TabB[TB_ID], COUNT(INTERSECT ([_THISROW].[TA_Match], [Match]))>0)

OR

SELECT(TabB[TB_ID], COUNT(INTERSECT ([TA_Match], [Match]))>0)

Edit: the above expressions did not work earlier, because you had [TA_Match] column as text type. I presumed like other 2 tables, you must have kept it Enumlist. When you posted picture of expression , I realized you had kept it text type and hence suggested to change it to Enumlist.

Also expression given by you, even though syntactically correct, I believe it will not work because you are first creating list of [Match] values across all rows and then comparing it.

The correct need is to compare at each row level.

[Stefano_Martell]

Oh YEAH, both are good! Great solution you have worked out Suvrutt!
I would never have gotten there indeed. Whatta noob I am!
Is this usual to use Enumlist types instead a virtual column?
Can I ignore the warning message for missing values?

Do you think I can add a condition AND() in the SELECT expression for matching the colors as well?
Considering I will have 100 records in both tables the risk is to have very high compute time?

THANKS!

[Suvrutt_Gurjar]

Stefano_Martella:

Is this usual to use Enumlist types instead a virtual column?

No, it is not necessary to have it asEnumlist. In fact whether it needs to be Enumlist or VC based list will depend on the business logic of your app.

Stefano_Martella:

Can I ignore the warning message for missing values?

Could you please elaborate what warning you are getting?

Stefano_Martella:

Do you think I can add a condition AND() in the SELECT expression for matching the colors as well?

yes , certainly you can.

Stefano_Martella:

Considering I will have 100 records in both tables the risk is to have very high compute time?

I cannot say for sure but in general , I believe the performance issues can pose problemes when you have multi row select expressions, especially in VCs and when you have multi thousand rows. However this will totally depend on complexity of ( For example SELECT() or FILTER()) expression conditions.

[Stefano_Martell]

Here the final result of these elaborations. Please let me know if you can open and if you see something to improve or add.
Seek Exp 1

As I understand as share the editable version I’ll do…