You are viewing limited content. For full access, please sign in.

Question

Question

Totaling up fields in collections and out

asked on November 25, 2019 Show version history

I am working on a form were I have 2 collections I need help with.

In Collection_1 I have a Quantity, Cost of Item, and Line Item total. I need Line Item Total to multiply Qty and Cost for their respective lines.

In Collection_2 I also have a discount field so that Line Item Total would need to me "(Qty x Cost) - Discount" How do I get these to work?

And at the end of each Collection I have a grand total for each, how can I get Collection 1 Grand Total to add together all the Collection Line Item Totals?

Any help would be lovely!

0 0

Answer

SELECTED ANSWER
replied on November 26, 2019 Show version history

The overall totals outside of the collections will be the easiest. All you need to do is click the Advanced tab for the field and use the SUM function on the target variable.

For example, =SUM(My_Variable)

The totals within the collections require a little bit more because you'll need to use the INDEX function to ensure you are only grabbing the value for the current row.

For example, =INDEX(My_Variable,ROW())

In this example, the INDEX function tells the calculation to target a specific index item, and the ROW() function tells it to use the index of the current row.

If you want to multiply two fields in the collection, it would be something like

=PRODUCT(INDEX(Quantity,ROW()),INDEX(Cost,ROW()))

OR

=MULT(INDEX(Quantity,ROW()),INDEX(Cost,ROW()))

Off the top of my head I can never remember which one works best, but the important thing is that you want all of the fields involved to be numeric (number, currency, etc.).

Just pay close attention to your parentheses because it is very easy to put them in the wrong place or miss a closing one, which will cause it not to work.

1 0
replied on November 26, 2019

So for the first one without the Discount I have this, but it doesn't seem to work, the Line Item Total fx field is set to;

=MULT(INDEX(PO,PO_Q,ROW()), INDEX(PO,PO_CPU,ROW()))

PO is the name of the Collection and PO_Q is QTY and PO_CPU is Cost Per Unit. 

Nothing ends up in the Line Item Total field. 

What am I doing wrong? 

0 0
replied on November 26, 2019 Show version history

It looks like you have a comma instead of a period separating the Collection and Variable names.

It should be like PO.PO_Q not PO,PO_Q

The best way to put the variables in and make sure everything is spelled correctly and such is to just put the cursor where you want them, click the > icon and select them from the variable menu.

Another note, if you refresh the form editor page, it will let you know when there are detectable problems with the calculations.

0 0
replied on November 26, 2019

Yep yep, I was able to work it backwards and figure it out thanks to your help. Thanks!

0 0

Replies

You are not allowed to reply in this post.
You are not allowed to follow up in this post.

Sign in to reply to this post.