CODEWITHSUNDEEP
coldfusion
David Brierton
David Brierton

Reputation: 7397

notify a user if there are no matching records found - coldfusion

How do you create something to notify a user if there are no matching records found with the queries you run?

I have a queries.cfm that I run like this:

<cfset result = {} /> 
<cftry> 
    <cfset date1 = CREATEODBCDATETIME(form.StartDate & '00:00:00')>
    <cfset date2 = CREATEODBCDATETIME(form.EndDate & '23:59:59')>

    <cfquery datasource="#application.dsn#" name="GetEmployeeInfo">
        SELECT  trans_location, date, associate
        FROM    cl_checklists
        WHERE   date >=  <cfqueryparam value="#date1#" cfsqltype="cf_sql_timestamp" />
                AND date <= <cfqueryparam value="#date2#" cfsqltype="cf_sql_timestamp" />
                AND trans_location IN ( <cfqueryparam value="#FORM.location#" cfsqltype="cf_sql_varchar" list="true" />  )
                AND associate IN ( <cfqueryparam value="#FORM.EmployeeName#" cfsqltype="cf_sql_varchar" list="true" />  )
    </cfquery>

    <cfquery datasource="#application.dsn#" name="GetLocationInfo">
        SELECT  trans_location, date, associate
        FROM    cl_checklists
        WHERE   date >=  <cfqueryparam value="#date1#" cfsqltype="cf_sql_timestamp" />
                AND date <= <cfqueryparam value="#date2#" cfsqltype="cf_sql_timestamp" />
                AND trans_location IN ( <cfqueryparam value="#FORM.location#" cfsqltype="cf_sql_varchar" list="true" />  )
    </cfquery>

     <cffunction name="getop_id" access="public" returntype="string"> 
        <cfargument name="associate"  > 
        <cfquery name="spitOutop_id" datasource="#application.userinfo_dsn#"> 
            SELECT assoc_name 
            FROM dbo.tco_associates 
            WHERE assoc_id= #arguments.associate# 
        </cfquery> 
        <cfreturn spitOutop_id.assoc_name > 
     </cffunction> 

     <cfquery name="allAssociatesQry" dbtype="query">
        SELECT DISTINCT associate, COUNT(*) AS associateCount FROM GetEmployeeInfo GROUP BY associate ORDER BY associate 
     </cfquery>

<table border="1" id="Checklist_Stats">
    <thead>
        <th><strong>Associate Name</strong></th>
        <th><strong>Location</strong></th>
        <th><strong>Checklists Generated by Associate</strong></th>
        <th><strong>Checklists Generated by Selected Location(s)</strong></th>
        <th><strong>Associate Percentage of Location Total</strong></th>   
    </thead>
    <tbody>
      <!--- aggregate variables --->
      <cfset aggrAssociateChecklist = 0>
      <cfset aggrLocationChecklist = 0>

      <cfloop query="allAssociatesQry">
          <!--- get Associate's name --->
          <cfset thisAssociateCode = trim(allAssociatesQry.associate)>
          <cfset thisAssociateName = getop_id(thisAssociateCode) />
          <!--- 1.1 get all trans_location code and total counts for the current Associate --->
          <cfquery name="allLocCodeForAssociateQry" dbtype="query">
              SELECT trans_location,count(trans_location) AS locCntr FROM GetEmployeeInfo WHERE associate='#thisAssociateCode#' GROUP BY trans_location ORDER BY trans_location
          </cfquery>
          <!--- 1.2 get the aggregate of checklist count generated by the current Associate for each location --->
          <cfquery name="qTotalChecklistCountForAssociate" dbtype="query">
              SELECT SUM(locCntr) AS totalAssocChecklist FROM allLocCodeForAssociateQry 
          </cfquery>

          <!--- 2.1 get the total location checklist for each location available for the current Associate --->
          <cfquery name="allLocChecklistForAssociateQry" dbtype="query">
              SELECT trans_location,count(trans_location) AS totalLocCount FROM GetLocationInfo WHERE trans_location IN (#QuotedValueList(allLocCodeForAssociateQry.trans_location)#) GROUP BY trans_location ORDER BY trans_location
          </cfquery>
          <!--- 2.2 get the aggregate of location checklist generated by the current Associate --->
          <cfquery name="qTotalLocChecklistForAssociate" dbtype="query">
              SELECT SUM(totalLocCount) AS totalLocChecklist FROM allLocChecklistForAssociateQry
          </cfquery>
          <!--- display record for the current Associate --->
            <cfoutput query="allLocCodeForAssociateQry">
              <tr>
                  <!---<td><strong>#thisAssociateCode#</strong></td>--->
                  <td><strong>#thisAssociateName#</strong></td>
                  <td>#allLocCodeForAssociateQry.trans_location#</td>
                  <td>#allLocCodeForAssociateQry.locCntr#</td>
                  <td>#allLocChecklistForAssociateQry['totalLocCount'][CurrentRow]#</td>
                  <td>#NumberFormat((allLocCodeForAssociateQry.locCntr/allLocChecklistForAssociateQry['totalLocCount'][CurrentRow]) * 100, '9.99')#%</td>
              </tr>
              <cfset thisAssociateName = "" />
            </cfoutput>
            <!--- 3.1 get sub total for each Associate group --->
            <cfset totalAssocChecklist = qTotalChecklistCountForAssociate.totalAssocChecklist>
            <cfset totalLocChecklist = qTotalLocChecklistForAssociate.totalLocChecklist>
            <!--- 3.2 add to the aggregate --->
            <cfset aggrAssociateChecklist += totalAssocChecklist>
            <cfset aggrLocationChecklist += totalLocChecklist>
            <!--- display sub total for each Associate group --->
            <cfoutput>
                <tr>
                    <td>Associate Total</td>
                    <td></td>
                    <td>#totalAssocChecklist#</td>
                    <td>#totalLocChecklist#</td>
                    <td>#NumberFormat((totalAssocChecklist/totalLocChecklist) * 100, '9.99')#%</td>
                </tr>
            </cfoutput>
      </cfloop>
      <!--- display calculated aggregate at the end of the result --->
     <!--- <cfoutput>
          <tr>
            <td><strong>Total</strong></td>
            <td></td>
            <td>#aggrAssociateChecklist#</td>
            <td>#aggrLocationChecklist#</td>
            <td>#NumberFormat((aggrAssociateChecklist/aggrLocationChecklist) * 100, '9.99')#%</td>
          </tr>
      </cfoutput>--->
    </tbody>
</table>
<cfcatch type="any"> 
        <cfset result.error = CFCATCH.message > 
        <cfset result.detail = CFCATCH.detail > 
    </cfcatch> 
</cftry>

But I am trying to notify the user when no results come back based on there search.

I was trying to do a catch for the results if there 0 do something but have been unsuccessful. Would any Cold Fusion gurus like to assist me in how you alert your users when there query came back empty.

I tried:

<cfif #allAssociatesQry# is 0>
<cfif #allAssociatesQry# is null>
<cfif #allAssociatesQry# is "">
<cfif allAssociatesQry is 0>
<cfif allAssociatesQry is null>
<cfif allAssociatesQry is "">

Upvotes: 0

Views: 63

Answers (1)

Dan Bracuk
Dan Bracuk

Reputation: 20794

Don't use try/catch. That's for code that doesn't run successfully. All you need is this:

<cfquery name="q">
sql
</cfquery>

<cfif q.recordcount is 0>
code for no records
<cfelse>
display records
</cfif>

Upvotes: 4

Related Questions