Forum Discussion
Solved
Calculated Column On Selection
Good morning all,
I am trying to make calculated column but might need to be a power automate.
I have a column where you would select a, b, c or so on and I then want it to appear in another column as a001 or b001 or c001 then the next time a002 and then a003.
Is this possible? any help or advice would be brilliant.
Thanks,
Edd
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
3 Replies
Edd_Keogh this isn't possible with a calculated column but is do-able with a flow in Power Automate. But I wasn't sure exactly what the result is you are expecting: is it
- A) a001,a002,a003,a004,a005, b001,b002,c001,c002,c003, or
- B) a001,b002,a003,c004,b005,a006,a007,c008,a009, b010,a011 etc
A flow ran to produce the result in option B above, but it would need to be a different flow if it is option A you want. So if you could let us know and I'll post up the flow.
Rob
Los Gallardos
Microsoft Power Automate Community Super User- Hi Rob,
Thank you very much for the reply. It would be random answers could be a, b or c but it would need to follow the number of same letter so the first one could be A so A001 second one B so B001 then another B so B002, then C so C001 then A again so look back and would be A002.
Hope that makes sense!
Thanks,
EddEdd_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
