Grandparental Inheritance

[T-Square]

Sorry for the subject; I couldn't imagine a clear subject that wasn't as long as the problem.

Short of a calculated field, is there a way to use an attribute of a grandparent record as a relationship component between the parent and child?

In my situation, my parent record is a Customer record, the children records are Deliveries, and the grandparent is the Subscriptions record, which has a primary service field. Each delivery record identifies the service it represents.

I am trying to get all the Delivery records for one customer that are the primary service for that customer.

For example:

Customer C uses Subscription 5.

Subscription 5 has Primary Service p.

I want Deliveries with Customer C, Service p.

Previously, I have done this by creating calculated fields and scripted finds to identify the primary service, but this is computationally slow.

I have tried putting the Subscription table in between the Customer and Delivery tables, but when I tried navigating from the customer to the delivery layouts, I got every delivery the specified type (regardless of the customer).

I am hoping someone has a suggestion.

TIA,

David

[Søren Dyhr]

My take here is that both subscription and service actually belongs to the same table, but the structure as such is recursive as well.

http://jonathanstark.com/recursive_data_structures.php

Is this too slow for you? The alternative is the transaction model, with all the shenanigans of implementing a proper rollback...

--sd

[LaRetta]

Hi David,

I posted and then, after reading Søren's post, deleted it. I find your post a bit unclear but I believe that is because the request isn't an easy standard relationship. I thought Subscriptions WAS same table as Primary Service so I might be very off base (I'm unsure why Søren is making his suggestion so I may not understand at all). Please see attached and see if we are on same page.

From Customers, you want ALL deliveries for this Customer which have the same primary service as the Service they subscribe to, right?

UPDATE: You will need to control navigation (or use script triggers) so that you pre-populate that Deliveries global with the CustomerID you are viewing otherwise when you switch customers, the wrong data will display in the portal.

Grandparental.zip

Edited June 6, 2009 by Guest
Added update

[LaRetta]

But I don't understand why your Customer table simply doesn't use a calculation of Subscriptions::Primary Service and you then create a portal to deliveries based upon both CustomerID and cPrimaryService so I'm probably missing what you want anyway ...

[T-Square]

LaRetta, Soren--

Sorry I wasn't fully clear. A subscription can be made up of different services. So, in my example, Subscription 5 is made up of services p AND s. I only want to see the p services listed. To put in real terms, a Home delivery subscription includes a (primary) veggie box service ($17) and a home delivery service ($5). I am looking for a list of the veggie box services only.

LaRetta, I looked at your structure, and was intrigued by the idea of adding the Customer table at the other end of the chain. I tried it, but unfortunately, this second TO doesn't have the same CustomerID active, and thus doesn't filter the record set.

As for your final suggestion, LaRetta, of using a calculation, that is essentially what I have already. I was trying to see whether there was a way to do this without a calculated field in part because ever since I went with the separation model, I have had an aversion to creating calculation fields in the back end of a distributed database.

Thanks for the suggestions; I'm going to keep digging.

David

[LaRetta]

. A subscription can be made up of different services. So, in my example, Subscription 5 is made up of services p AND s.

As a multiline? What?? We are just supposed to know what you are talking about? If your post was from someone else, how in heaven's name could you answer them? It would help if you provided US with YOUR structure showing the tables involved, their key IDs and a few records in each table so we understand what you have. I thought you would at least take my file and change it to match what you have!

this second TO doesn't have the same CustomerID active, and thus doesn't filter the record set.

Why can't you tell us what IDs or fields are in Subscriptions - or in any of your tables for that matter!! I use separation model as well but I cannot see what you have, David!!! I still don't know what fields and IDs we have to work with! My crystal ball is in repair this week, and shame on me for even attempting to solve your request with too little information; I know better.

Maybe others can come up with ideas for you. :smirk:

[T-Square]

LaRetta--

Please accept my apology. I was not trying to test your psychic abilities. I had wrestled with the problem for quite a while before I posted, and I wrestled with your example for another long while.

When I said I was going to keep digging, what I should have said was that I suspect I am trying to find a solution using the wrong method, and under the circumstances, I will dig for a better one. It truly was not an attempt to get others sucked in to this.

For what it's worth, I am going to probably go with a scripted solution, as indicated in my modified version of your example file, attached. The modified structure might also give you an idea of the conundrum (but I'm not asking you to put any more time into this, really!).

Sorry for the trouble.

David

GrandparentalMod.fp7.zip

[comment]

But I don't understand why your Customer table simply doesn't use a calculation of Subscriptions::Primary Service and you then create a portal to deliveries based upon both CustomerID and cPrimaryService

That's what I wonder about, too:

GrandparentalMod.fp7.zip

[T-Square]

Comment--

I apparently got it in my head somewhere that using a calculated field in a relationship would result in poor performance. That's probably because at some point in the past, I had a much gnarlier data structure...

I see that this approach will work for my needs. Thanks for opening my eyes.

David