Thanks Will for the quick response. I thought that I would probably need to do
this in the query and at the time couldn't find it. I have since found the
function and query (see below). Where and how do I go about adding the Where
clause you suggested and can there be a third "and" in the where clause.
<cffunction name="getUserBudgets" output="false" description="Returns a query
with the users documents" access="remote" returntype="query">
<cfargument name="cfcUserUserID" type="string" required="yes">
<cfargument name="cfcDocID" type="string" required="no">
<cfquery name="getUserDoc" datasource="HeartBeat">
SELECT DocAdmin_MasterList.DocID,
DocAdmin_MasterList.DocType, DocAdmin_DocType.sortorder AS DocType_SortOrder,
DocAdmin_MasterList.DocTitle,
DocAdmin_MasterList.DocDueDate,
DocAdmin_MasterList.DocFiscalYear, DocAdmin_MasterList.DocDateCreated AS
DateCreated,
DocAdmin_MasterList.DocLastModified AS LastModified,
DocAdmin_MasterList.DocLockDate AS LockDate, DocAdmin_FiscalYear.Active,
DocAdmin_FiscalYear.SortOrder AS FiscalYear_SortOrder,
DocAdmin_Access.UserID AS UserId, DocAdmin_Users.UserUserID AS UserUserId,
DocAdmin_Department.DeptID, DocAdmin_Department.DeptCode,
DocAdmin_Department.DeptName, DocAdmin_Department.DeptDetailCode,
BudgetApproval.BudgetStatusID
FROM DocAdmin_MasterList INNER JOIN
DocAdmin_DocType ON DocAdmin_MasterList.DocType =
DocAdmin_DocType.DocType INNER JOIN
DocAdmin_FiscalYear ON DocAdmin_MasterList.DocFiscalYear
= DocAdmin_FiscalYear.DocFiscalYear INNER JOIN
DocAdmin_Access ON DocAdmin_MasterList.DocID =
DocAdmin_Access.DocID INNER JOIN
DocAdmin_Users ON DocAdmin_Access.UserID =
DocAdmin_Users.UserID INNER JOIN
DocAdmin_Department ON DocAdmin_MasterList.DeptID =
DocAdmin_Department.DeptID INNER JOIN
BudgetApproval ON DocAdmin_Department.DeptID =
BudgetApproval.BudgetApprovalDepID
WHERE (DocAdmin_Users.UserUserID = <cfqueryparam value="#cfcUserUserID#"
cfsqltype="cf_sql_varchar">) AND (DocAdmin_MasterList.DocType = 'Budget') AND
(BudgetApproval.BudgetStatusID = 4)
ORDER BY
DocAdmin_FiscalYear.SortOrder, DocAdmin_DocType.sortorder
</cfquery>
<cfreturn getUserDoc />
</cffunction>
Thanks, Lorenzo
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-newbie/message.cfm/messageid:6048
Subscription: http://www.houseoffusion.com/groups/cf-newbie/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-newbie/unsubscribe.cfm
