In Appsheet exampes is SELECT(Students[First Name], ([Class of] = “2020”)). (must sow list of students with class 2020.)
I made new Slice from table “clients” and write: SELECT(clients[name], ([sex] = “m”))
result: “The expression is valid but its result type ‘List’ is not one of the expected types: Yes/No”
What is wrong? This is wil be the list of clients, but not yes-no result..?
In slices, it is an expression used as a Filter Criteria. The expression is meant as a Test that is applied against each row to determine by a Yes/No result if that row should be included.
So in your example, if you are trying to create a Slice that filters the Clients table for where the Sex value is “m” then all you have to write is:
[sex] = "m"
By the way, all expression assistants will give an indication of what results the expression should return. Below is an example from a Slice. Others might say (List) or even (List, Yes/No) - meaning one or the other and determine the context of how it is to be used.
But if I want to add one more criteria to Slice from linked tables “orders” (example: Clients = “m”, and Orders (another table) = “flowers”) can I use SELECT, and how?
If Orders are related to the table Clients, you can use the virtual column [Related Orders] in this case.
Try this:
AND([Sex]=“m”,ISBLANK([Related Orders])) : If you only want men’s without order related or
AND([Sex]=“m”,ISNOTBLANK([Related Orders])): If you only want men with at least one order related
I want to select Clients who ordered only “flowers”. Table yes, is related, but I want to use SELECT. Is it possible?
Yes! It is.
Try:
AND([Sex]=“m”,[Related Orders].[Product]=‘Flowers’)
If the name ‘‘Flowers’’ are not the Key of the table where this is located, you can try:
AND([Sex]=“m”,[Related Orders].[Product].[Name]=‘Flowers’)
or put the Key value of Flowers in the first expression instead of “Flowers”
aa… Thanks, but, where here is “SELECT” .. (
Oh sorry, in that case you can’t use Select because the Slice formulas needs to be a Yes/No expression. And Select always result in a List.
Like Willow say before
But this example is in a AppSheet - to use Select in Slices. So, where it
is possible to use SELECT at all?
First, you cannot dereference (i.e. use the “dot” notation) on a list. So you won’t be able to do_[Related Orders].[Product].[Name]=‘Flowers’_
You can use SELECT in the Slice Filter Criteria, as long as the result of its usage is Yes/No.
In your example, you wanted to select Clients who have ordered “Flowers”. Your Slice might be something like:
AND( <<client select criteria>>,
IN("Flowers", <<SELECT expression for list of Product names ordered by client>>)
)
Note: the SELECT can be replaced by other list functions if they provide the list of values you needed. For example, returning the “related” lists.
If the Order happened to have the [Product Name] as a column AND you have the table relationships setup so that you do get a [Related Orders] column on your Client table, you can use that for a simplified version of the SELECT like so:
AND( <<client select criteria>>,
IN("Flowers", [Related Orders][Product Name])
)
[Related Orders][Product Name] is a shortcut method to returning a list of Product Names on Orders by the Client.
Hey! But if I want to see a list of all the clients I have and let only those who ordered flowers have an “F” in the new column. How to organize it?
I take a Clients table and an Orders table. They have a connection with each other. Then I do WHAT :)? Am I making a new virtual column in Clients where I am writing a query formula to the Orders table? Or I make a slice from the Orders table .. but some customers may not be there at all .. I’m completely confused … (
Sorry, I wasn’t explicit since this post is about the expression for Slice Filter. I just assumed it was understood that the psuedo-expressions I provided would go into the Slice Filter Criteria expression area.
If you can describe you data structure in more detail (providing images of your tables is best), we can give a more precise expression to use.