Can someone help me create an expression that...

(Brady Lovell) #1

Can someone help me create an expression that will generate a sequential number every time a specific word is in a specific column?

I have the column [Pro #] which is basically a load number for accounting purposes. I want a new Pro # generated every time the word “Joe” is in the column [Broker].

For example, if Joe is on his 1st order,

his Pro # would be 1. I would then need that 1 to show up in the [Pro #] column.

If Joe is on his 7th order (which means the word “Joe” appears 7 times in the [Broker] column), than his Pro # needs to be 7, and that 7 needs to be shown in the [Pro #] column, on that particular order.

My research is exhausted, so any tips or advice will be greatly appreciated.


(Aleksi Alkio) #2

Try COUNT(SELECT(TableName[KeyColumnName],[Broker]=“Joe”))+1 into the initial value field.

(Brady Lovell) #3

@Aleksi_Alkio Much appreciated! Works exactly as needed.