SOLVED

Auto Generate Incremental ID

New Contributor

Hello... I have just joined this community to seek some assistance.

 

(Sorry I am not able to upload images for reason, so I apologize in advance for the wordy message).

 

I am having problems with managing the creation of an AutoID generator to use with a SharePoint list.

 

I have an existing dataset of around 400 rows, each containing approx 20 columns of information (originally in Excel). Some columns are set with default values, some columns are lookup to other SharePoint lists to aid with the administration of new possible available values in the main list.

 

When I created the SharePoint list and configured all of the columns to my liking, and created several test items in the process, so of course my ID counter now starts at say 25, which therefore is not helpful for my use case. I prefer a convention such as XXX-0000.

 

To accomplish this I then set about creating a Flow to fix the Auto ID generator problem, following the steps in this video.

https://youtu.be/zFmVxlS8xX8

 

Everything tested out ok... which at this point was an empty list. So, I prepped the original source data from excel (manually creating the ref number) and painfully imported 400 rows of data to Sharepoint List using edit in Grid Mode. (upload would not work as it creates a new list and doesn't have all the bells and whistles for choice, people etc..).

 

Before uploading the data I disabled the flow.. .turned off, as I already had ID's for each row in the source and wanted this to only process New Items created in the UI.

 

So all the data was available in Sharepoint... all looking good. So now I go to turn on the Flow. And disaster struck.

Flow tried to update every item in the list! And screwed all the data I had spent hours uploading. 

 

The flow in summary (as per the video) goes like this

 

In summary this approach uses one list to define the AutoID convention with a prefix, number format, suffix and a unique title that describes what the numbering convention is intended for. 

 

The Flow then should trigger when a New Item is created, initializes two new attributes, fetches the starting number from the AutoId master list and stores in one variable, then another expression is used to format the number desired, also using info from the master AutoID list., e.g. prefix, '0000' suffix.

 

The next step is to then update the created ID content with the new reference generated that is formatted, and then finally update the master list to increment the master counter + 1.

 

All pretty logical.

 

So why on earth did this flow decide to start from the first item? Not only did it mess the numbering I had already predefined, but all of the columns that had default values in the list all reverted to the default values. Which I still have yet to repair.

 

Please if anyone has any ideas as to why this has happened, I would love to hear from you. The flow is still available, just turned off... and I am extremely nervous to switch it back on until I know what is causing this.

 

I have created a copy of the List and can reproduce the exact same problem. HELP!!

 

Alan

 

 

 

 

 

2 Replies
best response confirmed by APZee (New Contributor)
Solution

@APZee - This behavior is unfortunately by design and working as expected per Microsoft.  See here: Turn flows on or off in Power Automate - Power Automate | Microsoft Learn.  What we've recommended to users is before turning the flow back on, add a terminate step right after the trigger.  This will still run the flow, but quickly clear the queued events.  Best of luck!

Thank you for the feedback. I can confirm I had the same information provided by our MS Partner also. The workaround or solution I have implemented to avoid this is to add one additional column in the list, "DataImported" with a default value set as No (For new items being created) and set the previously uploaded data as DataImported=Yes. Then simply added a condition to Terminate any item that is flagged as DataImported=Yes. So, the workflow is only really running when new items are added, and the existing data is skipped.