Expression to calculate the onboarding sequence

[Neeraj_Malik]

Dear All,

Request for help with expression to calculate the Client_Sequence (on board sequence) to have it tabulated as per the following

Thanks in advance @Steve @LeventK @Suvrutt_Gurjar @WillowMobileSys 

[Suvrutt_Gurjar]

Based on understanding of your requirement , please try below

I believe this is an indexing requirement and the suggested solution is based on a compact INDEXOF() expression shared by @Steve in the tip below

INDEXOF() - Google Cloud Community

In the [Client_Sequence] column , please try an expression of 

COUNT(
SPLIT(
ANY(
SPLIT(
(" , " & UNIQUE(TableName[Client] +LIST([Client])& " , "),
(" , " & [Client] & " , ")
)
),
" , "
)
)

Please substitute the "TableName" in the expression with the actual table name you have.

However using such an expressions in a VC can be sync time expensive. So, alternatively, you could use it in the initial value of a physical column. Please set the editable_if  to FALSE for this [Client_Sequence] column. This physical column based approach assumes the client order will never change and a client's records will never be deleted form the table. Or else you may need to use bots etc. to keep changing the order as some records get deleted or inserted in between.

[Neeraj_Malik]

Thanks for the exspression @Suvrutt_Gurjar . But looks like I'm still missing something. This is the exact expression I used

COUNT(
SPLIT(
ANY(
SPLIT(
(" , " & UNIQUE(Client_Primary_Account[Client_ID]) & " , "),
(" , " & [Client_ID] & " , ")
)
),
" , "
)
)

prodcuing the following result

Count_Sequence in first row should have 1, and second row as 2, and then follow the rest with 1 and 2 for respective Client_ID. Not sure why the value 3 as the total unique Client_ID are only 2.

[Suvrutt_Gurjar · Accepted solution]

My suggestion was a bit different. Please take a look at the highlighted part that is missing in your shared expression. BTW I have also added a missing parenthesis in the expression below

COUNT(
SPLIT(
ANY(
SPLIT(
(" , " & UNIQUE(TableName[Client] +LIST([Client]))& " , "),
(" , " & [Client] & " , ")
)
),
" , "
)
)

Also could you mention, how are you using it - in physical or virtual column, in initial value or app formula. If in physical column, could you mention how you are ensuring it works for the existing records? The initial value expression will not work for existing rows.

Edit: Just to test, the suggested expression is working in principle, you could test it in a virtual column.  As mentioned for physical columns, it will not work for existing rows. If you have existing records, you may need to add the sequence numbers manually at the back end.

[Neeraj_Malik]

Once again thanks @Suvrutt_Gurjar 

This time it's worked well. I could not correct the parentheses issue properly in the earlier expression given by you and experimented with the expression as whole.

Also, thanks for highlighting that it won't work well for existing rows in the table. My table was/is completely blank so I don;t think I need to use virtual column as advised by you.

Really appreciate your help. Thanks.