0

So I have a SharePoint List that clients need to fill out.

I need a column that autopopulates an incrementing ID for each Program Office.

I attached an image of how it should look. Can I do this as a caluclated column? I see alot of stuff to do a Power Automate, which i never used. But with instructions im sure i can follow easily. Thanks for the help! :) enter image description here

1 Answer 1

0

Here is a simple Power Automate that might work for your scenario.

Assumptions

  1. Incrementing ID will be 4-char long padded with leading zeros (0s). This can be adjusted by initializing a string variable in Power Automate with the required length
  2. The existing IncrementingID values will not be changed manually.

Power Automate Logic

  1. Create an Automated Power Automate When an item is created

  2. Initialize a string variable with only zeros like 0000

  3. Get the latest IncrementingID for the ProgramOffice that the item has been created for. Use ODATA Query - see expressions below

  4. If a previous IncrementingID exists, extract number part and increment by 1. Otherwise, the IncrementingID will be the first for that ProgamOffice and will start with 1 like 0001

  5. Using Power Automate expressions, format the new IncrementingID

  6. Update the item in the SharePoint list with the newly created IncrementingID

Power Automate Actions and Expressions enter image description here

Detail-1

enter image description here

Detail-2

enter image description here

Detail-3

enter image description here

first(outputs('Get_items')?['body/value'])?['IncrementingID']

Detail-4

enter image description here

length(trim(string(outputs('PrevIncrementalID')))) add(int(trim(replace(string(outputs('PrevIncrementalID')), '0',''))),1) concat(variables('varNewNumber'), string(outputs('NewIntID'))) substring(outputs('ConcatInitial_and_newInt'), length(string(outputs('NewIntID'))))

Detail-5

enter image description here

Final Output

enter image description here

10
  • Thank you so much! I’m trying to following along with this. I’m getting g an error message “Unable to process template language expressions in action ‘NewIntID’ inputs at line ‘0’ and column ‘0’. The template language function ‘Int’ was invoked with a parameter that is not valid. The value cannot be converted to the target type.’
    – Jesse Y
    Commented May 6 at 20:23
  • Could you check value of PrevIncrementalID? It must be empty or a valid int value for [NewIntID] step to work. Before Condition step, you could use compose to output PrevIncrementalID. The error message is on this statement, add(int(trim(replace(string(outputs('PrevIncrementalID')), '0',''))),1) Commented May 6 at 21:17
  • Not really understanding. What do you mean by check the value? It’s the formula you said to put. first(outputs(‘Get_items’)?[‘body/value’])?[SequenceNumber’]. That’s my column name, Sequence Number.
    – Jesse Y
    Commented May 7 at 13:27
  • At what step are you getting this error? Based on what you've shared, int() function can't convert a string value into an integer value to increment by 1. So, wondering what you have in PrevIncrementalID column? Make sure it has 1) Empty/Null 2) an integer with padding 0s like 0004 Commented May 7 at 13:58
  • Also, first review the steps and expressions I've shared and let me know if you have any questions for me. Commented May 7 at 14:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.