Auto-generate increment number based on specific column

%3CLINGO-SUB%20id%3D%22lingo-sub-2402835%22%20slang%3D%22en-US%22%3EAuto-generate%20increment%20number%20based%20on%20specific%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2402835%22%20slang%3D%22en-US%22%3E%3CP%3EFor%20example%2C%3C%2FP%3E%3CP%3Eif%20the%20user%20edits%20the%20column%20%22A%22%2C%20then%20the%20column%20%22B%22%20will%20generate%20the%20number%20%221%22.%20(first%20time)%3C%2FP%3E%3CP%3EFor%20the%20second%20time%20the%20user%20edits%20the%20column%20%22A%22%20again%2C%20the%20column%20%22B%22%20will%20increase%20to%202.%3C%2FP%3E%3CP%3E%3CSTRONG%3ECan%20we%20set%20the%20column%20to%20auto-generate%20increment%20number%20based%20on%20a%20specific%20column%3F%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2402835%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAdmin%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ELists%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMicrosoft%20Lists%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESharePoint%20Online%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESites%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2404013%22%20slang%3D%22en-US%22%3ERe%3A%20Auto-generate%20increment%20number%20based%20on%20specific%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2404013%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1037706%22%20target%3D%22_blank%22%3E%40mmchx%3C%2FA%3E%26nbsp%3Bto%20do%20this%20you'll%20need%20a%20flow%20in%20Power%20Automate.%20You%20must%20turn%20on%20versioning%20in%20your%20list%20settings%20for%20this%20flow%20to%20work.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20my%20example%20whenever%20the%20%3CEM%3EDepartment%3C%2FEM%3E%20column%20is%20changed%20the%20%3CEM%3EInc%3C%2FEM%3E%20column%20increments%20by%201.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20trigger%20is%20%22When%20an%20item%20or%20file%20is%20modified%22.%20Next%20I've%20added%20a%20Compose%20action%20to%20get%20the%20current%20value%20of%20the%20Inc%20column.%20In%20the%20list%20a%20number%20column%20is%20saved%20as%20a%20flot%20but%20we%20need%20it%20as%20an%20integer%20so%20we%20convert%20it%20and%20the%20expression%20you%20add%20into%20the%20dynamic%20content%20box%20is%26nbsp%3B%3CBR%20%2F%3E%3CSTRONG%3Eint(triggerOutputs()%3F%5B'body%2FInc'%5D)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENext%20we%20add%20an%20initialize%20variable%20action%20and%20set%20its%20initial%20value%20to%20the%20output%20of%20the%20previous%20compose.%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%221-Flow.png%22%20style%3D%22width%3A%20628px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F285310iBF051F7C5B3788B6%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%221-Flow.png%22%20alt%3D%221-Flow.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3BNext%2C%20add%20a%20%22Get%20changes%20for%20an%20item%20or%20a%20file%20(properties%20only)%22%20action%2C%20and%20because%20we%20want%20the%20changes%20since%20the%20previous%20version%20add%20the%20following%20expression%20into%20the%20Since%20field%3A%3C%2FP%3E%3CP%3E%3CSTRONG%3Esub(int(triggerOutputs()%3F%5B'body%2F%7BVersionNumber%7D'%5D)%2C1)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%222-Flow.png%22%20style%3D%22width%3A%20628px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F285321i895371D9E49EF855%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%222-Flow.png%22%20alt%3D%222-Flow.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3BNext%20we%20need%20to%20check%20if%20it%20was%20the%20Department%20column%20that%20had%20changed%2C%20so%20we%20add%20a%20Condition%20and%20select%20%3CEM%3EHas%20Column%20Changed%3A%20Department%3C%2FEM%3E%20%3CSTRONG%3Eis%20equal%3C%2FSTRONG%3E%20to%20%3CEM%3Etrue%3C%2FEM%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%223-Flow.png%22%20style%3D%22width%3A%20630px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F285313i81573023C270C798%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%223-Flow.png%22%20alt%3D%223-Flow.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20it%20isn't%20that%20column%20but%20another%20one%20then%20we%20don't%20want%20to%20do%20anything%2C%20so%20the%20red%20if%20no%20channel%20can%20stay%20empty.%20But%20if%20the%20Department%20column%20has%20changed%20we%20want%20to%20add%201%20to%20the%20Inc%20column.%20So%20in%20the%20green%20if%20yes%20channel%20add%20an%20increment%20variable%20action%2C%20select%20the%20variable%20you%20initialized%20earlier%20and%20set%20it%20to%20increment%20that%20variable%20by%201.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFinally%2C%20update%20the%20item%20in%20the%20list%20selecting%20the%20variable%20(with%20its%20new%20value)%20for%20the%20Inc%20column.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%224-Flow.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F285312i7BE3B565CF5A7A2D%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%224-Flow.png%22%20alt%3D%224-Flow.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERob%3CBR%20%2F%3ELos%20Gallardos%3CBR%20%2F%3E%3CFONT%20size%3D%222%22%20color%3D%22%23006400%22%3EMicrosoft%20Power%20Automate%20Community%20Super%20User%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

For example,

if the user edits the column "A", then the column "B" will generate the number "1". (first time)

For the second time the user edits the column "A" again, the column "B" will increase to 2.

Can we set the column to auto-generate increment number based on a specific column?

 

 

 

 

1 Reply

@mmchx to do this you'll need a flow in Power Automate. You must turn on versioning in your list settings for this flow to work.

 

In my example whenever the Department column is changed the Inc column increments by 1.

 

The trigger is "When an item or file is modified". Next I've added a Compose action to get the current value of the Inc column. In the list a number column is saved as a flot but we need it as an integer so we convert it and the expression you add into the dynamic content box is 
int(triggerOutputs()?['body/Inc'])

 

Next we add an initialize variable action and set its initial value to the output of the previous compose.

1-Flow.png

 Next, add a "Get changes for an item or a file (properties only)" action, and because we want the changes since the previous version add the following expression into the Since field:

sub(int(triggerOutputs()?['body/{VersionNumber}']),1)

 

2-Flow.png

 

 Next we need to check if it was the Department column that had changed, so we add a Condition and select Has Column Changed: Department is equal to true

3-Flow.png

 

If it isn't that column but another one then we don't want to do anything, so the red if no channel can stay empty. But if the Department column has changed we want to add 1 to the Inc column. So in the green if yes channel add an increment variable action, select the variable you initialized earlier and set it to increment that variable by 1.

 

Finally, update the item in the list selecting the variable (with its new value) for the Inc column.

 

4-Flow.png

 

Rob
Los Gallardos
Microsoft Power Automate Community Super User