Automatically add several records from one table to linked field in another table

[Leah_Relish]

I am trying to create a custom quoting system and because all of the items I sell are custom and have varying prices, I have come up with a way to create the quotes using 2 tables. But I want to know if there is away to automatically add several records from one table into a single record on another table via the linked field.

I start by creating several records on the “Quoting” Table, and they are grouped by the “Deal”. Once all of the items are listed, I click “Create Quote” on the first record and it creates a quote in another Table called “Quotes”. Because they are linked, it copies over the “Deal” and that one record that I ticked the box on. But I want to add all of the records from the “Quoting” Table that are grouped under the same “Deal”.

I tried creating an automation to find records and update, but couldn’t figure out how to make it work.

Any ideas?

[Karlstens]

Typically, when it comes to any automation where you’re needing to do something with both Multiple Records and a linked fields, you’ll need to script a solution.

I recently did a write-up (with screenshots and example code) that shows record from Table A, with two linked fields to Table B, and then an Automation of how to merge those two fields into a third linked field to Table B - it’s all detailed here;

Merge two "Link to another record..." fields Ask the community

I’ve created a sample base, you may copy it to your workspace if you like too, let me know. Here is a Palette table, and the idea is we have colours in three linked fields. An old, a new, and also a merged colour field. [image] The colour table is straight forward, containing colours and their associated linked records to the palette table (which are created automatically when you link fields in the Palette table). [image] The idea is, to trigger the Merge script, simply tick the record t…

Although it isn’t the exact solution that you’re looking for regarding your immediate problem (so many users have unique problems to one another, and it’s a challenge to assist everyone with their nuanced base/table layout), my write up will hopefully give you a broader idea of what’s involved with solving your solution from a scripting stance.

Are you keen to learn how to write this code? It takes time and practice, but it’s enjoyable and super rewarding when what you want in your head actually starts to work in practice. :grinning_face_with_big_eyes:

One thing that’s also helped me to learn over the years, is to simplify my immediate problem as much as possible - in my above example, you’ll see I use an example of Palettes and Colours - as they’re objects and things most of us are overly familiar with.

[TheTimeSavingCo]

Hi Leah, you mentioned you already have an automation that triggers when you mark the checkbox that creates a new record in the Quotes table?

If I were you I would make the following changes to your automation:

1. Insert a Find Record action that looks for records in the Quoting table where the Deal value matches the Deal value of the triggering record before your current Create Record action
2. Update the Create Record action to insert the list of record IDs it found from the Quoting table into the Quoting linked field

Does that make sense?

[Leah_Relish]

Hi Adam; It does make sense. I actually managed to figure it out after I posted this by doing Create Record, Find Records & Update Record, but maybe next time I will try that way as it sounds simpler.

Follow up question, is there a way to make it so that when I tick the “Create Quote” box to initiate the action, it doesn’t create a duplicate record if another line item also gets ticked. Does that make sense?

The reason I ask is because now I am making it so I can create several different quotes for each deal. I was able to add a field for “Option #” and then added that to the rule for finding records. So it separates my quotes into each option. But if I tick the “create quote” button on option 2, it also creates a duplicate quote for option 1.

[Leah_Relish]

Thank you, I will check this out in greater detail as it sounds very interesting. I did manage to get it to work with a standard set of automation rules. But I would like to start to learn more about how to use scripts.

[Karlstens]

Yah, automation are great, glad to hear that you’ve solved your immediate workflow. :slightly_smiling_face:

My favourite part of Automations is that they dynamically update if your Base schema changes (such as if you change a Field name from Task to Tasks, the Automation also update) - but eventually you start to hit limitations with Automations where Scripting becomes a requirement.

[TheTimeSavingCo]

Hmm, would adding an update record action to clear the triggering record’s checkbox field help with that?

Apologies! I don’t think I follow

[Leah_Relish]

The challenge I have gotten myself into is this:

On the “Quoting” Table I have a drop down to select which quote - or ‘option’ - each line item falls under, so that I can create several quotes for one deal. Which I have set up to go from the “Quotes” Table to a Page Designer extension. So in the “Quoting” Table I fill in all the details and then check the box for “Create Quote” when I’m ready:

In the “Quote Table” I want to list all of the line items under that deal in one record, so I set up an automation to Find records where Deal is exactly Deal, then create record in quotes, then update record to list all airtable record ID under “Items on Quote”. That worked fine until I added the “Options” selection so that in the “Quote” Table, it would separate each option into it’s own record with the “Items on Quote” grouped by Option 1, option 2,3,4,5. I did that by making 5 separate automations and adding the condition to “find Records” - “and Option #1 is exactly Option #1”.

So now what happens is that even if there is no “Option 3, 4 or 5” in the “Quoting Table”, it creates blank records for them in the “Quote Table”

The second problem is that if I create only 1 option the first time around and click “create quote” but then later come back and put together 2 more options and click create quote on option 2, it creates a duplicate record for option 1.

So I am trying to figure out if there is a way to A) not create duplicate records, B) note create blank records and/or C) delete duplicate or blank quotes automatically.

I’, not sure if I am explaining this correctly, so hopefully that isn’t totally greek!