Data Separation – A Couple of PitfallsPosted: January 18, 2012
Well, the data separation exercise has been going pretty well, and I can really see how it will make life easier, now that several organisations will be using the system. Swapping in a new interface file will be a doddle, with no need to import data etc.
BUT, there’s no gain without pain, and a couple of things recently have tripped me up.
1. GLOBAL VARIABLES
Global variables do not work across files. So, where a variable is set in the interface file, and it is used to set a field value, or in a calc field, in the data file, it doesn’t work. This has been an issue with the “portal sorting” technique that I use, whereby a variable is set to indicate the sort order to be used (“surname ascending”, “date descending”, etc.), and that variable is used to calculate the value in a field in the table (i.e the table on which the portal is based). The solution is simple, i.e. to use a global FIELD (in the Interface table for example) rather than a global VARIABLE, but it was a bit of a shock to see a time-honoured technique just not work.
2. RUNNING SCRIPTS WITH [FULL ACCESS]
The way I’ve been handling deletions so far is to have delete not allowed for system users, but then, after all the pre-checking and confirming etc., running a delete script with [Full access] privilege. Worked fine, until the data was separated out into a separate file – the [Full Access] thing doesn’t work across files.
So the possible solutions are:
- Allow deletion to all users – obviously not really an option.
- OR…do all the scripting of the deletion routine in the data file – rather messy, and spoils the principle of having no scripts “over there”.
- OR…set the delete protections to “limited”, and allow deletion only in certain circumstances.
I decided to go for the last option, as it was the easiest to implement, and seems pretty secure. I have a global field in the interface table (which can obviously be seen throughout the system) called INT_g_DeleteOK. Once all the deletion checking has been done, the script sets this field to “Yes”, does the delete, and IMMEDIATELY sets the field to “No”. Obviously, until the field is set, it is empty, so testing for INT_g_DeleteOK = “Yes” seems pretty bullet-proof.