Can I restrict "previous" formula to against a field? 1

[urbanhim]

I have a report which is comparing previous address with current address, each time a client amends their addresses, a new address record is added to our Oracle database.

I therefore have two tables..

ACCOUNT
AUDIT_ACCOUNT_ADDRESS

I have linked using ACCOUNT_ID which appears in both tables, there is only one record per ID in ACCOUNT and obviously multiple records in the ADDRESS table as they may have multiple addresses where they have changed or amended.

I have this formula

previous ({AUDIT_ACCOUNT_ADDRESS.ADDRESS_1})

I have built this for all the different address fields, however, it looks at previous address for another client, can I restrict it to ACCOUNT_ID, so something like this:

previous ({AUDIT_ACCOUNT_ADDRESS.ADDRESS_1}, {ACCOUNT.ACCOUNT_ID})

Hope this makes sense!?

Thanks





UrbanHim
Crystal XI Report Writer
London

[small]What's the best cure for a hangover?... Heavy drinking the night before!![/small]

 

[CR85user]

The previous() function compares the previous record.

If you link the two tables on with a left outer join from ACCOUNT to Audit_account_address it will then return all addresses listed for each account id as long as your record selection is not based on the table to the right of the join.

This will then return all addresses for each accountid which would then make the previous() function relevant.

'J

 

[CR85user]

note: previous() is grabbing the previous record from the returned rows based on the selection criteria *not* previous entries within the db etc.

I'd do a record selection of: {account.accountid} > ''
(or 0 if numeric)

Then add a group for {account.accountid}

Drop the {account.accountid} and {Audit_account_address.address_1} field in the details section and then use whichever formula to compare previous entries. Remember if you are comparing by accountid you can use that logic too:

//exampleformula
if {account.accountid} = previous({{account.accountid}) then
if {@trimadd} = previous{@trimadd} then 'Match' else 'Changed'
else ''

'J

 

[urbanhim]

Thank you for your help with this, and thank for your exlainations!

Cheers

UrbanHim
Crystal XI Report Writer
London

[small]What's the best cure for a hangover?... Heavy drinking the night before!![/small]