Adding Customer Totals from different Tables

[Jerarde_Felipe_]

Newbie here (with the obligatory “newbie here” intro lol):

I have a problem I can’t seem to solve despite reading multiple version of what I think are similar problems with solutions that don’t seem to work. I will try and describe what I have and what I want:

I am an artist looking to track collectors and paintings. I have successfully created 2 bases:

1. Base 1, Artwork: This base contains 3 tables. Each table is a category of art. Each table contains title, price, availability (sold or not) etc.

2. Base 2, Clients & Collectors: 1 table with list of clients and fields that include name, address etc.

Successfully implemented: The bases are synced. In base 1 of Artwork is has a table that is a shared view from the client base. In each art work table I have linked a corresponding client. So if I go to Base 1 Artwork, go to the Landscape table, go to a painting that is sold, I can see who the collector is. If I open a collectors record I can see all the paintings they have purchased from across tables.

PROBLEM: First, the client record of their art purchase only seems to be in the Artwork Base. When I go to the base with the clients those fields are not synced. How do I make it a two way sync? Second and more important issue: I want to add the totals up across tables in a field in the clients table. I hope that makes sense, I will give an example… Customer Bob buys 2 paintings from different categories. Bob has a Landscape and a Still Life. Each of those is listed in their own table in the artwork base. I want to open Bob’s record and not only see both paintings, I want a field that sums up total sales. Does that make sense? I figured out how to create a roll up table that does this but with only one table. I want it to sum up purchases from different tables.

THANK YOU!

[Rupert_Hoffsch1]

Hi @Jerarde_Felipe_Gutie !

Re. 1: You will have to sync back. Sync only works one-way as you’ve seen, so you’ll have to do the same again in the other direction (which will cause an additional table in your clients & collectors base, which you might not want…).

Re. 2: Here you can make use of the Count field. In your case, you would need three count fields and a fourth field summarizing those three fields.

Hope that helps!

[Rupert_Hoffsch1 · Accepted solution]

PS: If you meant adding the $ amounts of all purchases per client, then you’ll need to use a Rollup field for every linked records field and summarize those three rollup fields.

[Jerarde_Felipe_]

Yes, I mean to add up the $ of different tables (purchases) for a client.

[Jerarde_Felipe_]

Thank you for the feedback. I hope that helps too, I’ll let you know! Thanks! I am going to try the count fields.

[Rupert_Hoffsch1]

If you want to summarize values, you’ll have to use a Rollup field with a sum function.

[Jerarde_Felipe_]

It worked! For those interested in the solution: In my Art inventory Base, in the synced table for Clients, I created a roll up field for each table of art type, then created a formula field that SUM the corresponding columns (fields) to give me the clients overall purchase totals. If I open a clients records it not only shows me a link to which paintings they own, but also a quick view of how much they have spent with me in total.

The only thing I didn’t resolve is the two way sync. I wish there was an elegant way to have these 4 fields show up on the other base that is for client list.

Thanks again!