Joined: 22 Jan 2013
Online Status: Offline
Posts: 89
Topic: Removing Line Duplicates - 1 field Posted: 18 Jul 2019 at 6:24am
I have been using the following in my section expert to remove records that have the same auth number and it works fine.
Not OnFirstRecord
and
Previous({HSD_AUTH_MASTER.AUTH_NUMBER}) = {HSD_AUTH_MASTER.AUTH_NUMBER}
I am working with a report today that I want to keep that logic based on the auth number and I have added the fax number to my report, that is sometimes null and I also want to remove those NULLS.
I tried this below with my simple mind thinking today.
Not OnFirstRecord
and
Previous({HSD_AUTH_MASTER.AUTH_NUMBER}) = {HSD_AUTH_MASTER.AUTH_NUMBER}
and
{PROV_ADDRESS_CONTACT.FAX_NUMBER} = "";
That doesn't work and won't do the last line to remove the NULL fax numbers, only removes the duplicate auth numbers.
How can I do both?
NOTE: Each auth has multiple lines and I don't want the auth number to repeat and I only want the line that has the fax number.
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Posted: 22 Jul 2019 at 3:15am
There are a few ways to possibly get what you want here.
Your existing formula is a suppression formula. You can either use a select condition to exclude all the rules with no fax:
ISNULL({PROV_ADDRESS_CONTACT.FAX_NUMBER}) OR {PROV_ADDRESS_CONTACT.FAX_NUMBER} = ""
but this could omit an entire auth if all the auths's rows have no fax identified.
Or you could group on the authID and display the max value of the fax number fro that group.
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
