Help

Updating task dates based on changing dates in another table

Topic Labels: Base design
139 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Sara_Hardison
4 - Data Explorer
4 - Data Explorer

Hi, 

I have a Contest Definition Table  that is capturing specific dates related to a contest.  I have start and stop dates for about 5-7 different stages of a contest, so Nominations Open, Nominations Close, Voting Opens, Voting Closes, etc. 

I have a record template set to create a set of tasks in a separate Tasks table for each contest based on those dates. The tasks are linked to the Contest Definition Table, All of that works just fine.  Each task has it's own Start and End date. There are actuals tasks for Open Nominations, and Open voting which is set based on dates designated in the Contest Definition Table. 

What I am having a hard time figuring out is how to update theTask dates when dates change in the Contest Definition Table. 

Thanks for any help you can provide. 

2 Replies 2
FarioConsulting
6 - Interface Innovator
6 - Interface Innovator

Hi,
You could create a link field in your Tasks table to connect each task with the corresponding stage in your Contest Definition Table.....Then, add a formula field in the Tasks table that pulls start or end dates based on the relevant stage's date (like Nominations Open) from the Contest Definition Table.

You can also set up an automation to trigger whenever a record in the Contest Definition Table gets modified....it would help check if there are tasks linked and updates their dates accordingly based on your formula

In the Tasks table, what if you used lookup fields to pull over the dates and create a formula field to consolidate that data? 

The formula field would check what the name of the task was, and based on the name of the task would display the date from a specific lookup field.  You'd lose the ability to modify the dates from the Tasks table though

Screenshot 2024-12-08 at 5.42.40 PM.png

Screenshot 2024-12-08 at 5.42.46 PM.png

SWITCH(
  Name,
  'Planning', {Planning (from Table 1)},
  'Noms Open', {Noms Open (from Table 1)},
  'Noms End', {Noms End (from Table 1)}
)

Link to base

---

If you wanted to keep modifiable Date fields in both tables that synced up with each other you'd need to create automations to handle that for you I'm afraid

To handle that you'd create the lookup fields as mentioned previously, and use a formula field to compare the current 'Date' field value with the appropriate lookup field value.  If it doesn't match, make it output 'Update' or something, and then you'd get your automation to trigger off of that and paste the lookup field's value into the Date field