Solved: Is there anyway to search list within list? - Page 2

Yogesh_laddha · 07-24-2019 09:37 PM

Hello,
I have been stuck on this for a while. Any suggestions would be really helpful.

I am trying to send notifications to a list of users who match certain conditions.

Now, the condition.
I have a table called Farmers and another table called Users.

In Farmers, I need to select a column [FPO], where the created date is Yesterday.
Now I get a list of all the [FPO] which were created yesterday.

I need to now compare this list from farmers table with [FPO] in the Users Table. (In Users table, [FPO] is an enumlist field).

I cannot seem to find any expression that would let me do this since I am comparing List within List.

The expression I am using is
SELECT(Users[Email],IN(SELECT(Farmers[FPO],DATE([Created At])=TODAY()-1),[FPO]))

I know this is wrong and will not show any results.

Is there any way I can accomplish this?

Thank you.

@Aleksi

Steve

Try this:

SELECT(
  Users[Email],
  (
    COUNT([FPO] - LIST())
    <> COUNT(
      [FPO]
      - SELECT(
        Farmers[FPO],
        (DATE([Created At]) = (TODAY() - 1))
      )
    )
  ),
  TRUE
)

SELECT(Users[Email], ..., TRUE) gathers a list of distinct (per TRUE) values from the Email column from rows of the Users table that match the given criteria (...; see (6)).
COUNT([FPO] - LIST()) constructs a list of and counts the distinct values of the list in the FPO column. Subtracting one list from another has the side-effect of removing duplicates from the result.
COUNT([FPO] - SELECT(...)) constructs a list of and counts distinct values of the list in the FPO column that do not also occur in the results of the SELECT() expression (SELECT(...); see (4)).
SELECT(Farmers[FPO], ...) gathers a list of values from the FPO column from rows of the Farmers table that match the given criteria (...; see (5)).
(DATE([Created At]) = (TODAY() - 1)) matches only rows where the date component of the Created At column value (DATE([Created At])) matches yesterday’s date ((TODAY() - 1)).
(... <> ...) asks whether the counts from (2) and (3) are different. If different, the EnumList in the FPO column value contains at least one value also contained in the list gathered by the SELECT() expression (see (4)); if not different, the two lists did not overlap.

View solution in original post