Solving two conradictory requirements

In the past, there have been two outside customers that were granted permission to connect to our database, but only to see their data. In order to satisfy this requirement, I turned on the 'Authorisation for Sales Reps' option and gave them the authorisation to see anything with their agent number. In order to compensate for this, I marked for all our regular users the 'All Sales Reps Auth?' flag in the Details of Current Company form. So far so good.

I was given a thorny problem to solve a few weeks ago: someone who works with our company had full access to the database, but he has been 'demoted' and now works only as an agent dealing with after sales orders (e.g. a customer wants an extra table or similar). For this capability, he should have the Authorisation for Sales Reps' option marked for his agent number and the 'All Sales Reps Auth?' flag unmarked. But he also has to see orders of other agents so that he can deal with their installation when required.

On the face of it, then, this person has to be able to both see only his orders and to see all orders: two contradictory requirements. For some time (a week), I deliberated how I could achieve this; I wasn't sure that it was a problem that could even be solved. Then I had a brainwave (in the shower, of course, where all good ideas come): I could write a procedure that marked the 'All Sales Reps Auth?' flag via an interface, then all the required orders could be saved in a temporary table, then the permission would be revoked. This sort of worked, but when I tried it under the person's username, I got no data. I realised that this was that by the time the user came to look at the report containing all the required orders, he was no longer be able to see them as the permission to do so had been removed.

The solution then was to divide this procedure into three stages: the first would give the permission, the second would show the report (i.e. the orders) and the third would retract the permission. Despite trying very hard, I couldn't get the third part to work which was very frustrating. I had also missed the fact that this person could do whatever he wanted to do with these orders when displayed, which was not part of the mission requirement.

A day later, the perfect solution popped into my mind. Instead of being fixated on a procedure and a linked report, I should display the data in a form that would be based on the ORDERS table, but allowing write access only to the fields that this person was allowed to change. Also, many fields that appear in the ORDERS form wouldn't have to be displayed thus greatly simplifying the form's logic. The magic in this form comes from something that is documented but that most people would never need. On page 80 of the SDK for version 23.0 appears the following

PRE-FORM triggers perform operations before the form is opened. This applies
to all root forms, as well as sub-level forms for which the Automatic Display
column of the Sub-Level Forms form (a sub-level of the Form Generator) is
blank. This type of trigger may be used, for example:
 to reset the value of a user-defined variable
 to generate a warning or error message concerning retrieved data
 to retrieve and display all records when the user opens the form —
:KEYSTROKES = ‘*{Exit}’;
 to refresh all retrieved records in a form following a Direct Activation —
:ACTIVATEQUERY = 1;
 to deactivate data privileges in a form — :$.NOCLMNPRIV.T = 1;
 to deactivate data privileges for a specific table in a form: in a new PRE-FORM 
trigger for the form in question, define the :$.NOTBLPRIV.T variable
with the name of the desired table; if the table you want to exclude has a
join ID, this should also be specified

In other words, if I include in the pre-form trigger the command $.NOTBLPRIV.T = 'AGENTS' then the user would not be limited to seeing only his orders but could see everyone's (where a different field contained his order order). This didn't work, but swapping this command with :$.NOCLMNPRIV.T = 1 did.

So I managed to solve two conradictory requirements.