Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jun 22, 2022 10:45 PM
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!
Solved! Go to Solution.
Jun 22, 2022 11:40 PM
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.
Jun 22, 2022 11:39 PM
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!
Jun 22, 2022 11:40 PM
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.
Jun 22, 2022 11:42 PM
Yes, I mean to add up the $ of different tables (purchases) for a client.
Jun 22, 2022 11:45 PM
Thank you for the feedback. I hope that helps too, I’ll let you know! Thanks! I am going to try the count fields.
Jun 22, 2022 11:57 PM
If you want to summarize values, you’ll have to use a Rollup field with a sum function.
Jun 23, 2022 12:10 AM
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!