Is there anyway to search list within list?

[Yogesh_laddha]

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]

Is the FPO column in Farmers table an EnumList?

If NO, try wrapping SELECT(Farmers[FPO], ...) in ANY():

SELECT(
  Users[Email],
  IN(
    ANY(
      SELECT(
        Farmers[FPO],
        (DATE([Created At]) = (TODAY() - 1))
      )
    ),
    [FPO]
  )
)

[Yogesh_laddha]

Thanks, Steve.
The expressions work and when I test it shows a list of email addresses separated by a comma.

But the notifications don’t work. When I checked into logs it shows error as the “To” field is either invalid or missing.

Posting a screenshot of the log below.

*Edit - The expression also doesn’t work in all case scenarios. I assume that “Any” will just select one random value from the list and compare with Users[FPO]. Or Does it check all the values in the list against Users[FPO].

[Jonathon]

Are you using security filters on these tables?

[Yogesh_laddha]

Yes, I am.

[Steve]

Above, I asked:

Steve:

Is the FPO column in Farmers table an EnumList ?

[Yogesh_laddha]

No. It’s a ref field.

FPO in the users table is an enumlist field.

[Steve]

Yogesh_laddha:

No. It’s a ref field.

To what?

Is FPO in Users an EnumList of Ref?

[Yogesh_laddha]

To [FPO ID] in the FPO Table. It’s a different table then the Users table.
[FPO ID] is text field.

And Yes, FPO in Users is an Enumlist of Ref.
Here the reference table is also FPO.

[Jonathon]

As a quick test, try removing the security filter and see if it fixes your problem.

Workflows / reports are ran as the app creator, and sometimes security filters can mess with this.

When you are testing the app within the app creator, the rules are ran on the data you have already loaded, so testing the formulas in-app it may appear that things are working. When the workflow runs, the data cannot be accessed because of the security filter.

This is just something to check before proceeding.

[Yogesh_laddha]

That worked perfectly. Thank you.

But I still have issues with expression to list all the emails this notification should be sent to.
SELECT(
Users[Email],
IN(
ANY(
SELECT(
Farmers[FPO],
(DATE([Created At]) = (TODAY() - 1))
)
),
[FPO]
)
)

This expression doesn’t work in all cases. Is there any way else to resolve this?

[Steve · Accepted solution]

Try this:

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

1. 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)).

2. 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.

3. 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)).

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)).

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)).

6. (... <> ...) 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.

[Yogesh_laddha]

That is brilliant. Works perfectly.
I could have never thought of that.

Thanks a lot for helping Steve.
Really appreciate it.

[Yogesh_laddha]

@Steve
Do we always need to remove security filters from the tables to make the notifications work?
Or is this a bug you might want to look into.

Removing security filters would mean I have to create slices for all the views related to that table and put the condition on the slice. Which gets tedious if there are lots of tables and views in the app.

[Jonathon]

You can keep the security filters, but you need to make an exception in the rule to let the app owner read all data.

For example, you may have a security filter to only load each users data:

USEREMAIL()=[user_id].[email]

Workflow rules are always ran as the APP CREATOR. Therefore, you will need to rework your security filter as such:

OR(
   USEREMAIL()=appcreator@email.com,
   USEREMAIL()=[user_id].[email]
)

The only real drawback of this is, the security filters don’t function as intended for the app creator and therefore the app experience will be different for that user. For this reason, I use a separate account to create all my AppSheet apps under rather than my primary account that I use day to day.

[Yogesh_laddha]

Ahh got it.

In my case I am the app creator and I need access to all the data.(An admin).
So adding this rule won’t change much for me.

Thanks a lot for your help.

Really appreciate it.