Skip to main content

Automation to grab product column info and paste it into line items table

  • May 31, 2023
  • 3 replies
  • 39 views
L
Forum|alt.badge.img+7

Hello, 

Recently I have got an excel with information about customers and their ordered products and quantities.
I have added all the tables and fields in order to turn this information into airtable orders with line items.

Is there a way to automate the creation of every ordered item listed in the columns of the 'ordenes' table, into the 'line items' table. 

What I have done so far with the automation, is to append 'order id' from 'ordenes' table, into the 'line items' table but I dont know how to grab the products and quantities associated with the order and paste them as line items.

The column items on the 'ordenes' table are already records on the 'portfolio' table (which holds all the info about each product).

Thanx for the  help


 

3 replies

S
Forum|alt.badge.img+18

In Airtable/relational databases, you shouldn't need to create one column per product. Doing so is often considered bad practice. Instead, you can create a table of unique products and link this table to your Orders table using linked record fields. If you need to capture more metadata on the order -> product relationship such as different shipping dates per product per order or to create an itemized invoice, you can use a junction table approach.

This would look like this:
Orders (unique list) <-> Invoice (junction table) <-> Projects (unique list)

Structuring your data correctly will pay dividends later when it comes to producing the views/automations you need. Good luck!

L
Forum|alt.badge.img+7
  • Lewis_Sternberg
  • Author
  • Known Participant
  • June 1, 2023

In Airtable/relational databases, you shouldn't need to create one column per product. Doing so is often considered bad practice. Instead, you can create a table of unique products and link this table to your Orders table using linked record fields. If you need to capture more metadata on the order -> product relationship such as different shipping dates per product per order or to create an itemized invoice, you can use a junction table approach.

This would look like this:
Orders (unique list) <-> Invoice (junction table) <-> Projects (unique list)

Structuring your data correctly will pay dividends later when it comes to producing the views/automations you need. Good luck!


that is what  I am trying to do, i got this initial structure from an excel file

S
Forum|alt.badge.img+18

that is what  I am trying to do, i got this initial structure from an excel file


Ah got it. Sorry, I misunderstood your ask. Are you looking for a way to ingest future excel files that are in this format or do you only need to convert it once?

It looks like you need to pivot/transpose your Excel data before it comes into Airtable. Creating field values from column names in Airtable will require custom scripting.

Terms & ConditionsAccessibility statement