Calculations across forms

[Hugh Babington Smith]

Sometimes the simplest things baffle! I would like to multiply a number in one form by a number in another form. The second form is used more frequently, so I should get a running record of the different values. However, even though the calculation field picks up the field names, I cannot understand how sometimes I get an answer, sometimes in the second form these totals accumulate (which is not what I want at all), then suddenly they all disappear.
I also do not understand why there is a “calculation” section both in the Form Editing section and the Field editing section.
Grateful for any help
Hugh

[Brendan]

Hi Hugh,

The Calculation option on the Form section is for displaying totals, averages, minimums, maximums, and counts across records in the form.

The Calculation field type is for creating formulas that access the fields within a form.

As for multiplying a number in one form by a number in another form, how you go about that depends on the type of relationship you have and where the Calculation field is. If the Calculation field is on the child form, then you can get access to a single field’s value from the parent record as long as the relationship from parent to child is one-to-many. If it’s many-to-many or Join, then you can only get aggregate values from the relationship. That is, you can only get a total of a field or a minimum of a field, maximum, or average, etc.

What does your formula currently look like and how have you configured your relationship?

Thanks,

Brendan

[Hugh Babington Smith]

Thanks for quick reply, Brendan. The relationship I was working on is from the child to parent, so I have made another form with a parent to child one-to-many, but when I try to edit the formula (which is simply [parent:number]*[child:price]) I do not see the parent’s fields.

Can one have more than one relationship between forms? Can one have a single parent and several child forms? I used to use FM Pro where this was very flexible.

Hugh

[Hugh Babington Smith]

OK, I set the link in the parent form to “Show inverse relationship” and can then see the parent form fields in the child form calculation. Sometimes the calculation works, sometimes not, and I cannot for the life of me understand the behaviour. Grateful for pointers.

[Sam Moffatt]

If sometimes the fields that your calculation refer to are empty then your calculation can, depending upon the operations, end up empty as well. You should guard all of your accesses with IFEMPTY to set a fallback value.

Checkout the red alert box at the top of the calculations documentation.

[Author]

Posts