Archiving

[hsl9999]

I have a database for our front desk cashiers. They enter sales and payments, print reports etc. Each is assigned a suer code with password. I would like to log every transaction made every day and be able to later look at each user to see what transactions they keyed in. For example if a customer says that cashier Jane Doe took her money and did not enter the payment on her account, I want to be able to go to the log, search by date and user name and look up all transactions that that user made on that day. Any suggestions for a continuing log file?

[Tom Cone Jr]

Re: Archiving

This will involve a bit of work but it's not rocket science.

1) The table structure for your sales and payments needs to include fields to record user name (or code) that entered the record, date (& possibly the time) the record was entered, user name (or code) that last changed the record, and the date / time the last changed occurred. These 4 fields can be populated by defining calc field rules. They shouldn't be displayed on the form. Examples illustrating this are available on the message board.

2) A second table should be created to track the changes that may occur to a record in your sales and payments table. This table would permit the changes to be audited by a supervisor, and is sometimes called an "audit trail". Records in this table get populated automatically by addiitonal field rules defined in your sales and payments table. An example of this including all necessary code is published in Dr. Wayne's "Xbasic for Everyone" book available for purchase directly from Alpha Software.

-- tom

[CALocklin]

Re: Archiving

I use calc fields for creation date/time but I would almost NEVER make the last changed date/time calculated fields.

A calculated field can be changed by too many things. BTDT - more than once! For example, a global update of all records to modify some other field would end up changing the last changed date/time on ALL records to "now". This is seldom desirable but, if it is, it can be included as part of the update.

My preferred method is to put something like this in the CanSave record event of the table. That event is not triggered by most built-in operations. It is also not triggered by xbasic changes. NOTE that last sentence! If you do something in xbasic and want to update the last changed date/time, you must include the date/time update(s) in your xbasic script when using this method.

tc = table.current()
tc.change_dtf = date()
IF eval_valid( "tc.change_tmf" )
   tc.change_tmf = left(time(),5)
END IF
[COLOR=blue]'Following is used only if a *script* creates the record but doesn't include 
'the creation date. This will add the "creation date" BUT only as the date of
'the first change. It is only a "just in case" feature to get *something* in 
'the create_dtf field as soon as possible.
'You may not want or need it - especially if, unlike me, you never make mistakes.[/COLOR]
IF tc.create_dtf = {}
   tc.create_dtf = date()
END IF

In fact, I do this so frequently that it's one of my keyboard macros. I just type jjchgdt and the macro types the whole script - comments and all. (Although I added extra comments here for clarification.)

If you are concerned about "table.current()" pointing at the wrong table when using a set, don't be. Since it's in the table's own field rules, it will always point to that table - I've verified this with one of the programmers at Alpha. (Probably Lenny.)

[hsl9999]

Re: Archiving

CALocklin, I tried that as you stated by putting it in the CanSave record event of a table. but I get the following error:

Script:CanSave line:6
tc.change_dtf = date()
Property not found
tc.change_dtf subelement not found.

Then I saw your statement "you must include the date/time update(s) in your xbasic script when using this method."

Being a relative newbie I did not quite understand what to put in and where.

[John Oesterle]

Re: Archiving

Harold -

In Cal's script "change_dtf", "change_tmf" and "create_dtf" all refer to date or time field names in his current table. You are likely getting the error because your field names are different. Either change the field names in the script to match your table's field names or change your table's field names to match Cal's.

- John O

[Tom Cone Jr]

Re: Archiving

Thanks, CAL, for the reminder concerning the damage mixing calc field rules and table wide operations can cause. -- tom

[hsl9999]

Re: Archiving

I understand that each transaction would have a date and time marker using the method shown by CALocklin, however if a dishonest cashier with a bent for theft decided to charge a customer, receive the cash payment, and then later on delete the payment by deleting the transaction, then the date and time marker for the transaction would be deleted as well and no history of this would be available. Assuming that the cashier does have the user right to delete transactions, how can I use CALocklin's process to create an archival history record different than the transaction record, each time that a transaction is made that cannot be removed except by the administrator. I would like every activity, including adding user names to the user name table, printing an invoice would create an entry etc. For example the history file might look something like this:

USER NAME ACTIVITY DATE PERFORMED TIME
JJones Payment to acct 10025 $100.25 5/6/09 13:31:21
HSmith Invoice #506533 Printed 5/6/09 14:12:02
DMazoni CHARGE for 10233 on Bill 12082 $20.00 5/6/09 14:13:39
DMazoni CHARGE for 90255 on Bill 12082 $22.50 5/6/09 14:13:42
DMazoni CHARGE for 1923 on Bill 12082 $5.00 5/6/09 14:13:57
DMazoni CHARGE for 1233 on Bill 12082 $1.00 5/6/09 14:14:41
DMazoni CASH PMT to 5233 on Bill 12082 $48.50 5/6/09 14:13:39
DMazoni DELETE CASH PMT to Bill 12082 $48.50 5/6/09 14:20:04
DMazoni VISA PMT to 5233 on Bill 12082 $48.50 5/6/09 14:23:27

I hope that this gives you a better idea of what I am looking for.

[CALocklin]

Re: Archiving

There is a built-in method for tracking transaction changes but I don't recall what name to search for in order to find it.

If nobody can find it or if it doesn't do what you want to do, it's very possible to "roll your own" routine to either save the info in a separate table or in a text file. Depending on how many transactions you want to keep, the text file may eventually get pretty slow but I know it can take quite a bit before that would be an issue. Of course, neither is perfect - if someone finds the table or file, they can usually edit it or delete it unless you take more extreme measures.

Before going into any of that, let's see if someone can think of the built-in method.

FYI: The table doesn't have to appear in your control panel either - although you might want to at least add it during development. On the other hand, it's probably not a good idea to allow you users to have access to the control panel.

[Tim Kiebert]

Re: Archiving

'Audit trail'

Built in: http://support.alphasoftware.com/alp...il_Feature.htm

And User built: http://msgboard.alphasoftware.com/al...ad.php?t=68530

[Tom Cone Jr]

Re: Archiving

Harold, see point #2 in the second post in this thread. Dr. Wayne's approach uses a separate table to keep a record of all changes, including deletions. Works very well. -- tom