?
Solved

ColdFusion One Input Box for Multiple Field Inputs

Posted on 2012-09-20
20
Medium Priority
?
797 Views
Last Modified: 2012-10-05
Users input ID numbers when products are received - right now there is one field with one input box - I need to accommodate multiple entries when needed (could be one to four entries)
Is it possible to input in one box with a separator (a comma or semicolon) and have CF place in separate fields? (I.E. ID1, ID2, ID3, ID4)

What is the best way to approach this problem?
0
Comment
Question by:DJPr0
  • 10
  • 9
20 Comments
 
LVL 52

Expert Comment

by:_agx_
ID: 38421930
>  Is it possible to input in one box with a separator

If they're numeric id's - yes. You can use list functions to extract the values.  

<cfset FORM.yourSingleField = "123;456;789">
<cfloop from="1" to="#listLen(FORM.yourSingleField, ';')#" index="x">
      <cfset variables["ID"& x] = listGetAt(FORM.yourSingleField, x, ";")>
      <cfoutput>item #x# = #variables["ID"& x]#</cfoutput><br>
</cfloop>

But personally I prefer using separate fields (if possible). I name the fields field1,field2,field3, etc...

       <input name="ID1" ..>
       <input name="ID2" ..>
       <input name="ID3" ..>

       <input name="numOfIDs" value="3" type="hidden">

Then parse the values dynamically

     <cfloop from="1" to="#form.numOfIDs#" index="x">
         <cfoutput>item #x# = #FORM["ID"& x]#</cfoutput><br>
     </cfloop>
0
 

Author Comment

by:DJPr0
ID: 38422017
Since I don't know how many ID's are needed and not enough room on the page, I need to go with the first solution - problem is ID's could have letters with the numbers.

I could go with the second solution if the default is to show just one input box and have an option (maybe a plus sign) to show more input boxes - not sure how to do this.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 38422148
> problem is ID's could have letters with the numbers.

Well letter's are ok too as long as the id's never contain the delimiter ie comma or semicolon because then list functions won't know what to do. So the results would be wrong. Edit: But again, it's just letters and numbers that isn't an issue.

For the 2nd solution you need javascript.
http://ursdeep.wordpress.com/2008/07/09/dynamically-adding-and-removing-text-boxes-using-javascript/
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:DJPr0
ID: 38422473
Question on the first solution - what would be the field names for the table?

<cfset FORM.yourSingleField = "123;456;789">
<cfloop from="1" to="#listLen(FORM.yourSingleField, ';')#" index="x">
      <cfset variables["ID"& x] = listGetAt(FORM.yourSingleField, x, ";")>
      <cfoutput>item #x# = #variables["ID"& x]#</cfoutput><br>
</cfloop>

I'm using the following code to update multilple records at once - how do I incorporate the above code with the existing?

<cfinput type="text" style="background-color:##FF9" name="Invoice_#currentRow#" Value="#INVOICE#" size="7"

<cfloop from="1" to="#form.numOfFields#" index="counter">

<cfset variables.Invoice = FORM["invoice_"& counter]>

set
 Invoice = <cfqueryparam value="#variables.invoice#" cfsqltype="cf_sql_varchar">
0
 
LVL 52

Expert Comment

by:_agx_
ID: 38422592
update multipile records at once
          ... name="Invoice_#currentRow#" Value="#INVOICE#"

Hm... not sure I understand your setup. The naming convention seems more like method #2.  

Are you saying *each* input field stores multiple invoice numbers ie

               <input name="Invoice_1" value="123;456;789">
                <input name="Invoice_2" value="555;111;999">

And you then want to do a db update with *each* of the 6 invoice numbers?

            UPDATE Table Set SomeColumn = 'xxxx' WHERE Invoice = 123
            UPDATE Table Set SomeColumn = 'yyyy' WHERE Invoice = 456
            UPDATE Table Set SomeColumn = 'zzzz' WHERE Invoice = 789
            ...
            UPDATE Table Set SomeColumn = 'zzzz' WHERE Invoice = 999
0
 

Author Comment

