I have a START/END inside a template and the first column in my report is a number.
The number needs to increment according to the position of a Name inside a list.
The SELECT for the START produces a list of names with duplicates - [“ABC”, “ABC”, “LMN”, “LMN”, …“NameN”]
I need rows in my report where the first column is based on the above list - such as [1, 1, 2, 2,… 22]
Basically, I need my output to look like this:
ID Name Amt
1 ABC $20
1 ABC $30
2 LMN $22
2 LMN $45
.....
22 NameN $5
Right now about 70 unique names (and about 300 max in duplicate list) - it will undoubtedly grow but not too much more than 100 unique names this year.
Create a virtual column with a formula like
COUNT(SPLIT(LEFT(SORT(UNIQUE(Table[Name]),FALSE),FIND([Name],SORT(UNIQUE(Table[Name]),FALSE)))," , ")) and then use it as normally as <<[ID]>>
Thanks for the tip but unfortunately it doesn’t make sense to add a VC that gets calculated every sync rather than once a month when the report is scheduled. Since the list, Name, itself is dynamic and comes from a different table than the one for the report, I would need two VCs.
I think this points to the need for defining temporary variables that are accessed only by workflows/reports.
I think this points to the need for defining temporary variables that are accessed only by workflows/reports.
@praveen, each workflow has a list of records that match the If this is true condition at any given time.
What if we could specify a sort order for the rows as it pertains to the execution of the workflow, then provide the developer a template variable representing the current rows position in the matching set of rows? [_THISROW].[_Position] maybe?
It would be similar to [_RowNumber] in that it is not persistent, but would be tied to a user defined sort order, rather than the actual sheet’s current sort order.
In this case it probably won’t work because it’s not a pure 1.2.3.4.etc. list. Though this is a special case because the list is dynamic and can be like 1,1,1,2,3,3,3,4,5,6,etc.
I think I don’t know what to call it… I feel like the index function we have of what’s supposed to do it… But it’s backwards? INDEX() should return a position of a value in a list… Instead ours, you pass a position to it, and it returns the item from that position…
So I guess it would need to be
POSITION()
or
INDEXOF()
