Matching not 100% identical fields

[god of pettiness]

I'm trying to make a relationship between two records based on a text field. This text field in the Master file may not contain all the same text as the related field, but the related field would contain all the text in the Master File match field.

Filemaker book says Formulas work, but is that only for numbers?

Can someone suggest how this might work?

[Chuck]

Can you give a more specific example? How will FileMaker know how to match the fields? A good way to begin would be to ask how you would know that the records should match. If we can figure that out we can probably use calculations to help FileMaker figure it out.

Chuck

[god of pettiness]

quote:

---

Originally posted by Chuck:

Can you give a more specific example? How will FileMaker know how to match the fields? A good way to begin would be to ask how you would know that the records should match. If we can figure that out we can probably use calculations to help FileMaker figure it out.

Chuck

---

Well, I've got a calculation field that concatenates 4 test fields. I want to display records in a portal, but I'm trying to dynamically qualify it so that while 15000 records match my Client Code field, only 40 of those match the Release Date field. That's straight forward, but if I want to show records in the portal that match just the release date (say 10000 records alltogether) it won't work now, because the related field doesn't match the match field 100%. I want to avoid modifying the related record every time I play around with those source-for-calculation fields (by replacing the related field, or similar)

[manitobaangler]

So basically your match field (ie: releasedate) content from your master file will only find the same date from a concatinated field in your related db containing a bunch of other text.

FM Help's example only deals with numbers.

Is your match field in the master db a text or calculation with text results field?

[Chuck]

It sounds like you need more than one relationship. One that is between the concatenated Client Code and Release Date field (converted to a number) and one on just the release date field.

For instance, you could have a global date field called gRelease_Date and a number calculation field cRelease_Date set to gRelease_Date (since it's a number calc field it will convert the date to the internal number equivelant).

Then in your related database you have Client_Code|Release_Date which is set to Client_Code & DateToNum( Release_Date) and you have a number calculation field Num_Release_Date set to Release_Date.

Releate the cRelease_Date to the Num_Release_Date and you'll be able to view in a portal all of the records with a release date that matched the date in gRelease_Date.

Does that help?

Chuck

[god of pettiness]

Unfortunately No.

See, I'm trying to re-use this portal based on the four select fields that the user sets, then they hit a button that sets related fields in related records (Sales related through a customer code) to have a value that gets concatenated in the same way as the match field. The difficulty arrives when the user blanks out a field. Then that part of the concatenation has no value. It's not a huge deal, since I have this solved with my Replace script (sort of described in previous email. But for efficiencies sake, I'm making an export of all records modified since the last export time stamp. With me constantly replacing this field the modification field gets updated every time I try to display those darn ads.

Is there no way to make a logical match? the guide mentions a Unit Price > 100.0 example. Could I do a simlar text calculation that relates records that contain given chunks of text?

[Chuck]

I'm afraid that I'm unable to help, I guess. Basically, relationships need exact matches. However, you might look into using lookups somehow. Lookups are mostly a holdover from pre FMP 3 days, but they are still useful in come cases. One is where you need information to be related in a range. A lookup has the option, if there is no exact match, of going to the record that would be the next higher or next lower record. I would investigate this and see if it might work for you.

Chuck

[manitobaangler]

Chuck, I agree. I can't see how FM could have multiple match variables. Never thought of trying it until "Pettiness" mentioned it.

HOWEVER, I did try to recreate the sample that FM help AND the manual states, without success. (see quote below)

So, is FM blowing smoke or can this really work? Or am I interpreting the manual wrong?

FM5 Help Quote: The match field in either (or both) the master file or related file can contain a calculation formula. For example, the formula Unit Price in the match field of the master file establishes a relationship to a record (or records) in the related file whose match field contains the value of the Unit Price field in the current record of the master file. You can also define a calculation formula for the match field to match multiple records. For example, the formula Unit Price < 100.00 in the match field of the master file establishes a relationship to all records in the related file whose match field contains a value less than 100.00.

[Chuck]

These are still exact matches in the above example. Unit Price < 100.00 results in a 0 or a 1. If you have a database that also has a calc field set to Unit Price < 100.00, you can relate these two calc fields and from the first file be able to see all records with a unit price that matches the range set in the master file. Perhaps this could be used to solve the problem somehow?

Chuck

[signal]

Maybe I missed the boat but what about a calculated key field that lists all the possible variations of the four fields that would match to the original?

[god of pettiness]

If I put every possibility in the related field separated by a return, I can change the match field, and the results in the portal change!

Sir, you rock.

[This message has been edited by god of pettiness (edited January 17, 2001).]

[signal]

I am blushing.

You might try using a little utility I just found called MultilineKey by Todd Geist.

www.geistinteractive.com.

It creates key calculation for you and looks awesome. ( Please send him money if it works for you.)

His web site is down. Try [email protected]

If you can't get a hold of him, I could email it to you or I think you can find it on www.databasepros.com

[This message has been edited by signal (edited January 17, 2001).]