SOLVED

Auto-Increment Number Field in a SharePoint List

%3CLINGO-SUB%20id%3D%22lingo-sub-1141477%22%20slang%3D%22en-US%22%3EAuto-Increment%20Number%20Field%20in%20a%20SharePoint%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1141477%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20to%20create%20an%20auto-increment%20field%20with%20a%20standard%20amount%20of%20digits.%20The%20format%20needs%20to%20be%20like%20this%20T000001%2C%20T000002%2C%20T000003%2C%20......T000010%2C%20etc%20(7%20Digits).%20When%20it%20gets%20to%20the%2010th%20record%20it%20should%20not%20do%20this%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3ET000001%3C%2FSTRONG%3E0%20(8%20digits).%3C%2FP%3E%3CP%3EI%20tried%20using%20the%20popular%2C%20often%20referenced%20solution%20(link%20below).%20This%20method%20does%20not%20work%20as%20I%20hoped.%20If%20you%20put%20it%20in%20a%20format%20like%20this%20T1000%2C%20when%20it%20gets%20to%20the%2010th%20item%20it%20does%20this%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3ET10001%3C%2FSTRONG%3E0%20rather%20than%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3ET10010%3C%2FSTRONG%3E.%3C%2FP%3E%3CP%3E%3CSTRONG%3EHere%20is%20the%20popular%20solution%20I%20already%20tried%3A%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CA%20href%3D%22https%3A%2F%2Fwww.epmpartners.com.au%2Fblog%2Fhow-to-create-an-auto-incrementing-number-field-in-a-sharepoint-list%2F%22%20rel%3D%22nofollow%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Fwww.epmpartners.com.au%2Fblog%2Fhow-to-create-an-auto-incrementing-number-field-in-a-sharepoint-list%2F%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1141477%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ELists%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESharePoint%20Server%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESites%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1144667%22%20slang%3D%22en-US%22%3ERe%3A%20Auto-Increment%20Number%20Field%20in%20a%20SharePoint%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1144667%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F539288%22%20target%3D%22_blank%22%3E%40Ninjak%3C%2FA%3E%26nbsp%3Bhave%20you%20tried%20using%20a%20Calculated%20field%3F%3C%2FP%3E%0A%3CP%3EIn%20your%20list%20or%20library%2C%20the%20ID%20column%20(a%20system%20column)%20will%20increment%20each%20time%20an%20item%20is%20added.%20You%20can%20use%20a%20calculated%20column%20to%20create%20a%20prefix%20and%20append%20the%20ID%26nbsp%3B%20e.g.%26nbsp%3B%20T000%2BID%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2Fexamples-of-common-formulas-in-sharepoint-lists-d81f5f21-2b4e-45ce-b170-bf7ebf6988b3%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2Fexamples-of-common-formulas-in-sharepoint-lists-d81f5f21-2b4e-45ce-b170-bf7ebf6988b3%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1145521%22%20slang%3D%22en-US%22%3ERe%3A%20Auto-Increment%20Number%20Field%20in%20a%20SharePoint%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1145521%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Keenan%20(%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F539288%22%20target%3D%22_blank%22%3E%40Ninjak%3C%2FA%3E)%2C%20this%20complex%20auto-increment%20can%20be%20achieved%20relatively%20easily%20using%202%20lists%20in%20SharePoint%20and%20a%20flow%20in%20Power%20Automate.%20The%20result%20will%20look%20like%20this%20in%20the%20main%20list%20which%20I've%20called%20Crop%20Harvest%3A%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%20style%3D%22width%3A%20766px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F168446i17BB0540A04C990D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%220-SP-List-Result.png%22%20title%3D%220-SP-List-Result.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20will%20need%20a%20second%20list%20(which%20I've%20called%20Increment)%20to%20hold%20the%20next%20value.%20In%20the%20result%20above%20I%20manually%20changed%20the%20value%20to%20show%20the%20difference%20based%20on%20the%20number%2C%20and%20I've%20manually%20changed%20it%20back%20to%209%20for%20now%20but%26nbsp%3B%20you%20will%20see%20that%20it%20will%20auto-increment%20in%20the%20flow.%20This%20second%20list%20will%20only%20ever%20have%201%20item%20which%20is%20the%20next%20value%20to%20be%20used%3A%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%20style%3D%22width%3A%20841px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F168447i5D958D2A8DC49C3E%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%220-SP-IncrementList.png%22%20title%3D%220-SP-IncrementList.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20flow%20the%20trigger%20I've%20used%20is%20%3CEM%3EWhen%20an%20item%20is%20created%3C%2FEM%3E%20in%20the%20SharePoint%20main%20list.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENext%20we%20get%20the%201%20item%20from%20the%20Increment%20list%2C%20and%20the%20next%20action%20is%20to%20initialize%20a%20string%20variable%20which%20I've%20named%20varIncrementNumber%3A%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%20style%3D%22width%3A%20570px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F168448i577151A7ED6B33F4%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%221-Trigger-getItems-initVariable.png%22%20title%3D%221-Trigger-getItems-initVariable.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20next%20action%20to%20add%20is%20a%20Compose%20control%20which%20I've%20named%20ComposeNext%20and%20you%20select%26nbsp%3B%3CSTRONG%3ENext%20value%3C%2FSTRONG%3E%20from%20the%20dynamic%20content%20box%20on%20the%20right.%20What%20you%20will%20see%20when%20you%20do%20this%20is%20that%20it%20wraps%20itself%20in%20an%20%3CEM%3Eapply%20to%20each%3C%2FEM%3E%20control.%20It%20does%20this%20because%20although%20there%20is%20only%201%20item%20in%20the%20Increment%20list%20there%20could%20potentially%20be%20lots%20so%20it%20adds%20the%20apply%20to%20each.%20That's%20fine%2C%20it's%20expected%20and%20you%20don't%20try%20to%20delete%20it%20but%20just%20leave%20it%20there.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20next%20action%20is%20a%20%3CEM%3Econdition%3C%2FEM%3E.%20For%20this%20first%20condition%20we%20are%20only%20going%20to%20test%20if%20the%20output%20of%20the%20previous%20compose%20action%20is%20less%20than%2010%20(we'll%20do%20the%20others%20shortly)%3A%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%20style%3D%22width%3A%20723px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F168450iB77649146D4DF1C3%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%222-apply-composeNext-Condition1-9.png%22%20title%3D%222-apply-composeNext-Condition1-9.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20it%20IS%20less%20than%2010%20then%20in%20the%20If%20yes%20green%20channel%20we%20add%20an%20%3CEM%3EAppend%20to%20string%20variable%3C%2FEM%3E%26nbsp%3Band%20in%20the%20value%20type%20%3CSTRONG%3ET00000%3C%2FSTRONG%3E%20immediately%20followed%20by%20the%20output%20of%20the%20ComposeNext%20selected%20from%20the%20dynamic%20content%20box%20on%20the%20right%3A%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%20style%3D%22width%3A%20588px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F168451i93B759F3B33C4137%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%223-1to9-Append.png%22%20title%3D%223-1to9-Append.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20the%20ComposeNext%20value%20isn't%20less%20than%2010%20then%20in%20the%20red%20If%20no%20channel%20you%20add%20another%20condition.%20In%20the%20left%20hand%20box%20select%20the%20Output%20from%20the%20ComposeNext%20from%20the%20dynamic%20content%2C%20select%20%3CSTRONG%3Eis%20greater%20than%20or%20equal%20to%3C%2FSTRONG%3E%2C%20and%20then%20in%20the%20right%20hand%20box%20type%2010.%20Then%20add%20another%20row%20and%20do%20the%20same%20but%20make%20it%20less%20than%20100.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAgain%2C%20you'll%20get%20a%20green%20if%20yes%20channel%20and%20a%20red%20if%20no%20channel.%20In%20the%20green%20channel%2C%20as%20before%2C%20add%20an%20append%20to%20string%20variable%20action%20and%20this%20time%20in%20the%20value%20add%20T0000%20and%20then%20the%20output%20of%20the%20ComposeNext%3A%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%20style%3D%22width%3A%20656px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F168452i1C9E2FB1176F167A%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%225-10to99Append.png%22%20title%3D%225-10to99Append.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20red%20If%20no%20channel%20add%20another%20condition%20as%20before%20but%20make%20it%20greater%20than%20or%20equal%20to%20100%20and%20less%20than%201000.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20keep%20doing%20that%20-%20changing%20the%20number%20of%20leading%20zeros%20each%20time%20of%20course%20-%20until%20you%20get%20to%20the%20last%20condition%20which%20is%20if%20the%20output%20of%20the%20ComposeNext%20is%20greater%20than%20or%20equal%20to%2010000%20and%20less%20than%20100000%20then%20you%20will%20add%20an%20Append%20to%20string%20variable%20in%20both%20channels.%20In%20the%20green%20If%20yes%20channel%20it%20will%20be%20%3CSTRONG%3ET0%3C%2FSTRONG%3E%20plus%20the%20Output%20and%20in%20the%20red%20if%20no%20channel%20it%20will%20be%20just%20%3CSTRONG%3ET%3C%2FSTRONG%3E%20plus%20the%20Output.%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%20style%3D%22width%3A%20937px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F168453i3B08DF834A6C0E14%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%226-condition10000-99999.png%22%20title%3D%226-condition10000-99999.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20we've%20got%20the%20right%20value%20with%20the%20T%20plus%20correct%20leading%20zeros%20in%20our%20variable%20now.%20The%20next%20action%20is%20still%20inside%20the%20apply%20to%20each%20but%20not%20inside%20any%20of%20the%20conditions.%20The%20action%20is%20a%20SharePoint%20Update%20item%20action%20and%20in%20the%20text%20column%20where%20you%20are%20storing%20the%20increment%20text%20value%20you%20select%20the%20string%20variable%20varIncrementNumber.%20You'll%20see%20I've%20also%20stored%20the%20actual%20numeric%20value%20number%20from%20the%20ComposeNext%3A%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%20style%3D%22width%3A%20616px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F168455i6560D7FF528B2C36%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%227-updateMainList.png%22%20title%3D%227-updateMainList.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWe're%20almost%20at%20the%20end%20now%20and%20the%20next%20action%20is%20to%20add%20a%20Compose%20and%20add%201%20to%20the%20ComposeNext%20value.%20So%20for%20this%20we%20use%20an%20expression%20of%20add%20(outputs('ComposeNext')%2C1).%20Finally%20we%20use%20the%20output%20of%20that%20to%20update%20the%20Increment%20list%3A%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%20style%3D%22width%3A%20905px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F168456i07654293F00DC9F1%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%228-Compose-UpdateIncrement.png%22%20title%3D%228-Compose-UpdateIncrement.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhatever%20value%20you%20have%20in%20the%20Increment%20list%20the%20flow%20will%20check%20that%20and%20apply%20the%20T%20and%20the%20correct%20number%20of%20zeros%20and%20then%20the%20increment%20value.%20It%20will%20take%20just%202%20or%203%20seconds%20to%20do%20this%20and%20to%20update%20the%20main%20list%20and%20the%20increment%20list.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20took%20far%20longer%20to%20write%20out%20and%20do%20the%20screenshots%20than%20it%20took%20to%20prepare%20the%20SharePoint%20lists%20and%20do%20the%20flow!!%26nbsp%3B%3CIMG%20class%3D%22lia-deferred-image%20lia-image-emoji%22%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Fhtml%2Fimages%2Femoticons%2Fsmile_40x40.gif%22%20alt%3D%22%3Asmile%3A%22%20title%3D%22%3Asmile%3A%22%20%2F%3E%20%26nbsp%3BDo%20come%20back%20with%20questions%20about%20any%20of%20this.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERob%3CBR%20%2F%3ELos%20Gallardos%3C%2FP%3E%3CP%3EMicrosoft%20Power%20Automate%20Community%20Super%20User%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1234791%22%20slang%3D%22en-US%22%3ERe%3A%20Auto-Increment%20Number%20Field%20in%20a%20SharePoint%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1234791%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F539288%22%20target%3D%22_blank%22%3E%40Ninjak%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3ECan%20you%20please%20tell%20me%20the%20column%20type%20of%20the%20NextValue%20field%20in%20Increment%20List%3F%3C%2FP%3E%3CP%3EAlso%20I%20would%20like%20to%20know%20the%20Value%20to%20be%20given%20in%20Initialize%20Variable%20control%20for%20the%20varIncrementNumber.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1235363%22%20slang%3D%22en-US%22%3ERe%3A%20Auto-Increment%20Number%20Field%20in%20a%20SharePoint%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1235363%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F585303%22%20target%3D%22_blank%22%3E%40PriyankaJagarapu%3C%2FA%3E%26nbsp%3B%20I%20think%20your%20question%20might%20have%20been%20to%20me%20rather%20than%20the%20original%20poster.%20NextValue%20is%20a%20number%20column%20with%20decimal%20places%20set%20to%200.%20There's%20no%20initial%20value%20for%20the%20varIncrementValue%20so%20just%20leave%20it%20empty.%20All%20you're%20doing%20with%20that%20is%20to%20create%20the%20variable%20and%20it%20gets%20populated%20later%20in%20the%20flow.%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%233366FF%22%3EMicrosoft%20Power%20Automate%20Community%20User%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1335243%22%20slang%3D%22en-US%22%3ERe%3A%20Auto-Increment%20Number%20Field%20in%20a%20SharePoint%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1335243%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F174092%22%20target%3D%22_blank%22%3E%40Rob%20Elliott%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%3C%2FP%3E%3CP%3EThanks%20for%20this%2C%20it%20looks%20almost%20exactly%20like%20what%20I%20need%20to%20do%20with%20one%20exception.%26nbsp%3B%20I'm%20doing%20this%20for%20document%20numbers%20for%20projects.%26nbsp%3B%20So%20my%20increment%20list%20is%20my%20list%20of%20projects.%26nbsp%3B%20I%20have%20a%20next%20number%20column%20in%20the%20list.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%2C%20before%20I%20create%20the%20string%2C%20I%20need%20to%20get%20the%20next%20value%20for%20the%20specific%20project.%26nbsp%3B%20What%20action%20would%20I%20need%20to%20add%20(and%20where)%20in%20order%20to%20achieve%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%3Bm%20most%20grateful%20for%20your%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGreg%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1405511%22%20slang%3D%22en-US%22%3ERe%3A%20Auto-Increment%20Number%20Field%20in%20a%20SharePoint%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1405511%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20morning%2C%3C%2FP%3E%3CP%3EI%20recently%20built%20this%20flow%20for%20creating%20lot%20numbers%20at%20our%20organization%20and%20ran%20into%20an%20issue%20where%20if%202%20items%20are%20added%20very%20quickly%20then%20the%20same%20number%20is%20issued%20for%20both%20records.%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F174092%22%20target%3D%22_blank%22%3E%40Rob%20Elliott%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1405533%22%20slang%3D%22en-US%22%3ERe%3A%20Auto-Increment%20Number%20Field%20in%20a%20SharePoint%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1405533%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F653439%22%20target%3D%22_blank%22%3E%40mcaleer1%3C%2FA%3E%26nbsp%3Byes%2C%20we've%20also%20run%20into%20the%20same%20problem.%20I'm%20still%20trying%20to%20work%20on%20a%20way%20to%20avoid%20this%20issue.%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%20Use%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1405570%22%20slang%3D%22en-US%22%3ERe%3A%20Auto-Increment%20Number%20Field%20in%20a%20SharePoint%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1405570%22%20slang%3D%22en-US%22%3E%3CP%3EActually%20I%20just%20figured%20it%20out!%20If%20you%20select%20settings%20for%20'When%20an%20item%20is%20Created'%20you%20can%20turn%20on%26nbsp%3B%3CSPAN%3EConcurrency%20Control%20and%20adjust%20the%20slider%20to%20only%20allow%20one%20item%20to%20process%20at%20a%20time.%3C%2FSPAN%3E%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F174092%22%20target%3D%22_blank%22%3E%40Rob%20Elliott%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1405707%22%20slang%3D%22en-US%22%3ERe%3A%20Auto-Increment%20Number%20Field%20in%20a%20SharePoint%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1405707%22%20slang%3D%22en-US%22%3E%3CP%3EI%20also%20wanted%20to%20say%20thank%20you%20for%20the%20detailed%20instructions%20on%20how%20to%20build%20this%20flow.%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F174092%22%20target%3D%22_blank%22%3E%40Rob%20Elliott%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1405718%22%20slang%3D%22en-US%22%3ERe%3A%20Auto-Increment%20Number%20Field%20in%20a%20SharePoint%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1405718%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F653439%22%20target%3D%22_blank%22%3E%40mcaleer1%3C%2FA%3E%26nbsp%3BI%20could%20be%20wrong%20but%20I%20thought%20I'd%20tried%20it%20with%20concurrency%20control%20set%20to%201%2C%20but%20perhaps%20I%20didn't%20so%20thanks%20for%20flagging%20it%20up.%20I'll%20take%20another%20look%20at%20my%20flows.%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%23333399%22%3EMicrosoft%20Power%20Automate%20Community%20Super%20User%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1528972%22%20slang%3D%22en-US%22%3ERe%3A%20Auto-Increment%20Number%20Field%20in%20a%20SharePoint%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1528972%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F174092%22%20target%3D%22_blank%22%3E%40Rob%20Elliott%3C%2FA%3E%26nbsp%3BI%20have%20tried%20to%20set%20this%20up%20for%20a%20while%2C%20tried%203%20times%20and%20done%20everything%20exactly%2C%20but%20it%20does%20not%20work.%3C%2FP%3E%3CP%3EI%20dont%20get%20an%20error%20when%20I%20try%20to%20test%20the%20workflow%2C%20it%20just%20does%20not%20work....%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOne%20thing%20is%20however%20different.%20Every%20time%20I%20set%20up%20the%20composenext%2C%20I%20get%20an%20additional%20step%2C%20without%20being%20able%20to%20remowe%20it.%20I%20cant%20see%20if%20you%20have%20that%20on%20your%20end%20as%20well%2C%20since%20you%20cut%20of%20the%20image%20%3A(%3C%2Fimg%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22JesperTC_0-1594988650216.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F206035i3AF1A919786744FF%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22JesperTC_0-1594988650216.png%22%20alt%3D%22JesperTC_0-1594988650216.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1565256%22%20slang%3D%22en-US%22%3ERe%3A%20Auto-Increment%20Number%20Field%20in%20a%20SharePoint%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1565256%22%20slang%3D%22en-US%22%3EIs%20this%20available%20as%20a%20template%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1565329%22%20slang%3D%22en-US%22%3ERe%3A%20Auto-Increment%20Number%20Field%20in%20a%20SharePoint%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1565329%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F373230%22%20target%3D%22_blank%22%3E%40Asad_Yousaf%3C%2FA%3E%26nbsp%3Bno%20you'd%20need%20to%20build%20it%20from%20blank.%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%23333399%22%3EMicrosoft%20Power%20Automate%20Community%20Super%20User%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1700583%22%20slang%3D%22en-US%22%3ERe%3A%20Auto-Increment%20Number%20Field%20in%20a%20SharePoint%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1700583%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F174092%22%20target%3D%22_blank%22%3E%40Rob%20Elliott%3C%2FA%3E%26nbsp%3B%20-%20I%20wanted%20to%20thank%20you%20for%20this!%26nbsp%3B%20This%20is%20excellent!%26nbsp%3B%20I%20spent%20a%20few%20hours%20trying%20to%20get%20a%20similar%20situation%20to%20this%20to%20work%20and%20I%20couldn't%20get%20the%20variables%20to%20increment%20by%201.%26nbsp%3B%20Just%20wanted%20to%20give%20you%20a%20virtual%20high%20five%26nbsp%3B%20%3A)%3C%2Fimg%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1783529%22%20slang%3D%22en-US%22%3ERe%3A%20Auto-Increment%20Number%20Field%20in%20a%20SharePoint%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1783529%22%20slang%3D%22en-US%22%3E%3CP%3Ehow%20can%20I%20do%20that%20%2C%3C%2FP%3E%3CP%3Ei%20want%20a%20formula%20to%20add%2B1%20each%20time%20I%20adda%20task%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F43161%22%20target%3D%22_blank%22%3E%40Steve%20Knutson%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1918911%22%20slang%3D%22en-US%22%3ERe%3A%20Auto-Increment%20Number%20Field%20in%20a%20SharePoint%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1918911%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F539288%22%20target%3D%22_blank%22%3E%40Ninjak%3C%2FA%3Efor%20SharePoint%20server%2C%20you%20can't%20used%20Microsoft%20automate%20without%20using%20gateway.%3C%2FP%3E%3CP%3EInstead%2C%20you%20should%20use%20JSOM%20or%20SharePoint%20Designer!%3C%2FP%3E%3CUL%3E%3CLI%3E%3CFONT%20size%3D%224%22%3E%3CA%20href%3D%22https%3A%2F%2Fspgeeks.devoworx.com%2Fid-field-in-calaulated-column-sharepoint%2F%22%20target%3D%22_blank%22%20rel%3D%22bookmark%20noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3EUse%20SharePoint%20ID%20Field%20in%20Calculated%20Column%3C%2FA%3E%3C%2FFONT%3E%3C%2FLI%3E%3CLI%3E%3CA%20href%3D%22https%3A%2F%2Fspgeeks.devoworx.com%2Fauto-serial-number-new-form-sharepoint%2F%22%20target%3D%22_blank%22%20rel%3D%22bookmark%20noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3E%3CFONT%20size%3D%224%22%3EAuto%20Serial%20Number%20in%20SharePoint%20New%20Form%20using%20JSOM%3C%2FFONT%3E%3C%2FA%3E%3C%2FLI%3E%3C%2FUL%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

