Narrow selection in an EnumList/Ref list

[SJKonthego]

My goal is to narrow down the options from an enum list based on the responses to a selection variable. So when I select "Air Conditioning" I want the only Action items to be related to Air Conditioning. I then want to be able to select one or many of the actions listed (this part is working fine).

The ACTION field is an ENUM list in the table PROBLEM with a REF base type looking up to table ACTIONS. Unfiltered, it looks like this first screenshot.

The ACTIONS table has columns for multiple systems (AC, Furnace, Refrigeration) and each of those columns contains either a 0 if it doesn't apply to that system or an appropriate letter (A for AC, F for Furnace, etc). Those columns are then concatenated into a single column ACTIONSYSTEM. If ACTIONSYSTEM contains an A you know that action applies to ACs if it contains an F you know it applies to Furnaces, etc.

My current (ineffective) approach is to put the following into the data validity section of the ACTION field (only working with ACs and Furnace in this expression to keep it simple):

IFS([System]="Air Conditioning", CONTAINS(ACTIONS[ACTIONSYSTEM],"A"),[System]="Furnace", CONTAINS(ACTIONS[ACTIONSYSTEM],"F"))

Here's what I get - see below - (1) the ACTION field has morphed into a different format even though it is still set to STACK; and (2) it appears to be filtering in some way, but it is returning options that don't make any sense (the three below do match ACs, but so do 3 others that aren't shown). What is odder is that I get the same three ACTION options if I select Furnace, and none of these apply to furnaces. 

My questions:

1. Is there a more elegant way to approach this entire problem?

2. If not, if this is a reasonable approach, can you assist with expression building? I've been reading, but clearly I'm not getting there.

3. Finally, any suggestions on how to get this back to a stack?

My apologies for how complicated this got. Thanks for guidance.

[Marc_Dillon]

Replace this:

with a single column of comma-separated values (use the standard space-comma-space delimiter). Those values being the exact values that are available to select from the [System] column. Set it as an EnumList in Appsheet. Then your valid_if for the [Action] column can simply be:

FILTER( Actions_table , IN( [_THISROW].[System] , [new_enumlist-column] ) )

In general, this is called "dependent dropdown", just to give you a term to search if you need more info.

https://help.appsheet.com/en/articles/961554-dependent-dropdown

[SJKonthego]

@Marc_Dillon Thank you! But I want to be sure I am understanding. I have simplified the tables - there are actually 23 systems and hundreds of actions. For each action, I think you are suggesting that I am going to have an entry in a new column in the ACTIONS table that can contain up to 23 separate comma-delimited long-form items (rather than my solution which would have 23 single characters). I'm old-school - back in the day that would seem inelegant because the column content would be ridiculously long when only 23 characters are needed.  But, it isn't going to be seen and I'm all-in if this is best practice for appsheet. I just want to be sure I am understanding before I go refining my underlying table. For instance, the column would say "Air Conditioning , Furnace , Hot Water Heater , Refrigerator" for an action that applies to those four system but not to, for instance, Awnings (another System). Am I on track?

Note: It isn't that much work to generate the column, I just need to be sure I am understanding.

[SJKonthego]

OK @Marc_Dillon - I gave it a try to the best of my (limited) ability. My ACTIONS table now looks like:

My valid_if statement looks like:

FILTER( "ACTIONS" , IN( [_THISROW].[System] , ACTIONS[ACTIONSYSTEM] ) )

When I select "Air Conditioning" as the system, I get ALL actions returned, including "Conducted Timed Pressure Drop Test" which isn't attached to that system. When I select any other system (such as "Furnace") I get no actions at all. 

On the bright side, the stack formatting is back! Yay!

Sorry. I am sure this is user error. Is there anything else that I can provide to troubleshoot? 

[Marc_Dillon]

Yes, that's exactly how I meant. To clean it up, you can go a step further, create a new table to house your System values, with the current values as the label, and a shorter value as the key. Then you only have to input comma-separated key values into the new [actionsystem] column. Make sure to change your [System] column to be Ref to this new table as well.

FILTER( "ACTIONS" , IN( [_THISROW].[System] , ACTIONS[ACTIONSYSTEM] ) )

---

Same issue as in your earlier thread, and for the same reason.

[SJKonthego]

@Marc_Dillon before I clean it, I'm going to try to get it working in this version, but I see where you're going on the table end. Thanks.

One place where I'm still confused is that the filter expression, as currently written, parses and runs without error (just doesn't do what I want).

