Relationships not working consistently in 7.0

[Idaho]

I have two tables in my database. Both have two fields: technical name (tn) and common name (cn). The "lookup" table has only these two fields and is used to populate the common name in the main field. The link is with tn fields. The "main" table conists of the tn text field and the cn calculated field defined as:

if(tn=translator::tn; Translator::cn; "?")

The results are to print as text

The calculation copies successfully about 50% of the time, tho when it does, it always displays "?". Thinking it was typo problems, I cut and pasted from one the technical field from one table to the other so I know they are identical. There is no appearant commonality between the data that don't copy successfully.

I have tried testing this with the simplest possible database with only 2 fields in both tables. It too, was inconsistant. I am running v7.01a with Windows 2K and SP4.

Anyone have any ideas? Thanks

[ESpringer]

It seems that you've mixed up lookup and calculation.

First, I'd recommend not even having the CN field in your main table. On any layout associated with the main table, you can just place the cn field directly from the related names-conversion record. In fact, in FM7 I can think of very few reasons why you'd want the same field in different tables to replicate data in the way you were doing.

But if you did want to have the main table store the text data for both kinds of names, you want a lookup rather than a calculation. You'd define the cn field simply as text, but then choose "options" to have it Lookup data from the cn field of the related record in the names-conversion table.

[Ender]

Welcome Idaho! (Dune?)

There may be missmatches on the relationship, since the relationship is based on 'technical name'. You can test this by dropping a related field on the layout an seeing if the related data shows.

But why not use a lookup for cn instead of a calc? (with 'If no exact match, use "?"') Or just put the related cn field on the layout?

Also, there is a 7.0v2 updater which is highly recommended.

[Idaho]

Thanks for your help.

Well, DUUUHHHH! I should have known I could use the cn field from the Translation Table in the Main Table, thus eliminating the need for a calculation field. Unfortunately, that didn't solve the problem. Still about 50% of the common names do not appear on the Main Table even though they are all on the Translation Table.

I was successful in 2 times out of 7 of getting the common name to appear by deleting and retyping the technical name, then pressing TAB in the Main table. But that didn't work on the other 5 times, and neither did cut and paste.

I downloaded the 7.02 update but that didn't help my problem. Attached are two screenshots of my tables. Any suggestions you can offer are appreciated.

Samples .zip

[Ender]

My guess is that some of the keys (Technical Name) have slightly different spellings, extra spaces, or may exceed 20 characters per word. Names are generally not very good relationship keys for this reason. Using some sort of ID numbering is better.

[Idaho]

Bingo! Extra trailing spaces are the problem. My source data comes from a variety of banks, and it is their technical terms that the use repeatedly that I am translating into common terms.

Do you have any ideas how to set FMPro to strip out those trailing spaces? Otherwise I must spend time manually doing it.

[Idaho]

Bingo! Extra trailing spaces are the problem. My source data comes from a variety of banks, and it is their technical terms that the use repeatedly that I am translating into common terms. Thanks for putting me on the right track. I would have never thought of it.

Do you have any ideas how to set FMPro to strip out those trailing spaces? Otherwise I must spend time manually doing it.

[-Queue-]

You could set up an auto-enter calc for each required field with Trim( fieldname ) and 'Do not replace existing value for field' deselected.

[Idaho]

Thank you again. That helps.