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
>You'd need to do this in the sql of the query really.
>
>Depending on your database something like
>
>WHERE Year(DocFiscalYear) >= Year(DateAdd("yyyy",-1,now())) should do it.
>
>I'm assuming your DocFiscalYear column is a date.
>
>Cheers
>
>Will
>
>Hi all,
>
>I am new to ColdFusion and just took over an application that was built
>several years ago. As I am learning coldfusion one of the things I have
>been tasked with doing is limiting the results returned by the query to the
>current year minus 1. For instance we are currently in year 2013 so
>documents will only be retrieved for 2013 and 2012. As you can see below
>the the query "getuserdocuments" returns all documents, is there a way to
>limit the result based on the criteria i stated above?
>
>Thanks in advance for your help, Lorenzo
>
>
>
><cfset noRecBgColor="Yellow">
>
> <cfobject component="hbcfc.document" name="Documents">
> <cfinvoke component="#Documents#" method="getUserDocuments"
>returnvariable="getUserDocuments">
> <cfinvokeargument name="cfcUserUserID"
>value="#AppLogon_name#">
> </cfinvoke>
><!---Header needs to be included in every file ---> <cfinclude
>template="header.cfm">
>
>
>
> <h3><cfoutput> #AppTitle#<br>
> </cfoutput><span style="font-weight: bold"><img src="ekg.gif"
>width="198" height="76"></span></h3>
>
>
> <cfform name="DocList">
> <ul>
> <li style="font-weight: bold">HeartBeat Documents </li>
> <ul>
> <cfoutput query="getUserDocuments" >
> <cfobject component="hbcfc.document" name="Documents2">
> <cfinvoke component="#Documents2#"
>method="getDocumentsViewOrEdit" returnvariable="ViewOrEdit">
> <cfinvokeargument name="cfcUserUserID"
>value="#AppLogon_name#">
> <cfinvokeargument name="cfcDocID"
>value="#getUserDocuments.DocID#">
> </cfinvoke>
>
>
> <li><span style="font-weight: bold"><a
>href="#getUserDocuments.DocType#_<cfif ViewOrEdit is
>"Locked">View<cfelse>#ViewOrEdit#</cfif>.cfm?DocID=#getUserDocuments.DocID#"
>>
>
>#getUserDocuments.DocType# - #getUserDocuments.DocTitle#</a></span>
>
><font color="green" size="-2">#getUserDocuments.DocFiscalYear#
>(#ViewOrEdit#<cfif ViewOrEdit IS "Edit">/Approve</cfif>)</font></li><br>
> </cfoutput>
>.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:6047
Subscription: http://www.houseoffusion.com/groups/cf-newbie/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-newbie/unsubscribe.cfm
