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.
