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.
LinkedIn
Twitter
