I am creating multiple recurring checklists - with multiple Checklist Items AND multiple (record specific) fields associated with each Checklist Item.
I have four related tables for this:
Contacts - Contains ID (and misc contact specific info)
Checklist Name - Contains ID and Checklist Name
Checklist Items - Contains ID, Checklist Name Ref, Checklist Item Order and Checklist Items
Checklist Scheduled - Contains ID, Contact Ref, Checklist Name Ref, Checklist Item Ref, Date Due, Date Completed
My initial goal is to create reusable checklists of recurring steps within business processes to be attached to “Contacts” by selecting a “Checklist Name” from a list of checklists contained within the “Checklist Name” table. Then populate the “Checklist Scheduled” table with “Checklist Items” associated with the selected “Checklist Name”, then store the “Date Due” and “Date Completed” data for each “Item” as “Checklist Scheduled” records within the “Checklist Scheduled” table.
I am struggling to tie this all together.