Unstored Calculations & Relationships

[bankofbermuda]

The summary of my question follows the details of the following three files and associated fields:

PARTY

PK_PartyID

ct_PictureID_Expired

d_PictureID_Expiry

CLIENT

PK_ClientID

t_terminated

PARTY_CLIENT_JOIN

FK_ClientID

FK_PartyID

t_Individual

t_Exempt_Party

ct_Terminated

ct_PictureID_Required

The following relationships are setup:

PARTY_CLIENT_JOIN

ClientRelationship = FK_ClientID::PK_ClientID

PARTY_CLIENT_JOIN

PartyRelationship = FK_PartyID::PK_PartyID

Field definitions

PARTY_CLIENT_JOIN

ct_PictureID_Required = if ( t_exempt_party = "Exempted Party" or ct_Terminated="T" , "Not Required" , if (t_individual = "Yes", "Requried", "Not Required" ))

ct_terminated = if ( ClientRelationship::Terminated = "Yes" , "T", "" )

PARTY

ct_PictureID_Expired = if ( PartyRelationship::ct_PictureID_Required = "Required" , if ( d_PictureID_Expiry < Today , "Expired" , "Valid" ) ) , "" )

In the PARTY_CLIENT_JOIN database, I access the PARTY records via a portal. I have two records in the JOIN database related to one record in the PARTY database. Record one in the Join database has a "Not Required" result in the ct_PictureID_Required field. Record two in the JOIN database has a "Required" result. However, when viewing the PARTY record in the portal, the d_PictureID_Expiry field returns a null value for both JOIN database records. Based on the conditions of the calculation, I would only expect this in the JOIN record containing the "Not Required" result, not both JOIN records.

Can anyone explain what I have done wrong here?? What do I need to change to have the record in the PARTY database reflect the proper result based on the active record in the JOIN database??

Chris

[kennedy]

Did you word your question right?

d_PictureID_Expiry is not a calculation, right?

And it is NULL in both records?

The only thing that we'd seem to be able to conclude is that the data has not been populated properly.

[Ugo DI LUCA]

Also,

You explained the relationship structure from "the Join db to the Party db", but you didn't explicitly mentionned what was the relationships created from the Party db to the Join.

Since you're viewing records in a portal using this "reverse" relationship, it would be good to know which relationship you used, as I assume you have more then one relationship created from the Party to the Join.

Check that the field(s) in the portal are using the correct relationship.

If the field in portal don't use the same relationship that the portal itself, it may explain what you're experiencing.

Besides, you'd better use Case intead of If and Status(CurrentDate) instead of Today...

[bankofbermuda]

Ugo,

Thanks. By changing the test for the ct_PictureIDExpired field so it doesn't reference the Join database fields this corrects my problem. Thanks for your responses.

Chris