ColdFusion One Input Box for Multiple Field Inputs

>  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>

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.

> 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/

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">

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

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

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>

Select allOpen in new window

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>

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.

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.

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!

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.

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?

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

> 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

Select allOpen in new window

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>

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>

Select allOpen in new window

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_!

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 :)