Summing numerical fields across multiple records

[Daniel Jardine]

Hi there. Totally new to TapForms but I’ve tried to research this and just can’t understand what I assume is a straightforward function.

I have put together a Form “Parts” with a couple of hundred Records containing details of physical objects. Each record has many fields, including one for Cost in $.

How do I go about creating a summary Form which displays the total cost of all objects in the the “Parts” form? I’ve tried creating a Linked “Summary” form, but this has totally confused me.

If I can get a simple summary working then my next question is how can I sum only the “Cost” fields in records with some other categorizing field? For example only summarize the total cost of all parts with a drop-down selection of “Steel”?

Any help would be most appreciated.

[Sam Moffatt]

If you use the multicolumn list view, you can enable a summary row at the bottom of it which will let you aggregate numeric columns by total (sum), min, max, average and all columns by count (count is the number of records where that field has a value IIRC).

If you want to sum by a particular field, you can create a saved search to only include those matching records and the summary will be updated.

If you’re after a report use case where you want to print out the summaries grouped by a field value, if you use a Link to Form field set to “JOIN” it’ll let you pick a form and two fields that are in common between that form and the current form. That’ll automatically pull in those records that share the common field. The table that the Link to Form field creates allows you to select the aggregation you want and then you can print those records.

If you just want the value printed and not the rest of the records, you’ll still want to create your “JOIN” field above, but you’ll have to use either a calc field or a script. To be honest the calculation field is the easiest path. Create a new calculation field and then inside of it find the “Cost” field from your “Parts” form. If you double click on it, it should automatically create a “total” or sum aggregation. Just hit save and it’ll automatically calculate the total cost for the linked fields. You’ll probably want to make sure you go to the field settings and set the number format to “currency style” and set “decimal places” to at least 2.

The field script pathway is similar except you need to select the form name in the script editor and click “ID” to insert the Link to Form field ID, you’ll need to select the “cost” field and also click “ID” to insert it’s ID and then you can use the getTotalOfLinkedFieldForField function to get the sum of that field from the link (I told you the calc field is easier). For this one though, calculation field works well.

[Daniel Jardine]

Sam, thank you SO much! This is an incredibly helpful and detailed response. I really appreciate it.

[Author]

Posts