Hi All,
I've spent quite a lot of time trawling through the forums trying to find an answer to this questions but can't seem to find anything that can work for me (i've got a horrible feeling that what I'm trying to do is not possible!)
So, I currently have two tables.
1. Project - This table pulls in from an Outlook calendar when all of our projects are stored. I then use various filters to pull up only the jobs assigned to me.
2. Tasks - This table lists 14 todos, which are the same for every project. Each task has a deadline that calculates based upon the date of the project or the date of a site visit (for example, the Risk Assessment is due 2 weeks before the event and each event has a different start date).
I'd like to find a way of assigning all of the tasks to each project as they come in, so I can see their workflows on a gantt and then tick them off as their completed. The assignment of tasks doesnt need to be automatic, but quite straightforward (filling the cell with the ids of the 14 tasks or maybe an automation button).
I think the answer to this lies in using a many-to-many junction table, but Im struggling to make that work and get my head around it. Each project can have many tasks, and each task can have many projects. I assume I link the project and the task in the junction table, and then use the data here to calculate the deadline date.
Is there a way of automatically populating the link table? So for example, I have an event, add the tasks and then the new junction records are automatically made?
Alternatively, its about duplicating the same 14 tasks for each project, but if I need to make global edits (for example, we need Risk Assessments 1 week before instead of 2), I'll have to manually edit every record.
There might be a better way of doing this, but Im stumped if I know how!
Thanks
