I would like to know if someone has some suggestions for solving this issue.
Calender with information from several tables.
We are using Noloco-tables/collections. We have different tables with course dates, they are separate because they are quite different type of projects. It may be possible to have it all in one table, but it would be difficult to manage, and now it’s too late!
To have all of the dates in the same calender I’m think of adding a new table and have it syncronized. But I’m not sure which way to go:
Use workflows to update the new table for each change in one of the original tables. This is scary, if one syncronization is missing or wrong, the whole thing is of no value.
Use automation to delete the whole new table, and add all of the records from the other tables, i.e. hourly. This is heavy load, and our employees can not be sure that the calender shows the accurate information.
Depending on how many project tables you have one idea would be to redesign the database to only have a shared linked calendar table for start dates and end date with a lookup fields for the dates back to the project table from this new calendar table etc but keep the different project specific fields in their own table?
This way the data will always be correct as there is only one version of the truth. Setting up a sync model in any system is very tricky.
Not ideal as one issue is you will have to have an automation to update the project name in the calendar table on changes in the project table to keep that in sync or have some huge calculation formula in the calendar table to only display the project name that is not empty based on each lookup field back to each project table.
As I type this I wonder if It might be best to have a look at if you can redesign the whole database and start again . Hope that helps, Kieran
It might be a bit painful, but one suggestion would be to have one central table which has links to each of the other project tables (will need to be separate columns of course). Then use look-ups to get the start/end dates (and whatever else you need). You’ll need look-ups per linked field, then have a set of formula fields for start/end etc which look for populated roll-up values.
At least this way you just need to create one additional record each time, and don’t need to worry about syncing data across tables when start/end changes. And you can easily delete these records via a worflow if the project table record is deleted.
If you have 3-4 projects tables this is doable, more than that (or if you keep needing to add new ones), this will become pretty unwieldy!
Just a suggestion
EDIT: sorry, just realised this is pretty much the same solution as Kieran made, that’ll teach me to respond to things first thing Monday morning
I would very much like to have a “clean” solution, and that is for sure to have to start with a redesign, and put it all in one table… Scary! Something for christmas
