Expand all | Collapse all

I need to determine overlap between two multi select text fields

  • 1.  I need to determine overlap between two multi select text fields

    Posted 11-21-2018 15:32
    I am creating an APP to generate questions for an Audit system.  I have a list of 80 different requirements that apply to 26 different company procedures.  Not all of the requirements apply to each of the procedures.  In fact most procedures only have 4-5 of the 80 requirements that apply.  For each of the 80 requirements I have a multi select field where I've selected each of the procedures that apply to that requirement.  Some requirements only apply to one procedure while others apply to 4 different procedures.

    If I am only auditing one procedure at a time, it is easy, and I check to see if the procedure attached to that Audit is contained in the multi select field for each requirement.  If it is, then I check that requirement and then do a table to table import for all the checked requirements.

    However, my head auditor threw a curve ball at me and and she wants to audit multiple procedures at once.  So now I had to create a multi select field on the audit where she can select multiple procedures.  Now I have to check to see if any of the procedures selected in the audit match any of the procedures assigned to each requirement and check all the requirements that apply.

    QB isn't allowing me to filter based on if one multi select field contains another multi select field.  Any  advice would be appreciated.

  • 2.  RE: I need to determine overlap between two multi select text fields

    Posted 11-21-2018 16:39
    I don't have a good understanding of what your tables or workflow is. In your description you mention (1) Procedures, (2) Requirements, (3) Audits and (4) table to table imports. Which of these are tables and what tables are involved in the potential table to table import? Also in which table is the multi-select field and what options does it take?

    I would just describe your existing table and field structure without regard to what the final solution might be or if it can even be done using native features. I am 100% confident this can be solved using script and you will probably be amazed at how short the solution is. But I really don't understand how your application is structured.

  • 3.  RE: I need to determine overlap between two multi select text fields

    Posted 11-23-2018 15:43
    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.

  • 4.  RE: I need to determine overlap between two multi select text fields

    Posted 11-23-2018 17:41
    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.


  • 5.  RE: I need to determine overlap between two multi select text fields

    Posted 11-24-2018 15:01
    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).

  • 6.  RE: I need to determine overlap between two multi select text fields

    Posted 11-26-2018 16:59
    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.

  • 7.  RE: I need to determine overlap between two multi select text fields

    Posted 11-21-2018 22:59
    If this is a single user type app (I also have a multi user solution) my solution would be this.

    Create new table called Select Procedures and enter a single record and then lock down so no more records can be entered.
    It will be Record ID #1.

    Create a few multiple choice fields (not multi-select) but multiple choice, with drop downs for the 26  procedures.  Say you initially create 3 fields called
    [Audit Procedure 1],
    [Audit Procedure 2],
    [Audit Procedure 3]

    Create a relationship to your Requirements so that 1 Select Procedures has many Requirements and join the two tables with a formula numeric field with a formula of 1.

    [Audit Procedure 1], 
    [Audit Procedure 2],
    [Audit Procedure 3]

    down to Requirements

    convert the multi select field for Procedures on the requirements record to text in a new formaul text field called called [Procedures(text format)]

    Then have an formula checkbox

    Contains([Procedures(text format)],[Audit Procedure 1])
    Contains([Procedures(text format)],[Audit Procedure 2])
    Contains([Procedures(text format)],[Audit Procedure 3]) 

    That will highlight Requirements for the selected three procedures.

    I'm not sure what you are doing with the table to table import after that.