Need to make a summ of Checkbox's in Table 1 and release in Table 2

628 1
Showing results for 
Search instead for 
Did you mean: 
4 - Data Explorer
4 - Data Explorer

Need to make a summ of Checkbox’s in Table 1 and release in Table 2.

1 Reply 1

This could be done by using linked records, rollup fields, count fields, and a few formula fields.

Start by linking each main record to its associated table record in your summary table. For example, all records in [Table 1] should be linked to the “Table 1” summary record, records in [Table 2] should be linked to the “Table 2” record, and so on. This will create several reciprocal link fields in that summary table, one for each linked table.

In that summary table, you’ll need to add two fields for each related link field: a count field that will count the linked records, and a rollup field that uses the SUM(values) aggregation formula to count the number of checked records. Using the links from [Table 1] as an example, I’d name the rollup something like {Table 1 Checked}, and the count field {Table 1 Total}. Build similar fields for the incoming links from [Table 2] and [Table 3].

That will give you most of the values that you want, but they’ll be spread out across several fields, plus we’ll need to calculate the number of unchecked records. To collect them all for easier review, the three fields in your second screenshot could become formula fields. The formula for “Checked” could be:

{Table 1 Checked} + {Table 2 Checked} + {Table 3 Checked}

Even though all three rollup fields are being added, each record will only be rolling up data from a single table. For example, in the “Table 1” record, you’ll only have links from [Table 1], so the {Table 2 Checked} and {Table 3 Checked} fields will be empty (equating to zero), and the above formula will only show the total checked records from [Table 1]. In the “Table 2” record, the same formula will only show the checked records from [Table 2], etc.

For the other two formula fields, we’ll use a similar shortcut. First here’s the {Unchecked} field formula:

({Table 1 Total} - {Table 1 Checked}) + ({Table 2 Total} - {Table 2 Checked}) + ({Table 3 Total} - {Table 3 Checked})

Knowing the total number of linked records (from the count field) and the number of those that are checked (from the rollup) lets us find the number of unchecked records by calculating the difference.

The {Sum of Both} field will use this formula:

{Table 1 Total} + {Table 2 Total} + {Table 3 Total}