Find and Constrain Found Set return too few results

[PsyberDave]

For the most part, my database is running well, but ocassionally I get calls from my users that they aren't seeing all of the records they are supposed to. I have found something that fixes the problem, but the fix is temporary and the problem returns the next day or a few days later.

The database has a table of students and a related table of mentors. There is also a User table that contains which office each user belongs to. Each student and mentor record contains a code identifying which office they belong to. Permissions are set so that users can only view student and mentor records that belong to their office (e.g. for Student records, View permissions are set as Ocode = Users::Ocode or OcodeOrig = Users::Ocode where Ocode is the "office code" and OcodeOrig is the original office code for students who originally were with another office.)

A script runs at log in which looks up the user's office and finds the students that belong to that office. That script is set to run with full access privileges.

This all works fine for the most part, but I suspect a privileges-related problem. Sometimes my users do finds or constrain their found set and the resulting found set is smaller than expected. They may know they have 20 12th graders, but when they do a Find for 12 in the Grade field, they may get something less than all 20 of their 12th grader records. Similarly, this happens with constrain found set.

As a developer with full access to all records, I don't see this problem until users call me. I have found that if I go into Define Accounts & Privileges, go into the Custom Record Privileges for the Student records (or any of the other tables that has limited viewing) and open the calculation for View (without actually making changes) and then save everything, the problem resolves. Sometimes it stays fixed for a day, but often it works for several days. Clearly, this is not a good enough fix.

What might be going on? Is the issue on the server (Server 8) or might it be an indexing issue (Ocode is fully indexed in all tables that use it)?

[bcooney]

e.g. for Student records, View permissions are set as Ocode = Users::Ocode

How are you relating Student to Users? I would think that you'd have to store the current account's Ocode in a global field "gOcode"(set it in your startup script). Then the access priv calc would be Student::Ocode = gOcode.

[PsyberDave]

How are you relating Student to Users? I would think that you'd have to store the current account's Ocode in a global field "gOcode"(set it in your startup script). Then the access priv calc would be Student::Ocode = gOcode.

Right you are. My script sets the Students::UserGlobal field with Get(AccountName). The Students::UserGlobal field is linked to the Users table (Students::UserGlobal to Users::User) where there is a matching record containing an Ocode. That is how the privilege calculation can check the Students:Ocode against the Users::Ocode.

You have me thinking...Should I put the user's Ocode in a global field in the Students table (say, UserOcodeGlobal)and then check the Students::Ocode against Students::UserOcodeGlobal?

[bcooney]

Your permission calc should compare the record's Ocode to the global value, what ever table it's in, doesn't matter. You could even use a $$variable.