Let’s say I have a three-table database structure, and let’s keep things concrete and say that the tables are Clients, Projects and Activity. (This would be for an invoicing app.)
- Client records include fields for client name (“Acme Corp”), address (“San Antonio TX”), client contact (“Murray Morrison”) and a status (“Active”).
- Project records include a project name (“New website”), possibly a project contact (“Sally Samson”), etc. In addition, each project record MUST be connected to a Client record. (Per Tadabase’s preference in this matter, this is a many-to-one or child-to-parent connection: there can be more than one project per client.)
- Activity records include fields for: description of work done on the project, date done, an agent name, time spent. Each activity record must be connected to a project record, and that connection of course in turn means that it’s related to a client record. (I’m think I’m correctly distinguishing connected from related here per Tadabase’s use of these terms.)
I will spend most of my time in this app in the Activity table. When I create a new activity record and connect it to a project, I would like to be able to see one or two bits of data from the related “grandparent” record in Clients table: not just the client name but perhaps the client’s city and state info, or anything else that’s specific to the client record. When I go to create an invoice based on current activity, of course I’ll want the top of the invoice to show a lot more info from clients: client name, client contact, full address, perhaps a client #, whatever one might put in the header of an invoice. That info is related, not connected, that is, it’s not just one hop away from the activity record, it’s two hops away. (I feel obliged to add here that this is an easy example. I can imagine situations in which I might want to “see” info that is three hops away or more.)
So how do I see that related data from the Client record? I can get the ClientName value from the connected project record: it’s the value in the connection field that joins Projects to Clients. But how would I show — in a table based on Activity — not just the client name but the client’s city and state?
Note that I’m asking about showing data from the “grandparent” table in a table of grandchildren. I’m NOT asking how to see grandchildren from the perspective of a client record. (I know how to do that.)
Thanks.
William
