Problem using field from related file in calc

[Lougee]

It should be simple, just a concatenated list of fields to create a unique match field (not meant to be a unique ID) in my 'student' db.

I want to define the field as: lastname&firstname&birthdate&schools::district

students and schools are related by the field 'school'. This works fine, brings in the district (I have the field schools::district on several layouts). Except when I try to include schools::district in my match field definition. Then I get the error message "This field cannot be found."

Anyone know what's happening? The field definitely exists in the schools db and the relationship is working correctly as proven by the fact that the district shows up on my other layouts.

HELP!! Please (my mother taught me always to be polite and say 'please' and 'thank you' ).

Abbe

[LiveOak]

You can't use a related field as a "match field" for another relationship. It must be possible to index a field for it to be used on the "right side" of a relationship. In some cases (global field) you can use a field that can't be indexed on the "left side" of a relationship.

-bd

[Lougee]

I guess I didn't explain myself very well.

I'm not trying to use this related field as a match field in a relationship. The relationship is already established -- school in the students db to school in the schools db. The relationship works; district (a field from the schools db) shows up on several layouts in my students db. Now I just want to use the field schools::district in a calculation. Nothing fancy, just lastname&firstname&birthdate (all fields from the students db) &schools::district. This should work but I keep getting the error message "this field cannot be found".

I even re-established the relationship. When I check it -- schools::district shows in the students db; the relationship is still working.

Why can't FM find this field that is RIGHT THERE?:

Abbe

[Kurt Knippel]

Ok, please give the DB name, the field name and the exact calculation used in the field.

From your desctiption the advise that LiveOak give is right on. So either you are simply missing some important concept here or you have explained yourself incorrectly.

Giving the actual fields, thier db location and contents will help to identify where the problem is.

[Lougee]

OK. I have 2 databases -- one to track student contact information, call it STUDENTS and one to hold school district and school information, call it SCHOOLS. The relevant fields in STUDENTS are namefirst, namelast, birthdate and school. The relevant fields in SCHOOLS are school and district. The 2 databases are related STUDENTS/school on the left and SCHOOLS/school on the right.

The relationship is valid; when I enter a student's school into the database, the correct district (SCHOOLS::district) shows.

Among our thousands of students in about 50 schools, there are frequently 2 --or even more -- with the same first and last names. Now I just want to create a concatenated field which I've been calling 'match field' (obviously the source of the confusion) in the STUDENTS db so I can distinguish between these records at a glance.

The formula for the field should be -- namefirst&namelast&birthdate&SCHOOLS::district, saved as text. This effectively gives me a string that allows me to distinguish the correct student at a glance. However, when I try to define the field, I get the error message "that field cannot be found" referring to SCHOOLS::district.

The field has been chosed from the correct relationship. The field is obviously there -- it shows up on other layouts. What is wrong with this formula? Or is it me?

Now I know how cat herders must feel -- chasing after that which cannot be caught!

Thanks for being so patient. This help is greatly appreciated.

Abbe

[CobaltSky]

Hi Abbe,

I don't know if you've caught up with my other post on this subject, but one of the things that can cause this kind of anomaly is a non-standard fieldname or relationship name.

Most inadmissible names are visibly so, but one of the least visible is a leading space - and its effects are exactly as you've described. In fact a leading space on either the fieldname or the relationship name would do it.

The reason is that the calc engine uses the spaces to identify word-starts, and therefore isn't geared to 'see' them as part of the fieldname or relationship name.

It may not be that, but it would be worth checking it out in case.

[spb]

Ray's advice is good. More generally, look for problems outside your calculation. I just tested what you are trying to do--make a field that concats a field from parent db with a field from child db--and it *does* work. It worked for me, anyway.

Did you type "Schools:District" into your calculation, or choose it from the field list at the top of the calc window? I always go through the extra trouble of selecting from the window to avoid the likelihood of typoing a field name.

Steve Brown

[Lougee]

Ray was right! I had a space in front of the relationship name. When I removed that, the calc worked just fine.

I feel so dumb!! I eyeballed the names earlier for just such a problem and didn't see it!

Thanks everyone. Another learning experience... <G><G>

Abbe