LinkedIn
Twitter
Hi I am having a bit of trouble with a switch formula.
I have a previous Enum column called [CHANNEL].
The choices are: Key Accounts, SME, Others.
The next column is a text field with suggested values based on the selection from [CHANNEL].
What I want is, if [CHANNEL] is "Key Accounts", then return values from table "KA" as suggested values.
But if [CHANEL] is "SME", then return values from table "SME" as suggested values.
else, then return values from table "PCRegGen" as suggested values.
I used a SWITCH() function with the following expression.
SWITCH([CHANNEL],
"Key Accounts", SELECT(KA[MID], [DBA Name]=[_THISROW].[DBA Name], TRUE),
"SME", SELECT(SME[MID], [DBA Name]=[_THISROW].[DBA Name], TRUE),
SELECT(PCRegGen[MID], [DBA Name]=[_THISROW].[DBA Name], TRUE)
)
However, AppSheet is throwing me an error.
I also tried to use Filter instead but same result.
SWITCH([CHANNEL],
"Key Accounts", FILTER("KA", [DBA Name]=[_THISROW].[DBA Name]),
"SME", FILTER("SME", [DBA Name]=[_THISROW].[DBA Name]),
SELECT(PCRegGen[MID], [DBA Name]=[_THISROW].[DBA Name], TRUE)
)
Is there any workaround I can do?
If I may add my observations.
You seem to be using key values from each of the tables "KA" , "SME" and "PCRegGen" because you have used FILTER() expressions. May we know what these keys will in turn show to the user?
The testing shows that SWITCH() does not allow references to different tables as options. That is, when key values from the different tables are used in the resulting lists. The screenshot below has key value lists from three different tables and it fails.
You could instead try to use any other values such as labels from the tables "KA" , "SME" and "PCRegGen" as suggested values if the app permits that use.
The above expression when used with label values ( or non key values) from the first table table "Products" in the expression goes through. In the expression below, the [Product Name] is label. [Name] in Customers is key column.
The lists of key values from the same table are also allowed.
Essentially the SWITCH() and IFS() ( I also tested with IFS()) do not seem to allow switching references to different tables through the expressions. And that seems to be logical as one may not change references to tables through expressions or it could be an issue in ref or dereference columns. However, why it allows reference to be retained in the default value needs further testing.
Hope this helps.
I am sure other community colleagues will share their insights as well.
