aka_FATCAT
asked on
cfoutput group function
One check pays for multiple invoices.
I want this output to display one check number with one or more invoice numbers in the same line.
It works fine if 1 check paid 1 invoice, otherwise it lists the check each time it was used to pay an invoice. When I use the "group" function, it will list the check once, but will only list the first invoice it paid for.
Any ideas?
I want this output to display one check number with one or more invoice numbers in the same line.
It works fine if 1 check paid 1 invoice, otherwise it lists the check each time it was used to pay an invoice. When I use the "group" function, it will list the check once, but will only list the first invoice it paid for.
Any ideas?
SELECT *
FROM invoice
LEFT OUTER JOIN Banking ON invoice.Check_Number=banking.Check_Number
<cfoutput query="vchecks" maxrows="500" group="check_number">
use the <cfoutput query="qchecks"> check @ main and inside use another question with cfoutput group attribute
ASKER
<cfoutput query="vchecks" maxrows="500">
<cfoutput group="check_number">#invo ice_number #</cfoutpu t>
</cfoutput>
This returned every invoice number, not just the ones associated with that check.
<cfoutput group="check_number">#invo
</cfoutput>
This returned every invoice number, not just the ones associated with that check.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@ good u found answer!
Assuming that your check number and your invoice numbers are coming from that one query, your answer is not optimal, all you need is the group by with a 2nd set of cfoutput tags within it.
Also, to ensure the grouping works, you have to order your query by check number so the check numbers are all grouped together
SELECT *
FROM invoice
LEFT OUTER JOIN Banking ON invoice.Check_Number=banki ng.Check_N umber
order by check_number, invoice_date
<cfoutput query="vchecks" maxrows="500" group="check_number">
#check_number# - <cfoutput>#invoice_number# </cfoutput >
</cfoutput>
Also, to ensure the grouping works, you have to order your query by check number so the check numbers are all grouped together
SELECT *
FROM invoice
LEFT OUTER JOIN Banking ON invoice.Check_Number=banki
order by check_number, invoice_date
<cfoutput query="vchecks" maxrows="500" group="check_number">
#check_number# - <cfoutput>#invoice_number#
</cfoutput>
I agree with gdemaria's answer. It's the more optimal approach.
<cfoutput query="vchecks" maxrows="500"
... another performance tip, limit the number of rows in the db. The current code actually pulls every record in the 2 tables, but only displays the first X records. If you only need 500 records, specify that in your sql.
<cfoutput query="vchecks" maxrows="500"
... another performance tip, limit the number of rows in the db. The current code actually pulls every record in the 2 tables, but only displays the first X records. If you only need 500 records, specify that in your sql.
Got it.
<cfoutput query="vchecks" maxrows="500">
<cfloop query="invoicenumbers" >#invoice_number# - </cfloop>
Hm... on second glance, ... I don't believe that would work. At least not if there's more than 1 checknumber as your query suggests.
<cfoutput query="vchecks" maxrows="500">
<cfloop query="invoicenumbers" >#invoice_number# - </cfloop>
Hm... on second glance, ... I don't believe that would work. At least not if there's more than 1 checknumber as your query suggests.
ASKER
Here is the complete working code.
<cfquery name="vchecks" datasource="#request.dataSource#">
SELECT *
FROM banking
WHERE 0=0
<!---LEFT OUTER JOIN Banking ON invoice.Check_Number=banking.Check_Number--->
<cfif isDefined ("URL.check_status")>
AND cstatid = '#URL.check_status#'
</cfif>
<cfif isDefined("FORM.check_date")>
AND check_date = '#FORM.check_date#'
<cfelseif isDefined("URL.check_date")>
AND check_date = '#URL.check_date#'
<cfelse>
AND check_date > '#DateFormat(startdate, "mm-dd-yyyy")#'
</cfif>
<cfif isDefined ("URL.date")>
ORDER BY check_date
<cfelse>
ORDER BY banking.check_number desc
</cfif>
</cfquery>
<cfoutput query="vchecks" maxrows="500">
<cfquery name="cstat" datasource="#request.dataSource#">
SELECT cstatid, check_status
FROM cstat
WHERE cstatid = '#vchecks.cstatid#'
</cfquery>
<cfquery name="invoicenumbers" datasource="#request.dataSource#">
SELECT invoice_number
FROM invoice
WHERE check_number = '#vchecks.check_number#'
</cfquery>
<tr>
<td>#check_number#</td>
<td>#DateFormat(check_date, "ddd, mm-dd-yyyy")#</td>
<td>#pay_to#</td>
<td><cfloop query="invoicenumbers" >#invoice_number# - </cfloop></td>
<td>#DollarFormat(amount)#</td>
<td>#notes#</td>
<td>
<cfform name="cstatus" method="post" action="/_admin/view_checks.cfm">
<cfselect name="cstatus_select" query="cstatf" value="cstatid" selected="#cstat.cstatid#" display="check_status"/>
<cfinput type="submit" name="submit" value="Submit">
<cfinput type="hidden" value="#vchecks.cid#" name="cid">
</cfform>
</td>
</tr>
</cfoutput>
</table>
That is one way to do it, yes. Did you post it so we could help you do it better or just to show the "answer?"
Right now you have one main query, plus two queries that run for EVERY check number, so if there are 50 checks, you will run 101 queries. The way agx and I suggested, you would have one query no matter what.
You will also have 50 forms instead of one form.
If you want more help, let us know, if you were just posting because you wanted to show it working, that's fine too.
ASKER
Right now, I'm just happy it's working.
If you can tell me how to do it better as in more efficiently, I would be very grateful.
Thanks.
If you can tell me how to do it better as in more efficiently, I would be very grateful.
Thanks.
Something like this..
<cfquery name="vchecks" datasource="#request.dataSource#">
SELECT TOP 500
b.*
, inv.invoice_number
, cs.check_status
FROM banking b
left join invoice inv on inv.check_number = b.check_number
left join cstat cs on cs.cstatId = b.cstatid
WHERE 0=0
<!---LEFT OUTER JOIN Banking ON invoice.Check_Number=banking.Check_Number--->
<cfif isDefined ("URL.check_status")>
AND b.cstatid = '#URL.check_status#'
</cfif>
<cfif isDefined("FORM.check_date")>
AND b.check_date = '#FORM.check_date#'
<cfelseif isDefined("URL.check_date")>
AND b.check_date = '#URL.check_date#'
<cfelse>
AND b.check_date > '#DateFormat(startdate, "mm-dd-yyyy")#'
</cfif>
<cfif isDefined ("URL.date")>
ORDER BY b.check_date, b.check_number desc
<cfelse>
ORDER BY b.check_number desc
</cfif>
</cfquery>
<cfform name="cstatus" method="post" action="/_admin/view_checks.cfm">
<cfoutput query="vchecks" group="check_number">
<tr>
<td>#check_number#</td>
<td>#DateFormat(check_date, "ddd, mm-dd-yyyy")#</td>
<td>#pay_to#</td>
<td><cfoutput>#invoice_number# - </cfoutput></td>
<td>#DollarFormat(amount)#</td>
<td>#notes#</td>
<td>
<cfselect name="cstatus_select#vchecks.currentRow#" query="cstatf" value="cstatid" selected="#vchecks.cstatid#" display="check_status"/>
<cfinput name="cid#vchecks.currentRow#" type="hidden" value="#vchecks.cid#" >
</td>
</tr>
</cfoutput>
</table>
<cfinput type="submit" name="submit" value="Submit">
</cfform>
ASKER
Found the answer myself.