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

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

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

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

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

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

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!)

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!

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

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. 

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.

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:

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.

That is a very creative solution - thank you very much Alexey!

There is definitely a possibility that multiple imports will happen within 15 minutes, but I will play around with additional conditions to see if this problem can be resolved.

Thank you for your detailed response.