getting fields from a related table of a related table

Archived from the Xataface Users forum.

cookie720 — Thu Jun 28, 2012 6:32 pm

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 — Wed Jul 04, 2012 10:22 am

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 — Wed Jul 04, 2012 10:49 pm

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