Automatically creating and updating new tables based on a record


#1

As the name suggests, I would like to copy and consolidate records based on one field into new tables. Perhaps an example would help:

Let’s say I’m a store that sells apples, oranges, and bananas. Every time I sell a fruit, I record it in the Main Table by selecting the fruit in a dropdown menu with other purchase information like customer name, date and time of purchase, etc. The dropdown menu is multi-select since customers can buy more than one kind of fruit at a time.

In addition to all fruit purchase information in the Main Table, I would like to create three new tables: Apples, Oranges, and Bananas that only contain the purchase history of the one respective fruit. The idea is that each table will automatically copy over a record from the Main Table when I select a fruit.

Any ideas on how I might go about doing this? Thanks in advance!


#2

I don’t know of any way to implement precisely what you’ve described – but there may be ways to provide you with equivalent functionality. My problem is I can’t quite suss out your endgame from your description. Could you give a little more detail as to how you envision the completed base? (Here or by private message if you have concerns about exposing core processes.) It just feels like there should be an Airtable-based solution possible.

You might want to look through the Support forum for examples of how other users have managed to pass information from one table to another; while none are likely to provide a ‘plug-compatible’ solution to your problem, some of the techniques used might be adaptable to your application. As a start, take a look at the potential solutions and demonstration bases I’ve offered in the replies to the following posts. Again, while none is an exact match for your needs, in each case the desired behavior required moving information through Airtable in a manner not explicitly supported by an internal function.





#3

Without more info about your goals and with a superficial understanding of what you’re trying to achieve, it looks like that you should try using a bunch of rollups/lookups and link fields. What you say about the multiple select, that’s probably what will end up leaving you stuck if you really want to have one table per product type. By using the link fields you will be replicating that functionality of creating a new record without any automation. I’ve done it for an inventory management base I made for myself.


#4

Hi there! Thanks for the reply. I put together a brief table that hopefully illustrates what I’m imagining a bit better. https://airtable.com/shrjGqvhzDrHvPdLd

So you’ll notice that in the main dataset, the first three rows have already been assigned “fruit tags” in the second column. The idea is that once I assign a fruit-tag, that entire row would automatically appear in the respective fruit-specific table.

The last two banana purchases have not been “tagged” yet, so they do not appear in the banana table. The idea though is that would automatically happen once the banana tag is assigned to the row.

Does that make sense? haha i know it’s a bit confusing (I confused myself several times trying to explain it) - i really appreciate your help!

edit

i haven’t had a chance to dig through the links you mentioned just yet, so i’ll be sure to do that now to see if i can figure something out!


#5

@Yihwan_Kim Did you find answer to this issue? Have a similar need!


#6

I too am looking for an answer on this. Did you have any luck?


#7

I did have success, but by using an external service. I wanted to convert 1 ‘week’ entry into 5 ‘day’ entries automatically based on column values from the week. From there, I wanted to create 5 ‘meal’ values from each day value. So first a cascade of 5 rows which then in turn cascade to 25 rows.

I did this by using Zapier, by using Airtable as both the input and the output. In my use case, I knew I needed a cascade of 5, so I set up 5 steps in my ‘Zap’. If you need a 1:1 relationship, you won’t even need to go that far. If you need a custom amount based on the origin record, that’s going to be harder to do. I’m looking into writing a script inside Zapier in order to achieve this.

I should also note that Zapier will update only at 15 minute internals (5 minute intervals on a paid plan). So if you need it instantaneous, this is not your solution. Hope this helps.


#8

You have probably already figured out a solution to this considering the question was written in august 2017 but here is what you do for those reading this:

The way you would do this is by making a new view for each of the fruits and then in each view filter it to only contain one specific type of fruit. If you wanted to show every transaction for each specific day you can also add a new view and group it by the date of the purchases.