Adding fields from multiple records

[Simon Smailus]

Is it possible to add all the same fields from multiple records in one form?

For example:

I have an annual leave form on which I enter the days annual leave I take. The form has the field, “Days Taken” in a number format. I may have five entries each specifying the number of days annual leave taken during a specific date period.

I would like a summary field that shows the accumulated days. I cannot seem to create a formula that will add the “Day Taken” field from all records in the form? Is this possible?

[Brendan]

Hi Simon,

Sure it’s possible.

Look for the setting called Calculation Summary Field and Calculation Summary Type.

Or if you’re on the Mac version, switch to the multi-column list view and click the Sigma button to reveal the Calculation Summary Row. Then click any of the popup buttons there and choose one of the aggregate math functions. You can choose between Count, Total, Minimum, Maximum, and Average.

In an upcoming update you’ll be able to display group summary totals too. But for now it’s just a total at the very bottom of the list of records.

Thanks!

Brendan

[Simon Smailus]

I’m really not getting this. Can you please post an example.

I don’t understand why there is a calculation and summary calculation.

[Mike Schwartz]

Simon,

A calculation field works with information contained within a single record, and produces a result for that record. It’s just another field type. Summary calculations operate on number fields across a set of records (all of them, or a subset of found records) and provide the count, total, min, max, or average (your choice) for a specific field across the set of records.

If you’re in the multicolumn list view on the Mac, you can specify different summary statistics for each of the numeric fields. Play around a little bit like Brendan was suggesting, and I’m sure it will make more sense.

Hope that helps,
Mike

[Simon Smailus]

Thank Mike, that clears it up.

Does that mean calculations do not work across records?

I have a form that contains records with a number field that collects the number of days annual leave I have taken. I now get the summary and have that listing the total days. I was hoping to be able to subtract the total from my total days of annual leave to know how many days are left.

Is that possible?

I was also thinking of a separate form collecting days taken. A third form with staff member details. I was hoping somehow the first (main) form could show days taken and days left.

[Brendan]

Hi Simon,

Calculation fields work across only the fields within the same record. Summary Calculations as Mike said work across a set of records from the same form. A Calculation field cannot access the aggregate total of its own form’s records.

However, a Calculation field can reference fields from linked forms when you have a Link to Form field in your form.

For example, if you have a Form A that links to Form B and Form B has a Number, Checkmark, Rating or Calculation field then you can reference those fields from a Calculation field’s formula in Form A.

An example might look like this:

"Total days: " + [TOTAL::Vacation Tracker::Number of Days]

Where Vacation Tracker is a linked form that has a Number of Days field. Form A (whatever that’s called) could display the result in one of its records.

So in that way you can reference the fields in another form to gather aggregate totals (and avg, min, and max results).

See the topic on Relationships in the online user manual to see how to setup the Link to Form fields:

https://www.tapforms.com/help-mac/5/en/topic/relationships

And here’s a video on how to do it:

Hopefully that helps a bit more.

Thanks,

Brendan

[Brendan]

By the way, the [ ] brackets were there just to show you that it was a field reference. On macOS you don’t type in the square brackets. However, on iOS you do, but they’ll be put in for you, so you don’t really have to do it there either. It happens automatically when you select a field to insert into your formula.

[scneophyte]

This is incredibly helpful information and totally just helped me to validate that the sum of the gross income for the entries on the child form matches the total Invoice amount in the parent form!

If I understand this correctly, any field on a child form that has a designated Summary Calculation can be subject to statistics; e.g. TOTAL on the parent form.

Brilliant!

[Brendan]

Yes, that’s correct :)

[Author]

Posts