Relational lookup utilizing more than 2 fields

[James Gill]

I'm trying to find a quick and dynamic way to perform a search of contacts utilizing a portal to display results. I have 3 or 4 fields I want to perform this based upon (First name, Last name, SSN, DOB) but I'm having issues with getting the lookups to work. I can get an exact match to work, but getting a partal match ("Chris" finds "Chris", not "Christina", etc)

I don't want to use a portal filter because in my final solution we're going to be searching in excess of 500,000 records, so I believe that using a filter will be an issue with this many potential records.

I've attached a sample file that uses a global table and a related contacts table to perform the search. I assume that this has already been done many times in many different solutions, so I'm wondering what the best practice is.

Relational Lookup.zip

[eos]

A portal filter might indeed be an issue; on the other hand, when using the exploded key technique for relationship filtering,

be prepared for huge indexes!

Have a look at this one:

Relational Lookup_eos.fp7.zip

[James Gill]

Interesting technique, I'll check it out. I am a little concerned about the size of the index though. Thanks!

[James Gill]

Well, after looking at the technique, I decided to scrap the idea of a relational lookup because the cost of the indexing was just too high. Instead, I decided to use a scripted find to gather the list of records that comprise the found set. I then dumped the ID's of the found records into a global field and am using it to build the relationship for the portal.

It's a little more complex as a result of the scripting but I think the benefits of not having to store a monstrous index are worth it.

Thanks for the help!