Execute an Action on Set of Rows - Filter Duplicates

AppSheet AppSheet Q&A
1

I have an action to add rows to another table and I’m using a select expression to reference which rows i want. However, i want to filter the list of referenced rows to omit duplicate values in a column that is not the primary key.

My Key is ‘Use ID’ and the column I want to filter for is ‘Track ID’.

My Select expression is SELECT(Uses[Use ID],[Opportunity ID] = [_THISROW].[Opportunity ID)

If i were to add the ,TRUE to the end of the expression, this would omit duplicate USE IDs but I want to omit duplicate Track IDs. Is there any way to do that?

1 Like
2

So you want a Use ID of each distinct (Use ID, Track ID) pair from rows with the matching Opportunity ID?

SELECT(
  Uses[Use ID],
  AND(
    ([Opportunity ID] = [_THISROW].[Opportunity ID]),
    (
      [_ROWNUMBER]
      = MIN(
        SELECT(
          Uses[_ROWNUMBER],
          AND(
            ([Opportunity ID] = [_THISROW-1].[Opportunity ID]),
            ([Track ID] = [_THISROW-1].[Track ID])
          )
        )
      )
    )
  )
)

This is a heavy query and not well suited for a virtual column or format rule, but is reasonable for other uses such as in an action.

6 Likes
3

Maybe this is another possibility (the inner SELECT being a VC, perhaps?)

SELECT(Uses[Use ID],IN([_THISROW].[Opportunity ID],SELECT(Opportunities [Opportunity ID], TRUE, TRUE)))

2 Likes
4

Steve:

auto> SELECT(> Uses[Use ID],> AND(> ([Opportunity ID] = [_THISROW].[Opportunity ID]),> (> [_ROWNUMBER]> = MIN(> SELECT(> Uses[_ROWNUMBER],> AND(> ([Opportunity ID] = [_THISROW-1].[Opportunity ID]),> ([Track ID] = [_THISROW-1].[Track ID])> )> )> )> )> )> )> >

LOOKUP([_THISGUY].[Steve],“SheetGods”, “God ID”,“ThankYou”)

5 Likes