I have data spread over multiple tables within my document and would like certain sums calculated within a widget, and grouped by employee.
I have these totals now in a widget filtered by month, but I would like them non filtered as well so that I can keep a running total.
Please help.
After playing with it, I think I answered my own question. At least concerning my issue, several different types of relationships need to occur.
I’ve got the first 3 cells set up I believe, but the last 2 cells since they are coming a different table I think need to use the lookup function. It says Grist does not support lookups.
I have the totals that I want to add in another table. Is there a way to reference just that cell? or some other solution that would achieve the same thing?
Hi @Chris_Scott, thanks for posting! Looks like you found Summary Tables which are helpful for grouped sums. There are a lot of ways to do lookups in Grist. There are general-purpose lookupOne and lookupRecords functions: see Intro to formulas: Lookups. If you are familiar with Excel’s VLOOKUP function, Grist has an equivalent: Function reference: VLOOKUP. And for any record with a reference column in it (see Reference Columns) you can look up columns in the target table for that reference with $ref.Column.
I think I’d need a little summary of the structure of your document to give a more specific answer. If you happen to have an Employees table with Calls_Made columns in it and just want to merge that in here, the Calls_Made column in the summary could be as simple as $Employee.Calls_Made. Otherwise, you may want to do something like <OtherTable>.lookupOne(Employee=$Employee).Calls_Made.
hi @paul-grist thanks for your answer! To start off, I have limited experience with Excel’s advanced functions. I know the basics like Sum etc.
In fact, as far as database limitations I’m also a novice. What’ I’m asking may not be possible, idk. I tried to do some of the functions that you recommended but either I’m misunderstanding or I just don’t know what I’m doing.
My table structure is:
People (Employees)
Call Logs
Months
Time_Sheets
Time_Sheets_Entries
Accounts
I’d like to have a table combining People, Call logs, Time Sheet, and Account data so that I can see an employees total calls, how many appointments they’ve set, how much we’ve paid them, and how many hours they’ve worked and on what account. Is that possible?
I made a quick example of combining data from multiple summary tables, but I’m not sure if that is exactly what you are looking for: https://public.getgrist.com/xk7pTLnSFyLU/Combining-Summary-Tables
@paul-grist Ok that makes me feel good! That is definitely a version of what I’d like to be able to do. I think the main issue is my lack of knowledge of python.
I just tried to reverse engineer what you do in that table, and add it as a formula in mine. I think I’m having a comprehension issue. I made a quick video of what I’m doing, please tell me where I’m going wrong.
@Chris_Scott I think we are getting close! For the Calls_Made column in the PEOPLE widget on the Employee_Summary page that you are showing at the very end there, we need to tweak how the formula is being entered.
In the right bar, do you see the part that says “DATA COLUMN” and then has an “ACTIONS” dropdown? Just above where you are entering the formula. Can you try clicking on the “ACTIONS” dropdown and selecting “Convert to formula column”?
Explanation: Right now, this column is set to run the formula only when new records are created, and to save that value (we call this a “data column”). You want the formula to be always active, and to update anytime the data it refers to changes (we call this a “formula column”).
Then, in your formula, I think you need “Employee=” instead of “Person=”, if the column in the summary you are matching is called Employee rather than Person. Sorry we don’t have autocomplete yet to help catch that kind of problem.
Let me know how it goes, I appreciate your persistence! 
@paul-grist Bingo!! Thank you very much for you time. That was very instructive.
I actually think I understand Grist a little better now and have since created several other Lookups that I have been wanting to do! Thanks again!!
Hi @paul-grist
where it comes from this naming? GristSummary_5_Calls.lookupOne(Person=$id).Hours
I understand that you reference to the table Calls but the GristSummary_5_ I do not see where it comes from.
Best
Hi @anefta - Summary tables are created automatically, and given automatic names by Grist. They always start with GristSummary_ so in a formula one practical way to find them is to type GristSummary_ and look at the suggested completions.
I believe (and hopefully others will correct me if I am wrong) that 5 is just the length of Calls in letters. There can be many summary tables referring to the same source table, and they get labelled e.g. GristSummary_5_Calls, GristSummary_5_Calls2, GristSummary_5_Calls3 etc. If the 5 wasn’t there, then there could be problems if you had a table called Calls2 and started making summaries of it. Kind of an idiosyncratic solution, but that’s what the 5 is about as far as I know 
Hi Paul,
In your example above, why is the formula for $Total_Calls
GristSummary_5_Calls.lookupOne(Person=$id).Hours
and not
GristSummary_5_Calls.lookupOne(Person=$Name).Hours?
Also, how would this work if Person was a Reference List instead of Reference?
Thanks in advance!
Hi @mike, good questions! For formula purposes, reference columns always contain the numeric id of the record they are referring to. You can choose how the reference column is displayed visually (e.g. to show names or anything else from the record referred to) but that doesn’t affect what the column contains for formulas. If you’ve worked with databases, you can think of references as foreign keys.
If you need to find a particular record within a Reference List: just for concreteness, imagine you have a table of zoos, where each zoo has a reference list of animals, and for each animal you now want to calculate which zoos it is in. You could do that with [zoo for zoo in Zoos.all if $id in zoo.Animals]. If you find yourself needing this kind of formula, it might be worth considering using a “bridge table” (in this case, a table linking individual zoos with individual animals) rather than reference lists.
Hi @mike, welcome to the Grist Community Forum! Can you expand your question about Reference Lists? If Person is a group-by column then it may be a reference list in the source table but it should have been flattened out into a reference column in the summary table. If it’s a non-group-by column then it can still be a reference list in the summary table.
@paul-grist I think you can write that formula as Zoos.lookupRecords(Animals=CONTAINS($id)), and that doing so should give much better performance.
