Convert custom text to custom number

[Anonymous]

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.

[RobElliott · Accepted solution]

@Anonymous 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.

When you add the Switch control that will wrap itself in an Apply to each.

 The result:

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]

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

[RobElliott]

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

Rob
Los Gallardos

[Anonymous]

@efialttes 

Can you visualize flow process please?

@RobElliott Thanks !

[efialttes]

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

[Anonymous]

@efialttes Sure

Here it is...

[efialttes · Accepted solution]

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...

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'

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

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

[efialttes]

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:

Hope this helps

[efialttes]

@Anonymous 

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!