How to use not match when it is a many to many relationship

[Anonymous]

I have a functioning list box that displays all personnel who have "Qualification A" using this expression:

     If(match(CertTypeRequired, CertTypeName), FULLNAME)

I'd like a list box displaying the personnel who do not have "Qualification A".  I tried:

     If(not match(CertTypeRequired, CertTypeName), FULLNAME)

But, since my data set is a many to many relationship, there are many "Qualifications" that do not match other "Qualifications Required".

Data Example:

FULLNAME1

Has:               Is Required to have:

Qual A            Qual A

Qual B            Qual Z

Qual C            Qual H

The If(match...) statement correctly returns Qual A.  The If(not match...) statement returns all personnel since everyone has a Qual A that does not match Qual Z and Qual H; the Qual B does not match Qual A, Qual Z or Qual H.

I would like the list box to return anyone that does not have Qual H when Qual H is selected in essence comparing a many to many relationship.

Anyone have suggestions?

Lisa

I am using this in an expression of a list box.  I know this is not a scripting question, but a moderator suggested I post in Scripting so I could have a chance to get better answers!   Thanks Again!!

Added file Message was edited by: Lisa Clifton

[sunny_talwar]

Dummy Data:

Table:

LOAD * Inline [

FN, QUAL

N1, QA

N1, QB

N2, QB

N3, QC

N4, QA

N5, QC

N5, QA

];

Try using something like this:

=If(not WildMatch(Aggr(Concat(QUAL, '|'), FN), '*QA*'), FN)

Also attaching a qvw file for your reference.

Best,

Sunny

[Anonymous]

Thank you!  Unfortunately, I have over 900 FULLNAMES and each can have up to 20 Quals and it is a constantly changing data set.  So, Load inline is a little time consuming and requires too much maintenance, if I understand the suggestion.

[sunny_talwar]

You don't have to do a inline load. That was just a sample I prepared to show you the functionality of how it can be achieved. If you share part of your database, I can replicate the same for your data also.

HTH

Best,

Sunny

[Anonymous]

I uploaded my file.  Thank you for your interest in assisting me!!  The third tab is where I am trying to get the Missing Certifications list box to show who does not have the certification that is selected in the Certifications Required list box.

So it is expect6ed that the user will choose a rank.  That selected filters the Certifications Required list box to show only what is required for that rank.  The user will then pick a Certification from Certification Required and the Missing Certification list box should display who does not have that certification.

Thanks again,

Lisa

[sunny_talwar]

Only one selection will be made in the Certifications Required list box or users can make multiple selections as well to check if a person has both the certifications?

[Anonymous]

Usually just one, but the option to check multiple would be beneficial.  Thank you so much!

[sunny_talwar · Accepted solution]

Right now it works for only one selection in Certifications Required. See if it is useful, we can build it from here. It uses a trigger on the field CertTypeRequired field to set value for a variable.

HTH

Best,

Sunny

[Anonymous]

Oh, THANK YOU!!  You used a variable I see.  I need to look that up so I understand what you did!  But, thank you for your assistance.  I only create a QV app once every 3-5 years and seem to have to learn it over each time especially with our upgrades.    You are a gem!  (and may see more questions from me on this app!)

A happy Lisa!

[sunny_talwar]

Not a problem. Have a wonderful weekend now.

Best,

Sunny