To comment on:

"Sorry, I don't understand why do you store the 'changed value for this
field' in the audit form, but don't store the 'value of the field before
the changed occured'."

If you create an audit entry on submit for all the fields that are
audited on a form, then capture the transactional changes to these
fields on an individual basis, you have a cronology of the
transactional changes per field since the beginning, showing the
time/user making the change. The data in this format is pretty easy
to render in a table field for users to view the audit data on a per
field basis. I think this is a pretty good design because users
usually want to know when one field changed (assigned to, status,
etc.) and not all of the audit data lumped together.

A few more details/areas for expansion:
- There is some tuning that is pretty necessary when designing
something like this. I wont go into gory detail, but if you have
questions, do ask. An issue you may run into is if you audit values
on submit, and you attempt to create 10 tasks on a change request at
the same time. Don't fret though, if written properly this does not
present an issue.
- Update the old audit records with an end date/time
- Calculate time diffs (business / real) between the start/end times
(good for reporting)
- Use the status field to mark the record either current or historic
(makes reporting and indexing easier). This can be used to allow
users to view all the last changes for audited fields for a given
request, instead of on a per field basis.
- Don't use the form/entryid to relate audit records, use a guid
instead (This indexes much better than the form/eid, and the guid (FID
179) is available to filters on submit without any special handling)
- If the audit form is lean, storage shouldnt present an issue.
Figure that you can get each row into 250 bytes. 10m rows (which is a
very large number) only equates to 2.5gb (plus the history table
data)). (avg of 100 audit records per 100,000 entries)

On the subject of getting the value of the field before it changed, I
encourage you to just store the audit data in submit so that you
already have the value before it changed. Performing a lookup on each
field that changed after the fact will cause you to incur a
select/GLEWF against your ticketing form for each field, which can be
expensive.

Axton


UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
(Support: mailto:support@arslist.org)

This is good stuff! Thank you and Carey for sharing this.

I just put together an auditing system after reading your posts. The
only tip I might add (you probably already have this on your forms) is
on the Audit Config form that you use to enter the schemas and fields,
create a SQL menu for the names of the forms (from the ARSchema table)
and a menu for the names of the fields (from the Field table). This
makes entering a new field to audit very quick.

Stephen

-----Original Message-----
From: Action Request System discussion list(ARSList)
[mailto:arslist@ARSLIST.ORG] On Behalf Of Axton
Sent: Friday, December 23, 2005 3:44 PM
To: arslist@ARSLIST.ORG
Subject: Re: Application-Copy-Field-Value

I use this in one case to walk a table field of fields ids to audit for
transactional changes. I use this run process to copy the audited value
to a display only character field so one filter can be used to push the
audited value to the audit form.

Without this, I would have to write a filter for each field I wanted to
audit.

The presence of this run process comnand allows me to use a form to
dynamically change the fields to audit on the form without having to
write any new workflow.

Axton

On 12/23/05, Mark Milke wrote:
> Chris,
>
> >Also, it's data driven. One can include logic to move values around
> >rather than hard-coding the source and target fields in a Set Field
action.
>
> This sound really interesting! Does it mean I can decide at runtime,
> from where to where I move the data? How exactly do I that? The manual

> says targetid and sourceid are being accepted as parameters. Can I
> provide those ids as a value of a field?
>
> Mark
>
>
> UNSUBSCRIBE or access ARSlist Archives at
> http://www.ARSLIST.org
> (Support: mailto:support@arslist.org)
>



UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
(Support: mailto:support@arslist.org)


UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
(Support: mailto:support@arslist.org)

Stephen,

Actually I would suggest using the Data Dictionary (DD) menus in v6.x
(6.3?) instead of SQL menus. They are more portable and also allow you
to honor users permissions to visibility to forms too. :) I have not
tested the field DD menus to verify that they honor field level access
for the users, but I would think that those ARS objects should honor
the ARS permission model. (Direct SQL menus would have no such
feature.)

Generally the config form is reserved for "Admin" or some kind of
elevated "Manager" level access too. So the permission differences may
not make as much of a difference in some installations too. However,
the DD menus have some nice and subtle features worth mentioning.

--
Carey Matthew Black
Remedy Skilled Professional (RSP)
ARS = Action Request System(Remedy)

Solution = People + Process + Tools
Fast, Accurate, Cheap.... Pick two.
Never ascribe to malice, that which can be explained by incompetence.




On 12/27/05, Heider, Stephen wrote:
> This is good stuff! Thank you and Carey for sharing this.
>
> I just put together an auditing system after reading your posts. The
> only tip I might add (you probably already have this on your forms) is
> on the Audit Config form that you use to enter the schemas and fields,
> create a SQL menu for the names of the forms (from the ARSchema table)
> and a menu for the names of the fields (from the Field table). This
> makes entering a new field to audit very quick.
>
> Stephen


UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
(Support: mailto:support@arslist.org)