Answered

NEED TO ADD FORM VALUES and POST TOTAL in RESULTING DOCUMENT

(0) Share

Report

Posted on by oemmanuelli

Hi All:

We created a form for our Medical practice. Its a basic questionnaire we want to use a proof of concept. Its supposed to help us reduce staff involvement for file creation. The flow currently pulls data from a form, fills in blanks into a word doc which is then emailed back to the staff.
the questionnaire has a section that requires some simple math. This in particular is just to add 7 values. We havent found an effective way to get those added and posted in the the appropriate field in the final word doc.

We've tried with a formula on a separate column in the XL sheet. but the GET ROW doesnt seem to know how to get the data (obviously its me who doesnt know). We're having issue with the KEY COLUMN, KEY DATA match.

All responses (7)

Answers (2)

oemmanuelli 16 on at

Like (0)

Report

Re: NEED TO ADD FORM VALUES and POST TOTAL in RESULTING DOCUMENT

Hi Rob:
Thanks for your detailed response. Ed had indeed given me the answer. I however continued to make the same typo on my formula. I do like your approach as it does breakdown the "formula" into a more visual fashion.
Thanks so much!
Verified answer

RobElliott 10,118 Super User 2025 Season 1 on at

Like (2)

Report

Re: NEED TO ADD FORM VALUES and POST TOTAL in RESULTING DOCUMENT

@oemmanuelli adding to what @edgonzales has said and in case it helps you at all, I've just done some screenshots of how you could do it without Excel. I find de-bugging expressions can be easier if I break everything down into smaller pieces so we've got a series of Compose controls and enter add() expressions to them until we get the grand total of all 7 fields from the form:

This is the form:

The flow triggers when the form is submitted and we get the response details. Next, in the Expression tab (circled) we enter an add() expression for fields 1 and 2 and wrap them in int(). When you are entering the expression you can still select the fields from your form from the dynamic content tab.

We then build up the other Compose controls, ending up with a Compose for the Grand Total:

In my example I've sent an email rather than create a document but you can tailor the final action to whatever you want. You'll see that the grand total is added from the output of the final Compose control.

The resulting email is:

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.
oemmanuelli 16 on at

Like (0)

Report

Re: NEED TO ADD FORM VALUES and POST TOTAL in RESULTING DOCUMENT

I've read those, i think im a little dense with this. But i dont find those examples clear enough. I've tried the "add()" on that line so it fills the TOTAL field. Thats what resulted in it "printing" the formula itself on my document, albeit with each variable value.

And i still cant get it to read the XL fields properly. There is very little in the way detail howto's for something that i would think is so common!

Thanks for listening.
Ed Gonzales 4,531 Most Valuable Professional on at

Like (0)

Report

Re: NEED TO ADD FORM VALUES and POST TOTAL in RESULTING DOCUMENT

@oemmanuelli

Hey there. You'll want to put the expression in the 'expression' box (right next to where it says "Dynamic Content").

Here's a blog article showing the details of how to add expressions:

Use expressions in flow actions

We're getting closer 🙂 Keep at it.

-Ed
oemmanuelli 16 on at

Like (0)

Report
Re: NEED TO ADD FORM VALUES and POST TOTAL in RESULTING DOCUMENT

The "Key Column" is asking where you would like to look, to define the unique value about the row you want. If available, you can select "Row ID" column. --ROW "ID" was selected
"Key Data" is asking 'What value should I be looking for in the 'Key Column' to find the row you want?' - For this, you should be able to select the dynamic value of the "Row ID" from when you added the Forms information to the spreadsheet. --HERE I CANT FIND "ID" when trying to MATCH it. So FAR this is the part where it gets lost.
Once you've got the right row selected, your "Total" column should show as a dynamic value. If it doesn't, make sure it is included as part of the table in Excel (sometimes, when we add columns to a worksheet, they are *next* to the table rather than *in* it). Also make sure that the math is actually happening in Excel (the totals look correct). The values LOOK CORRECT IN EXCEL but they're not transferring to the "TOTAL" field
oemmanuelli 16 on at

Like (0)

Report

Re: NEED TO ADD FORM VALUES and POST TOTAL in RESULTING DOCUMENT

I greately appreciate the suggestions. I tried the "add(b,c)" chaining up all values as suggested but im just getting that exact string as my resulting item. not the addition.
as in the end result in the document is "int(add(add(int(3),int(3)),add(int(3),int(3)),add(int(3),add(int(3),int(3)))"
It may be missing one or two items.

In any event, where am I supposed to place the "add()" function? Inside the field name? I am using the expression in my TOTAL field. but its not adding any of the values, just displaying the expression when the flow is done.
Verified answer

Ed Gonzales 4,531 Most Valuable Professional on at

Like (1)

Report
Re: NEED TO ADD FORM VALUES and POST TOTAL in RESULTING DOCUMENT

@oemmanuelli

Hi there...so, a couple of ways to handle this depending on your comfort level. I think you're on a good path having Excel do the math, but I'll outline an alternative just in case:

The first option is to have the flow trigger when the form response is submitted, then grab the 7 fields with the numbers and add them up to spit out on the Word template (more on that later).

The catch with this is that the add() function can only handle two numbers at a time. Super weird, I know. So, you'd have to group the numbers in pairs and add them up two at a time until you have a final sum.

Also, Forms doesn't really have a "Number" data type, so you might want to convert it to a number inside the flow before trying math with it. Depending on the data you expect, either int() or float() would work when wrapped around your field value. (Use float if you expect decimals)

Since we're limited to 2 'summands' and you have 7 values (I'll use letters in the example: A, B, C, D, E, F, G), you would use something like this: add(int(A), add(int(B), add(int(C), add(int(D)...and so on until you have a bunch of )))))) at the end. It's ugly, but would be quicker and more stable that going to Excel, probably.

Second option (easier to deal with but maybe a little slower) - Do the thing exactly as you have it, but just make the "Get row" part work.

Each row needs to be unique in some form, right? That way your flow knows which row specifically it is that you want to look at.

That's why most tables will have a (sort-of) hidden "Row ID" column.

The "Key Column" is asking where you would like to look, to define the unique value about the row you want. If available, you can select "Row ID" column.

"Key Data" is asking 'What value should I be looking for in the 'Key Column' to find the row you want?' - For this, you should be able to select the dynamic value of the "Row ID" from when you added the Forms information to the spreadsheet.

Once you've got the right row selected, your "Total" column should show as a dynamic value. If it doesn't, make sure it is included as part of the table in Excel (sometimes, when we add columns to a worksheet, they are *next* to the table rather than *in* it). Also make sure that the math is actually happening in Excel (the totals look correct).

Keep us posted.

-Ed

If you liked this reply, please give it a thumbs up! If this reply has answered your question or resolved your challenge, please consider marking it as a Solution. This helps other users find it more easily via search.