Audits is the main table. Procedures are not a table. They are internal procedures/processes in our company. Originally they were just documents you could attached to the audit and when you created the Audit, you would name it with a reference to the procedure you were going to audit.
I'm beginning to think that I will have to create a table of Procedures. But then I will have several many to many relationships.
The Requirements are the ISO 9000:2015 standard. It is divided into about 80 sections and each section can apply to multiple of our internal processes. I have converted the standard into an excel file and then imported it into QB into a table called requirements.
Then I have another table called Questions. These are a list of hundreds of questions that apply to each section of the standard. Each section of the standard has 2-10 questions assigned to it.
Then I have another table called Checklist questions. This is a table that is directly attached to each audit. Each audit can have multiple Checklist questions. We just had an audit in the spring, and each of the 26 audits we did had 10-80 checklist questions. Since many sections of the standard apply to multiple of our procedures, many of these questions are identical but depending on which audit they are attached to, they were answered differently for each audit and have different evidences attached to them.
If I were to create a table for each of our procedures, each procedure would have many sections of the standard assigned to it and each section of the standard would have many procedures. Also each audit can have many procedures and each procedure would have many audits. That sounds too complicated.
So now the procedures live in two multi select fields. One in the audit table and one in the Standard table. The idea is to do a table import from the Questions table into the Checklist every time you create a new audit and assign procedures to it. Since each section of the standard (and therefore all the questions assigned to it) identifies which procedures it applies to, and since I have identified each audit for which procedures it applies to I feel they should be able to talk to each other.
If an audit has the same procedure selected as any part of the standard, then I want to check a checkbox on the section table that will get passed to the questions table. Then I can import all the checked questions to the Checklist table and assign them to the audit in question. Then I go back and uncheck all the checked sections.
I have the automation built, I just don't know how to do the logic comparison between the two multi select fields.
