Self join on the audit form? I was describing a subquery (which is
probably the same thing). But the real problem is how to restrict
results to that exact parameter (Records where State was X and then went
to Y). That is, I can run a query against the audit form and retrieve a
list of audit records for a particular record that shows the state:
record 123 state A
record 123 state B
record 123 state X
record 123 state Y
record 456 state X
record 456 state A
record 456 state Y
record 789 state X
record 789 state Y
However, this isn't the form we want the data in-- we'd want the report
to just indicate the two records that matched the criteria (X to Y),
thus the report would omit record 456:
record 123
record 789
Does this make any more sense?
Thanks!
Brien
On 6/22/2011 9:41 AM, LJ LongWing wrote:
Brien,
You need to do a self join on the form to get the previous records status.
-----Original Message-----
From: Action Request System discussion list(ARSList)
[mailto:arslist@ARSLIST.ORG] On Behalf Of Brien Dieterle
Sent: Wednesday, June 22, 2011 10:25 AM
To: arslist@ARSLIST.ORG
Subject: reporting on changed values
Hi List.
I've been setting up auditing to track changes in values for forms for a
while. Trouble is, I can't think of an easy way to report on these
changes. For instance, we have a need to find tickets that were in
state X and then transitioned to directly to state Y. We're not using
the Status field for this data, so that is out. The audit form
contains the data, but it is hard to mine. I would have to basically
create a query that shows records in state Y where exists (records in
state X) against the audit form. Then I would need to make the query
more complicated to trim out records where state was X then Q THEN Y...
Or results that where state went in the reverse direction Y to X, etc.
One idea we floated (and it sank pretty quickly) was to create another
form to store FROM and TO values for each modification. The obvious
downfall of this is there overhead in creating the filters to perform
the logic, the problem of data type mismatches if you wanted to use it
as a general repository, and the lack of ability to even push the
DB.value to another form (without storing it first in another local field).
Does anyone have any other suggestions, or is it really just a matter of
creating the query and using sophisticated reporting tools for this kind
of information?
Thanks!
Brien
____________________________________________________________________________
___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
attend wwrug11 www.wwrug.com ARSList: "Where the Answers Are"
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
attend wwrug11 www.wwrug.com ARSList: "Where the Answers Are"
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
attend wwrug11 www.wwrug.com ARSList: "Where the Answers Are"