Incrementing number inside a START based on unique position of value in List

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.

  1. The SELECT for the START produces a list of names with duplicates - [“ABC”, “ABC”, “LMN”, “LMN”, …“NameN”]
  2. 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

Would appreciate any hints.

How about <<Start: ORDERBY(SELECT(…),[ID],FALSE,[Name],FALSE])>>

ID is not actually a column in the table (and I don’t want to add it to the table) - I need to generate it using a formula only for the report.

Are you using spreadsheet as a database?

Yes, for now but I will be moving this app to SQL after I get these reports working.

How many unique names you could have?

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]>>

1 Like

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.

@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.

2 Likes

I would second the [_THISROW].[_Position] proposal.

1 Like

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.

1 Like

If the data is coming from the same table, the template would be like this…

4 Likes

Hi @Aleksi,

Very well constructed expression for placing in a template.