Syncing tables across bases--help needed

[Tom_Keysers]

Hi, I could use some advice on the following use case. One base, four tables:

• Products
• Orders
• Order Line Items
• Out of Stock Products

Orders are linked to its Order Line Items, and each Order Line Item record is linked to 1 Product record from the Products table. Additional lookup data of the Product record is gathered in both the Orders and the Order Line Items table.

Now here is the main issue: Product records are automatically (via scripting) moved to the Out of Stock Products table once they hit a quantity of 0 (and let’s assume we manually adjust the quantity of these product records). Once a product record is moved though, the link between the Product record and Order Line Item record is removed, causing all the additional lookup and summed up data of my final Order data to be flawed.

Somehow, I need to retain that product data in the order.

I’m looking for any type of suggestion to deal with this use case to retain the product data in the orders.
Could be going anywhere – from a scripting solution (eg to make the Order Line Item look for the same product in Out of Stock and create a new link), to a different setup of my base.

I am experienced in crafting custom Airtable scripts and extensions, so feel free to delve into that realm.

Thanks in advance!

EDIT: keeping in stock and out of stock products in one table and thus not moving any records at all is not an option (for various unimportant reasons).

[ScottWorld]

You could do it that way, but that’s a lot of work and a lot of hassle to maintain.

My recommendation? I wouldn’t move out of stock items to another table. I would just mark them as out of stock, and then create a different filtered view to show the out of stock items.

For a deep dive into views, you can check out my free Airtable training course:

LinkedIn

Learning Airtable Online Class | LinkedIn Learning, formerly Lynda.com

Learn the basics of Airtable, a spreadsheet-database platform, including how to create tables, use different types of fields, leverage views, work with records, link tables, and more.

[Tom_Keysers]

Yeah, not moving them into an Out of Stock table and just using views would be the easiest way. But for some reasons that actually isn’t advisable for us at this time, so not really taking it as an option at the moment. Sadly enough. Thanks though!

[TheTimeSavingCo · Accepted solution]

Hmm, how open are you to the idea of putting the data you’re looking up from the Product record directly into the Order Line Items record instead?

That is to say, no more lookups, just paste that data in every time

I’ve been burned a few times where we were referencing product data the way you are now, and then made changes to said product data, accidentally breaking all of our old history

This would also solve your problem with the links!

[ScottWorld]

You could just do this with a normal Airtable automation

Just create a new record for the product in your “out of stock” table with all the same information from the original product table — including all the data from your linked record field. That will relink the product to all of the orders that are associated with that item.

Then, back in your original product in the old product table, you could easily clear out the old linked record field. But ideally, you will want to delete that record altogether. For that, you would need to write a script or use an automation tool like Make.com or an API tool like DataFetcher.com.

[Tom_Keysers]

Hmm, how open are you to the idea of putting the data you’re looking up from the Product record directly into the Order Line Items record instead?

That is to say, no more lookups, just paste that data in every time

Hmm, actually, that is a very intriguing suggestion.

Hadn’t really considered that properly because it is not that “tech smart”, but it could be the smartest way of doing things in the end.

Thanks for your input, very much appreciated!

[Tom_Keysers]

Just create a new record for the product in your “out of stock” table with all the same information from the original product table — including all the data from your linked record field. That will relink the product to all of the orders that are associated with that item.

Thanks for your input!

Duplicating the product to the Out of Stock table indeed is just a small automation/script away.
The only thing I dislike with this method is that product link will end up in a different link field inside the Order Line Items table – am I right? Since a link field is table specific. And with that, also different lookup fields.

[ScottWorld]

Oh, I see what you’re saying. Right, that is correct.

If you created a new set of linked line items in a new table, you would need to create a completely separate set of lookup fields, and then you would need to create formula fields to compare the old lookup fields to the new lookup fields to give you the “final totals” that you want. (In other words, the formula field would decide whether to use the new lookup field or the old lookup field, based on the presence of data in the linked record field.)

This is totally possible and I’ve seen it done before, but I’m super-curious about why you would voluntarily want to put yourself through this? :stuck_out_tongue_winking_eye:

[Tom_Keysers]

As you explain that, it’s indeed doable but a total hassle. I’m more and more skewing towards “hard pasting” the product data into the Order Line Items instead using linked lookup data
This “hard pasting” has other benefits which I hadn’t thought of before, eg the price will always be the price which the customer ordered instead of a possibly updated price over time.

And the main reason why I’m considering these sort of hassles, instead of just keeping in stock and out of stock products in one table, is because the sales team creates and shares views with clients all the time. Opening the risk of them forgetting to filter out out of stock products is just too big and too sensitive :grinning_face_with_smiling_eyes: Plus it would complicate their product hunts in the database in general.