How to determine intersection between two fields?

Hello Everyone, One of our clients wants a new feature added to contact cards. They want to control which contact card a user can see. On the contact card, we created a new field called 'Program Code". This is a text field and a contact can be associated with multiple program. We modified the user card, and added a list form to the bottom where the administrator can list the program that he/she (user) can see. Problem Let say the contact’s Program fields contains the following value: A|D|M|Q UserCard mentions that the following user can see these program: M|C|P Now I need to write a code, that if any values intersect between the Program Code field in contact card and the list of programs the user has permissions to view, that contact should be visible to the user. How do I wrote this code in the most efficient way. I wrote some code, OnOpenForm Tigger and it works, but the performance is HORRIBLE. Please look at my code below and suggest me alternatives to make it better or help me come up with a better way to determine when values intersect between program code and user permission. UserSetup.GET(USERID); IF UserSetup.“Contact Management Supervisor” <> TRUE THEN BEGIN IF Contact2.FINDSET THEN BEGIN REPEAT CLEAR(Executed); UserSetupLine.SETRANGE(“User ID”, USERID); IF UserSetupLine.FINDSET THEN BEGIN REPEAT Position := STRPOS(Contact2.“Program”, UserSetupLine.“Program”); IF ((Position > 0) AND (Executed = FALSE)) THEN BEGIN Executed := TRUE; IF AllContacts = ‘’ THEN AllContacts := Contact2.“No.” ELSE AllContacts := Contact2.“No.” + ‘|’ + AllContacts; END; UNTIL ((UserSetupLine.NEXT = 0) OR (Executed = TRUE)) ; END; UNTIL Contact2.NEXT = 0; END; FILTERGROUP(2); Rec.SETFILTER(“No.”, AllContacts); FILTERGROUP(0); END;

I apologize how messed up the code turned up in the post above. I had it properly indented, I am not sure why it did.

Hi Nitin,

The first observation I’d like to offer is that it looks like you’re storing multiple values in a single field, and storing data in this way defies conventional wisdom related to data normalization. I’d recommend that you straighten that out before doing anything else. For each User, you’d want an entry in a separate table to identify which Programs the user is allowed to view. In a similar manner, you’d make a list of each Program to which a Contact belongs. If you really wanted to get cute, you could do both with a single table definition, but I think making two tables (one for User Program, and one for Contact Program) to show the relationships, and a third to list Programs, might be your best approach.

Once you have that solid architecture in place, building a process to associate Users with Contacts should be pretty straight-forward.

But, all that notwithstanding, if you must keep this existing architecture, then you might look at the MARK and MARKEDONLY functions. Rather than stepping through every single Contact record, looking for a match, you might parse your way though the various Program values in the User record, one Program at a time, then filter the Contact record, once for each of the Program values in the User record, after filtering the Contact record, loop through the filtered Contacts, setting MARK on each one. Once you’ve been through all of the Contacts for each of the User Programs, set MARKEDONLY on the Contact record then display it.