Field Calculation Does Not Work?

[John Chamberlain]

Can someone tell me why this field definition does not work?

I have a file (DARTS Master) that contains records with two names each (one male and one female). This file has a calculated field for each person that is a combination of the record number and an "M" or "W". Thus, there is a Man's ID and a Woman's ID for each record.

I have a second file (Participants) that only has one person's name per record. Creating a record in this file consists of entering the Man's or Woman's ID, then displaying the Man's or Woman's name, address, etc.

I have related this file to the first file through two relationships. This works OK, but I have to have two fields for each in each record to display the necessary data.

My thought was to create a calculated field for each that would display the correct data, and allow me to have just one field per record for each field From DARTS Master that I wanted to display. So I wrote this calcualtion:

case(right(DARTS ID,1)= "M", DARTS Master Man's ID::Man's First Name,

right(DARTS ID,1)= "W", DARTS Master Woman's ID::Woman's First Name)

This returned a message saying that the field DARTS Master Man's ID::Man's First Name could not be found.

How is it that I can put the field DARTS Master Man's ID::Man's First Name in a layout, but the calculation cannot find the field? Could it be that, because the Man's ID and/or Woman's ID fields in DARTS Master is a calculation, that the relationship does not work properly?

TIA

[IdealData]

Could be 2 problems here:

1. Your field names are the problem. Get rid of the apostrophes and spaces. FMP should have warned you about this when you created the field - but it won't prevent you from creating the field

Try this:

DartsMasterManID

DartMasterWomanID

and

FirstNameMale

FirstNameFemale

(or similar)

Notice NO SPACES too, but it is still very readable

2. You haven't defined the relationships, or you have mispelled them.

I'd go with 1, though.

Incidentally, you don't need the second "case" test, as "non-men" are inevitably women. Also you failed to have a "default" result, which would mean if you had records without either "M" or "W" then you would yield null as a result.

Simplify - good luck.

[John Chamberlain]

Thanks Mark - I completely forgot about having the apostrophe in the field names - when I set up the DARTS Master seven years ago I'm sure that I got the "can't use this field in a caculation" message - at that time it didn't matter because the name fields were just used for text - I think I will adopt your approach and quit using special characters (except for the tilde key) in all of my fields.

[John Chamberlain]

Well, it sounded reasonable, so I took all of the punctuation marks out of all of the fields in both files but it still does not work

I think it must have something to do with the fact that the Mans and Womans ID field in the DARTS Master are calculated as a concatenation of the record ID and the letter M or W.

I can get the test to work OK (I used this formula):

Case(Right(DARTS ID,1)="M", "MM", "WW")

That returns the correct answer. Also I know the relationship works because I can display the DARTS Master name fields in the Participant layouts. But, if I change the formula to:

Case(Right(DARTS ID,1)= "M", DARTS Master Mans ID::Participant File Mans Name , DARTS Master Womans ID::Participant File Womans Name)

I get the "this field cannot be found" message.

[Ugo DI LUCA]

John,

I can't test it because of my French Version, but wouldn't this be that you fall in another issue here. Your fieldname has "File" in it, so I believe FM won't accept it in a calculation.

[CobaltSky]

Hi John,

Let me quickly clear up a few myths that have surfaced on this thread:

1. Spaces *within* field names do not present a problem within FileMaker (although they can pose a problem down the track if the data is to be accessed via ODBC or CWP etc).

