Select expression enumlist IN()

Seth_Berman · 06-19-2020 10:05 AM

I have a ‘report’ detail view that allows the user to enter a date range and choose clients from an enumlist dropdown. It then uses VCs to return some statistics using these criteria as filters. To keep this kind of efficient, we were going to use a single column to query the data table, and then use that list to calculate the VCs.

Data Select Column:

SELECT(
Data-Table[Key],
AND(
([Date] >= [_THISROW].[Begin Date]),
([Date] <= [_THISROW].[End Date]),
IN([_THISROW].[Client],[Client])
)
)

The expression builder reports this as valid, but it doesn’t work. I have tried more complex variations using LIST(), SPLIT() with IN() and sometimes i get all the data in the data table, other times i get nothing. Sometimes it seems to work but then the selection is marked as invalid and the numbers change in the detail view on the VCs. I assume it is because IN() doesn’t like comparing 2 lists. I have tried this method before with single values and IN() works fine. I have also tried INTERSECT(), but it always throws a type error and i’m not sure i can use it in a SELECT. the lists are type text.

So, any guidance on how to search an enumlist against another list in order to return matching rows?

The data table contains rows of deliveries, the report is to show statistics on volume using different metrics and allows the user to choose different groups of or all clients.

Austin

Wouldn’t the deliveries only have a single client? So you would want to search the data table’s [client] column vs the enum list column that is in your detail view.

Seth_Berman

Yes, the enumlist being created in the report view is comprised of users selecting 1 or more clients from the delivery table. I am then attempting to use SELECT to pull a list of those rows with deliveries within the date range of just those clients.

Austin

Try:
SELECT(
Data-Table[Key],
AND(
([Date] >= [_THISROW].[Begin Date]),
([Date] <= [_THISROW].[End Date]),
IN([Client],[_THISROW].[Client])
)
)
Your current formula is checking that all of the selected clients are in the [client] column of your delivery table. Is the [client] column also a list?

Seth_Berman

i have also tried it with the IN() expression that way and have the same problem.

Deliveries Table - Data Table:
[Client] is an Enum of type text, it uses a Valid IF pointing to the Clients table in order to create a dropdown in the form that populates this table.

Reports Table:
[Client] is an Enumlist of type text, it uses a Valid If that points to the Deliveries table in order to create a dropdown in this detail view.

Austin

Why not have the Enumlist in the reports table point to the Clients table as well?

Seth_Berman

In an attempt to keep the dropdown limited to those clients with deliveries, there are 500+ clients in the clients table, which makes for a large dropdown.

is there a particular benefit to having both [client] columns point to the client table with their valid if statements?

Seth_Berman

when i remove the IN() statement altogether and just select records based on the date range it works 100% of the time. since the dates are obviously individual values and the clients are multiple values, this is what led me to believe it was an issue of using an enumlist in the IN() expression and wonder about alternatives.

Austin

You may be running into an issue where your dropdowns are not the exact same data. Using Ref column types is the preferable method when you’re referencing values from other tables. I would recommend that you make the [client] column a Ref pointing to Client table and then make your enumlist a list of references to the client table. Then you can limit the enumlist in it’s valid if with a SELECT from clients table where the related delivery is not blank.
Enumlist work in IN statements to the best of my knowledge.

Seth_Berman

interesting. i will give this approach a try and report back. thank you.

Seth_Berman

so in the deliveries table i changed the [client] column to type ref and pointed it at the clients table (instead of just relying on the valid if as before)

in the reports table i change the [client] column to type Enumlist with base type of Ref and pointed it at the clients table.

i updated the IN() expression:
SELECT(
Data-Table[Key],
AND(
([Date] >= [_THISROW].[Begin Date]),
([Date] < [_THISROW].[End Date]),
IN([Client],[_THISROW].[Client])
)
)

it is not throwing any errors BUT it is returning all the rows in the deliveries table that are between the date range no matter which client is selected in the Enumlist. so the date range portion of the select is working properly but the IN() expression is grabbing all clients.

Austin

@Steve assist?
He probably knows immediately what’s the issue is so hopefully if he is free he can help.

Steve

Nope!

@Seth_Berman, please post a screenshot of the entire expression.

Please also post screenshots of the configuration screens of both Client columns.

Finally, please post a screenshot of the column list of the Data-Table table that includes ta least the key column.

Austin

To me that should work showing every client also sounds like a returning null in the IN ?

Steve

I’d think it means [Client] is blank (for every row!) and [_THISROW].[Client] is not empty and includes at least one blank entry.

Seth_Berman

Select Expression:

Table Layout:

Data Table Structure:

Data Table Client Column:

Report Table Client Column:

Client Table Client Column:

Steve

Column type should be Text:

Valid If should be Client Profile[Key]:

Same here:

Ref values must always be equal to a key column value of the referenced table:

I’m going to guess you have a lot of yellow triangles in your app where clients are referenced.

Seth_Berman

thank you for the explanation.

i made those suggested changes and the report view is now returning no data. the client column in the report table is returning a list of keys being selected in the enumlist. i tried changing the label on the client table to client but that did not help.

Steve

Screenshot(s), please.

Seth_Berman

thanks again for all the help.

data table columns:

data table client column:

client table columns:

report table client column:

spreadsheet of report table after entering some data:

that same test report in detail view:

Steve

In your SELECT() expression, replace this:

IN([Client],[_THISROW].[Client])

