struggling to combine tables and create a summary

1482 3
Showing results for 
Search instead for 
Did you mean: 
5 - Automation Enthusiast
5 - Automation Enthusiast

My goal is to combine 2 data tables into one, and then get a single output sum value for each "agent" within the respective table. Right now, when I combine the tables and syncing, I am getting limited or no data to transfer across. The goal would be to have agents from each agency on one table, and then that table can do a weekly or monthly sum total for each field. 
Here I have a demo agency and demo agency copy with everyone2 for differentiation. I am open to ideas and options.

Just feeling like I am trying to drive a nail with a screwdriver on this one. 

Screenshot 2023-03-30 at 6.49.28 PM.pngScreenshot 2023-03-30 at 6.49.39 PM.png

3 Replies 3
7 - App Architect
7 - App Architect

Create an Automation that links records in each table to records in the "Agents" table when the right field is matching (I'm guessing user?).

Then you can add lookup fields that pull in data from each table, and use formula fields to sum the relevant numbers together.

In "Imported table", I assume the "user" linked field goes to the "Agents" table?

If so, in your "Grid View" synced table:
1. Create a formula field that will remove the "2" at the end of the "user" field
2. Create a linked field to the "Agents" table
3. Click the field header of the formula field from step 1, selecting the entire column's values.  Copy this
4. Click the field header of the linked field from step 2.   Paste into it

This should automatically create a link between the data in "Grid View" and "Agents"

In "Agents", create a rollup field to sum whatever values you want from "Imported Table", and another rollup field to sum values from "Grid View".

Finally, create a formula field to add those two rollup field values together


Just switch off grouping, sorting, filering, hide all computed fields, ensure the fields are in the same order/quantity and copy-paste (when pasting, put cursor in a new record. it will ask your confirmation to add X records) whole second table to first. Do a snapshot before if you doubt in what you doing.