How to group and sum values from database/table A in database/table B by a specific field?
Let’s say you have a Sales Data database(Table A) with columns: Platform, SKU, and Quantity Sold. You want to create a monthly summary in Table B to show the total quantity sold per SKU.
Table A:
Table B:
Here’s how to configure this:
- Create a “Link” Field in Table B
- Add a Link type field in Table B and link it to Table A (Sales Data), let’s call it “Sales records of SKU”.
- Create a “Lookup” Field for Aggregation
-
Add a Lookup type field named “Quantity Sold of SKU” in Table B:
- Linked Database: Select Table A.
- Lookup Field: Choose
{Quantity Sold}. - Aggregate Function: Select
SUM. - Lookup Count: Set to
All(to include all linked records).
- Add Monthly Records in Table B
-
Create a record for each month (e.g.,
2025-01). -
In the Link field cell, select all Table A records where SKU = SKU1 and save.
- Verify Real-Time Calculation
- The “Quantity Sold of SKU” cell will automatically display the total sum for the linked SKU records.
Example Result:
This setup allows dynamic aggregation and ensures data stays updated as Table A changes.