TWO QUESTIONS: (1) I wanted to know how to ...

(Samantha Lim) #1


(1) I wanted to know how to build a reference column that shows dependent drop downs (shows me choices based on previous answer–in my example its a store format, and category of product). Why reference format? Because I want to be able to build new choices as you continue using the app.

EXAMPLE: My enumlist is a set of products. If I want to add more products in the future, building a reference column leads me to a separate page that tells me what to properly fill in to register a new kind of product. At the same time it updates the total product masterlist/source.

The thing is when I try putting in any expressions in the app formula bar, verifying it shows me an error message that says it a list is not compatible with a reference formatted column. Is there a better approach to this?

(2) Also, I wanted to know how to reference a specific column header in my spreadsheet based on a previous answer.

EXAMPLE: My dereference shows that the store I selected is a “Large” store. I want to see only the product selection that is present in this store format.

My referenced table with this data shows that for each product name, there follows three columns after it with headers “Large, Medium, Small”. In these columns, if the product is present in a specific format of store–it is ticked as TRUE, otherwise it’s ticked as FALSE. How do I reference a specific column header based on previous answers?

(Tony Fader) #2

@Samantha_Lim (1) The error message is probably because you’re comparing the EnumList column’s value with some other value. An EnumList has a list of values (the ones that are selected). You may want to use the IN(…) function instead. That checks to see if a particular value is in the EnumList, like this: IN(“Some value”, [Your EnumList Column]) That will return true of “Some value” is in the list of selected values.

(2) I don’t completely understand, but it sounds like you could use an IFS formula to do it. If [Large] is True, then “Large”. Otherwise, if [Medium] is True, then “Medium”. etc.

(Samantha Lim) #3

Hi tony, am not sure how exactly to execute. Please see my screenshot with error message below:

(Tony Fader) #4

@Samantha_Lim SELECT expressions look like this SELECT(TableName[ColumnName], ). Inside the condition, [Some Column] will refer to a column in the table you’re selecting from. [_THISROW].[Some other column] will refer to a column in the row that the SELECT expression is running in.

(Samantha Lim) #5

@tony Thank you it worked!

(Samantha Lim) #6

When I try using “IN” it says that it’s the wrong function so I removed it from the category classification and still didn’t get any results. Am referencing with “THIS ROW” because I want the dereferenced store type to match whatever “IFS” stand true

(Samantha Lim) #7

This is the reference data I use

(Tony Fader) #8

@Samantha_Lim Your [_THISROW].(IFS(…)) will not work. You can’t have the column that you’re dereferencing be a formula. What you could do is create a virtual column with the output of your IFS formula. Then instead of [_THISROW].(IFS(…)) you could do:

IFS( [Your IFS Column] = Foo, [_THISROW].[Foo], [Your IFS Column] = Bar, [_THISROW].[Bar], …etc )


(Samantha Lim) #9

Hi upon trying to build expression in virtual column this is what I get

(Samantha Lim) #10

@tony please let me know how to approach

(Tony Fader) #11

@Samantha_Lim The syntax TableName[ColumnName] returns a list of values (the entire column), not a single value.

(Samantha Lim) #12

@tony Hi but when I initially tried it without putting in the table name in the syntax it showed me this:

(Samantha Lim) #13

@tony for reference, “Name” is a column found in my data sheet that gathers all responses. I wanted this to initially reference “SKUs” table hence the inclusion of “SKUs” in the initial syntax