Correct address

[Horns]

Our database has an address table (ADDR), within this table are a few fields a few of which are - ADDRTEXT, ADDRTYPE, EFFDATE and REFKEY.
ADDRTEXT is the address text
ADDRTYPE is a code '0' for customers perminant address, '1' for temporary address, '2' for site address etc...
EFFDATE is the effective from date i.e if a customer changes their address, both will be stored on in the database.
REFKEY - the customer key.
Our software is able to determin the customers current address by selectin the ADDRTEXT where the EFFDATE is most recent, ADDRTYPE is 0.
I've no idea how to get CR to do this, agian I'm guessing its going to be some sort of formula field but don't know where to begin.
Thanks very much

 

[Naith]

You only want to select the most recent address for each customer?

No problem: group by Customer, go to the Report menu, Edit Selection Criteria, Group, and enter;

Maximum({EffDate},{Customer})

and in Report menu, Edit Selection Criteria, Record, enter;

{AddrType} = 0

This should be all you need, but let us know if you're still falling short of what you expect.

All the best,

Naith

 

[Horns]

Thanks very much, sounds simple enough but I'm still managing to go wrong somewhere! I've created my group (on REFKEY (CustomerKey), then when I go to the Group selection criteria and type
Maximum({ADDR.EFFDATE},{ADDR.REFKEY})
when I check the formula I get an error 'the result of selection formula must be boolean'. I tried changing the {ADDR.REFKEY} to be the groupname.. but that errored with 'The summary/running total could not be created'. Any ideas? Your help is much appreciated.

 

[Naith]

You've spotted the deliberate mistake.

The Group selection criteria should actually be:

{EffDate} = Maximum({EffDate},{Customer})

Sorry about that. At least you're more awake than I am.

Best wishes,

Naith