I have an invoice table and a work table, with a one-to-many logical relationship. I’d like to add the summary of work.hours to the invoice table.
How would I do this?
Hello Craig!
I think I understand what you’re describing. I created an example for you here: Summarizing data from linked records
The Invoice table lists invoices identified by an invoice number.
The Work table summarizes hours worked. It has a reference column to the Invoice table to relate each work record to an invoice record. Multiple work records may point to the same invoice.
In the Invoice table, we can find which Work records are pointing to that invoice by using the formula
Work.lookupRecords(Invoice=$id)
The formula syntax is OtherTable.lookupRecords(OtherTable’sColumn=$ThisTable’sColumn). There are examples in our help center.
The “Invoice” column in the Work table is a reference column. Though it is showing the invoice number for convenience, the value of the cell is the entire record that is being referenced. Each record in Grist has a unique id. If you enter $id into a formula column, you can see records’ ids.
Returning to the lookupRecords formula, we’re matching records wherein the value in the reference column (the record’s id) matches the record’s id in the Invoice table.
Try entering just Work.lookupRecords(Invoice=$id) to see what Grist returns. That list is a Record Set. We can do cool things with record sets in Grist!
First, we can add .Hours to the end of the lookupRecords formula so that the list returns the “Hours” field of each related work record.
Now we have a list of hours and can sum them up with a simple sum.
sum(Work.lookupRecords(Invoice=$id).Hours)
Let me know if I missed anything about your case. Happy to help!
I’m trying a variation of your solution with a second table named Projects. It has a Text , primary-key field named Number.
The Work table has a Project field (text) that is a reference to Projects.Number. This works as expected.
I’d like to include an aggregate of the Work.Hours in the Projects table.
I added a formula column Hours to Projects and assigned this formula: SUM(Work.lookupRecords(Project=$Number).Hours). This produces 0 for all rows.
If I modify the formula to be Work.lookupRecords(Project=$Number), the table displays Work [[]] in red for all rows. Might might be the problem?
Try
SUM(Work.lookupRecords(Project=$id).Hours)
$id is a unique record identifier in Grist, it must be typed just as shown.
Every record has a $id. If you type =$id into its own formula column, you’ll see that each record has a fixed identifier. If you sort the rows or filter the rows in any way, that id does not change.
Because Project is a reference column, its value is an entire record, which is identified by $id.
Although the Project field is displaying the referenced record’s $Number field, that $Number is not the reference field’s value. I added a quick video to the example on a new page that explains this with a visual aid.
Our references column video tutorial is also a great resource for understanding reference columns: Reference Columns Guide - Grist Help Center