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.
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.
Solved! Go to Solution.
If the data is coming from the same table, the template would be like this…
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]>>
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.
I would second the [_THISROW].[_Position] proposal.
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.
If the data is coming from the same table, the template would be like this…
Hi @Aleksi,
Very well constructed expression for placing in a template.
Wow, that was a hard expression to deconstruct. Here’s copy-able text of Aleksi’s expression for anyone else curious like me that wants to examine it.
COUNT( SPLIT( LEFT( SORT( UNIQUE( Data[Data] ) , FALSE ) , FIND( [Data] , SORT( UNIQUE( Data[Data] ) , FALSE ) ) ) , “,” ) )
_POSITION would be great.
Also, how do we not have an INDEXOF() expression?
I want POSITION() as well…
How about a feature request?
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()
Upvote for INDEXOF()
User | Count |
---|---|
61 | |
25 | |
14 | |
11 | |
6 |