Calculation field in a relationship

[Myron S.]

Hi there,

I am trying to have a field in table 2 reference and take on the value of a field in related table 1 whenever table 1 changes in order to dynamically establish another relationship for table 2 on the right side of a different table occurrance. I know I can't use a calculation field for the relationship, so I though that maybe a number field with an auto-enter calculation would do the trick, but it doesn't. Any idea how I can have the field in the right-hand side of a relationship take the value of a field in the left-hand table as it changes without making it a calculation field? Or, perhaps there's a way to use an unstored calculation field on the right-hand side of a relationship?

Thanks for any suggestions

Edited June 25, 2008 by Guest

[comment]

Please explain this using real table names, or at least use the generic Parent/Child terms - so we can understand the basic situation. Also explain what's the purpose of the added relationship.

[Myron S.]

I'll try to explain it better, but it might sound complicated:

In my travel business I have a RESERVATIONS table. With each reservation record there are related PASSENGERS. For each passenger there are related SERVICES offered by vendors on the trip, such as restaurants with meal choices.

In the RESERVATION table, a StatusCode field indicates the status of the reservation, for example: Paid in full = 1, Pending Payment = 2, waitlist = 3, and so on.

Each SERVICE line item associated with the Reservation also has a StatusCode field that needs to reflect the value of the StausCode field in the reservation. The reason for this is that, independant of the reservation itself and for calculation purposes, I need to relate each SERVICE record as a child of its respective vendor, based on the vendor record's Primary key and the value in its StatusCode field. Deposits due to the vendor are each calculated differently, depending on the status of that line item, so this is why I need the StatusCode field to reflect the StatusCode field in the Reservation.

I hope this makes some sense.

[comment]

I am sorry, but it IS a bit confusing. I think I got as far as:

Reservations -< Passengers -< Services >- Vendors

Now, do I understand correctly: you want another relationship, say:

Vendors -< Services 2

which will only include services with a specific value in their grandparent Reservation record?

Or am I completely off?

[Myron S.]

Yes, I think you've got it.

The Vendors -< Services 2 should only include specific records based on the value in the StatusCode field, which needs to update when the grandparent Reservation status changes.

[comment]

OK then, try this:

1. Define a new calculation field cCondID in Services =

Case ( Reservations::StatusCode = ; ServiceID )

Make the result the same type as ServiceID (ServiceID is your primary key in the Services table).

2. Define a self-join of the Services table as:

Services::cCondID = Services 2::ServiceID

Now you have a relationship from Vendors to Services 2 (through Services), filtered by the StatusCode field in Reservations.

Note that as always with relationships that depend on another relationship, you can expect some refresh issues after modifying the StatusCode. If you do this by script, include a Refresh Window [Flush cached join results] step after coming back to the Vendors layout.

[Myron S.]

Looks like that's the answer. Thank you so much for your help :-)