Alerts using a trigger in one sheet to update information in another sheet

A&W Admin

Hi,

Here's my issue - in our Project Intake Sheet (which is connected through Control Centre), we have 3 columns of Target Dates related to restaurant opening. These 3 columns are estimated on intake, and updated throughout the life of the project. However, we have a situation where the PM often forgets to update these dates as we get into Construction, as the columns are on the Intake Sheet.

I was going to set up an automation in one of our rollup sheets to trigger an Alert to the PM when the Construction End Date changes to remind them to update these dates. However, since the source of the dates is the Intake Sheet, you can't amend the dates in the rollup sheet without breaking the links.

My initial thoughts are:

1. Somehow link the Construction End Date back into the Intake Sheet so I can run the automation alert from the Intake Sheet. However, I'm not sure if this is recommended since it's an intake sheet (nor do I know if it's actually possible without manually linking each row? Maybe through an automation?)
2. Send the automation through the Rollup sheet with a link to the Intake Sheet or a Current User Report filtered for only that PMs project. It's inelegant because the PM will have to search for the project

Has anyone found a more elegant solution, or have any feedback on the options I've listed above?

Thank you!

Paul Newcome

Do you have a Project Plan as part of your template set? If so, does that contain a line item for each of these milestone dates you are wanting to track?

A&W Admin

Yes, we have a project plan, but no, these line items are not milestones in that Project Plan. The columns are linked into the Metadata sheet for each project before being reported into a Rollup sheet.

Paul Newcome

In that case, I would stick with sending the alerts from the Summary Sheet and including a link to the Intake Sheet in the alert.

A&W Admin

Thanks for your response, Paul. I appreciate you taking the time.

Can I ask why you suggest this route? Just a best practice, or is there another reason?

Paul Newcome

Happy to help. 👍️

Based on your requirements including wanting to avoid a report that users would need to search through, that's just what happens to be a good mix between user friendliness and build complexity vs effort. It could probably be made to be more user friendly, but that increases the build complexity to a point where it isn't really worth the effort when you look at the user interface of this solution.