It says: "The list of values of column 'AID' ....from rows of table 'ACTIONS' ....where this condition is true: ((The value of 'System' from the row referenced by 'PID') is one of the values in the list (ACTIONS[ACTIONSYSTEM]))" which is, I think what I want. What I'm getting is the entire list, and only when the first system value (Air Conditioning) is selected - any other selection returns no actions.

Based on my earlier issue, and reading your article, I had tried:

FILTER( ACTIONS , IN( [_THISROW].[System] , [ACTIONSYSTEM] ))

But that doesn't parse and immediately errors, noting "Parameter 2 of function IN is of the wrong type"

I thought that your point was that once you had established the table (ACTIONS) you shouldn't repeat the table reference (ACTIONS[ACTIONSYSTEM]) in order to iteratively work through the table.

So, still not getting it. Still appreciative of the guidance. Still learning. A lot. Thanks.

[Marc_Dillon]

FILTER( ACTIONS , IN( [_THISROW].[System] , [ACTIONSYSTEM] ))

But that doesn't parse and immediately errors, noting "Parameter 2 of function IN is of the wrong type"

---

---

@Marc_Dillon wrote:

a single column of comma-separated values (use the standard space-comma-space delimiter). Those values being the exact values that are available to select from the [System] column. Set it as an EnumList in Appsheet.

---

[SJKonthego]

@Marc_Dillon   I swear I'm trying.

In the PROBLEM table I set the PACTION variable that looks up to the ACTIONS table as an enumlist/ref type (I had thought this was the correct place) and then put the valid if statement there. This is where I had already set it and generated the errors referenced above (see pic):

Alternatively, in re-reading your explanation, I tried setting the value of ACTIONSYSTEM in the ACTIONS table to a self-referencing enum list, but that doesn't seem to work at all.

Yes. I feel like an idiot at this point.

😞

[Marc_Dillon]

problem[paction] should be Ref, NOT EnumList. actions[actionsystem] should be EnumList.

[SJKonthego]

Yes! We're almost there @Marc_Dillon - thank you so so much.

What is working: Relationship between fields, and now filtering so I return only those actions related to the appropriate system.

What is not working: Now, I've again lost the stack and I'm back to a choose-one drop down list.

If I change it to "Stack" in the screenshot below, it changes back to Dropdown when saved. 

The more logical place to get a multiple-choice stack seems to be at PAction. But I only have buttons and dropdown as options, the stack/multiple choice isn't even there.

[Marc_Dillon · Accepted solution]

[actionsystem] should be a plain EnumList, base type text, NOT a ref to action table. Also, the input mode selection here doesn't do anything for you in the job/problem form.

To get the "stack" mode option back on the problem[action] column, change it to Enum w/ base-type Ref.

[SJKonthego]

Homerun! "To get the "stack" mode option back on the problem[action] column, change it to Enum w/ base-type Ref." - I had to use "EnumList" to get the multiple choice, but it works! @Marc_Dillon you are a hero and, working backwards, I can see why this works I believe.

And, perhaps more importantly, I definitely have a better grasp on both how AppSheet handles table relationships and on the expression syntax. Haven't mastered it, but ready to trouble-shoot more adeptly. You rock.

[SJKonthego]

Damn. @Marc_Dillon wish I could give you the 4 or 5 "Accept as Solution" for the multiple intertwined questions. You'd be on top!