Return Column Values that Match ALL ENUMLIST Selec...

Josh_B · 03-17-2023 07:28 AM

Hi,

I'm stumped on how to do this and would appreciate any support.

I have a lookup table that links three types of ID values, like this;

App users will provide a selection that will determine the TD_ID value. This is from an ENUM field, so there is only ever one option.

The DE_ID is also provided by app users, but this is from an ENUMLIST field, and can have many values. I have created a virtual column [DE_ID_Virtual] with the type LIST in order to capture all the selected values in a useable manner.

These values determine the applicable PROG_ID options available. If a user selected TD_ID 1269 and DE_ID 2835, then applicable PROG_IDs are 1,2,3,4,5 and 6. Where I am struggling is in managing multiple DE_ID values.

I attempted this;
SELECT(TD_DE_PROG[PROG_ID],AND([TD_ID]=[_THISROW].[TD_ID],IN([DE_ID],[_THISROW].[DE_ID_Virtual])))

This returns all PROG_ID values that match the TD_ID and ANY of the DE_ID values.

I need to only return values that match the TD_ID and ALL of the DE_ID values.

If the user selected TD_ID 1269 and all three of the displayed DE_IDs, the highlighted values below should not be returned, as they are not applicable to ALL DE_IDs.

I hope that makes sense? I've considered creating some additional virtual fields to capture number of DE_IDs selected and maybe use that in some way... but not sure how to progress.

Thanks

Marc_Dillon

The DE_ID is also provided by app users, but this is from an ENUMLIST field, and can have many values. I have created a virtual column [DE_ID_Virtual] with the type LIST in order to capture all the selected values in a useable manner.

I don't understand why you created the VC. An EnumList column is also a List, you can use it with an IN() in the same way.

SELECT(TD_DE_PROG[PROG_ID],AND([TD_ID]=[_THISROW].[TD_ID],IN([DE_ID],[_THISROW].[DE_ID_Virtual])))

That looks correct to me, besides my previous comment. Are you sure it's not returning the appropriate values?

Josh_B

You're right about the virtual list. I'm using a virtual column for other reasons that aren't relevant to this specific issue.

The problem I have is that with;
[TD_ID] = 1269
[DE_ID] = 2835, 2841, 3154

The SELECT formula returns;
1,2,3,4,5,6,7,9

I'm trying to understand a way to return;
1,2,3,4,5,6

Which are the values applicable to ALL selected DE_ID values. The IN function seems to not be the right way to do this, but I'm not sure what is more suitable.

Marc_Dillon

Like I said, your expression looks right. Are you sure it's returning inappropriate values?

You haven't mentioned where this expression is returning to. Is it perhaps a suggested value expression in another enum column, which also has the "auto-complete other values" option turned on? That would explain why you're seeing extra values beyond what the SELECT is returning.

Josh_B

Hi again,

I've tried to build a mini copy of my specific scenario to illustrate it better. Hopefully sharing this works...

Here is a dummy subset of data that I am using. You can see that IDs link to more understandable values. IDs need to be used as there is a risk the associated text values could be changed.

And here is a mock-up of this part of the AppSheet

If you add a new item, you only have one TD option to choose, for simplicity.
This returns the TD_ID and sets the valid options for DE to be the applicable values.
Selecting DE values returns the DE_IDs for them - a virtual column with the type LIST and a text field to record the concatenation of this (because lists need to be in virtual columns?)
This should then return the appropriate PROG options, but it is not working as intended.

If you select Red and Blue, you are given 7 options. The 7th option - 'Whistles' - is valid for Blue, but not for Red, so I don't want it to appear. Similarly, if I then also select Yellow, 'Blocks' because an available option for PROG, even though it is not applicable for Red or Blue.

I hope that better illustrates my issue. I really appreciate the support.

Marc_Dillon

@Josh_B wrote:

If you select Red and Blue, you are given 7 options. The 7th option - 'Whistles' - is valid for Blue, but not for Red, so I don't want it to appear. Similarly, if I then also select Yellow, 'Blocks' because an available option for PROG, even though it is not applicable for Red or Blue.

Ahhh, I had misunderstood what you meant by "match all". Hmmm... I'm not really sure how to do that, I'll have to come back when I have more time. My first thought is that your data structure may not be able to produce such a result.

Josh_B

No problem. I'm going to keep trying to crack it.

Maybe there's some potential to use INTERSECT to help, but that still has challenges - I would basically need to create a bunch of lists (one for each DE_ID selected), but that could be any number of selected items.

Marc_Dillon

Yah, pretty much. This ain't pretty, but it should work. You'll just need to expand it to the reasonable max number of items that will be selected.

SWITCH( count([b]) ,

1 , SELECT( ref[c] , AND( [a]=[_THISROW].[a] , [b] = INDEX([_THISROW].[b],1) ) ) ,

2 , 
  INTERSECT(
    SELECT( ref[c] , AND( [a]=[_THISROW].[a] , [b] = INDEX([_THISROW].[b],1) ) )  ,
    SELECT( ref[c] , AND( [a]=[_THISROW].[a] , [b] = INDEX([_THISROW].[b],2) ) )
  ) ,

3 ,
  INTERSECT( INTERSECT(
    SELECT( ref[c] , AND( [a]=[_THISROW].[a] , [b] = INDEX([_THISROW].[b],1) ) )  ,
    SELECT( ref[c] , AND( [a]=[_THISROW].[a] , [b] = INDEX([_THISROW].[b],2) ) ) ) ,
    SELECT( ref[c] , AND( [a]=[_THISROW].[a] , [b] = INDEX([_THISROW].[b],3) ) ) 
  ) ,

list("")
)

Return Column Values that Match ALL ENUMLIST Selections