How do I remove duplicates in a list on an e-mail or attachment template

AppSheet AppSheet Q&A
,
1

Hello Appsheet community… Long time listener. First time caller.

I am using the Start: tag on an e-mail template to create a list on my attachment. When using Start:, you have to provide it with a list of Ref values. I am struggling to find a way to remove ref values based upon having duplicate data in one of their columns. I don’t know if that makes sense.

I am trying to return a list of every staff member that my team supported in a specific time period. I am trying to show how much support we provided to each of these specific job titles. When I run my script, it is returning duplicates. Because we may have supported three doctors in that time period or four nurses, etc. I do not want duplicates. I want a unique [Staff_JobCode] per row.

Here is my current script:

<<START:
   SELECT(
      Staff[Staff_ID],
         IN(
            [Staff_ID],
            SELECT(
               Participants[(FK) Staff_ID],
               AND(
                  IN(
                     [(FK) MIs_ID],
                     SELECT(
                        MIs[MIs_ID],
                        AND(
                           [MIs_Date] >= [Reports_StartDate],
                           [MIs_Date] <= [Reports_StopDate],
                           [(FK) InteractionTypes_ID] = "4",
                           [MIs_Active]
                        ),
                        FALSE
                     )
                  ),
                  [Participants_Active]
               ),
               FALSE
            )
         ),
         TRUE
   )
>>
<<[Staff_JobCode]>>
<<END>>

Thank you all in advance for your help.

2

Caleb_Graham:

I want a unique [Staff_JobCode] per row.

I I am understanding correctly, your current SELECT is returning a list of Staff_ID’s and these correspond to a person? But for a Staff_JobCode, there are likely several people who worked under that code. You want to show the Staff_JobCode and then the list of people who worked that job on the same row.

Do I have that right?

If so, you will need double loop. The outer loops selects the list of Staff_JobCodes, I assume, if they have people who worked them. The inner loop then selects the people based on the outer loop JobCode being processed.

There are a couple of common problems:

  1. If the Staff_JobCode column is the same name in the outer loop as it is in the inner loop, you will have an ambiguity problem when trying to match by this column name.

  2. How do you plan to show the list of people? You would not be able to, or at least it would be VERY difficult, to have a format with a variable number of columns to list the people on the row for your JobCode. You could just show the people as a list in a single template column or you could show your JobCodes in a Grouping format - the JobCode is a GRoup header and the people are a list of rows under that header.

Do you have a sample of the template format you are aiming at?

1 Like
3

Hi John,

I actually do not need the names of the staff. What I intend to do is show a table. Display one Staff_JobCode (without duplicates) per row in the first column. In the adjoining column, display the percentage of support encounters per the whole. It should look like this:

3X_b_0_b0c38d4b36f18d1c166aa49c37ee20cd46b2722c.png

I don’t think an outer loop would work. It would still need to return the Ref values. And Staff_JobCode is not the Ref.

4

Caleb_Graham:

is not the Ref.

You can have more than one REF column per row.

Where does the Percentage of Support Encounters come from?

1 Like