I was able to make this work by adding the whereClauseExtension to the
DisplayList tag:
<cf_DisplayList table="EmployeePhone" rowsPerPage="10" label="Phone Numbers"
displayALinkToThisSearchForm="No"
whereClauseExtension="EmployeePhone.EmployeeID=#URL.EmployeeID#">
The whereClauseExtension attribute shouldn't have to be used for this, but I
can't get it to work otherwise.
Jeff
Jeff Fleitz wrote:
Hi David,
I am referencing this message, because I am seeing a similiar problem
now with
another MD formset. To refresh your memory, you provided me with
updates to
DisplayList.cfm and DatabaseBlocks.cfc, so that I could get the proper
filtering working between the company and office tables below.
Now I am having the same problem.
I have a table Employee which has a child EmployeePhone, which hold one
or more
phone numbers for the employee and which is also related to another parent
PhoneType, which describes the type of phone number (work, home, mobile,
etc).
This child list is in the EmployeeEditForm, and the list is not
filtering the
phone numbers for each employee. All phone numbers show up for each
employee.
I have a JoinToParent call inside the DisplayList call for the
employeeID, but
it doesn't seem to matter. You can see that the Where Clause is not
picking up
a reference to the employeeid, so no filtering occurs.
SELECT PhoneType.PhoneType AS
PhoneType_PhoneType,EmployeePhone.PhoneNumber AS
EmployeePhone_PhoneNumber,EmployeePhone.Extension AS
EmployeePhone_Extension,EmployeePhone.EmployeePhoneID AS
EmployeePhone_EmployeePhoneID,EmployeePhone.EmployeeID AS
EmployeePhone_EmployeeID,EmployeePhone.PhoneTypeID AS
EmployeePhone_PhoneTypeID
FROM (EmployeePhone INNER JOIN Employee ON EmployeePhone.EmployeeID =
Employee.EmployeeID) INNER JOIN PhoneType ON EmployeePhone.PhoneTypeID =
PhoneType.PhoneTypeID
WHERE 1 > 0
ORDER BY EmployeePhone.PhoneNumber ASC
<cf_DisplayList table="EmployeePhone" rowsPerPage="10" label="Phone
Numbers"
displayALinkToThisSearchForm="No">
<cf_PrimaryKey primaryKeyColumn="EmployeePhoneID" primaryKeyType="integer">
<cf_JoinToParent table="Employee" primaryKeyColumn="EmployeeID"
foreignKeyColumn="EmployeeID">
<cf_JoinToParent table="PhoneType" primaryKeyColumn="PhoneTypeID"
foreignKeyColumn="PhoneTypeID">
etc.
David Churvis wrote:
Hi Jeff,
Does your child DisplayList tag have a JoinToParent nested inside it? If
not, is this one that you manually created or one that was generated from
the IDE?
Thanks,
Dave
----- Original Message ----- From: "Jeff Fleitz" <[EMAIL PROTECTED]>
To: "[email protected]"
<[email protected]>
Sent: Saturday, September 18, 2004 6:52 PM
Subject: [plum] Filter Criteria Sticking on Master Detail forms
I don't know if this problem is related to the drilldowns/ups so I'll
post it. This is all latest build and I have generated the tags several
times.
I am playing with a MasterDetail View form that has a DisplaySpouses tag
call on it (works fine), and a list to a child. This is based on the
Company -> Office parent child relationship I have been testing with
recently.
When I go into the view form, the Office List I built displays rows from
all companies, instead of from the company I am viewing. I couldn't
figure out why it is not being filtered, and could not find any
references to the problem, so I manually added a
whereClauseExtension="Office.CompanyID = #URL.CompanyID#" for grins. I
should not have to use this attribute, correct?
This worked, but if I returned to the Company list, selected a different
company to view, the filter criteria from the first call is sticking,
and the filter criteria from the second view call is appended to the
end. See SQL below for example.
SELECT Office.OfficeName AS Office_OfficeName,Office.Headquarters AS
Office_Headquarters,StateProvince.Abbrev AS
StateProvince_Abbrev,Country.Country AS Country_Country,Office.OfficeID
AS Office_OfficeID,Office.CompanyID AS
Office_CompanyID,Office.StateProvinceID AS
Office_StateProvinceID,Office.CountryID AS Office_CountryID FROM
((Office INNER JOIN Company ON Office.CompanyID = Company.CompanyID)
LEFT OUTER JOIN StateProvince ON Office.StateProvinceID =
StateProvince.StateProvinceID) LEFT OUTER JOIN Country ON
Office.CountryID = Country.CountryID WHERE 1 > 0 AND Office.CompanyID =
1 AND Office.CompanyID = 4 AND Office.CompanyID = 1 ORDER BY
Office.OfficeName ASC
I have to click the list links on the navbar, where refresh=1 to clear
the criteria and start over, otherwise errors are generated.
Is this related to what David is re-engineering right now? I didn't
install the tutorial, but I gather the example in the help file must
work?
Jeff
--
_______________________________
Jeff Fleitz
Tekquest, Ltd
Integrated Digital Solutions
http://www.tekquest.com
_______________________________
