Skip to main content
Question

I need to determine overlap between two multi select text fields

  • November 21, 2018
  • 0 replies
  • 142 views

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.
This topic has been closed for replies.

  • Registered
  • November 21, 2018
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.

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.

Lookup
[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])
or
Contains([Procedures(text format)],[Audit Procedure 2])
or
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.