getting fields from a related table of a related table

[cookie720]

i have three tables, client, case, and appointment

my clients are related to case
my appointments are related to case
one client to a case, many appointments to a case
the problem is getting the client info in appointments.
i can get the case info in each appointment, but i cant get the client.

im guessing its in the appointments relationships.ini

[cases]
cases.CaseID = "$CaseID"

[clients]
__sql__ ="SELECT * FROM `clients` now what? "

the cases works, the client....not sure how to get the client....is it even possible? I have all clients appearing now in the related section. I just want the one that is related to the case. is it possible to get it "through" the case?

[shannah]

This is trickier, as your clients relationship probably won't be able to add/remove related records how you like. But you can set up the relationship and see what happens. You just need to do a many to many relationship:

Code: Select all

[clients]
    cases.CaseID="$CaseID"
    clients.ClientID=cases.ClientID


[cookie720]

Shannah, ThankYOU! that worked, chucked that into my ini and it works. Can get clients in my appointment pdf!
perfect-o