i have a master table
closingReports
'closingReports' has a foreign key to 'offices' table.
'leads' are assigned to 'agents' who are assigned to 'offices'.
i want to show Details and be able to edit leads using closingReports as the Master.
so in my closing reports, i want a link to details page for all leads (leads that are assigned to agents that are assigned to offices ...that match the closingReports.officeID
my leads query looks like:
SELECT
leads.id,
leads.generatedBy,
leads.firstName,
leads.lastName,
leads.addr1,
leads.addr2,
leads.city,
leads.state,
leads.zip,
leads.mlsID,
leads.VZPhone,
users.officeID,
leads.crid
FROM leads
INNER JOIN users ON leads.generatedBy = users.id
but when i try to create a master details link between closingReport and leads [in Datasource tables step], I am not able to use the users.officeID to link back to the closingReports table. I can only choose from fields that are in the leads table - none of the other fields in the query are available.
is there any way to get this to work?
I also tried making the offices table the detail table... and it's corresponding query pulled in leads ... but when i do it this way, i cannot EDIT any of the leads (in the Choose pages step, the only Selected Key Column I can choose, is from the offices table.).
How can i add an editable "granddaughter" table in a details relationship with it's "grandparent" master?