Setting up one-to-many link with existing tables

Topic Labels: Base design
533 0
Showing results for 
Search instead for 
Did you mean: 
4 - Data Explorer
4 - Data Explorer

I’m trying to move some spreadsheets into Airtable to track ecommerce sales

I have an order-data table that has a primary field of “Order-Type” that is a formula combining the order number and payment type (two other fields in the table). This represents a single sale:

Order-source, order id, total, source
1234-web, 1234, $50, web

I have another table for line-items where the primary field is order-number, which is the order that the line(s) belong to. It contains item cost, item quantity, list price, etc… For example:

Order, sku, costper, quantity, ordercost
1234,widget-1-red, $10, 2, $20
1234,widget-2-green, $15, 1, $15

I want to add a rollup column that sums the ordercost field for all line items by order and pull it into order-data, to get:

Order-source, order id, total, source, ordercost
1234-web, 1234, $50, web, $35

I set up a link relationship from order-data>order number to the line-item table and I made sure the toggle for “allow linking to multiple records” is set to on.

It worked, but it only associated one record from line-items to each record in order-data:

Order-source, order id, total, source, ordercost
1234-web, 1234, $50, web, $20

I can manually add additional line items, but I’ve got thousands of rows. Is there a way to make the system include all of the rows from the “many” side in the “one” record in a one to many relationship?

0 Replies 0