Skip to main content
T
trojnfn
Inspiring
December 2, 2008
Question

cfloop question

  • December 2, 2008
  • 2 replies
  • 1128 views
I query a table using an orderNumber, and cfoutput my display in a table, listing partNumber, quantity, and serialNumber.

If the quantity for partNumber PN12345 is 3, then there are three serial numbers for that partNumber. For PN9999, if the quantity is 2, then there are two serial numbers for that part number.

For this example, I would display 5 records for the order number and put a checkbox next to each record, and if checked, delete that record.

This is the code that I use for display and checkbox :

<td valign="top" class="TitleText" align="center">
<cfinput type="checkbox" name="del/#serialNumberID#/#quantity#" value="Yes"></td>
<td valign="top" class="TitleText" align="center">#partNumber#</td>
<td valign="top" class="TitleText" align="center">#quantity#</td>
<td valign="top" class="TitleText" align="center">#serialNumber</td>

If I check one line item to delete for PN12345, that record should be deleted (actually updated to 0) and the quantity should be reduced by 1, since there are only two lines now.

This is the code I am using in my action page :

<cfloop index="i" list="#FORM.FieldNames#" DELIMITERS="," >
<cfif LEFT(i, 4) IS "del/">

<cfset select_serialNumberID = listgetat(i, 2, "/")>
<cfset select_quantity = listlast(i, "/")>

</cfif>

<cfquery name="qryUpdateSerial" datasource="recDisc">
update gfmSerialNumbers
set status = 0
where serialNumberID = '#select_serialNumberID#'

</cfquery>

<cfquery name="qryUpdateQuantity" datasource="recDisc">
update gfmPartNumbers
set quantity = #select_quantity# - 1
from gfmPartNumbers inner join gfmSerialNumbers
on gfmPartNumbers.gfmPartNumberID = gfmSerialNumbers.gfmPartNumberID
where gfmSerialNumbers.serialNumberID = '#select_serialNumberID#'

</cfquery>

</cfloop>

Everything seems to work fine and the quantity is now 2, since one line was deleted.

But here is my problem : If two checkboxes are selected for PN12345, those two lines are deleted and the quantity should be 3 - 2 = 1 (original quantity minus 2 deleted lines). But my query in the loop only is performed once and the quantity is 2 even though both of the selected lines are deleted.

Is my query to delete the quantity in th wrong place ? How do I perform the proper loop to delete the checked lines and get the proper quantity value ?
    This topic has been closed for replies.

    2 replies

    T
    The_ScareCrow
    Inspiring
    December 3, 2008
    A much easier way instead of looping through form fields

    Give the checkboxes all the same name, but enter serial number as the value
    (This will then give a list of serial number in the action page)

    Thus if more than one checkbox with the same serial number is checked then the serial number will be in the list that many times

    <cfinput type="checkbox" name="delRecords" value="#serialNumberID#">

    Then in the action page

    <cfif isDefined("form.delRecords")>
    <!--- make sure at least one checkbox was selected --->

    <cfquery name="qryUpdateSerial" datasource="recDisc">
    update gfmSerialNumbers
    set status = 0
    where serialNumberID IN (#ValueList(form.delRecords, " ' ")#)
    <!--- note spaces in value list for readability --->

    </cfquery>

    <!---
    No need to pass the quantity from page to page.
    As the quantity is just decremented if the serial number is found
    this can be done straight in the sql statement
    --->
    <cfquery name="qryUpdateQuantity" datasource="recDisc">
    update gfmPartNumbers
    set quantity = quantity - 1
    from gfmPartNumbers inner join gfmSerialNumbers
    on gfmPartNumbers.gfmPartNumberID = gfmSerialNumbers.gfmPartNumberID
    where serialNumberID IN (#ValueList(form.delRecords, "'")#)

    </cfquery>

    </cfif>

    Ken
    T
    trojnfnAuthor
    Inspiring
    December 4, 2008
    Hello ScareCrow,

    I tried your sample code and get this error. Here is part of the code and the error below.

    <cfinput type="checkbox" name="delRecords" value="#serialNumberID#">

    <cfif isDefined("form.delRecords")>



    <cfquery name="qryUpdateSerial" datasource="recDisc">
    update test_gfmSerialNumbers
    set status = 0
    where serialNumberID IN (#valuelist(form.delRecords, ",")#)
    </cfquery>

    The ValueList() function has an invalid parameter: form.The column delRecords is not present in the query named form. It is likely that you have misspelled the name of the column. The specific sequence of files included or processed is: E:\devxtroot\RecDisc\GFMUnReceivables\forms\deleteGfm.cfm, line: 24

    I think the only difference is in the valuelist, you have " ' " and I changed to comma, " , "
    T
    The_ScareCrow
    Inspiring
    December 4, 2008
    Sorry wrong function should be

    ListQualify(form.delRecords, " ' ")


    Ken
    D
    Dan_Bracuk
    Inspiring
    December 2, 2008
    The best solution is to delete the quantity field altogether and use the sql count() function instead.
    T
    trojnfnAuthor
    Inspiring
    December 3, 2008
    How would I do that ?

    Count the number of records and use that to update the quantity field ?

    I am not sure I understand what you are attemtpting to do.

    thanks
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices