Blank entries in a list.

[paulferree]

Let's say I have a list:
John,Doe,11-01-08,,red,Jane,Doe,11-15-08,circle,blue

I'm breaking up this long list into 2 separate lists storing the individual lists in array values:
MyArray[1] = John,Doe,11-01-08,,red
MyArray[2] = Jane,Doe,11-15-08,circle,blue

Then I'm looping over the array pulling each individual list and I'm trying to insert the data into a DB. I simply loop over the list and put them in FORM elements so I can use CFINSERT (which may be my first problem.

My problem is when I'm using ListGetAt and I reference position number 4, if there is no data in that position it skips and pulls the next available position with data. So, ListGetAt(MyArray[1],4) pulls a value of red instead of Null or blank.

The desired effect is when there is an empty field in the list that <NULL> is entered in the DB instead of skipping the blank field and inserting the next available data.

As I mentioned before I probably should just avoid using the cfinsert, but it was just a quickest point from A and B. Any ideas?

Paul

[Kronin555]

If you're on CF8, you can convert the list to an array using ListToArray and tell it to include empty fields:
http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=functions_l_21.html#131626
<cfset listarray = ListToArray(myarray[1],",",true)>
Now you can get your 4th value as:
listarray[4]

If you're not on CF8, you can always do a string replacement on 2 commas in a row and replace it with a known value that you can then use as an empty value.

<cfset updatedlist = replace(mylist,",,",",EMPTY,","ALL")>
Now when you do your ListGetAt(updatedlist,4), you'll get "EMPTY" back.

There are some pains with this method. First, you have to do 2 replaces in a row, because if mylist has 2 empty values in a row ("hello,,,goodbye") then with the first replace, you end up with "hello,EMPTY,,goodbye". Second, all code from then on needs to know about the placeholder "EMPTY" and replace it with an empty string. So, if you're going to do this, you'll need to do:
<cfset updatedlist = replace(replace(mylist,",,",",EMPTY,","ALL"),",,",",EMPTY,","ALL")>
to get around the first issue I mentioned.

Don't use cfinsert, you won't get NULL values regardless of whether you are on CF8 and use the listtoarray method or not. If you want NULL values in cases where list values are empty, you need to handle that yourself.