Intersection -- Terrible Performance

nverma

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;

UlisesSas

//Edited, for several Program Codes per contact and updates
Hi Nverma, I don't know what NAV version you are using, but if its >= 5.0 SP1, I think your best option would be to call this code on Codeunit 1, maybe on CompanyOpen:

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(1);
  Contact2.SETFILTER("No.", AllContacts);
  FILTERGROUP(0);

END;

It's almost the same you are using, with two basic differences:
-First, it runs only once per user per session
-Second, sets a session-wide filter for that user with the specified "Contact" filter

Whats left is for you to handle new contacts and updates. Probably creating a "Modification datetime" field in every Contact and update it every time a Contact is created or a Program Code relationship is altered to verify every time the user opens the form, would do the trick. If you found newly modified contacts, you could verify if it should be incorporated to that particular users filter, in which case you could just re-run the previous function.

If you choose this way, I would suggest creating a field in User Setup table to store the "Program Code" filter for each user, so you can easily design with error prevention in mind, specially if you consider the 1024 char limit of any text field/variable, and avoid having to execute the same code every time the user opens the application. But this does not sound like a particularly good design, as the same could happen with the Contacts filter too easily.

Hope it works for you!

UlisesSas