I am trying to figure out the best way to implement a set of required tasks that need to be marked as done before an event can then be marked and considered complete.
I have a table that has 7 fields representing these tasks that need to be completed
- Did we get the event times
- Did we advance the sound
- Did we advance the lighting
- Did we advance the staging
- Did we send that info over to the shop
- Did we call all the employees for the gig
- Did we invoice the client
(Note: these are things that need to happen for each Event / record that is created.)
(Note: some of these items do not always need to be included like sometimes the Event will only need sound, sometimes it will need sound and lights, sometimes it will need sound, lights, and staging.)
I already have 3 other fields that are used for representing what type of gear is going to the Event. Sound, Lighting, and Staging. When an event is created they will choose what of those 3 categories are going to the event with a button that changes those category fields from 0 to 1 to be able to use in calculations as a boolean or numerically for calculation. This solves "what" will determine if the question field ie "Did we advance the sound", "Did we advance the lighting", or "Did we advance the staging" will be included.
Users will then interact with a layout that is pretty much the checklist of these tasks and it consists of 7 buttons. Each of them run a script that sets the corresponding field from 0 to 1 representing that the task has been completed.
I then have 2 other fields one representing the total number of task fields that are included. ie if the sound category has a value of 1 then include it in the total if the lighting has a value of 1 include it in the total. The other field represents the total of those tasks that have been marked as done.
I would then like to use those 2 fields representing the total tasks and tasks that are done in a dashboard with a portal that lists all the current events and show x number of tasks are done out of x number of total tasks for each event.
I am not sure exactly the best way to calculate those last 2 fields for the total tasks and total tasks that are done. I tried writing a calculation that would look at all the fields representing the tasks and if they are marked as 1 then add 1 and iterate through all of them. This would give me the completed number but not sure how to calculate the total number of included tasks so the total number of tasks to do will reflect what is actually being sent to the event like sound lights or staging.
It would be really great if I could get some feedback on if this is the right direction to go or if there is a much more simple way to go about this. Also, I wanted to mention that the example above is just an example. The real use case has over 25 tasks and 15 of them are optional. I also do not want to put something in that is going to effect performance because these are calculations and they will be shown in a dashboard portal with a list of current events and this is deployed from the internet. So trying to not make it calcualte of the fly everytime someone goes to the dashboard.
Let me know if there is any other info I can provide. Hopefully I explained this enough that it makes sense.
Thanks in advance!