I need to create an auto-increment field with a standard amount of digits. The format needs to be like this T000001, T000002, T000003, ......T000010, etc (7 Digits). When it gets to the 10th record it should not do this T0000010 (8 digits).

I tried using the popular, often referenced solution (link below). This method does not work as I hoped. If you put it in a format like this T1000, when it gets to the 10th item it does this T100010 rather than T10010.

Here is the popular solution I already tried: https://www.epmpartners.com.au/blog/how-to-create-an-auto-incrementing-number-field-in-a-sharepoint-...

16 Replies
Highlighted

@Ninjak have you tried using a Calculated field?

In your list or library, the ID column (a system column) will increment each time an item is added. You can use a calculated column to create a prefix and append the ID  e.g.  T000+ID 

https://support.office.com/en-us/article/examples-of-common-formulas-in-sharepoint-lists-d81f5f21-2b...

 

Highlighted
Best Response confirmed by Steve Knutson (MVP)
Solution

Hi Keenan (@Ninjak), this complex auto-increment can be achieved relatively easily using 2 lists in SharePoint and a flow in Power Automate. The result will look like this in the main list which I've called Crop Harvest:

 

0-SP-List-Result.png

 

You will need a second list (which I've called Increment) to hold the next value. In the result above I manually changed the value to show the difference based on the number, and I've manually changed it back to 9 for now but  you will see that it will auto-increment in the flow. This second list will only ever have 1 item which is the next value to be used:

 

0-SP-IncrementList.png

 

In the flow the trigger I've used is When an item is created in the SharePoint main list.

 

Next we get the 1 item from the Increment list, and the next action is to initialize a string variable which I've named varIncrementNumber:

 

1-Trigger-getItems-initVariable.png

 

The next action to add is a Compose control which I've named ComposeNext and you select Next value from the dynamic content box on the right. What you will see when you do this is that it wraps itself in an apply to each control. It does this because although there is only 1 item in the Increment list there could potentially be lots so it adds the apply to each. That's fine, it's expected and you don't try to delete it but just leave it there.

 

The next action is a condition. For this first condition we are only going to test if the output of the previous compose action is less than 10 (we'll do the others shortly):

 

2-apply-composeNext-Condition1-9.png

 

If it IS less than 10 then in the If yes green channel we add an Append to string variable and in the value type T00000 immediately followed by the output of the ComposeNext selected from the dynamic content box on the right:

 

3-1to9-Append.png

 

If the ComposeNext value isn't less than 10 then in the red If no channel you add another condition. In the left hand box select the Output from the ComposeNext from the dynamic content, select is greater than or equal to, and then in the right hand box type 10. Then add another row and do the same but make it less than 100.

 

Again, you'll get a green if yes channel and a red if no channel. In the green channel, as before, add an append to string variable action and this time in the value add T0000 and then the output of the ComposeNext:

 

5-10to99Append.png

 

In the red If no channel add another condition as before but make it greater than or equal to 100 and less than 1000.

 

You keep doing that - changing the number of leading zeros each time of course - until you get to the last condition which is if the output of the ComposeNext is greater than or equal to 10000 and less than 100000 then you will add an Append to string variable in both channels. In the green If yes channel it will be T0 plus the Output and in the red if no channel it will be just T plus the Output.

 

6-condition10000-99999.png

 

So we've got the right value with the T plus correct leading zeros in our variable now. The next action is still inside the apply to each but not inside any of the conditions. The action is a SharePoint Update item action and in the text column where you are storing the increment text value you select the string variable varIncrementNumber. You'll see I've also stored the actual numeric value number from the ComposeNext:

 

7-updateMainList.png

 

We're almost at the end now and the next action is to add a Compose and add 1 to the ComposeNext value. So for this we use an expression of add (outputs('ComposeNext'),1). Finally we use the output of that to update the Increment list:

 

8-Compose-UpdateIncrement.png

 

Whatever value you have in the Increment list the flow will check that and apply the T and the correct number of zeros and then the increment value. It will take just 2 or 3 seconds to do this and to update the main list and the increment list.

 

That took far longer to write out and do the screenshots than it took to prepare the SharePoint lists and do the flow!! :smile:  Do come back with questions about any of this.

 

Rob
Los Gallardos

Microsoft Power Automate Community Super User

Highlighted

@Ninjak 

Hi,

Can you please tell me the column type of the NextValue field in Increment List?

Also I would like to know the Value to be given in Initialize Variable control for the varIncrementNumber.

 

 

Highlighted

@PriyankaJagarapu  I think your question might have been to me rather than the original poster. NextValue is a number column with decimal places set to 0. There's no initial value for the varIncrementValue so just leave it empty. All you're doing with that is to create the variable and it gets populated later in the flow.

 

Rob
Los Gallardos
Microsoft Power Automate Community User

Highlighted

@Rob Elliott 

Hi

Thanks for this, it looks almost exactly like what I need to do with one exception.  I'm doing this for document numbers for projects.  So my increment list is my list of projects.  I have a next number column in the list.

 

So, before I create the string, I need to get the next value for the specific project.  What action would I need to add (and where) in order to achieve this?

 

I;m most grateful for your help.

 

Best

 

Greg

Highlighted

Good morning,

I recently built this flow for creating lot numbers at our organization and ran into an issue where if 2 items are added very quickly then the same number is issued for both records.@Rob Elliott 

Highlighted

@mcaleer1 yes, we've also run into the same problem. I'm still trying to work on a way to avoid this issue.

 

Rob
Los Gallardos
Microsoft Power Automate Community Super Use

Highlighted

Actually I just figured it out! If you select settings for 'When an item is Created' you can turn on Concurrency Control and adjust the slider to only allow one item to process at a time. @Rob Elliott 

Highlighted

I also wanted to say thank you for the detailed instructions on how to build this flow. @Rob Elliott 

Highlighted

@mcaleer1 I could be wrong but I thought I'd tried it with concurrency control set to 1, but perhaps I didn't so thanks for flagging it up. I'll take another look at my flows.

 

Rob
Los Gallardos
Microsoft Power Automate Community Super User

Highlighted

@Rob Elliott I have tried to set this up for a while, tried 3 times and done everything exactly, but it does not work.

I dont get an error when I try to test the workflow, it just does not work....

 

One thing is however different. Every time I set up the composenext, I get an additional step, without being able to remowe it. I cant see if you have that on your end as well, since you cut of the image :(

 

JesperTC_0-1594988650216.png

 

Highlighted
Is this available as a template?
Highlighted

@Asad_Yousaf no you'd need to build it from blank.

 

Rob
Los Gallardos
Microsoft Power Automate Community Super User

Highlighted

@Rob Elliott  - I wanted to thank you for this!  This is excellent!  I spent a few hours trying to get a similar situation to this to work and I couldn't get the variables to increment by 1.  Just wanted to give you a virtual high five  :)

Highlighted

how can I do that ,

i want a formula to add+1 each time I adda task@Steve Knutson 

Highlighted

@Ninjakfor SharePoint server, you can't used Microsoft automate without using gateway.

Instead, you should use JSOM or SharePoint Designer!