0 Replies Latest reply on Nov 6, 2006 4:37 PM by Newsgroup_User

    looping and Calculating

    Level 7
      I have the following code below. I need to calculate a backorderqty on a
      product if it has partially been invoiced(shipped) on a previous invoice.
      The code below identifies if the product in the query results has been
      shipped previously and invoices run in numeric ascending order.

      How do I identify the products that have been shipped previously in the
      query results. I tried putting it in a list like below.
      I then need to get same products and add all the QtyInvoiced from previous
      invoices and subtract them from the QtyOrdered field.

      This will get me a Product BackorderQty on the current invoice that the
      customer has requested.

      any help appreciated, I'm still learning



      <cfquery name="query" datasource="#client.dsn#" username="#client.username#"
      password="#client.password#">
      Select a.accountno, a.line, a.purchaseorder, b.unitofmes, a.invoice,
      a.orderno,
      a.delivery1, a.delivery2, a.delivery3, a.delivery4, a.delivery5,
      a.delivery6, a.delivery7,
      a.price, a.product, a.qtyordered, a.qtyinvoiced, b.description,
      a.orderdate, a.shipmentdate
      FROM
      order_history a INNER JOIN products b ON a.product=b.product
      WHERE a.OrderNo='823719' <!-----#form.orderno#---->
      AND a.accountno=4028 <!------------#trim(client.account)#-------->
      AND a.invoice<=172079 <!-----#form.invoice#----> order by a.invoice,
      a.line
      </cfquery>

      <cfset mylist=#trim(ValueList(query.product))#>

      <cfset Shipped=#ListValueCountNoCase(mylist, query.product)#>


      <cfif shipped neq 0>
      <cfloop ??????????????????????????

      THIS IS WHERE I NEED TO ADD MY LOOPING & CALCULATIONS

      </cfif>