Filter duplicated result

[Tamara]

Hi,
i am working on a food allergy management system. My company delivers 1700 lunches to kids every day and many of those kids have special dietary requirements. some are dairy free, some are gluten free but there are other which are combined like dairy and gluten free.
i created a form where the user selects the relevant allergies in the meal (trigger), which school is going to be served that meal and it filters the relevant students with that particular allergy to know how many special meals are required. My problem is that a single student (studentID) could have more than 1 allergy (trigger) and the system returns a duplicate result.
For example: if i select gluten and dairy i want the system to return 1 result for Dairy and gluten free student but instead it returns 2 results for that student id based on the 2 different triggers. How can i fix this?

[TyAlevizos]

Hi,

Do you have control over or flexibility in changing the structure of your source data? Asking that first, as your answer might indicate one or more different solutions.

[Tamara]

hi,

i do have control over all the tables. as a matter of fact i tried creating at one point reference tables for each student id to avoid duplicates but it didn’t end up working.

[TyAlevizos]

Hi,

I was working on a simple example for this. If you copy and customize this app, it may help explain one way to do this. There are probably other ways to solve it.

Items of note for this solution in this app include:

• A special table devoted to “searching”. It’s only supposed to have one row of data, we then reference it’s columns using this syntax: Any(Search[School])
• A new virtual column on the student table to get all of their allergies rolled up into a LIST field.
• An Appsheet slice. Take note of the filter condition for this slice.
• An Appsheet dashboard. We place a search page and a results page on the dashboard. The results page also includes a download link to CSV.

I put an arbitrarily large amount of data into the app to ensure that performance is expected - which it is…

Hope this helps!

[Tamara]

Hi thanks for your help. i am still having problems. the filter condition on the slice only returns the id of a student where the 2 or more allergies selected are true.
What i need is:
student 1 Gluten free
student 2 Dairy Free
student 3 Gluten and Dairy free.

if i select Gluten, Dairy in an enum list
to get a return of the 3 students not only the gluetn and dairy free.

Hope this makes sense.

[Steve]

help.appsheet.com

SELECT()

Gather column values from row in table or slice

[Tamara]

Hi Steve, i tried to used the select() expression but i must be doing something wrong because it always gives me a different error.
Could you help me on how should I modify the slice filter condition posted on the sample app to include the select option?

Thanks

[Tamara]

Please, @TyAlevizos @Steve i’m going bonkers trying to fix this.

[TyAlevizos]

Hi, I just saw this, apologies for missing it.

I just flipped the expression around in the original app I posted (created a new slice, new view, left the old slice and view alone). So the new expression is

AND(
[School] = Any(Search[School]),
In(Any(Search[Allergy]),[Allergy List])
)

Seems to be doing what you were asking for? Can you verify? The app is here. The view called “filter” is unchanged, and the new results view is called “search reversed”.

??

[Tamara]

Thanks for following up @TyAlevizos

i spent some time trying to understand where the issue is coming from.

i checked the First Sample app and it works perfectly fine.
i made a copy of it and change the Data Values.
The Student table has now more columns but the settings from the original sample app remains the same.
Triggers table structure remains the same.
Search table structure remains the same.

But when i change the data and select more than one allergy the app only returns values for the first allergy selected.
When i use the sample app and select more than one allergy the sample app works perfectly and returns all the results requested.
i compared both apps and everything except for the data values is the same.

Honestly i don’t know where the mistake is.

i tested the app slice filter condition in both of them (my app and the sample app) and the results are different:

[Steve]

If the Allergy column of the Search table is of type EnumList, try this as your row filter expression:

ISNOTBLANK(
  FILTER(
    "Search",
    AND(
      OR(
        ISBLANK([School]),
        ([School] = [_THISROW].[School])
      ),
      OR(
        ISBLANK([Allergy]),
        ISNOTBLANK(
          INTERSECT(
            [Allergy List],
            [_THISROW].[Allergy]
          )
        )
      )
    )
  )
)

[Tamara]

Hi Steve,
i tried your expression but it gives me this error:
Error in expression ‘[_THISROW-1].[Key].[Allergy]’ : Unable to find column ‘Allergy’