with this:

IN([Key],[_THISROW].[Client])

Seth_Berman

im afraid that didnt help.

Steve

Yep, my bad. It was right the way it was.

Steve

I dunno. It looks right to me. I’m afraid I’m going to have to refer you to support@appsheet.com. Somone will probably have to look at your app configuration.

Seth_Berman

no worries. thanks for your time this afternoon.

Seth_Berman

so it dawned on me that the client dropdown menu is an enumlist comprised of the [Key] values of the “Client Profile” table and the [Select Specimens] column was checking to see if those keys were in the [Client] column from the data table, which contain the actual client names. that’s why it was failing. i tested this by temporarily changing the key column in the “Client Profile” table to [Client] and the expressions in all the virtual columns suddenly started working.

so here is where i am getting stumped. i put everything back the way we left it the other day and then experimented with LOOKUP() in my [Select Specimens] expression and it just wouldn’t return anything, due to text/list incompatibility, so if i am pulling in an enumlist of keys, how i can get the corresponding [Client] column for the [Select Specimens] expression.

Steve

If [Select Specimens] is an EnumList of Ref to the Client Profile table, you can get a list of the [Client] values from the rows identified by [Select Specimens] with:

[Select Specimens][Client]

Is that what you’re looking for?

Seth_Berman

not exactly, im not quite getting that far. Specimen Report[Client] gets populated by the user via dropdown and is enumlist / ref with a valid_if to the key of Client Profile[Key]. Client Profile has a column, [Client] that is the label for the table so the user can select from the client names and not the key numbers.

The Specimen Report table uses Specimen Report[Client] in a select expression [Select Specimens] to create a list that a bunch of VCs then use to report on some statistics.
currently its:

FILTER(“Specimen Count”,
AND
(
[Date] >= [_THISROW].[Begin Date],
[Date] < [_THISROW].[End Date],
IN([Client],[_THISROW].[Client])
)
)

The problem i am having is IN() expression in the query. It is trying to see if the client names from Client Profile[Client] are in the list Specimen Report[Client], but that list contains Client Profile[Key] so the expression fails. I have tried using lookup() on the Keys to get the client names, but it fails with list/text type mismatch, even when i SPLIT().

Steve

Your description doesn’t match your expression (reformatted here for my clarity):

FILTER(
  "Specimen Count",
  AND(
    ([Date] >= [_THISROW].[Begin Date]),
    ([Date] < [_THISROW].[End Date]),
    IN([Client], [_THISROW].[Client])
  )
)

In your expression, [_THISROW] refers to the row in the Specimen Report table for which the FILTER() expression is being evaluated, as you’ve said. But the values from that row are being compared to rows of the Specimen Count table, not the Client Profile table.

Seth_Berman

sorry, i was unclear.

in this context
IN([Client], [_THISROW].[Client])
[Client] are the rows in the Filter expression from the Specimen Count data table.
[_THISROW].[Client] are the rows from the local [Client] in Specimen Report table, this column is populated by the enumlist dropdown which refs to Client Profile table.

basically, the user would be selecting which clients to from the dropdown, to view statistics on orders that are listed in the Specimen Count data table.

GreenFlux

Try removing the apostrophe (directly from the spreadsheet) and sync the app.

If the references start working after that, it’s probably the Google Sheet’s auto-formatting. Change it to ‘Plain Text’.

Lucinda_Mason

I’ve had multiple places where google’s auto formatting messed up things in AppSheet. My SOP is to specifically mark each column, in Sheets, as Plain Text if it isn’t used as a number for calculations, etc. This clears up a lot of strange data issues.

Seth_Berman

that wasn’t the issue here, but i’ve made note to start doing that to all sheet columns rather than leave it on automatic. thanks for the suggestion.

Bahbus

Not sure if it can cause an issue:

You have no item separator here. Try put " , " minus the quotations.

If it still doesn’t work, then after that try:
FILTER(
“Specimen Count”,
AND(
([Date] >= [_THISROW].[Begin Date]),
([Date] < [_THISROW].[End Date]),
IN([Client], SPLIT([_THISROW].[Client], " , "))
)
)

Seth_Berman

i think i discovered the root of my problem, which was, of course, stupid, so i thought i would post it to save anyone else the headache who comes across this.

question first: can you setup forms in such a way as to enforce data integrity thru formatting? reject certain characters maybe?

the client list data set that was imported into the app originally had a handful of names that had commas in them. so what followed was, whenever appsheet was pulling the client column from a data table to compile a list, it would get to these entries and see another list and discard it! this has resulted in all sorts of strange behavior. expressions not calculating properly, other expressions that would just return 0 for no reason, calculations that would change before my eyes after syncs completed and the browser caught up, all kinds of craziness.

this is of course because appsheet defaults to using a comma as an item separator in lists. after comparing the data to that what we knew would change consistently to a clean copy of the dataset we were able to spot the ‘missing’ records and the commas that shouldn’t have been there was the only thing in common. after changing the line separator to a different character we were able to confirm.

moral of the story, its a lesson old as time, and all that. never trust the data you are given, check and normalize and then recheck your data.

Steve

To some extent, but it gets cumbersome quickly.

Actually, AppSheet uses space-comma-space (" , ") by default. If you manually SPLIT() using just comma (","), yes, you’ll run into problems.

Austin

This is quiet the journey for a small thing to be the answer, dang.