Jump to content
Sign in to follow this  
BeeBee

Keys in 7 on partial words?

Recommended Posts

BeeBee

I thought I would try relationship instead of find in 7. I didn't have luck in 6 but 7 is better, right? I mean to go to related records.

 

In 6, I had a find on a combined field of First, Last and Phone. I used Insert Calculated Result *text*. I want to relate on anything - even part of a word. So if we type 'south' I want to match Southern and NeoSouth.

 

I duplicated my table and my left key is global holding partial text. But I'm stumped on the right. I want the right to grab the left global and match if anything is related. Has 7 improved so this is possible now? laugh.gif

 

BeeBee

Share this post


Link to post
Share on other sites
frigante

The only way I know how to do this is using what I call the 'parsed field' method but it's not foolproof.

 

Make a new calculation field, set its type to be text in the calc window.

 

Call it SearchField or something like that where your concatinated First, Last and Phone field is called Combined and:

 

SearchField=Left(Combined; Length(Combined))&"¶"&Left(Combined; Length(Combined)-1)&"¶"& ... Right(Combined; Length(Combined))&"¶"&Right(Combined; Length(Combined)-1) ...

 

Then you'd relate your global to the SearchField field instead of the Combined field.

 

This will work, but only in a limited capacity, ie: going with the example you made, a search for 'south' will yeild Southern but not NeoSouth. To get those 'middle' search terms, you'd have to start parsing out the middle characters like this:

Middle(Combined; 2; 1)&"¶"&Middle(Combined; 2; 2) ... etc..

 

I could show you how to do it a little better in a script, but I'm thinking others on this forum may have better ideas for you.

 

I'd be curious, at least!

 

pc

Share this post


Link to post
Share on other sites
CobaltSky

Sounds like a good use for a recursive custom function in FileMaker 7! ;)

Share this post


Link to post
Share on other sites
BeeBee

Hi Ray. wink.gif I found a recursive CF and used it but I have one snag ... Search table with only global (gSearch) text and three tables to search: Company, Address and Phone.

 

Relationships:

Company:CompanyID to Addresss::CompanyID

Company:CompanyID to Phone::CompanyID

 

Custom Function Exploded Key (Andrew LeCates) is:

 

ExplodedKey= If( Length(txt)>1; ExplodedKey(Left(txt;Length(txt)-1)) & ¶ & txt; txt)

 

Created it then created a calculation text in Company with:

 

ExplodedKey ( CompanyName ) & ¶ &

ExplodedKey ( ContactName ) & ¶ &

ExplodedKey ( Addresses::City ) & ¶ &

Addresses::Zip & ¶ &

Right(Numbers::Number; 4)

 

Then joined gSearch to this calculation. A GTRR produces this error: "This operation could not be performed because one or more of the relationships are invalid."

 

I know my other relationships work so it must be this new key. Any ideas? I would greatly appreciate any direction. smile.gif

 

BeeBopper

Share this post


Link to post
Share on other sites
BeeBee

I can be a dummy. I just pasted the calc and it doesn't even produce what I want. If I search Brown in Contact with David Brown, it produces:

 

D

Da

Dav

Davi

David

David

David B

David Br

David Bro

David Brow

David Brown

 

... but no BROWN!! I guess I'd better keep looking for CF to do it right. However, I don't think that is my problem here. I think something is wonky in my thinking of combining this CF and I had better figure that out anyway. Do you have any clues on why it won't work? The results in the calculation key look fine, even though I wasn't using the correct CF. confused.gif

 

Frigante, when you said it wouldn't find NeoSouth, I didn't pursue your suggestion. I want a pattern count search. I want a pattern count CF of some sort, I think. Thank you so much for responding anyway. laugh.gif

 

BeeBee

Share this post


Link to post
Share on other sites
andygaunt

Hi BeeBee -

 

To get the explosion on your contact name to include the last name as its own entity would require you to point the recursive function to the last name only field as well as a combination of the first and last names.

Share this post


Link to post
Share on other sites
BeeBee

Thank you Andy. Okay, I will include RightWord. I will try that.

 

However, I don't think that explains why I got that message, does it? Usually a correct GTRR, if no records match, just produces a 0 records. Or at least that's what I've always seen. I have never seen that message before. :rolleyes:

 

Okay, adjusting key produced:

 

Davi

David

Brown

 

Not quite what I wanted but closer. and I still get that error message. confused.gif

Share this post


Link to post
Share on other sites
andygaunt

Hi BeeBee -

 

your key should look like this

 

/* Explode First Name Only From Contact Name Field*/

ExplodedKey ( LeftWords ( ContactName; 1) ) & "¶" &

 

/* Explode Last Name From Contact Name Field*/

ExplodedKey ( RightWords ( ContactName; 1)) & "¶" &

 

/* Explode Company Name */

ExplodedKey ( Company Name ) & "¶" &

 

/* Explode City */

ExplodedKey ( Addresses::City) & "¶" &

 

/* Zip */

Addresses::Zip & "¶" &

 

/* Last 4 digits of phone number */

Right(Numbers::Number; 4)

 

 

as to why you are getting the error. Is the calculation stored?

Share this post


Link to post
Share on other sites
BeeBee

Ummmm, calc can't be stored because it refereces related. Of course. I forgot about that.

 

I give up on using GTRR to find in several dbs on pattern count. I don't think it is possible. And I wish I hadn't split my addresses and phone numbers. Now they're always related and I can't find them.

 

Major bummer. Thanks anyway Andy.

Share this post


Link to post
Share on other sites
BeeBee

I become an idiot when I get too tired. Of course I can't find Addresses and Phones if it's only through the Company TOG. Duh!

 

I rejoined search directly to all three and provided a portal. It works but breaks if there are three 'words' in the ContactName (middle initial, etc.). Is there a way to include a test for how many words and split those words to explode also without hardcoding LeftWords and RightWords (and MiddleWords)? I thought that's what the CF was doing.

 

My quest for relationship on pattern count continues.

Share this post


Link to post
Share on other sites
This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  



×
×
  • Create New...

Important Information

Terms of Use