Relate on fields with reserved character

[sbg2]

We have a customer who insists we use # (Pound Sign/Number Symbol) in their company name. The # character is contained within so many records and tables which makes me very hesitant to change anything. The # symbol MUST be used when sending data back to the customer and to a third party which makes me even more hesitant to change anything.

So is there anyway to create a valid relationship on fields that use the # symbol?

For example match when fields in both tables contain the text:

MyCustomerStore#0001

[Fitch]

It seems to work fine, and why wouldn't it? It's just text. And even if you presumed that FileMaker would ignore it (it doesn't) for some reason, well then by that logic it'd be ignored on both sides of the relationship so it would still work.

[Razumovsky]

This sounds like another example of why people advise not to use customer names and the like as match fields.

Why not just use a simple serial number as your match fields in the backend while leaving all the display fields that the user or customer would see the same. Then you can do business with customer named "**#^^^+%©¥∂" if you want...(or Prince, back in his glyph days).

It does look like this can be accomplished though:

From FM8Help>About match fields for relationships:

To force match fields to consider non-alphanumeric characters, change the default language of the match fields to Unicode. For more information, see Defining field indexing options and Choosing a language for indexing or sorting.

-Raz

[Tricky]

I totally agree. We sometimes tend to tell the customer too much about the Filemaker backgrounds. You do not have to tell them that you are in fact using a serial code as a match field, they do not even have to notice. I would opt for that.

Having said that, I have a large solution that in its selection procedures does use concatenated text values, but when redesigning in FM8 I will revert to serial codes.

[sbg2]

Hmmm, I must have missed something with the file at the office. Making a sample file here at home worked fine. I will check the office file tomorrow when I get in, probably a stupid mistake.

While my tables are set up with serial ID's, the table that holds the related information comes from an outside source. Therefore information needs to be matched up somehow without (initially) having the serial ID of the company in the related file.

[sbg2]

OK, I have discovered why the relationship does not work. If the field is an unstored calculated value it will not work, but manually entering the same value does work. So I guess I can use an auto-enter for the calculated value rather than an unstored calc.

Attached is a sample of the tables I am using for this. Can anyone see a reason I would want to link the external data in a different way?

MyDB.zip

[comment]

And the calc cannot be stored, because...?

[Fitch]

It shouldn't be necessary in this case to index in Unicode. You might think so from this section of FileMaker Help (Working with related tables and files > About relationships > About match fields for relationships)

"To force match fields to consider non-alphanumeric characters, change the default language of the match fields to Unicode."

However, take look at (Finding, sorting, and replacing data > Sorting records)

"Non-alphanumeric values before the first word are ignored (unless you change the sort language to Unicode)"

The implication is that non-alpha characters WILL be indexed, as long as they don't precede the first word. And this was borne out by my quick testing -- which was done on a Mac by the way. On Windows YMMV, but I'm pretty sure you'll get the same results.

As for using match fields that store the company name, any time your key fields store actual data it's generally considered a Bad Idea. It would be preferable to use serial numbers that are hidden from users, but maybe at this point in your development cycle it's not feasible.

We've all violated 2nd normal form on occasion, for one reason or another (e.g faster searching). But you should at least think about why you're doing it.

[sbg2]

I did look at and experiment with the indexing in unicode but as you pointed out that was not the problem. As I stated above the problem was the calculation was unstored. As comment suggests a stored calculation could be used but I am a little surprised FileMaker did not throw an error when I used the unstored calc.

As for a serial match field... if the data you are matching to comes from an outside source and does not contain your internal serial key how then do you match the data?

I could perform a find in the Order file for each ExternalData record but wouldn't this be the same thing as using the relationship? My find would be the same - order::Company=child::Company AND Order::PO = child::PO. The key serial of the Order could then be stored in the ExternalData table but then again it is still based on the non-serial matches between company and PO.

note: The reason for my initial assumption that the problem was a reserved character was that doing a find for the Company name produces no matches. The Company has to be enclosed in quotes because # is reserved in find mode.

[Ender]

...I am a little surprised FileMaker did not throw an error when I used the unstored calc.

Since FileMaker doesn't know which side of the relationship you intend to use as the parent side or the child side, so it's not fair to think of this as a shortcoming. Fortunately, you can tell when the key uses a global or unstored calc visually by the sideways "T" that connects to the TO. If you see that, you know you can't access anything other than globals in that side of the graph from that relationship's children.

[Fitch]

The Company has to be enclosed in quotes because # is reserved in find mode.

Good catch, I hardly ever use that in a Find.

I didn't mean to get all preachy on ya -- my last post was more of a general statement, not really directed to you specifically. Sounds like you pinned it down pretty well. Like you say, the data comes from an outside source; you go to war with the database you have... not the database you might want or wish to have at a later time.