I am trying use “Ref” for input.
I would like to control the data with “Valid if”.
My app shows an error as below.
Here is my data.
-table “staff”
Name | Place | Time |
James | True | True |
Emily | True | False |
David | False | True |
I would like to “ref” both of Place and Time are True.
Here is my formula,
AND(
FILTER(“staff”, [Place]=TRUE),
FILTER(“staff”, [Time]=TRUE)
)
This is error massage.
Condition AND(SELECT(staff[IDstaff],([Place] = “TRUE”)), SELECT(staff[IDstaff],([Time] = “TRUE”))) has an invalid structure: subexpressions must be Yes/No conditions
By "control the data with “Valid if” " I assume you mean to set the list of possible choices.
AND() is a boolean operator that returns Yes or No. It doesn’t join things together. However, you can join two lists together using the “+” operator. Change your expression to this:
FILTER("staff", [Place]=TRUE)
+
FILTER("staff", [Time]=TRUE)
2 Likes
Thank you for your reply.
I tried to use “+” to combine 2 factors.
Unfortunately it seems doesn’t work well.
Name | Place | Time |
James | True | True |
Emily | True | False |
David | False | True |
The name I want to pick up is only “James”.
Using “+”, Emily and David also appears to be picked up.
You probably would be best off using a slice on the “staff” table with the expression AND([Place]=TRUE,[Time]=TRUE) and then use the slice as your ref table.
You could also use a SELECT expression in suggested values to only show Names in which time and place are true:
SELECT(staff[name],AND([Place]=TRUE,[Time]=TRUE))
1 Like
Why didn’t it work? Please show us.
Thank you for your advice!
I can make it with SELECT expression.
For my understanding, I would like to pick up the “selected” value when “Ref” use.
I still watch all data on the “plain” table view (without slice).
In my case, “slice” can be used?
I set the “+” in the “Valid if”,
then all data can be picked up on “Ref” column.
Sorry I don’t quite understand “For my understanding, I would like to pick up the “selected” value when “Ref” use.”
As for the slice, if you need to display all values, then the SELECT() option seems better
1 Like