Question

Complex Collection Math Using Formulas

Forms Business Processes

Updated January 17, 2019

asked on January 17, 2019

I'm wondering if this is possible. I have a collection with radio buttons that determine if the row is a goal, action, or resource. If it's a resource then it gets added to the action above it. If it's an action that get's added to the goal total. So for example, a goal can have several actions and an action can have several resources. Is there a way using the built in formulas in forms to check the radio button and if it's a resource add it to the action above it? And then add all the actions to the goal above it? Afterwards have all the goal totals go in a field outside the collection.

I have this working with javascript but I'd prefer doing it through the formulas.

0 0

Replies

replied on January 17, 2019 • Show version history

I'd say this is possible, but it is hard to provide a specific formula because I'm not 100% clear on the process (i.e, are the totals using the same fields as the non-totaled values, etc.).

As an example, to retrieve a value from a previous row in a collection you could use the Index and Row functions. For example,

INDEX(Collection.Field,(ROW() - 1)

would get the target field from the previous row.

You have a lot going on so your functions are going to get a bit more complicated, and you'll need an IF condition to make sure you're first row doesn't get a calculation error by having an invalid index. For example, something like

IF(ROW() > 1,INDEX(Collection.Field,(ROW() - 1),0)

Would grab the previous row value, or if there is no previous row, it would return a 0; that should prevent errors since it shouldn't try to find a "0" index.

The function for the total at the end depends on what fields you're actually adding to in the collection because you want to make sure your not adding things twice.

0 0

replied on January 17, 2019

Each total has it's own field and class. Can I determine if the row needs to be used in the calculation based on the radio button value? Would an IF statement work for that? The way I'm envisioning it in my head is this

IF RadioButtonValue = 2, get Cost and add to previous row with RadioButtonValue = 1

Then for all RadioButtonValue = 1 add to previous row that RadioButtonValue = 0

0 0

replied on January 17, 2019 • Show version history

I see separate fields for Cost, Total, and Total Cost, so I'm still not clear on where the logic and math are happening.

To figure out a possible solution, it depends on the following

What math/logic happens on the individual rows
What is each field in the individual rows used for
- Cost
- Total
- Total Cost
What math/logic happens in the bottom total

Depending on the answers, the SUMIF function might come into play.

SUMIF can check a column value, and if a match is found, add the total for another column in the matched row.

For example,

=SUMIF(Collection.Include,"Yes",Collection.Value)

Gives the following results

You can see here that it adds rows 1 and 3, but excludes row 2 because No is selected, giving you a total of 101.

0 0

replied on January 17, 2019

So for each row the first thing that happens is the radio button gets selected. This determines which fields show up. If it's a goal you get the name, description and total cost. When an action is selected you get action and total and when you select resource you get resource and cost.

For each action row the math is adding all the resource costs below it and putting that value into the total field.

For each goal row the math is adding all the action totals below it and putting the value into the total cost field.

Each goal can have multiple actions and each action can have multiple resources associated with it.

0 0

You are not allowed to follow up in this post.

Question

Question

Complex Collection Math Using Formulas

Replies

Sign in to reply to this post.