Error with blanks and ListGetAt 1

[jmcg]

I have a form collection data and am using ListGetAt in an update query to save any changes.
If a user deletes an entry and then saves I get an error, "Invalid list index 7". If all entries are complete it works fine.
The fields are all Text boxes allowing zero lengths so don't think it's the database side.

Must every field be completed for ListGetAt to work, or is there something simple (Likely) I am missing.

	UPDATE inc_KPI
	SET 
	Last_Full_Year = '#ListGetAt(Last_Full_Year,x,",")#',
	pct_increase = '#ListGetAt(pct_increase,x,",")#', 
	Previous_Full_Year = '#ListGetAt(Previous_Full_Year,x,",")#',
	Comments = '#ListGetAt(Comments,x,",")#'
	WHERE ID = #ListGetAt(ID,x,",")#

 

[cfSearching]

Most of CF's list functions ignore empty elements. So the value "a,,b" is considered to have only two elements, not four. There are a few options. Some are version dependent.

1. For CF8 you could convert the list to an array and use ListToArray's "includeEmptyFields" option

2. Look into using the underlying java split() method. (I have not tested this recently)

http://coldfused.blogspot.com/2007/06/listtoarray-with-empty-elements.html

3. Use Replace() to replace all empty elements (ie ",,") with a space or some other character. CF will then recognize them. Do not forget to handle the first and last elements.

4. Convert your form to use unique sequentially named fields instead of using the same name for multiple fields

<input type="text" name="Last_Full_Year_#counter#">

Of course if the data contains commas all bets are off. Personally I prefer option #4 which avoids the problem altogether.

----------------------------------

http://cfsearching.blogspot.com/

 

[jmcg]

Thanks
1. Am using CF6 so that rules out number.
2. Looked through this but seemed to suggest that it doesn't work with a blank end element.
3. Tried the replace as it looked quite simple.

#Replace(ListGetAt(Last_Full_Year,x,","),",,",", ,","all")#

However it does not work, not sure where I have gone wrong
4. Tried this also however was confused when trying to do the update part.
What would be the code to loop through the dynamically create field names?

 

[cfSearching]

RE Option 2: I tried option 2 and it seems to work correctly. However, I am using CF8 not MX 6. So that may account for the difference:

<cfset arr = str.split(",", javacast("int", -1))>

RE Option 3: The Replace() should be done before your loop. You also need to replace the first and last elements if they are empty.

A simple hack is to add a space to the beginning and end of the value. Just remember to trim the list values inside your loop to remove the extra whitespace.

<cfset Last_Full_Year = ",c,">
<cfset Last_Full_Year = char(32) & Replace(Last_Full_Year, ",,", ", ,", "all") & char(32)>

RE Option 4:
Generally speaking you save the total number of fields in a hidden form field. Then use that total value to extract the form field values inside a loop.

Since the fields will be named sequentially:

form.Last_Full_Year_1, form.pct_increase_1, ...
form.Last_Full_Year_2, form.pct_increase_2, ...
form.Last_Full_Year_3, form.pct_increase_3, ...

You can use array notation to reference the form field names: #form["the_base_field_name"& counterNumber]#

<cfloop from="1" to="#form.totalFields#" index="counter">
        <!--- extract the values of current fields --->
	<cfset variables.Last_Full_Year = form["Last_Full_Year_"& counter]>
	<cfset variables.pct_increase = form["pct_increase_"& counter]>
	
        ... Then use the extracted values in your update query 
</cfloop>

----------------------------------

http://cfsearching.blogspot.com/

 

[cfSearching]

4. Tried this also however was confused when trying to do the update part.

Yes. The first time I tried it threw me for a loop too! But once I understood how it worked, I realized it is simpler than I thought.

Here is an example that might help. It generates a form with multiple sets of form fields. When the form is submitted the form field values are extracted and displayed on screen. I think once you see how it works it should be easy to add your update query inside the loop.

HTH

<!--- create a test query --->
<cfset yourQuery = QueryNew("")>
<cfset queryAddColumn(yourQuery, "ID", listToArray("1,2,3"))>
<cfset queryAddColumn(yourQuery, "Last_Full_Year", listToArray("2007, , "))>
<cfset queryAddColumn(yourQuery, "pct_increase", listToArray("38,18, "))>
<cfset queryAddColumn(yourQuery, "Previous_Full_Year", listToArray(" ,2008, "))>

<cfoutput>
<form method="post" action="#CGI.SCRIPT_NAME#">
	<!--- generate a set of form fields for each record in the query --->
	<cfloop query="yourQuery">
	   ID <input type="text" name="ID_#currentRow#" value="#ID#">	
	   Last_Full_Year <input type="text" name="Last_Full_Year_#currentRow#" value="#Last_Full_Year#">	
	   pct_increase <input type="text" name="pct_increase_#currentRow#" value="#pct_increase#">	
	   Previous_Full_Year <input type="text" name="Previous_Full_Year_#currentRow#" value="#Previous_Full_Year#"><hr>	
	</cfloop>
	
	<!--- save total number of fields --->
	<input type="hidden" name="totalFields" value="#yourQuery.recordCount#">
	<input type="submit" name="testThis">
</form>
<br>

<cfif structKeyExists(FORM, "testThis")>
 	<b>Debug - Show form field values:</b><hr>
	<cfloop from="1" to="#form.totalFields#" index="counter">
		<!--- extract the values for the current set of fields --->
		<cfset variables.id 				= form["ID_"& counter]>
		<cfset variables.Last_Full_Year 	= form["Last_Full_Year_"& counter]>
		<cfset variables.pct_increase 		= form["pct_increase_"& counter]>
		<cfset variables.Previous_Full_Year = form["Previous_Full_Year_"& counter]>
		
		<!--- display the values for the current set of fields --->
		form.ID_#counter# = #variables.id#<br>
		form.Last_Full_Year_#counter# = #variables.Last_Full_Year#<br>
		form.pct_increase_#counter# = #variables.pct_increase#<br>
		form.Previous_Full_Year_#counter# = #variables.Previous_Full_Year#<hr>
	</cfloop>
</cfif>
</cfoutput>

----------------------------------

http://cfsearching.blogspot.com/

 

[jmcg]

cfSearching
Thanks you for you full patient explanation.

Went with the loop over the form fields and it worked perfectly.
Have a star

 

[cfSearching]

Thanks. Glad I could help!

So the value "a,,b" is considered to have only two elements, not four

A correction for the archives:
the value "a,,,b" is considered to have only two elements, not four

;-)

----------------------------------

http://cfsearching.blogspot.com/