[Tamara]

Hi @Steve @TyAlevizos

i tried the ty’s reversed formula and it’s still not working.

[Tamara]

@Steve @TyAlevizos i think i narrowed where the issue is coming from:
IN() expects it first argument to be a single value, not a list. If given a list as the first argument, it will interpret it as a single Text value and try to match that single text value against each value in the list of the second argument.

i tried to use intersect but it returns:
INTERSECT does not accept a list of list.

[TyAlevizos]

ok I think I see design flaws in my original app, so I have modified it. Same app here.

I have edited both of the SLICES - one is now a boolean AND (all values in the search must match the students’ allergy records) and one is an OR (any value from the search must be found from the list of student allergies).

To achieve this, I added a sort to each list. By “Each list” I refer to A) the entered search term, and B) the allergy records for each student.

In the case of boolean AND, we then do an equality check after sorting. For boolean OR, we use the IN() function after sorting.

Very expensive calculations at scale, but seems to work ok.

Please take a look and let me know if this latest version is working.

[Tamara]

@TyAlevizos
Thanks for the reply!
i tested the new formula and it’s still returning false even when the value i want to search is part of the list.

[TyAlevizos]

If you are referring to my sample app with its fake data, yes I just noticed that when I generated the fake data, I incorrectly marked students with “Gluten” twice or three times, same thing with the other values… so it was garbage data. E.g. a student’s allergy list was “Gluten, Gluten, Dairy, Dairy, Wheat” which would never match

I have just corrected that by cleaning up the sheet. In the sample sheet called “Trigger”: only a few students have more than one allergy. All 4990 or so other students have exactly one allergy. So to test my latest Slices, you would want to add new allergies for some students. It should work. I am seeing it work ok with this sample app and sample data, now that I have removed the duplicate rows of allergies.

[TyAlevizos]

darnit, the boolean OR is still not behaving. Tough one… I will continue to look. The boolean AND seems to be behaving now.

[TyAlevizos]

OK I think I got it. The app sample is updated (sorry that you will want to copy it again).

The Boolean OR I came up with requires a new column in our “search” table for each allergy. Then we can write an expression like so:

AND(
if(isblank(any(Search[School])),true,[School] = Any(Search[School])),
OR(
if(Any(Search[Dairy])=TRUE,in("Dairy",[Allergy List]),false),
if(Any(Search[Gluten])=TRUE,in("Gluten",[Allergy List]),false),
if(Any(Search[Grass])=TRUE,in("Grass",[Allergy List]),false),
if(Any(Search[Nuts])=TRUE,in("Nuts",[Allergy List]),false),
if(Any(Search[Whey])=TRUE,in("Whey",[Allergy List]),false)
)
)

[Steve]

Replace cumbersome IF() with appropriate AND() or OR():

AND(
  OR(ISBLANK(ANY(Search[School])), ([School] = ANY(Search[School]))),
  OR(
    AND(ANY(Search[Dairy]), IN("Dairy",[Allergy List])),
    AND(ANY(Search[Gluten]), IN("Gluten",[Allergy List])),
    AND(ANY(Search[Grass]), IN("Grass",[Allergy List])),
    AND(ANY(Search[Nuts]), IN("Nuts",[Allergy List])),
    AND(ANY(Search[Whey]), IN("Whey",[Allergy List]))
  )
)

[Steve]

How about:

ISNOTBLANK(
  FILTER(
    "Search",
    AND(
      OR(
        ISBLANK([School]),
        ([School] = [_THISROW].[School])
      ),
      OR(
        ISBLANK([Allergy List]),
        ISNOTBLANK(
          INTERSECT(
            [Allergy List],
            [_THISROW].[Allergy List]
          )
        )
      )
    )
  )
)

[TyAlevizos]

This looks tight Steve, trying that next (later today)

[TyAlevizos]

Your version (without the extra fields) and my version (with a field for each allergy) have slightly different results, @Steve . I have to study it a little more… not sure why. Thanks for the cleaner AND() syntax replacing the IF statements!