Filtering Portal To Show Unique Records Only

[pmmarketing]

Here is my scenario:

I have a contributions database and a members database. I have setup a relationship from a global field in contributions to a calculation field named "c_quick_key" in the members database. This relationship is used to allow the user to search by part of a member's name or their giving id. By entering part of the member's last name records are displayed in the portal. When a user click's on a record in the portal the GIVING ID field is set. This works great.

The problem is that most husbands & wives have the same giving id. This is for tax purposes. Since most of our members file their taxes jointly they share the same giving id. Now some husbands & wives have seperate giving id's which is ok to show them in the portal. I would prefer not to show duplicate giving id's in the portal.

How can this be done? This is what I have attempted to do:

I have setup a Self Join with a unique serial number RecordID field in the members database. I then have a calculation named "show_unique".

show_unique = Case(RecordID = Max(Selfjoin::RecordID), c_quick_key, "")

I then setup the relationship from the global field in conributions to the show_unique field in the memebers database. The problem is that the show_unique field cannot be indexed.

Is there another way of doing this to filter the records?

Sorry for the winded post but I am clueless.

Thanks in advance

[Ugo DI LUCA]

Hmm..So you're using a Claircoyance tool.

Try this one.

keep your classic c_quick_key as it is, as well as your relationship global::c_quick_key.

In your portal, drop the LastName field and set it to the same color of the backgroud.

Change your calculation to

show_unique = Case(RecordID = Selfjoin::RecordID, Name, "") and put this calc just over the Name field, in a "readable" color.

Finally, use an additional calculation :

c_sortkey = Case(RecordID = Selfjoin::RecordID, 1, 0)

In the define relationship box, use the c_sortkey as your sort key.

Now, all the duplicates would drop down the list and be invisible, showing only a list of single items.

[pmmarketing]

FYI.

I was able to fix this by changing the show_unique field into a text field. Then I set the text field with my startup script for all of the records. Now that the field can be indexed it works fine.

If someone knows a better way of doing this please let me know.

[Ugo DI LUCA]

Hi,

Sure having an index is still the best way to go.

As you're only looking for a way to DISPLAY the records, the "Display workaround" would just be quicker than any scripted process.

If you were to print a report, I'd totally agree with the "flag" method, even if I'm not sure exactly what your script is doing (changing the calc to a multikey text string ? by a Replace command or loop through the whole database ?)

[pmmarketing]

Yes, I am using a loop command through the whole database. I like your idea better because it does not require the script to loop through all of the records. Soon we will have all 2000 members in the database and it may take some time to loop through them everytime..

Thanks for all of your help Ugo. I really appreciate it.

[Ugo DI LUCA]

Glad you find it useful. I use this tip daily and it never failed.

I'd suggest you keep your t_index field though.

A portal view is sometimes the first step for a print report process. Yours is a Clairvoyance Filter, but if you have the "All" text string available to show 'all members' and want to print this purged list , you'd just have to use another little script that sets the text field for a new valid relationship.

[pmmarketing]

I must be doing something wrong. The c_sortkey field reults are coming up as 1 on all records and the show_unique field result is the name on all records.

[pmmarketing]

Nevermind I had the calculations wrong.

[Ugo DI LUCA]

Ahah.

The SelfJoin is surely based on the Record_ID, so it's always true. It should (Oh my god, what I'm saying here) be based upon the Name field, or better on some unique key as a concanation "Name & Home phone number".

[Ugo DI LUCA]

Consider the "Concanation" though if it was based on Name field...

Now, about the index, here comes a better approach than a loop.

Create a blank layout with only one calculation filed on it.

C_showID = Case(Record_ID = Selfjoin::Record_ID, Record_ID, "")

Create another layout with only one global field.

Create a global field g_Match.

The script would from the Main file.

Go to Related Records (your current relationship-show only)

Go to "Blank Layout"

Copy all records

Go to "Global Layout"

GotoField (g_Match, select)

Paste

Go to Related records (g_Match::Record_ID)

Go to Layout "Report"

Sort by Name

Print

[-Queue-]

Btw, the "GoToField (g_Match, select) Paste" steps can be shortened to one: Paste(Select, g_Match).