2. Apostrophes are acceptable within FileMaker field names, will not generate an error message on naming and can be referenced via calculations (again, there may be issues if and only if the data is to be accessed remotely). *Quotes* (") are a different story, of course, but apostrophes are fine.

3. There is no problem at all with the use of the word 'File' in a FileMaker Pro field name.

However there *is* a problem with using a space as the very first (or, for that matter, the very last) character in a field name, because FileMaker use leading spaces as its delimiters to parse out field names and compare them to the metadata when validating calc syntax before it is accepted. This is a bit of a trap for the unwary because FileMaker won't warn you about it when you create a field name with a leading space, and leading spaces are not highly 'visible to the naked eye' when scanning the list of field names.

Looking at the formula you've posted above, I'd hazard a guess that you may have a space on one end of the field name of the "Participant File Mans Name" field in the Participants file or its associated relationship (eg there is currently a space appearing after the word 'Name' in the formula in your post). That being the case, if you remove any leading/trailing spaces from the field and relationship names then try your calc again, you might just find that the formula will be accepted.

[Ugo DI LUCA]

Ray,

There is no apparent problem with having an ending trailing space, but surely with a beginning one.

None of the fields in the attachment, nor in the formula John posted appears to have this beginning space :

Case(Right(DARTS ID,1)= "M", DARTS Master Mans ID::Participant File Mans Name , DARTS Master Womans ID::Participant File Womans Name)

So I hazarded for the "File" explanation, but you may have guessed 'something' here if not the exact problem John is experiencing.

1. If the fieldName actually has been defined as " Participant File Mans Name" (space at the beginning)

A- FM won't accept it in a classic calculation, even keeping the spaces where they are.

B- FM would accept it if used within a relationship, by respecting the spaces between the "::" and the fieldName.

DARTS Master Mans ID:: Participant File Mans Name would be accepted

DARTS Master Mans ID::Participant File Mans Name won't.

2. If the fieldName actually has been defined as "Participant File Mans Name" (no space)

A - FM won't consider spaces as determinant in a clasic calculation as :

Case(Right(DARTS ID,1)= "M", Participant File Mans Name , Participant File Womans Name)

B- You *must* trim any space if referred with a relationship as :

DARTS Master Mans ID::Participant File (no space) will be accepted

DARTS Master Mans ID:: Participant File (space) won't be

My guess is that the Formula John posted wasn't a strict copy/paste from his calculation box, and that the real calc has this trailing space between ".." and the fieldName.

[John Chamberlain]

OK! Ray and Ugo had the answer!

When I first started this redesign/integration, I put a space in front of the two primary files (DARTS Master and Paticipants. I did that so they would always be at the topof the window, and so would be easy to find.

When I established the first relationships I just used the names of the files as relationship names. This, of course, caused the relationships to have a leading space in the names for both the Man's ID and the Woman's ID.

Sometimes I think I should have taken up basket weaving instead of computers.

Best regards, and thanks to all of you for your interest.

John

[CobaltSky]

Hi Ugo,

There are a few problems with trailing spaces - and the ways they are handled differs somewhat between versions and releases of FMP, but you may be right that they do not apply in this instance.

However to re-empahsise one of the issues I raised on this thread, leading spaces on relationship names can present similar problems.

It would also be wise to check for non-breaking space characters as these can also present problems if the occur at the start or end of a field name or relationship name.

Moreover if field names are copied and pasted into the Define Fields dialog from elsewhere (either from FMP or another application), it is possible for other invisible leading or trailing characters to be included (tabs, CRs etc) and these can also be responsible for problems of the type John is experiencing.

[CobaltSky]

Oh Hi John,

It seems our posts just crossed in 'cyberspace'.

Glad to hear you've got your problem solved. As I said, spaces are a bit of a trap because they are not immediately obvious when viewing names - especially not when looking at them within the text of a calc expression.

[Ugo DI LUCA]

Ray,

So the clue was in between our posts

And the basic rule would be to avoid ANY space in any field, relationships, value lists,..., which is BTW one of CoreSolutions recommandations.

True that spaces in relationships are leading to this same behaviour.

By curiosity, what kind of troubles could an ending space bring, apart with manipulating fields ?

[CobaltSky]

There are some differences in the way trailing spaces on both field names and relationship names are handled on different operating system versions and platforms, and also between different versions and revisions of FileMaker. That alone is reason enough to avoid them in my view.

In addition, because they are not easy to see there is a significant risk that user-entered literal values will erroneously omit them. In most cases and on most version/platform/system combinations, FileMaker corrects for this, but I've seen a few cases where it did not. When that occurs, trouble-shooting it is every developer's worst nightmare, particularly if the solution has many files and many fields.

Most versions of FileMaker automatically strip trailing spaces from relationship names, which is good. It would probably be preferable if FileMaker stripped trailing spaces from both field names and relationship names on all systems, but as recently as 6.0v4, that is not the case.

[Ugo DI LUCA]

Oh these Cross Platform things too...

Agreed to not add trailing spaces. FM shouldn't let you enter a beginning space while in Define Fields, without any alert. Again a improvement for these FMI staff lurking here...