Calculated Column On Selection

Edd_Keogh OK, so to do this you will have your main list, my list is called A90. The Letter column is a choice column. The LetterID column is just a single line of text.

But you will also have a separate list to hold the current ID of each letter, mine is called A90ID. The Title column will hold the letter. Set the CurrentID column as a number with zero decimal places and an default value of 0.

The flow isn't difficult and the overall flow looks like this:

1. The trigger is "when an item is created" and the first action is a "get item" to get the details of the item created.

2. Next, initialize 2 variables: a string variable named varLetter where the initial value is Letter Value selected from the dynamic content box, and a float variable named varID with the Value field empty:

3. Next, add a "get items" action. We only want to bring back the same letter from the A90ID list as was used when the item was created in the A90 list, so in the Filter Query field enter Title eq '' and between the apostrophes select Letter Value from the get item section of the dynamic content box.

4. Even though we are only bringing back 1 item from the A90ID list we still need an apply to each so add that and select value from the dynamic content box. Next, inside the apply to each add a compose action and select CurrentID from the get items section of the dynamic content box.

5.  Below that add another compose which will add 1 to the CurrentID. So add the expression

add(outputs('GetCurrentID'),1)

6. Still inside the apply to each add a "set variable" action, select varID and for the Value field select the outputs of the Add1 compose. The last action inside the apply to each is an "update item" action to set the new value of the CurrentID column to the varID value.

7. We want to have the result in the format a001 if the number is less than 10, a011 if it's between 10 and 99 and a100 if it's 100 or more. So outside the apply to each click on new strep at the bottom of the flow and a condition of  varID is less than 10

 8. In the green if yes channel add an "update item" action to update the A90 list LetterID column with the varLetter value, 2 zeros and then the varID value using the expression :

concat(variables('varLetter'),'00',variables('varID'))

9. In the red if no channel add another condition that will check if the varID value is between 10 and 99. In the green if yes channel add an update item action the same as before but this time the expression is concat(variables('varLetter'),'0',variables('varID'))

Finally, in the red if no channel (i.e the varID value is 100 or more) add another update item action and this time the expression is concat(variables('varLetter'),variables('varID'))

One advantage of this method is that if you add another letter later, D for example, to the A90 letter choice column and to A90ID list you won't need to change the flow.

Hope that helps.

Rob
Los Gallardos
Microsoft Power Automate Community Super User