Jump to content

Relationships not working consistently in 7.0


Idaho
 Share

This topic is 6433 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • Newbies

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • Newbies

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.

Link to comment
Share on other sites

  • Newbies

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This topic is 6433 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.