by:DJPr0
ID: 38433007
Here is an excel file to better explain what is needed - your right, that solution 2 may be better (having separate boxes for inputs which should cut down input error's).

Also in the excel file is the existing code for multiple record input with one button.

Sorry, I took long to respond.
CFMulInvoice.xlsx
0
 
LVL 5

Expert Comment

by:mrigsby
ID: 38442326
Assuming that you want to perform an update on the invoice numbers that where submitted, then you could do something like this.

If your form has four input fields, each with only one invoice id in it like this:

<input name="ID1" >
<input name="ID2" >
<input name="ID3" >
<input name="ID4" >

Then when you process the form submission you could use logic like this:

<cfloop index="HH" from="1" to="4">
	
	<!--- 	
		Check if there was a value submitted in the field. 
		If there was, then perform the update for that invoice ID. 
		Use trim() to help remove spaces 
	--->
	<cfif len(trim(FORM["ID"& HH]))>
		
	    <!--- set thisLoopsInvoiceID to the current loops iteration invoice ID --->
	    <cfset thisLoopsInvoiceID = trim(FORM["ID"& HH]) >
        
	    <!--- Update the invoice however you need to using the invoice id in the variable thisLoopsInvoiceID --->
            <cfquery>
            UPDATE YOUR_TABLE_NAME 
            Set OTHER_COLUMN = <cfqueryparam value="#form.SOME_OTHER_FORM_FIELD_VALUE#" cfsqltype="CF_SQL_VARCHAR"> 
            WHERE YOUR_INVOICE_ID_COLUMN = <cfqueryparam value="#thisLoopsInvoiceID#" cfsqltype="CF_SQL_VARCHAR">
            </cfquery>
        
	</cfif>
	
</cfloop>

Open in new window

0
 
LVL 52

Expert Comment

by:_agx_
ID: 38442345
If you always need to display 4 invoice fields for each row, give the invoice fields an extra suffix ie Invoice_#currentRow#_1, Invoice_#currentRow#_2, ....   Then add an extra loop on the action page to extract the values. See example below.

        > <cfinput type="text" name="Invoice_#currentRow#" Value="#INVOICE#"

EDIT BUT.. the actual code depends on how you're storing the INVOICE numbers in your db tables. Are you storing them separately ie one invoice # per row (good)? Or as a single list in one column (bad)?

(Just a demo. you'll need different code for your final form ...)

Form:
<!--- just for testing --->
<cfset results = queryNew("")>
<cfset queryAddColumn(results, "REC_DATE", listToArray("10/11/2012,10/12/2012"))>
<cfset queryAddColumn(results, "REC_QTY", listToArray("3,2"))>
<cfset queryAddColumn(results, "KEYID", listToArray("44,55"))>

<cfform method="post">
      <cfoutput query="results">
            <strong>Group #currentRow#</strong>:
            <cfinput type="Text" style="background-color:##FF9" name="REC_DATE_#currentRow#" title="Enter date if not today" value="#DATEFORMAT(REC_DATE, "m/d/yyyy")#" size="7">
          <cfinput type="Text" style="background-color:##FF9" name="Qty_#currentRow#" value="#REC_QTY#" size="7"></td>
               <cfinput name="keyid_#currentRow#" type="hidden" value="#KEYID#" size="15">
            <div><strong>Invoice</strong>
          <cfloop from="1" to="4" index="groupIndex">
                  <cfinput type="text" style="background-color:##FF9" name="Invoice_#currentRow#_#groupIndex#" size="7">
          </cfloop>
            </div><hr>
      </cfoutput>
      <!--- save number of dynamic fields --->
      <cfoutput><input type="hidden" name="numOfFields" value="#results.recordCount#"></cfoutput>
      <input type="submit" name="save">
</cfform>

Action Page:
<cfparam name="form.numOfFields" default="0">

<cfif structKeyExists(form, "save")>
   <cfloop from="1" to="#form.numOfFields#" index="mainCounter">
       <cfset variables.KEYID   = FORM["keyid_"& mainCounter]>
       <cfset variables.REC_QTY = FORM["qty_"& mainCounter]>
       <cfset variables.REC_DATE = FORM["REC_DATE_"& mainCounter]>
                     
       <!--- loop through ALL of the 4 invoice fields --->
            <cfloop from="1" to="4" index="invoiceCounter">
                  <cfset variables.INVOICE = FORM["INVOICE_"& mainCounter &"_"& invoiceCounter]>

                  <!--- if the other two fields are populated --->
                <cfset isEverythingValidated = false>
                  <cfif isNumeric(variables.REC_QTY) and len(variables.INVOICE)>
                    <!--- valid if a date was entered OR is empty --->
                    <cfset variables.isDateEmpty = len(trim(variables.REC_DATE)) eq 0>
                    <cfif isDate(variables.REC_DATE) OR variables.isDateEmpty>
                          <cfset isEverythingValidated = true>
                    </cfif>        
                    <!--- if it's empty, substitute date and time now --->
                    <cfif variables.isDateEmpty>
                           <cfset variables.REC_DATE = now()>
                    </cfif>
                  </cfif>      
            <!--- if the values are all good, go ahead an do the UPDATE --->
            <cfif isEverythingValidated>
                        do the update here ie
                        <cfoutput>
                        <pre>DEBUG:
                    UPDATE duein
                  SET    REC_DATE  = cfqueryparam value="#variables.rec_date#" cfsqltype="cf_sql_date">,
                         Invoice = cfqueryparam value="#variables.invoice#" cfsqltype="cf_sql_varchar">,
                         REC_QTY     = cfqueryparam value="#variables.REC_QTY#" cfsqltype="cf_sql_varchar">,
                         UPDATEBY =  cfqueryparam value="#GetAuthUser()#" cfsqltype="cf_sql_varchar">,
                         UPDATEDT =  cfqueryparam value="#now()#" cfsqltype="CF_SQL_TIMESTAMP">          
                  WHERE  keyid = cfqueryparam cfsqltype="cf_sql_integer" value="#variables.keyID#">
                        </pre>
                        </cfoutput>
                  </cfif>
            </cfloop>
      </cfloop>
</cfif>
0
 

Author Comment

by:DJPr0
ID: 38448829
I have incorporated _agx_'s code changes and see the extra 4 invoice fields, and seems to run without errors but only one invoice number is inputted into the database in the INVOICE field which is not displayed in any of the four Invoice fields performing a search after input.

I'm using separated fields for each invoice number in the database with the following labels: INVOICE_1, INVOICE_2, INVOICE_3 and INVOICE_4.

I.E. Each record has four Invoice number inputs.

I don't understand how this Invoice_#currentRow#_1 relates to the actual database field name.

Please help with the field names for the database.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 38449095
Yeah, like I mentioned the above is *not* fully working code. The "right" code depends on your db table structure (which we didn't know at the time).

     > I'm using separated fields for each invoice number

Conceptually that de-normalized structure is easier for inserting/updating. The disadvantage is some operations, like searching, become more complex / less efficient. For example if you wanted to find all records which referenced Invoice X, you'd have to search all columns. Plus if you ever wanted to change the number of invoices it requires alter the db table AND code.

          SELECT Columns FROM Table
          WHERE  Invoice_1 LIKE '%blah%'
          OR        Invoice_2 LIKE '%blah%'  
          OR        Invoice_3 LIKE '%blah%'  
          OR        Invoice_4 LIKE '%blah%'  

Personally I prefer a separate table for 1-N relationships. The advantage is you can have as many or as few invoices as needed.  The disadvantage is it would require changing code that only expects 1 row per invoice.  

Which approach do you want to use? Then we can address the code needed for the FORM and UPDATE.
0
 

Author Comment

by:DJPr0
ID: 38449260
I would like to stay with "I'm using separated fields for each invoice number" due to trying to keep it simple and the database shouldn't grow over 6K records.

Can I use the JavaScript option you posted above to display a default of 1 invoice box with + to display additional boxes?

Thanks for explaining and giving options!
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 2000 total points
ID: 38449563
> Can I use the JavaScript option you posted above to display a default
     > of 1 invoice box with + to display additional boxes?

If you switch to a regular <input> instead of <cfinput> yes. But let's get the original part working 1st.

    >  I would like to stay with "I'm using separated fields for each invoice number"

Then use the numbered invoice columns ie INVOICE_1, ... to populate the input fields. For the UPDATE, you'll need a loop to populate all 4 columns

<!--- just for testing --->
<cfset results = queryNew("")>
<cfset queryAddColumn(results, "REC_DATE", listToArray("10/11/2012,10/12/2012"))>
<cfset queryAddColumn(results, "REC_QTY", listToArray("3,2"))>
<cfset queryAddColumn(results, "KEYID", listToArray("44,55"))>
<cfset queryAddColumn(results, "INVOICE_1", listToArray("1A,1B"))>
<cfset queryAddColumn(results, "INVOICE_2", listToArray("2A,2B"))>
<cfset queryAddColumn(results, "INVOICE_3", listToArray("3A,3B"))>
<cfset queryAddColumn(results, "INVOICE_4", listToArray("4A,4B"))>

<cfform method="post">
      <cfoutput query="results">
            <strong>Group #currentRow#</strong>:
            <cfinput type="Text" style="background-color:##FF9" name="REC_DATE_#currentRow#" title="Enter date if not today" value="#DATEFORMAT(REC_DATE, "m/d/yyyy")#" size="7">
          <cfinput type="Text" style="background-color:##FF9" name="Qty_#currentRow#" value="#REC_QTY#" size="7"></td>
               <cfinput name="keyid_#currentRow#" type="hidden" value="#KEYID#" size="15">
            <div><strong>Invoice</strong>
          <cfloop from="1" to="4" index="groupIndex">
			 <!--- extract invoice number --->
			<cfset variables.Invoice = results["Invoice_"& groupIndex][currentRow]>
            <cfinput type="text" style="background-color:##FF9" name="Invoice_#currentRow#_#groupIndex#" 
						value="#variables.Invoice#" size="7">
          </cfloop>
            </div><hr>
      </cfoutput>
      <!--- save number of dynamic fields --->
      <cfoutput><input type="hidden" name="numOfFields" value="#results.recordCount#"></cfoutput>
      <input type="submit" name="save">
</cfform>		

<cfif structKeyExists(form, "save")>
   <cfset variables.MAX_INVOICES = 4>
   <cfloop from="1" to="#form.numOfFields#" index="mainCounter">
       <cfset variables.KEYID   = FORM["keyid_"& mainCounter]>
       <cfset variables.REC_QTY = FORM["qty_"& mainCounter]>
       <cfset variables.REC_DATE = FORM["REC_DATE_"& mainCounter]>
	  
      <!--- validate ALL 4 invoice values --->
	  <cfset variables.invoices = []>
      <cfloop from="1" to="#variables.MAX_INVOICES#" index="invoiceCounter">
      	  <cfset variables.INVOICE = FORM["INVOICE_"& mainCounter &"_"& invoiceCounter]>
		  <cfset variables.isEverythingValidated = false>
       	  <cfif isNumeric(variables.REC_QTY) and len(variables.INVOICE)>
			  <cfif len(trim(variables.REC_DATE)) eq 0>
                    <cfset variables.REC_DATE = now()>
			  </cfif>
			  <!--- if its valid, save it the array --->
			  <cfif isDate(variables.REC_DATE)>
				  <cfset arrayAppend(variables.invoices, variables.INVOICE)>
              </cfif>
		  </cfif>
	  </cfloop>   

      <!--- at least ONE value is good, go ahead and do the UPDATE --->
      <cfif arrayLen(variables.invoices)>
           <cfoutput>
           <pre>DEBUG:
           UPDATE duein
           SET    REC_DATE  = &lt;cfqueryparam value="#variables.rec_date#" cfsqltype="cf_sql_date">,
				  REC_QTY   = &lt;cfqueryparam value="#variables.REC_QTY#" cfsqltype="cf_sql_varchar">,
                  UPDATEBY  = &lt;cfqueryparam value="#GetAuthUser()#" cfsqltype="cf_sql_varchar">,
                  UPDATEDT  = &lt;cfqueryparam value="#now()#" cfsqltype="CF_SQL_TIMESTAMP">
				 <cfloop from="1"  to="#variables.MAX_INVOICES#" index="counter">
					 , INVOICE_#counter# = 
						<cfif counter lte arrayLen(variables.invoices)>
							&lt;cfqueryparam value="#variables.invoices[counter]#" cfsqltype="cf_sql_varchar">
						<cfelse>
							&lt;cfqueryparam null="true" cfsqltype="cf_sql_varchar">
						</cfif>
				 </cfloop>
           	WHERE  keyid = cfqueryparam cfsqltype="cf_sql_integer" value="#variables.keyID#">
            </pre>
			</cfoutput>
		</cfif>
	</cfloop>
</cfif>

Open in new window

0
 

Author Comment

by:DJPr0
ID: 38456924
The code works with the testing data:
<cfset queryAddColumn(results, "INVOICE_1", listToArray("1A,1B"))>

How can we make it work with the database, with the fields: Invoice_1, Invoice_2.etc

I tried commenting out the first 8 lines of code with no luck.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 38457025
As long as your query columns have the same names ie INVOICE_1, INVOICE_2, ... it should work out of the box.

What do you mean by "no luck"? If you're getting an error can you post it?
0
 

Author Comment

by:DJPr0
ID: 38461616
None of the input fields are working (no data is being inputted into the database) - could it be the validation area?

Turned on debugging - here is the data after inputting data for one record.
Scroll to the bottom to see invoice data.

Form Fields:
FIELDNAMES=REC_DATE_1,QTY_1,INVOICE_1_1,INVOICE_1_2,INVOICE_1_3,INVOICE_1_4,KEYID_1,REC_DATE_2,QTY_2,INVOICE_2_1,INVOICE_2_2,INVOICE_2_3,INVOICE_2_4,KEYID_2,REC_DATE_3,QTY_3,INVOICE_3_1,INVOICE_3_2,INVOICE_3_3,INVOICE_3_4,KEYID_3,REC_DATE_4,QTY_4,INVOICE_4_1,INVOICE_4_2,INVOICE_4_3,INVOICE_4_4,KEYID_4,REC_DATE_5,QTY_5,INVOICE_5_1,INVOICE_5_2,INVOICE_5_3,INVOICE_5_4,KEYID_5,REC_DATE_6,QTY_6,INVOICE_6_1,INVOICE_6_2,INVOICE_6_3,INVOICE_6_4,KEYID_6,REC_DATE_7,QTY_7,INVOICE_7_1,INVOICE_7_2,INVOICE_7_3,INVOICE_7_4,KEYID_7,REC_DATE_8,QTY_8,INVOICE_8_1,INVOICE_8_2,INVOICE_8_3,INVOICE_8_4,KEYID_8,REC_DATE_9,QTY_9,INVOICE_9_1,INVOICE_9_2,INVOICE_9_3,INVOICE_9_4,KEYID_9,REC_DATE_10,QTY_10,INVOICE_10_1,INVOICE_10_2,INVOICE_10_3,INVOICE_10_4,KEYID_10,REC_DATE_11,QTY_11,INVOICE_11_1,INVOICE_11_2,INVOICE_11_3,INVOICE_11_4,KEYID_11,REC_DATE_12,QTY_12,INVOICE_12_1,INVOICE_12_2,INVOICE_12_3,INVOICE_12_4,KEYID_12,REC_DATE_13,QTY_13,INVOICE_13_1,INVOICE_13_2,INVOICE_13_3,INVOICE_13_4,KEYID_13,REC_DATE_14,QTY_14,INVOICE_14_1,INVOICE_14_2,INVOICE_14_3,INVOICE_14_4,KEYID_14,REC_DATE_15,QTY_15,INVOICE_15_1,INVOICE_15_2,INVOICE_15_3,INVOICE_15_4,KEYID_15,REC_DATE_16,QTY_16,INVOICE_16_1,INVOICE_16_2,INVOICE_16_3,INVOICE_16_4,KEYID_16,REC_DATE_17,QTY_17,INVOICE_17_1,INVOICE_17_2,INVOICE_17_3,INVOICE_17_4,KEYID_17,REC_DATE_18,QTY_18,INVOICE_18_1,INVOICE_18_2,INVOICE_18_3,INVOICE_18_4,KEYID_18,REC_DATE_19,QTY_19,INVOICE_19_1,INVOICE_19_2,INVOICE_19_3,INVOICE_19_4,KEYID_19,REC_DATE_20,QTY_20,INVOICE_20_1,INVOICE_20_2,INVOICE_20_3,INVOICE_20_4,KEYID_20,REC_DATE_21,QTY_21,INVOICE_21_1,INVOICE_21_2,INVOICE_21_3,INVOICE_21_4,KEYID_21,SAVE,NUMOFFIELDS
INVOICE_10_1=
INVOICE_10_2=
INVOICE_10_3=
INVOICE_10_4=
INVOICE_11_1=
INVOICE_11_2=
INVOICE_11_3=
INVOICE_11_4=
INVOICE_12_1=
INVOICE_12_2=
INVOICE_12_3=
INVOICE_12_4=
INVOICE_13_1=
INVOICE_13_2=
INVOICE_13_3=
INVOICE_13_4=
INVOICE_14_1=
INVOICE_14_2=
INVOICE_14_3=
INVOICE_14_4=
INVOICE_15_1=
INVOICE_15_2=
INVOICE_15_3=
INVOICE_15_4=
INVOICE_16_1=
INVOICE_16_2=
INVOICE_16_3=
INVOICE_16_4=
INVOICE_17_1=
INVOICE_17_2=
INVOICE_17_3=
INVOICE_17_4=
INVOICE_18_1=
INVOICE_18_2=
INVOICE_18_3=
INVOICE_18_4=
INVOICE_19_1=
INVOICE_19_2=
INVOICE_19_3=
INVOICE_19_4=
INVOICE_1_1=21212
INVOICE_1_2=31313
INVOICE_1_3=45454
INVOICE_1_4=65656
0
 
LVL 52

Expert Comment

by:_agx_
ID: 38462359
> could it be the validation area?

There's no javascript validation in your code. The db isn't updated because the fields are all empty .. except those last 4 invoices. Are you sure you're populating the fields properly? If you run the test form it works fine. Notice how everything has a value? So it sounds like the form code isn't populating the values to begin with OR the query fields are empty.

Al
Form Fields:
FIELDNAMES=REC_DATE_1,QTY_1,KEYID_1,INVOICE_1_1,INVOICE_1_2,INVOICE_1_3,INVOICE_1_4,REC_DATE_2,QTY_2,KEYID_2,INVOICE_2_1,INVOICE_2_2,INVOICE_2_3,INVOICE_2_4,NUMOFFIELDS,SAVE
INVOICE_1_1=1A
INVOICE_1_2=2A
INVOICE_1_3=3A
INVOICE_1_4=4A
INVOICE_2_1=1B
INVOICE_2_2=2B
INVOICE_2_3=3B
INVOICE_2_4=4B
KEYID_1=44
KEYID_2=55
NUMOFFIELDS=2
QTY_1=3
QTY_2=2
REC_DATE_1=10/11/2012
REC_DATE_2=10/12/2012
SAVE=Submit Query

Open in new window

0
 

Author Comment

by:DJPr0
ID: 38464053
The invoice fields are supposed to be empty due to, I only inputted 4 invoices for one record. The reason for all the field names is the update all button updates all records being displayed at once.

I'm not sure what you mean by JavaScript validation code. I was thinking about the code below that validates that at least one invoice number, Quantity and today's date (is auto inputted  if no date has been input) before an input to the database is authorized. I.E. If just a quantity is inputted with out the invoice the update table will not occur.


<!--- validate ALL 4 invoice values --->
        <cfset variables.invoices = []>
      <cfloop from="1" to="#variables.MAX_INVOICES#" index="invoiceCounter">
              <cfset variables.INVOICE = FORM["INVOICE_"& mainCounter &"_"& invoiceCounter]>
              <cfset variables.isEverythingValidated = false>
               <cfif isNumeric(variables.REC_QTY) and len(variables.INVOICE)>
                    <cfif len(trim(variables.REC_DATE)) eq 0>
                    <cfset variables.REC_DATE = now()>
                    </cfif>
                    <!--- if its valid, save it the array --->
                    <cfif isDate(variables.REC_DATE)>
                          <cfset arrayAppend(variables.invoices, variables.INVOICE)>
              </cfif>
              </cfif>
        </cfloop>
0
 
LVL 52

Expert Comment

by:_agx_
ID: 38464365
Oh wait.. I thought your form dump included all of the fields (date, qty, etc...) and that those were empty. But I see now that's just the FIELDNAMES value, duh! Sorry for the confusion.

So are the fields populated with *valid* values? Because you're right. The CF validation code will skip the update if a required field is missing.  Since it works with the test form, either your fields have invalid values OR something's different in your actual code.

1) Dump the whole FORM scope and make sure the required fields are present
2) If they are, add some debugging to see if the validation is failing/skipping the update

I can't test this right now, but something like:

   <cfloop from="1" to="#form.numOfFields#" index="mainCounter">
       <cfset variables.KEYID   = FORM["keyid_"& mainCounter]>
       <cfset variables.REC_QTY = FORM["qty_"& mainCounter]>
       <cfset variables.REC_DATE = FORM["REC_DATE_"& mainCounter]>
	  
      <!--- validate ALL 4 invoice values --->
      <cfset variables.invoices = []>
      <cfloop from="1" to="#variables.MAX_INVOICES#" index="invoiceCounter">
      	  <cfset variables.INVOICE = FORM["INVOICE_"& mainCounter &"_"& invoiceCounter]>
	  <cfset variables.isEverythingValidated = false>
       	  <cfif isNumeric(variables.REC_QTY) and len(variables.INVOICE)>
	       <cfif len(trim(variables.REC_DATE)) eq 0>
                    <cfset variables.REC_DATE = now()>
	       </cfif>
	       <!--- if its valid, save it the array --->
	       <cfif isDate(variables.REC_DATE)>
		      <cfset arrayAppend(variables.invoices, variables.INVOICE)>
               <cfelse>
                    <cfoutput>
                        debug: SKIP date [#variables.REC_DATE#] is not a valid date
                    </cfoutput>
               </cfif>
          <cfelse>
              <cfoutput>
                 debug: SKIP qty [#variables.REC_QTY#] or invoice [#variables.INVOICE#] 
                        value is invalid or empty<br>
              </cfoutput>
          </cfif>
       </cfloop>   

      <!--- at least ONE value is good, go ahead and do the UPDATE --->
      <cfif arrayLen(variables.invoices)>
               .... code to do update
      <cfelse>
            debug: no valid invoices found to update
      </cfif>
   </cfloop>

Open in new window

0
 

Author Comment

by:DJPr0
ID: 38468216
I found the problem, the code had some weird characters in lieu of < in the update section (in bold):

SET    REC_DATE  = &lt;cfqueryparam value="#variables.rec_date#"cfsqltype="cf_sql_date">,
         REC_QTY   = &lt;cfqueryparam value="#variables.REC_QTY#"   cfsqltype="cf_sql_varchar">,
         UPDATEBY  = &lt;cfqueryparam value="#GetAuthUser()#" cfsqltype="cf_sql_varchar">,
         UPDATEDT  = &lt;cfqueryparam value="#now()#" cfsqltype="CF_SQL_TIMESTAMP">


Thanks for the help _agx_!
0
 
LVL 52

Expert Comment

by:_agx_
ID: 38468766
Oh, lol. That was just for debugging, to <cfoutput> the SQL and see what it looked like. I guess you missed the <pre> and DEBUG" makers. Sorry about the confusion :)
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Over time, the online landscape has altered considerably, but that’s nothing compared to the up-and-coming trends that will shape the web design industry in the coming year. Keep reading to find out which trends will shape B2B web design in 2018.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The is a quite short video tutorial. In this video, I'm going to show you how to create self-host WordPress blog with free hosting service.
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question