Select expression enumlist IN()

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.

1 Like

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.

1 Like

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.

1 Like

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?

1 Like

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.

1 Like

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

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?

1 Like

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.

1 Like

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.

1 Like

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

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.

1 Like

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

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.

1 Like

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

1 Like

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

Select Expression:

Table Layout:

Data Table Structure:

Data Table Client Column:

Report Table Client Column:

Client Table Client Column:

1 Like

Column type should be Text:

Valid If should be Client Profile[Key]:

image

Same here:

image

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

image

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

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.

Screenshot(s), please.

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:

1 Like