How to copy the value of a sharepoint calculated column into another column type or alternatively use flow to add up choice columns containing numerical data.

[Victoria]

Hi. I need to get the contents of a SP list calculated col into a word doc template using Quick Parts. This is step one of setting up a flow to autgenerate reports from items added to a list. Word quick parts doesn't recognise calculated cols. Flow doesn't recognise my calculated col either. The calculated col in question is called 'Total Score' and adds up 6 sharepoint choice columns containing numbers.  

I've tried replacing the calculated column with a regular number column and getting flow to add up the choice columns, so I don't need the calculated column but I'm struggling with the formula. 

add(triggerBody()?['Q1Score'],triggerBody()?['Q2Score'])
I'm getting errors. Flow doesn't seem to be able to add up choice columns containing numerical values or am I missing something?

Is there a better way to copy the value of a calculated column into another list column, (which can then be recognised in Quick Parts) ? I need to get my column info into my report template somehow.

I'm relatively new to flow and I'm going in circles! Thanks for your help. 

[RobElliott]

Hi @Victoria a choice column with numbers as the choices doesn't save the result as a number and even converting it to an integer or float in Flow gives problems. You are presumably using the default SharePoint form, but if you've got PowerApps you can create a customized form which will allow you to do the addition and save it back to the SharePoint list so it can then be used in a flow or for some other purpose.

So I have 3 columns that are important here: Choice1, Choice2 and Addition (number type column). When you fire up PowerApps you'll get the dropdowns for the Choices (which I've renamed to ddChoice1 and ddChoice2) and a text box for the addition. Make sure the format of that is set to number - it should be already as it knows the SharePoint column is a number type. Then you can set the default value of the Addition field to ddChoice1.Selected.Value+ddChoice2.Selected.Value

When you create a new item in the SharePoint list or edit an existing one the value of the Addition field will be saved back to the Addition column. Whenever you change either the Choice1 or Choice2 dropdown selections the addition will be updated. Saving the form will save the new calculation back to the SharePoint list.

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.

0-SharePoint list1-PowerApps form2-Default value for addition field3- SharePoint list with addition

[v-lin-msft · Accepted solution]

Hi @Victoria,

Choice format in the column of each option is Object format field, and saved values will convert to string format.

So if we want to get in the Choice of value, can be achieved by the following statement:

First to pick up the numerical value of the string, and then through the function int () converts it into an integer, then add up them with the add () function.

add(int(triggerBody()?['Q1Score']['Value']),int(triggerBody()?['Q2Score']['Value']))

"Q1Score": {
"@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
"Id": 1,
"Value": "10"
},

"Q2Score": {
"@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
"Id": 1,
"Value": "20"
},

Please have a try. If there is any problem, please contact us.

Best Regards,

Community Support Team _ Lin Tu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Victoria]

That's perfect, thank you! Vicki

[Victoria]

Hi Lin Tu,

This works fine adding up two cols but I cannot get the formula to work when I add in more cols, Q3Score, Q4Score etc. I have 6 cols to add up and convert to text. I'm probably missing something basic in the formula. I am new to flow!

This works: add(int(triggerBody()?['Q1Score']['Value']),int(triggerBody()?['Q2Score']['Value']))

This doesn't: add(int(triggerBody()?['Q1Score']['Value']),int(triggerBody()?['Q2Score']['Value']),int(triggerBody()?['Q3Score']['Value']))

I'm not sure why. Any help would be gratefully received. Thanks Vicki

[v-lin-msft]

Hi @Victoria,

The add() function only supports adding two elements, so you want to add six columns, you need to two columns separately.

Best Regards,

Community Support Team _ Lin Tu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Victoria]

Thanks that's really helpful.

I have discovered a further issue. I find if I use the trigger 'when an item is created or modified' my flow is triggering itself in an endless loop, since I am updating items! I can't have that because it will burn thrrough my flow allowance. 

I can use 'when an item is created' but it's not ideal. The list data will be used to generate reports using a word doc and quick parts. Users are bound to need to update their scores or forget to save something and I need the reports generated to contain up to date data.

I have spoken to flow support but they say this is a limitation of flow but it seems so basic. All I want to do is get flow or sharepoint to add up 6 columns and have this data in a format for quick parts so I can use a word template. It all seems like pretty basic stuff. Am I'm missing something?

Thanks again for your help, Vicki

[RobElliott]

I'm still not quite sure why you didn't go with my PowerApps solution which works perfectly well.

Rob
Los Gallardos

[v-lin-msft]

Hi @Victoria,

About your problem, the trigger 'when an item is created or modified' my flow is triggering itself in an endless loop, you can refer to this post:

https://powerusers.microsoft.com/t5/Building-Flows/Sync-data-between-2-Sharepoint-lists-Inifinite-lo...

Best Regards,

Community Support Team _ Lin Tu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.