I’m currently working on a base to track our inbound containers. We’ve had a simple one for a while where we will update the movement of our shipping containers, and then manually update the ETA, based mostly off guesswork. I’m looking to automate the ETA updates, based off when a container was last seen and where it was last seen, using the average number of days it takes to get from place to place.
For some context: we have 5 different shipping routes spread across 3 different warehouses. These all have a different number of steps to get through before they reach our warehouse.
The way I’ve designed it thus far (with the help of someone in a forum, thank you!), is to have the following tabs:
Containers
ETA Calculation
Paths (Routes)
The container tab has generic information about the containers we need to track. I have it linked to the Path tab to show which Port it will need to go through. I also have a “Last Activity” as a date field and “Last Known Movement” as a single field drop down to show the last step it went through. The goal is to get a “Current ETA” field in this tab to generate automatically using a formula.
The ETA Calc tab is linked with the Containers tab to show the Last Activity and Last Known Movement fields. It also has the PATHS field to show which route it is taking. Alongside the name of the route, I’ve also included all the days from the linked record that it takes to get from step to step - one field per step. This is to show the numbers for the route that specific container will be taking. I’m hoping to use these as the data with which to create my formula.
Lastly is the Paths page. This simply has a list of each route and how long it takes to get from place to place.
My initial thinking was to create a Nested If formula that basically said if it’s arrived at the warehouse, use that date. If it hasn’t arrived at the warehouse, use the step before that and DATEADD the number of days it will take to get there. If it hasn’t arrived at the step before that, use the step before that one, and on and on. This was resulting in a large, unwieldy formula that I know isn’t formatted correctly (but I’m still in the brainstorming mode). It looked something like this:
=if
(ARRIVED AT WAREHOUSE >0, ARRIVED AT WAREHOUSE,DATEADD 1st PORT DISCHARGE,4,'days,
(IF(1st PORT DISCHARGE >0, DATEADD 1st PORT DISCHARGE,4,‘days’,), IF 1st PORT ARRIVAL>0,
but with a lot more steps to it.
Now I’m thinking that instead of working backwards and going through every single step, I’d like to just calculate how many days it takes to reach the warehouse from wherever the container currently is. However, this would be reliant on being able to use data in a field as part of a formula. That formula looked something like this:
=IF
(LOCATION=(1st DEPART),DATEADD({1st DEPARTURE TO 2nd PORT (from PATHS)},TODAY(‘days’)))
I’m not sure if this is possible, but I feel like it’d be way more efficient if it is.
Hopefully this makes even a smidge of sense. I’m new to formulas in general, but especially Airtable formulas. If you have any input or a different way of looking at things, I’d appreciate any perspective or ideas I can get about this. I’d also be happy to share screenshots of what the base currently looks like if that would be helpful. Thank you in advance!
