How to Structure Varying Due Dates for Multiple Tracking Systems?

[Coral_Fallon]

Hello! I’m looking to get some help on how to structure my base. We are tracking shipping containers and would like to make our base automatically update the ETA based off where the container was last seen and which path it’s taking to reach us.

For example, if the container has reached the 1st port in a particular path, I would plug in numbers to show it usually takes 60 days to reach the second port, and then 30 days after that to reach the warehouse. I would like an “ETA” field that automatically updates to show the estimated end date.

We have multiple warehouses, and 5 different sets of timelines based off how they’re reaching us (which I am calling paths). We initially had all of our tracked containers in one base. The ETAs did not update automatically, so I’d have to calculate where each shipment is and how long, based off its path, it would take to reach us and then manually update the ETA field.

I was playing with the idea of having each different path have its own table and then trying to link in the ETA fields to another table that shows all the containers we’re tracking. Ideally, all the containers would be on one page at some point for accessibility. The alternative would be to have all the timelines in one table and just have the views dedicated to each path, but because the different timelines would require different formulas for each path, it would be clunky and have a lot of inaccurate information just hidden away (if they would be generating dates for every path, for every container).

I’m hoping to get some help or direction on how to most efficiently structure this. Do they belong in different tables? Would it be possible to have all the containers show up on one page and be accurate?

How can I make the average amount of time it takes to get from Port 1 to Port 2 into a formula that would then show an ETA in a separate column? I’m very new to formulas in Airtable, so any help would be immensely appreciated.

Thank you!

[Vivid-Squid]

Hi @Coral_Fallon
Is the travel time between port 1 and port 2 always the same? If so you can use the DATEADD formula to increment between ports.

So, Ship Date is a date and the travel time is 30 days Port 2 ETA formula would be DATEADD({Ship Date}, 30, ‘days’)

[Coral_Fallon]

It is always the same for each path, but there are many steps to account for. I guess I could just build each timeline into each step though, so I’ll definitely use that formula for the calculations, thank you.

[TheTimeSavingCo]

Hey man, this sounds like a super interesting problem to solve and I’d love to figure it out with you. I don’t really understand your workflow right now, and so my suggestions are probably not going to be that useful, but let me know what you think.

It seems like you’ve got three sets of information and so I’d have them each in one table:

1. Containers
2. Paths
3. Container Last Seen

• Containers would have a link to Paths and Container Last Seen
• Paths would have a record for each path, with fields to indicate all the steps
  • E.g. Path 1 has three steps, going from Location A → B → C
  • If so, we’d create two fields for each location:
    • Step [Step number]
    • Time taken (days)
  • You’d end up with a whole bunch of fields here, and there may be a better way to structure this, but let’s go with this for now
• Container Last Seen would contain three pieces of information for reach record
  • Date of entry
  • The container in question (This would be the link field to the Containers table)
  • Location

With this set up, we should be able to just consistently update the Container Last Seen and the ETA should update automatically

I’m probably missing something super obvious, but I think this could work if my understanding of your workflow is somewhat accurate!

Let me know if this sounds about right and I’ll try to whip something up for you real quick

[Coral_Fallon]

This sounds really interesting, and probably like a much more straightforward way to organize things than I’ve been looking at!

Most recently I was looking at just using a formula in the “Current ETA” field to calculate “if so and so is empty, add this many days to the ETA”, but I feel like it’s going to get unwieldy. I’d need some help with that.

I’m now working on putting together a table like you suggested, but I’m still not sure I completely understand how to organize it in this way. It sounds like it’s on the right track, but linked records always get kind of jumbled in my head. I’d definitely be interested in exploring this idea though! (and thank you so much for taking the time to think about it!)

[TheTimeSavingCo]

Yeap, let me know if I can help with the formulas!

---

I’ve thrown together a working example of what I had in mind here and you can duplicate it to view the formulas! (I actually made a mistake in my base set up recommendation above sigh, sorry about that)

With this, your workflow would be:

1. Set up all the paths and their step days in Paths
2. Add a record to Container whenever there’s a new one and link it to Paths
3. Add a record to Last Seen whenever there’s a new sighting, and update it with the day of the sighting as well as what step it’s on

The assumption I’ve made here is that the sighting is always made at the beginning of the step though

Also, it’s a little unwieldy in that you’d need to remember what each “Step” means for each path too as well, but I figure we can tackle that later