Multiple-Field Relationship Issue

[agutleben]

I have two tables that are related using multiple fields. Those fields are the ID (primary and foreign) and a name field. What I am trying to acheive is the ability to enter a global search field, and have the results in the portal show names relating to what is entered in the global search field. I have this all working fine. What I cannot figure out, is how to have the portal show all records, and THEN filter based on the global input. Once vaules are removed from the global field, all records in the portal should show again. I am using FMP Advanced 8 and OSX 10.4. Thanks in advance.

[Ender]

Hi Aaron,

In general, to get all records to match with a blank search field requires a calculated key on the parent side that sets to "All" or something similar if the field is blank, like:

cParentKey (calculation, text result) = case(isempty(gSearch); "zzzAll"; gSearch)

and then on the child side, all records must have that "All" text appended to the key of every record, like:

cChildKey (calculation, text result) = Name & "¶zzzAll"

I've added the zzz to the keys to prevent false-positives.

If you need help with the search method itself, there are a couple methods that can be used. Choosing the method depends on how flexible you want the search to be:

1. Do you want to allow partial matches, starting from the beginning of the word, like matching "John" in "Johnson"?

2. Do you want to allow partial matches that exit anywhere in the Name, like "son" in "Johnson"?

3. Could the Name contain multiple words, like "Jane Johnson"?

Edited July 11, 2006 by Guest

[agutleben]

That worked great. Is there a way to set this search so that I could enter "C" and see any record starting with C, or I could enter "client" and see any record with client in the name. I have already created a calculated names field that separates any name into individual words with a hard return, so a title of "client meeting" would result in:

client

meeting

This works using the calculations you provided. Is there a calculation that would allow me to separate the characters into their individuial letters up to and including the entire word? My relationship is current setup as:

pkClient_ID = fkClient_ID

cParentKey_Filter ≤ cChildKey_Filter

cParentKey_Filter ≥ cChildkey_Filter

Again, this works perfectly now if I enter at least one entire word from the name I am searching on. Thank you.

[agutleben]

I am on the right track, but still struggling. Here is what I have:

If a name is "Project Assignment", I need a calc that will provide the following result:

p

pr

pro

proj

proje

projec

project

a

as

ass

assi

assig

assign

assignm

assignme

assignmen

assignment

How can I accomplish this in a dynamic way that can take apart the name and provide it in the above fashion?

[Ender]

See the examples I site here:

http://fmforums.com/forum/showpost.php?post/183594/

[agutleben]

Works beautifully! Thank you so much.

[Fenton]

Here is another technique, which I think may be better, as it does not add all the extra data that those "explode" CF's add. It may not be quite as fast on huge tables.

It is a file by John Mark Osborne, which I've only been able to find within his DevCon 2005 files download. Its name is AUTOCOMP.FP7.

http://www.filemakerpros.com/DevCon05.sit

Basically it leverages the new (to 7) operator options of relationships.

[Ender]

Hey Fenton, it's been a while!

That technique is nice for its simplicity, but it does have a couple drawbacks. Each word of the search text really needs to be handled with separate fields (and relationships), as the range relationships can't parse the words separately. For example, trying to find "John Mark Osborn" by typing "Mark" in a single Name field wouldn't work, you'd need to type "Mark" in a Middle Name field, or assume that that's part of the last name, in which case "Osborn" wouldn't match. Then there are similar issues with hyphenated names.

With a multi-key, you're able to match on multi-word names from a single search field (including parts of hyphenated names if you wish). It's also fairly easy to add CFs to handle those partial matches of case 2 that I mentioned above. Of course, this ability does come at a cost. These keys do add to the size of each record and to the size of the index.