Help

The Community will be undergoing maintenance from Friday February 21 - Friday, February 28 and will be "read only" during this time. To learn more, check out our Announcements blog post.

Re: Create record in table B and link to multiple imported records in table A on import

299 2
cancel
Showing results for 
Search instead for 
Did you mean: 
jwag
6 - Interface Innovator
6 - Interface Innovator

Hi guys,

We'd like to be able to import a list of around 30 items (BoM) at a time to a Cost Estimates table, create a single record in a Cost Centres table and link all the items in the BoM import to this Cost Centres record. The name of the Cost Centre record won't be known on import, so can't be specified in the importing csv. Each import should create a new Cost Centres record.

I have tried playing around with automations but hit the issue of automations running in parallel and creating several Cost Centre records before the BoM records find them and start linking to them.

I'm sure this can be done with a script but I'm hoping there's a way I can avoid that. If not, I'm happy to pursue that avenue and am open to some advice on how best to implement that.

Thanks in advance for any suggestions!

Josh

10 Replies 10
Mike_AutomaticN
10 - Mercury
10 - Mercury

Hey @jwag!

Is there any reason why having a Cost Center field with a temporary name for each row on you CSV file would not work?

E.g. You could have all your fields with such field filled with "Placeholder CC Name", and then make the import. 

This would create the record (only one as long as the name is the exact same for every row) on the Cost Center table. You could then go ahead and re-name the field once you know what name to use.

Let me know if I am missing smth!

Mike, Consultant @ Automatic Nation

Alexey_Gusev
13 - Mars
13 - Mars

Hi,
Add Created Time field in import table, or formula like 

DATESTR(CREATED_TIME())=DATESTR(TODAY()) or use DATETIME_DIFF or view with just created records, anything that can separate just imported from others.
Assuming you creating record in Cost Centre after import, setup automation to trigger on Cost Centre ('when record created' can be used, but in general I almost never use it, instead I trigger when some field not empty)
Put Find Records  step to find a set of just imported records.
Final step - 'Update record', Insert list of records ID (from Find Records) into linked field of trigger record.
jwag
6 - Interface Innovator
6 - Interface Innovator

Hi @Alexey_Gusev ,

Thanks, but I would like the record in Cost Centre to be created automatically when the BoM records are imported. Do you have any idea for how that could work?

Thanks

jwag
6 - Interface Innovator
6 - Interface Innovator

Hi @Mike_AutomaticN ,

The real reason is that the Cost Centre primary field is a formula field (concatenate of various other fields), so that won't work sadly.

Thanks

Mike_AutomaticN
10 - Mercury
10 - Mercury

@jwag got it! What is your formula like/what are the fields being concatenated? Trying to further understand the use case.

jwag
6 - Interface Innovator
6 - Interface Innovator

The specifics are TBC but it will be along the lines of:

concatenate({project ref}, " - ", {cost estimate version number})

Project ref looked up and imported from BoM.

Version number calculated in Cost Estimate table. I do want this to be calculated rather than imported to prevent human error. This will be done by rolling up the version numbers (max) per project in another table, adding 1 with a formula, looking it back up in the Cost Estimate table and using an automation to apply it each time a new Cost Estimate record is created. I've done this elsewhere for other use cases (though I'm also open to simpler suggestions!)

Mike_AutomaticN
10 - Mercury
10 - Mercury

The leanest you can go (if that is what you are looking for) will probably be to manually create a record on the Cost Estimate table before any given import. You can edit the Primary Field's formula for it to show "Placeholder" if certain fields are empty.
This will give you a Placeholder record and name you can use on your CSV. You can then import the CSV which will automatically link to the new record.
Once you are done, you can have your Placeholder record updated as needed?

Thinking out loud!

jwag
6 - Interface Innovator
6 - Interface Innovator

Thanks for the ideas Mike. I would very much like to automate the creation of the Cost Centre record. The intention is to do the BoM import via Airtable's email sync, so without the user having to do anything in Airtable at the point of import.

It could be an option to have an automation that checks if there is currently a Cost Centre record that is not linked to any BoM items and if so, create a Placeholder Cost Centre record as you suggest - I'll have to think about how to trigger that. A potential problem is that the email sync is not immediate, so there could be multiple imports in the time it takes for Airtable to sync, and the Cost Centre record may not be created in time.

I'm resigning myself to the idea that this will need a script - which is fine, but I was just hoping for a way around it!

Then I don't get right you phrase about "The name of the Cost Centre record won't be known on import"
If you want it to be created automatically, it's possible

Alexey_Gusev_1-1739323900317.png

 

You need 2 automations
The problem is to set a correct trigger, I don't know how could you add 30 records and trigger automation only one time. But if you don't worry about runs limit, it's doable.

You need to add field with record ID and create view (I call it 'Sorted'), where only new added records will be visible. I did that with DATETIME_DIFF formula, where minutes ago is <15. As you can see, Airtable is not very precise when counting minutes, but as long as it can differ "<15 minutes" from ">15 minutes", it's ok.
Or you can use 'hours ago', depending on how often you import.
Also create some column with checkbox. All these new columns can be hidden. 


The trick is to Find records by this view and limit Records found to 1. 

Alexey_Gusev_2-1739324017005.png

Then use update record, which ID is output of this list. Actually each added record runs it's own automation that update a checkbox in the same record for all runs.

Alexey_Gusev_3-1739324169694.png

The second automation runs only once and reacts only when checkbox is checked first time. Other 'checks' not affecting the trigger as they all pointed to the same record, where checkbox was enabled already.

It uses the same view 'Sorted', but not limited this time, and then a list of records from it to link new created record:

Alexey_Gusev_4-1739324470221.png

There is a possible flaw is when you import 10 records and it does the link and then next 15 records 5 minutes  after which will be in the same view and not linked. You can add additional linking automation for records which created more than 10 minutes ago and still not linked.

This solution is funny, but I think the perfect way is to improve the import procedure.