I need to determine overlap between two multi select text fields

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.

David,You setup is too complicated for me to take the time on the forum,  but if you did want to book some time with me, I'm pretty good with relationships and can work through with you to find a solution.

QuickBaseCoach.com

I concur with Mark - this is too complicated. Your most recent description now makes reference to tables for (1) Audits, (2) Requirements, (3) Questions and (4) Checklists and you are contemplating adding yet another table for (5) Procedures and more many to many relationships. I think you would be best served by going offline for a time with or without Mark's help and documenting your tables, relationships and critical fields along with your desired workflow and reporting requirements. Again I would present this schema information without regard to how you would eventually solve the problem using native features.

With regard to what I think is the nucleus of your inquiry ("I need to determine overlap between two multi select text fields") I doubt you are going to find a native solution without a large number of artificial relationships that shuffle information between tables by "looking up" and "look downing" along the table to table relationship. The overlap that you are seeking to identify and report on between two multi select text fields is not something that can easily be done with native queries and formulas but is almost trivial to do using script. In fact using script determining the overlap (ie intersection) is a one line of code once the relevant data is accessed through the API.

The bottom line is that you need to solidify your tables, relationships and critical fields and then use script to calculate the overlap between the multi-select fields (or between the same information modeled as child records).

Thanks guys for your attention to this issue.  I was able to find a solution.  Using a post from Mark on another thread where he showed how to take a Multi-select field and turn it into a formula text field, I did that to both fields and then created six more fields for the audit table called Process 1, Process 2, . . . Process 6.  I used the Left/NotLeft function to parse out each of the processes into one of these six fields.  I doubt my auditor will ever want to audit more than 6 processes at once.  Then I ran a filter that said if any of the Process1-6 fields are contained in the Requirement formula text field field, to check the requirement as applicable to the audit.  Once that was done, doing the import and then unchecking the requirements was easy.