How to link two date columns and freeze one date?

Platform discussions
, ,
1

I schedule deliveries. After the initial delivery, sometimes I have to do multiple reorder deliveries. I would like to be able to see the original delivery date at a glance. My thought was to create an automation that will duplicate that original delivery date and won’t change when I change the delivery date for upcoming deliveries.

I currently use a “Delivery Date” column to track upcoming deliveries and a “Delivery Date - Reorders” column to let me know there’s a reorder delivery coming up. I also use an Order Status column that I’ve automated to put the order into a Completed group once I change the status to Done after all reorders have been completed. (see below for setup screenshot)

I thought about linking the Original Ship Date to Order Type when it changes to Done and setting the date to current but that would exclude the orders with reorders. I also thought about “Adjust this date to reflect changes in this other date” but I can’t add to it to tell it to freeze.

I’d love some suggestions, thank you!

image
image1091×272 30.2 KB

2

Hi Molly,

To answer your question directly, one way of “hacking” or the automation or controlling when that date dependency freezes would be to do the following:

  1. Create an additional date column and hide it. Let’s call this “Hidden Date”.
  2. Create two “Adjust this date to reflect changes in the other date” automations. The first make changes to Column A modify the Hidden Date column. The second should have changes to Hidden Date modify Column B. So rather than A > B, the chain of date changes would be A > Hidden > B. This will make no practical difference until…
  3. Depending on your requirements (I don’t fully understand them), you can have an automation clear “Hidden Date”. This will break the chain of automations and changes will no longer have an effect on other dates.

Sorry if that sounded vague, but the logic of your system is not super clear to me!

Another point here is that you have a board here where items appear to be Clients and also Orders. From a data perspective, this would only really be effective if Clients only had a small or fixed number of Order. That might work for you, however a more standard approach might be to model Orders on a different level such as subitems or items on a connected board. This would allow you to aggregate the dates and show “Earliest” or “Latest” only, depending on what might be relevant to you.

Hope that helps!

Francis