Please help! strange relationship behaviour

[David Mosedale]

Hi all,

I have three files - call them A, B and C.

C contains just four fields. Two text fields, a calculation field that is the concatenation of the first two text fields and a container with GIFs in it.

B contains a whole heap of data.

A has a relationship with B (this works fine).

I then have a relationship between a calculated text field in A and the concatenated field in C in order to pull the GIFs from file C. Bizarrely for most values of A this works fine, but for some it does not! For some of the values in the calculated text field in A, it brings up the wrong GIF. I know that it is the matching of records that is at fault because I used the relationship to pull up the record ID of the matched record instead of the GIF and it pulls up the wrong one. Yet when I look at the text fields that it is supposed to be matching they are identical. So any idea why it might match some records correctly and not others?

Is there by any chance enough information in the above for anyone to lend a hand?

I am using FM Pro 6 on Mac OS X.3.

Thanks, Dave

[Ugo DI LUCA]

Hi,

If it works for some of your records, then it should work for all of them.

Check that the key is indexable.

Make sure your key isn't too long. You may use a space in your concanation calc, as A & " " & B

[David Mosedale]

Aaahh. My field in file A is not indexable. I guess this is a major problem? It is a calculation that depends on another calculation that depends on a related field. I guess from my 2 minutes of playing that I will not be able to index it because of that.

How do I get round this one?

Dave

PS unbelievably quick response - thank you. This has been driving me nuts for ages!

[Ugo DI LUCA]

Hi,

So fast I didn't clearly mapped the probem. Duh !

You said it was working for some of them. If this was the case, it shouldn't though.

There are several ways you can convert an unstored calc into an indexable field, depending upon the situation.

Ray Cologon, AKA Cobalsky did an article just for this. Check the Article section for "Indexable" and you'll find it.

HTH

[Ugo DI LUCA]

It's here , but you may attach a file here too so that we look at it closer.

[David Mosedale]

Thanks. I will go away and read that article. As you say, though I do not understand why it works for some of them and not others.

My match field contains two parts. Each part can be either "OK", "Grade1", "Grade2", "Grade3", "PossGrade1", "PossGrade2" or "PossGrade3". So the entire field to be matched might be "OK_Grade3" or "PossGrade1_OK". Bizarrely, if the field contains one of the PossGrade parts it will not work, whether it is at the beginning or the end! If it just contains OK and the grade parts it works! And when it does have "PossGrade1_OK" it shows the GIF / record ID for the "Grade1_OK" record from file C i.e. it matches the WRONG record, not just no record!

Anyway, I'll go away now and read my homework. Thanks for your help!

Dave

[Ugo DI LUCA]

Hi,

I suspect it has nothing to do with the "Reference a related field", as this field is on the left side of your relationship, as I get it now.

But rather on the length as explain earlier, if the x number attached to the concanation is more than 8 digits long.

PossGrade1_OK is 13 character length but PossGrade12345678_OK can't be indexed, unless you use a space so that it becomes PossGrade 12345678_OK or whatever you want to get a valid key.

HTH

[David Mosedale]

I also suspect that it is nothing to do with the indexing problem. I have rejigged it so that the left hand side of the relationship is indexable, using a script to generate the result of the calculation. I still have exactly the same problem.

However, it should not be to do with the length of the number - the choices I put in the previous post were all of the possibilities (well, missed out "Unknown"). Combined in a sort of square where the first and last part could be either of the eight options, the file C just has 64 records, and this is fixed. But all of the records that have "PossGradex", where x is just "1", "2" or "3" fail on the relationship lookup.

Anyway, I am in the UK and am on my way home. Thanks for all of your help. Will check tomorrow to see if you have any more ideas.

Dave

[Ugo DI LUCA]

...Combined in a sort of square where the first and last part could be either of the eight options...

Try out the "key" separation then.

As a little example, Marc ELBOW would result in MarcELBOW, but if you have another Marcel BOW in your database, you'd end up with the same match.

It is even more accurate with numbers where 1 and 11 joined together as 111 would be either 1&11 or 11&1.

So, using a space might solve some issues.

Good Evening.

[David Mosedale]

You have been a fantastic help on this. I have set up the databases so that they have shorter keys with spaces in them - instead of "Grade2_PossGrade3" it is now "G2 PG3" - and it works! I also have the fields indexable (as I think I mentioned earlier) by scrapping the calculation and inserting the key as a calculation using a script, as suggested by Ray in his article.

Thanks again for all of your help!

Dave

[Ugo DI LUCA]

Glad I could help.

Note that the Left hand side of the relationship doesn't need to be indexed though, for the relationship to work.

It can be cleaner to have it indexed, if this key might be used elsewhere, but for just the purpose of a valid relationship, it doesn't help much.

Cheers