cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AnkitShah55
Advocate III
Advocate III

Convert custom text to custom number

Hi,

I have a SP list of around 11k items(A datasource for PowerApps Canvas). There is a ShiftEntry column(Single line text) containing name of shift code (A, B, G and C).

There is a requirement to have number column with respect to shift code, for the same have created new column ShiftNumEntry(Number column)

 

Now, need a manual flow which updates all item's ShiftNumEntry column as per ShiftEntry... A=1, B=2, G=3 and C=4

 

This is one time process as in future ShiftNumEntry column will automatically have its value from PowerApps form.

2 ACCEPTED SOLUTIONS

Accepted Solutions
RobElliott
Super User
Super User

@AnkitShah55 you could use nested conditions but as you have a big list that might possibly run quite slowly. So instead add a Switch control and 4 cases, 1 for each shift. Then add a SharePoint Update item action in each case. On the settings of the Get items make sure you turn pagination on and set the threshold to something like 12000.
1-trigger-Get.png

 

1a-threshold.pngWhen you add the Switch control that will wrap itself in an Apply to each.

2-switch-Update.png

 The result:
0-SP-Result.png

Rob
Los Gallardos
If I've answered your question or solved your problem, please mark this question as answered. This helps others who have the same question find a solution quickly via the forum search. If you liked my response, please consider giving it a thumbs up. Thanks.

View solution in original post

Hi!

And the number shall be stored in a new column that has not been added yet and will be called 'ShiftNumEntry' right?

If so, here you are the steps, almost identical to what Rob already shared. This is my Test SP List...

 

Flow_ShiftSPList.png

THis is the way I add the expression I shared in my previous posts: just place the cursor on 'ShiftNumEntry' input from 'Update item', click on 'Add dynamic content', on the new menu select Expression, copy-paste

 

if(equals(item()?['ShiftEntry'],'A'),1,if(equals(item()?['ShiftEntry'],'B'),2,if(equals(item()?['ShiftEntry'],'G'),3,if(equals(item()?['ShiftEntry'],'C'),4,0))))

 

... and finally press 'OK'

Flow_ShiftFlowLogic.png

 

This is the way my Flow design looks after adding the expression:

Flow_ShiftFlowLogicFinal.png

 

Please remember to activate PAgination on 'Get Items' if more than 5,000. Nice explanation on how to activate it in this post:

https://alextofan.com/2019/08/22/how-to-get-more-than-5000-item-from-sharepoint-online-in-flow/

Hope this helps

 



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



View solution in original post

9 REPLIES 9
RobElliott
Super User
Super User

@AnkitShah55 you could use nested conditions but as you have a big list that might possibly run quite slowly. So instead add a Switch control and 4 cases, 1 for each shift. Then add a SharePoint Update item action in each case. On the settings of the Get items make sure you turn pagination on and set the threshold to something like 12000.
1-trigger-Get.png

 

1a-threshold.pngWhen you add the Switch control that will wrap itself in an Apply to each.

2-switch-Update.png

 The result:
0-SP-Result.png

Rob
Los Gallardos
If I've answered your question or solved your problem, please mark this question as answered. This helps others who have the same question find a solution quickly via the forum search. If you liked my response, please consider giving it a thumbs up. Thanks.

efialttes
Super User
Super User

Hi!
Your one time process to update the new column can be achieved by means of an 'Apply to each'. According to this official announcement, current 'Apply to each' limits are broad enough to iterate over your 11k items:
https://flow.microsoft.com/es-es/blog/gateway-ha-increased-apply-to-each/

So I would suggest the following strategy:
-manual trigger
-get items. Please remember to activate pagination so you can get the 11k items, as explained here:

https://alextofan.com/2019/08/22/how-to-get-more-than-5000-item-from-sharepoint-online-in-flow/
-apply to each, using get items output as its input

Now, inside the apply to each:
-calculate new column value for current iteration item.

-Update current iteration item on your SP

 

In order to calculate new column value, as @RobElliott suggest, you can add a Switch inside your Apply to Each. Or, you can directly calculate the value in one single expression. Assuming your ShiftEntry column(Single line text) is called 'ShiftEntry' ad the new column is of type Number:

 

if(equals(item()?['ShiftEntry'],'A'),1,if(equals(item()?['ShiftEntry'],'B'),2,if(equals(item()?['ShiftEntry'],'G'),3,if(equals(item()?['ShiftEntry'],'C'),4,0))))

You just need to assign this expression on 'Update item' input for the new column

 

Hope this helps



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



@efialttes I always forget about putting it into a single expression. Good solution.

Rob
Los Gallardos

@efialttes 

Can you visualize flow process please?

 

@RobElliott Thanks !

Sitting in front of my laptop, give me just some minutes.
BTW can you share a screenshot of your Sharepoint List column Names?
Thanx!


Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



@efialttes Sure

 

Here it is...

 

Annotation 2020-03-07 224410.png

Hi!

And the number shall be stored in a new column that has not been added yet and will be called 'ShiftNumEntry' right?

If so, here you are the steps, almost identical to what Rob already shared. This is my Test SP List...

 

Flow_ShiftSPList.png

THis is the way I add the expression I shared in my previous posts: just place the cursor on 'ShiftNumEntry' input from 'Update item', click on 'Add dynamic content', on the new menu select Expression, copy-paste

 

if(equals(item()?['ShiftEntry'],'A'),1,if(equals(item()?['ShiftEntry'],'B'),2,if(equals(item()?['ShiftEntry'],'G'),3,if(equals(item()?['ShiftEntry'],'C'),4,0))))

 

... and finally press 'OK'

Flow_ShiftFlowLogic.png

 

This is the way my Flow design looks after adding the expression:

Flow_ShiftFlowLogicFinal.png

 

Please remember to activate PAgination on 'Get Items' if more than 5,000. Nice explanation on how to activate it in this post:

https://alextofan.com/2019/08/22/how-to-get-more-than-5000-item-from-sharepoint-online-in-flow/

Hope this helps

 



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Oooops!

Two more comments I forgot to mention...

 

1.- I guess processing 11,000 items will take some time, not sure if activating Concurrency in your Apply to Each can speed the process. You will find a great explanation on how to do it here:

https://www.magnetismsolutions.com/blog/satyvirjasra/2019/10/22/improving-microsoft-flow-runtime-usi...

 

2.- This is my SP Test list after flow execution:

Flow_ShiftSPListFinal.png

Hope this helps



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



@AnkitShah55 

I've seen you marked one of my answers as the Solution for the topic. Thanks for your kindness!

 

Please note you can mark more than one answer as Solution, even if they come from different users.

In my opinion @RobElliott deserves most of the credit for the solution, since 95% of the design comes from him. I just suggested a little optimization

Thanx for making this community so great!



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Power Automate Designer Feedback_carousel.jpg

Help make Flow Design easier

Are you new to designing flows? What is your biggest struggle with Power Automate Designer? Help us make it more user friendly!

Top Solution Authors
Top Kudoed Authors
Users online (1,381)