multi criteria relationship not working

[julesw]

hi all,

any idea why a multi-criteria relationship in a portal wouldnt work?

it has 3 criteria. a similar relationship using 2 of the 3 works perfectly.

i have double checked, and there are correct values in all 3 fields on both sides for it to bring up at least 3 related records but it's not showing them. if i try to enter data in the portal (as i have got it set to allow creation of related records) it says 'the operation could not be performed because one or more of the relationships between these tables are invalid. it doesnt explain why though!

i am really stumped, because it is basically identical to the relationship with 2 criteria, and the additional one i have added is really simple.

it is based on a global field on one side, and a calculation field using an if formula on the other.

any suggestions? i thought it might be to do with the order of the criteria in the relationship, but i have tried changing these around, and it didnt make any difference.

any help will be most appreciated, as always.

PS - i only work at this job one day a week (and its now the end of the day) so forgive any delays in replying...

[MoonShadow]

"it is based on a global field on one side, and a calculation field using an if formula on the other."

If the global is on the side expected to receive the new data/record, it won't work. A global can't be used as a key to the 'receiving' side because it can't be indexed. If you look at the lines in your graph, you may see the side with the global ends with a sideways T as ----- | indicating data will not flow into it.

That does NOT mean data can't be passed using it however; only that the global can't be used (as part of a key) for a relationship in that TO. And if the calculation contains an aggregate function, Get() function or other unstored beast it can't be used either.

Also you can't change key field data (in a portal) that is used for a relationship. But the field data should nonetheless be displaying for you. I doubt a mismatch of data-type would produce this error although I've never done it to find out.

Which is the culprit in your situation?

[julesw]

okay, it seems to be a problem with the calculation field, which is on the side meant to receive the new record. it is displaying that ----| sign. i tried to make the calculation stored, but it didnt work.

will have to think of doing this another way by the look of it...

[julesw]

yeah, the calculation couldnt be stored because it referenced related fields. i have got round it by creating another field, which is just a text field which auto enters the value from the calc field. this works: this field can be indexed, and now my relationship no longer has that ----| sign, and the portal is displaying the related records correctly!

the only remaining problem is that it still won't allow the creation of new records. it shows the blank portal row, but when i try to enter data, it says 'This action cannot be performed because this field is not modifiable.'

do you think this would have anything to do with the calc field again?

maybe its too circular, ie the new field ive created for the multi-criteria relationship relies on a value being in that calc field i was using previously, so it is somehow in conflict. i presume when you create a new related record, it essentially automatically puts the matching data in the fields used in the relationship, yeah?

it may be that i just have to sacrifice this function, but if anyone could help me understand how this works a bit better, it would be really appreciated. (i dont mind doing things differently, but its nice to know why something doesnt work.)

thanks again, j