Are + and - ignored in text match relationship

[jtwilber]

I have two files (Product and lineitems) have a text field that is

the basis of the relationship (ProductCode::Linecode). So that

a match is made for the portal Dog=Dog. However some of my code fields

do not only say DOG but Dog+ or Dog- (indicating dog types etc.).

When FM seems to be ignoring the "+" and the "-" and calling it a match on DOG regardless of the + or - .

Therefore when I Bring up the Product for for DOG+ The portal lists all the line items for DOG+ AND DOG AND DOG-.

Whats up with that???

[Lee Smith]

Hi Jt,

Yep, that sounds right to me. What else should FileMaker find in in a "Text" field, but text. The + and - are numerical operators.

I haven't tried this, but I would think you could use just a P and M, or N, or the words Plus and Minus instead?

HTH

Lee

[jtwilber]

G.R.E.A.T. Now that I've populated the database does anybody have the simple way to change all the feilds with "-" ending to an "M" ending a the "+" feilds with a "P" ending. Something like for everything ending with a "-" cut off the "-" and then write the code conconating a "P" to the end. (still don't think I could pronounce that word at a cocktail party, but I sure as hell know what it means)

Jonathan

[Lee Smith]

Not to worry, you can use the replace by calculated result (in FM 6 there is the built in Find and Replace). However, I would test the relationship with a couple of manually changed dogs = and dogs - first. Once that is done, and if it works, I tell you how to get rid of the + and -s with the replace function or Find and Replace.

Let me know what version of FileMaker you are using.

Lee

[jtwilber]

Hi Lee,

version 6.

Can't figure out how to isolate codes ENDING only in "-" or "+". Find does not seem to recogize operator in same way relationship did not.

??

Jonathn

[Lee Smith]

Try putting them in quotes ( "-"), and see what happens

Version 6,

Find and Replace

Find "-" Replace with M

Find and Replace

Find "+" Replace with P

HTH

Lee

[Lee Smith]

I should have tested version 6 before I replied.

Good news, I just tested the Find and Replace funtion (Menu >> Edit >> Find and Replace) in Version six and you can do a find for either just the - (no "") or the + (no "") and it *WILL find them and replace them with whatever you want.

HTH

Lee

[Ugo DI LUCA]

Another way :

calculation Substitute(Substitute(Right("your key",1), "-", "M"), "+","P")

A selfjoin on record ID

Define Key to be a lookup from calculation using the selfjoin.

Relookup.

Back to define relationship - delete relationship

Back to define fields - changed lookup def. and delete calculation.

[Lee Smith]

Hi Ugo,

There really isn't any reason to use a separate calculation field to remove the + or - , it can be done using the Replace Function. First click into the field that you want to make the change to. Then Go to the Menu >> Record >> Replace >> Replace with Calculated Results [color:"blue"] (CMD = on a Mac) . Your calculation should look like this:

Substitute(Substitute([color:"red"] YOURKEY , "-", "M"), "+", "P")

Note: change [color:"red"] YOURKEY to the name of the field that you are changing.

BTW, this is still available in version 6 but, with the new Find and Replace feature, I see no reason to use it in this situation.

AND

Since you are not changing the original structure in anyway, you wouldn't have to do anything else, unless I'm missing something here?

Lee

[Ugo DI LUCA]

Yes Lee,

I just forgot about this feature. I used it in one of my Files with FM4.1. Thanks for pointed it out.

Though the right function should be used in order to not change a field "a-b-" in "aMbM".

[Lee Smith]

You can use the double quotes in place of the M and P and either with no space or with a space in it to make the field return either

ab or a b by modifing the calculation.

Substitute( YOURKEY , "-", "")

It would change A-B- AB

Substitute( YOURKEY , "-", " ")

would make change A-B- A B

And don't forget you can search for patterns. [color:"blue"] field "a-b-" in "aMbM". /color]

Substitute( YOURKEY , "a-b-", "a b")

Lee

[jtwilber]

The challenge comes in creating the found set of only those with "-" (Dog-) at the END. Ugo was right that I do not want to change when the "-" is in the middle (Dog-Cat). When I do a search for (-) without quotes i get nothing, when I search for "-" I get all cases as I do with "*-" trying other combinations as well. How do I use the RIGHT function to search for only those ending in "-". Sounds similar to Ugo's first reply ??

Jonathan

[Lee Smith]

Why are us using "-" in these fields?

[jtwilber]

I was trying to just FIND those... but I ended up using your "replace" function with Substitute(Substitute( YOURKEY , "-", "M"), "+", "P").. I chickened out and made a dummy field to dump the new stuff in. It worked after hashing around a bit and I moved it in. VWALAA. It works, GREAT. Thank you . Now all I need is an solution the the "printing related related" post in scriptology and I'm on to new adventures in FM.

Jonathan!

[Vaughan]

You might find that changing the storage language to ACSII solves part of your problem. When indexing FMP ignors these symbols and just indexes the words. Changing the language changes which characters FMP considers word separators. At least that's my theory.

However, you shouldn't be using values like this as key fields for a relationship. Build the relationship on serial numbers or some other meaningless autogenerated string.