I am creating an in-house solution where we keep track of (among other things) contacts and am at the moment working on adding functionality for storing address history for our contacts.

 

I will take care of all editing with scripts - my question is about best practice regarding storing and displaying the data.

 

My idea is to:

1. use a related table and timestamp both creation of a new address and when an address is no longer the "current" address. This would  allow me not only to see previous addresses but to identify which was the current address at a given time.
2. have a plain field in the layout for displaying the current address.

The question then is:

• Can I be certain that the plain field on the layout will always show the address that was last input into the system?
• Would it be better to use a filtered portal?
• Or should I setup the relation to only give me one record?

We will be using this on a WAN so speed matters.

 

And, yes, the address is stored in more than one field - I am just assuming that this doesn't make the basics of this any different. Please correct me if I'm wrong there.

I'd add a from and thru date to the address record. You could filter on addresses where isempty(thru_date) for the portal or simply sort.

I'd add a from and thru date to the address record. You could filter on addresses where isempty(thru_date) for the portal or simply sort.

 

A sorted relationship? Which would then give me the (first or last?) of the related records?

 

Or should I rather use ExecuteSQL?

IMHO, if performance is a concern, you should set up a dedicated relationship to include only current address/es. Which should be quite easy to do, if you define a calculation field in the Addresses table =

Case ( not EndDate ; ContactID )

That said, I haven't actually tested this against a filtered portal speed-wise. If each contact has only a handful of addresses, the difference might be negligible.

For filtered portals, the difference between 5 historical addresses and 50 historical addresses will be noticeable, but not between 1 and 5.

 

But sorting the relationship with no portal would be just fine. I'm not sure if the sorting takes up any more time than the relationship does anyway.