Need an easier way to compare fields!!!!!!!!
We have developed a auditing file for or order billing system.
There are 3 main files where with a trigger on them that captures the before and after image of a record as well as some other tracking information such as User, File, Library.
Because I am captureing data from 3 different files I am storing the before and after image in 2 fields as one large string.
Now we want to create the inquiry/reporting over the audit data.
Here's my question.
I would like to be able to refer to fields as an index of some sort. I need to compare the before and after image field by field to determine what fields changed during the transaction. There are many fields in these files.
Is it possible, using a array, data structure to refer to field names by some sort of index.
I am not thrilled about having to compare each field by name
Example:
If BeforeCustomer = AfterCustomer;
Change = 'Y';
EndIf;
I would like to be able to do something like this
x = 1
DoU X = 56; // 56 Fields in record
If BeforeFields(x) <> AfterFields(x);
Change(x) = 'Y';
EndIf;
X +=1;
EndDo;
Any help is much appreciated
There are 3 main files where with a trigger on them that captures the before and after image of a record as well as some other tracking information such as User, File, Library.
Because I am captureing data from 3 different files I am storing the before and after image in 2 fields as one large string.
Now we want to create the inquiry/reporting over the audit data.
Here's my question.
I would like to be able to refer to fields as an index of some sort. I need to compare the before and after image field by field to determine what fields changed during the transaction. There are many fields in these files.
Is it possible, using a array, data structure to refer to field names by some sort of index.
I am not thrilled about having to compare each field by name
Example:
If BeforeCustomer = AfterCustomer;
Change = 'Y';
EndIf;
I would like to be able to do something like this
x = 1
DoU X = 56; // 56 Fields in record
If BeforeFields(x) <> AfterFields(x);
Change(x) = 'Y';
EndIf;
X +=1;
EndDo;
Any help is much appreciated
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
tcfrey:
QUSLFLD was behind my quick suggestion. The API list of fields provides everything needed for populating an array just as you mention. The command DSPFFD provides a similar capability but to an *OUTFILE. (The system catalog might be useful too.)
Given a choice of an API list or a file of field level info (or even writing code to populate your own *outfile from the API list), you should have a fairly easy time of developing almost any kind of process you can imagine.
Encapsulate parts in procedures. Generalize for multiple uses. Shouldn't be too far before you have parts that plug together to handle whatever.
One potential tricky part is "before and after image of the field to a subfile or report". Keep in mind that a %subst() is a character variable. It has no direct knowledge of items such as number of decimal positions. If you intend to write changed values to reports, you'll want to consider use of perhaps the edit APIs. You can pass in a pointer (by value) plus type, length, etc., and edit code or edit word. The result is an edited string.
You don't really even need to know much about the "field" itself except perhaps if it's numeric. Pass the attributes to the edit API and let it figure it out.
Tom
QUSLFLD was behind my quick suggestion. The API list of fields provides everything needed for populating an array just as you mention. The command DSPFFD provides a similar capability but to an *OUTFILE. (The system catalog might be useful too.)
Given a choice of an API list or a file of field level info (or even writing code to populate your own *outfile from the API list), you should have a fairly easy time of developing almost any kind of process you can imagine.
Encapsulate parts in procedures. Generalize for multiple uses. Shouldn't be too far before you have parts that plug together to handle whatever.
One potential tricky part is "before and after image of the field to a subfile or report". Keep in mind that a %subst() is a character variable. It has no direct knowledge of items such as number of decimal positions. If you intend to write changed values to reports, you'll want to consider use of perhaps the edit APIs. You can pass in a pointer (by value) plus type, length, etc., and edit code or edit word. The result is an edited string.
You don't really even need to know much about the "field" itself except perhaps if it's numeric. Pass the attributes to the edit API and let it figure it out.
Tom
Hi
how are you wanting the audit to work?
will it be a report that is run in batch or is it a trigger program that will populate an existing file.
I am just thinking about the performance implications if the file is a high volume transaction file.
Dave
how are you wanting the audit to work?
will it be a report that is run in batch or is it a trigger program that will populate an existing file.
I am just thinking about the performance implications if the file is a high volume transaction file.
Dave
ASKER
Dave:
I already have set up collecting the data I wish to capture. I use a trigger to capture the transactions.
I am only storeing the before and after image when the trigger is fired. Also some tracing data like, calling program, file, User, date and time.
I realize that there are possible issues with performance doing something like this but, in this case, i am willing to trade a little performance for the audit trail.
Our order billing system is rather complicated and there are many users in different departments that have a hand in the life cycle of a customer order. When there is a problem with an order there is way too much finger pointing between departments as to who did what when. (ever heard of that before?)
Trying to "Field" the data will not occr in the program that captures the data via the trigger. Only in the inquiry program / reporting tools to view orders in question.
A larger issue will is the volume of transactions being collected. File size will have to be monitored closely.
Thanks
Thomas
I already have set up collecting the data I wish to capture. I use a trigger to capture the transactions.
I am only storeing the before and after image when the trigger is fired. Also some tracing data like, calling program, file, User, date and time.
I realize that there are possible issues with performance doing something like this but, in this case, i am willing to trade a little performance for the audit trail.
Our order billing system is rather complicated and there are many users in different departments that have a hand in the life cycle of a customer order. When there is a problem with an order there is way too much finger pointing between departments as to who did what when. (ever heard of that before?)
Trying to "Field" the data will not occr in the program that captures the data via the trigger. Only in the inquiry program / reporting tools to view orders in question.
A larger issue will is the volume of transactions being collected. File size will have to be monitored closely.
Thanks
Thomas
ASKER
Dave,
I'm awarding you the points. Since you at least confirmed my thought process.
Thanks again
Thomas
I'm awarding you the points. Since you at least confirmed my thought process.
Thanks again
Thomas
Hi
just a side issue, normally when I need to do a repatative comparison like the example; I tend to cut and past the fields into EXCEL and write a nasty VB program to generate the code, FTP this into a /Copy member then include that in the compile.
usually takes 5-10 minutes and you eliminate the typo's
Dave
just a side issue, normally when I need to do a repatative comparison like the example; I tend to cut and past the fields into EXCEL and write a nasty VB program to generate the code, FTP this into a /Copy member then include that in the compile.
usually takes 5-10 minutes and you eliminate the typo's
Dave
ASKER
Yes, my before and after fields hold a exact image of the record.
Actually I have begun to head in that direction.
I was thinking of using the QUSLFLD API to retrieve a list of field names,field text, lengths and offsets for the correct file. This could be eaisly used to drive the %Substr Functions for the compares.
Therefore, with the example you gave me. I could use values returned from the API.
OffSet = InBuffPos
Len = FldLen
In the example from my orginal posting where Change(x) = 'Y'. I could write the field name, text and before and after image of the field to a subfile or report.
What do you think?