- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Feb 10, 2025 08:20 AM
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
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Feb 10, 2025 08:39 AM
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
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Feb 10, 2025 08:57 AM
Hi,
Add Created Time field in import table, or formula like
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)
Final step - 'Update record', Insert list of records ID (from Find Records) into linked field of trigger record.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Feb 10, 2025 09:27 AM
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
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Feb 10, 2025 09:28 AM
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
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Feb 10, 2025 09:53 AM
@jwag got it! What is your formula like/what are the fields being concatenated? Trying to further understand the use case.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Feb 10, 2025 10:32 AM - edited ‎Feb 10, 2025 10:43 AM
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!)
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Feb 10, 2025 10:49 AM
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!
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Feb 11, 2025 08:25 AM
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!
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Feb 11, 2025 05:43 PM - edited ‎Feb 11, 2025 06:01 PM
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.
