Joined: 29 Mar 2012
Location: United Kingdom
Online Status: Offline
Posts: 6
Topic: Most recent home address Posted: 26 Feb 2018 at 1:01am
What would be the best method of only showing the current address for an individual whilst ignoring all previous addresses?
In our db, there is an "Effective To Date" which, in the most recent address, would be blank.
When adding address lines to the 'details' area in a report, all adresses for the person are displayed but I want to create a letter which needs the most recent.
Any ideas on how to approach this?
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Posted: 26 Feb 2018 at 7:15am
Are you trying to reduce the data pull before you get it into Crystal? Can you write a stored procedure or view to limit the data and you that as your data source? What about a crystal Command?
Depending on outer join requirements and how many other tables were involved I would do this via a store procedure that would either conditionally join use a where condition as "and addresstable.EffectiveToDate is null).
You may be able to add that as a select criteria in Crystal but I don't know if you have other data issues like outer joins or future dated end dates or other issues to deal with.
Joined: 29 Mar 2012
Location: United Kingdom
Online Status: Offline
Posts: 6
Posted: 26 Feb 2018 at 10:21pm
I got the result by adding a formula whereby if the end date isnull, show "O" (for open). Then showing only "O" in select expert.
Probably not the slickest method but it works!
Thanks for the reply.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum
IP Logged
