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?
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.
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)))
LOOKUP([_THISGUY].[Steve],โSheetGodsโ, โGod IDโ,โThankYouโ)
User | Count |
---|---|
37 | |
25 | |
21 | |
20 | |
16 |