CFIf Syntax and Logic

I have a webpage that inserts array values into a database based on the criteria that the value in one of the fields is not equal to zero.  So I have:
YearArray:  2014, 2015, 2016
AmountArray:  100,0,0

The insert will only insert a single record with the 2014 and the 100 into the table.

Now the process has changed and the user wants to be able to insert a record where there is a zero in one of the array positions something like:
YearArray:  2014, 2015, 2016
AmountArray:  0,,

So I am thinking that I would want to test for not equal to "", but I am having difficulty.  Do I need a separate if evaluation or do I want to have a compound evaluation of if not equal to zero OR not equal to ""?  I am not sure of the placement whether I should evaluate for the "" before the 0 or after the 0 evaluation.

Also if the user has this array, how do I ensure that the right position of the array is inserted into the database if the data was:

YearArray:  2014, 2015, 2016
AmountArray:  ,0,

Thank you for any help that you can provide.  I can provide the actual page code if it helps, there is just a lot more to the page that complicates it that I didn't think would help in answering this question.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

erikTsomikSystem Architect, CF programmer Commented:
where are you getting the amount from? From the database or its a user entry?

You can always  do the check for the user entry if isNUmeric() then put wharever value the client provided otherwise insert 0 into your array,

If from SQL just do isNULL(amount,0) as amount that will make sure no empty field is inserted, and the array will insert blanks so your indexing will be correct

Hope that explains
mounty95Author Commented:
Not sure that I follow...the arrays are user entry from a previous page.

So there is a loop that says to loop thru the length of the array, but insert into the table only when the AmountArray value is not equal to zero.

Where would you suggest putting the isNumeric function?  Part of the CFIf?
erikTsomikSystem Architect, CF programmer Commented:
when you populate the array to a check if the field empty or not . If empty write 0 otherwise whatever they entered.
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

mounty95Author Commented:
But my problem is that I need to be able to insert when there is only a single 0 in one of the array spots and all the other array spots are empty.  The logic for the insert scripts run right now when the amount is not equal to 0, but now i have to be able to run the insert scripts when there is a 0.  Does that make sense?
erikTsomikSystem Architect, CF programmer Commented:
do you have a cfif in the code if array elelmnt is greater than 0 then remove that.

Also can you post your code so I can go through the logic
when there is only a single 0 in one of the array spots and all the other array spots are empty

Oh, that is different than the impression I had from the initial question.  As Erik mentioned, can you post some code?  

Also could you explain the rules a bit more?  For example:

1. Do the arrays always have 3 elements?
2. What should happen if one of the elements are 0, but the others are not? ie 0,50,90  OR 0,,75

Some additional examples and the expected results would help.
mounty95Author Commented:
OK I am going to clean up the code some so you don't have to wade thru all of the other stuff as it won't necessarily make sense to you and will post it.

1.  The array can have any number of elements from 1 to probably about 10
2.  So I am going to instruct the user that if they want to post a 0 in the array they will have to remove the  other 0s to then be able to evaluate on that 0 and not "".

So to try and simplify the explanation of this:
The user is presented with a table of multiple years or maybe only one year.  There is a dollar figure field that the user then enters dollar figures into.  The table populates the dollar figure with 0 initially and then the user can enter a dollar figure where they need to.  The page that posts the transactions to SQL evaluates the amount array and only posts one transaction where there is a dollar figure.  If it is 0 then it does not post.  Now the user wants to be able to post a 0.  So to leave the evaluation of 0 alone, my thought is to tell the user to remove the other 0s and then add logic to evaluate for "" and not 0 but not sure where that goes.

Does this make sense?  How about I post the whole page as is and then if it is too complicated I will strip out all the other stuff that isn't pertinent to the question
Okay, that makes sense. The form allows the user to assign a dollar amounts for one or more years. (BTW, are negative amounts allowed? I'm assuming "no").

>> The table populates the dollar figure with 0 initially
>> my thought is to tell the user to remove the other 0s

What about skipping the step that populates it with 0? That way if there's something in the field, you'll know you should record it.  Otherwise, ignore it. Though the code converting the amounts to an array might be what's causing the problem, ie  

>>  <cfset AmountUseArr = ListtoArray(form.AmountList)>

By default, list functions ignore empty elements. CF9+ added the ability to preserve them. I think you could solve that problem by doing the following:

A)  Leave the initial field blank, instead of populating them with 0
B). Use listToArray(list, delim, includeEmptyFields=true):

         <cfset AmountUseArr = ListtoArray(form.AmountList, ",", true)>

C)  When you loop through the amount array, verify the value IsNumeric as Erik described above

      <cfloop ....>
        <cfif isNumeric( currentAmount)> save to database </cfif>

Just keep in mind, if the user enters a comma in any of the fields - all bets are off. That's why I prefer to use unique field names instead. For example, grouping related fields by adding a sequential suffix,    ie yearNumber1,amount1  | yearNumber2,amount2 | etc.... yearNumberN,amountN

          <cfloop list="#form.numOfYears#" index="x">
                       <cfset amount = FORM["yearNumber"& x]>
                       <cfset amount = FORM["amount"& x]>
                       <cfif IsNumeric(amount)>
                              do something here

OR use the year number to create a link with each amount, so you end up with:

               form.yearList = 2014,2015,2016,2017

Then you just loop through the list of years and extract each amount:

          <cfloop list="#form.yearList#" index="currYear">
                       <cfset amount = FORM["amount"& currYear]>
                       <cfif IsNumeric(amount)>
                              do something here

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
>> select max(Transaction_ID)as TransactionID

Totally unrelated to the original issue, but is Transaction_ID some sort of auto incrementing column? If so, use cfquery's "result" structure to get the ID of the inserted record instead. It's safer than select max(...) which is not thread safe.  For CF9+

        <cfquery result="insertResult" ....>
               INSERT INTO Source_Of_Fund_Transaction ....

        <cfset theNewID = insertResult.GENERATEDKEY>

Open in new window

mounty95Author Commented:
Thanks for the information.  Will have to sit and digest this a bit.  The select max() query is getting the value for the transaction that was posted to then use in the second insert statement so that there is a relationship between the transaction in the one table and the transaction in the other table.

A couple of things:
1.  The user will not enter negative numbers
2.  I think the user would rather not have to enter a comma when entering the number
3.  The form that the user is inserting the dollar figures into puts 0s in the text boxes I think so that it can call a javascript that adds the figures up as the user keys the entry so...

Again thank you for the answers and I will see what I can do with this the beginning of the week.
Yep, I understood the purpose of select max(). I just meant that CF8+ provides a safer way of doing the same thing, ie grabbing the new ID generated by the previous INSERT.  It is supported in most major db's, ie SQL Server / type identity, MySQL / type auto_increment, etc.

2. I think the user would rather not have to enter a comma when entering the number
<cfset AmountUseArr = ListtoArray(form.AmountList)>

Yes, I wasn't suggesting they enter commas manually :) Without seeing the form code, I didn't know how it was structured, but the use of ListtoArray() suggested the commas were already there.  However, I think I see the problem now ...

If you're trying to allow users to leave an "amount" field blank (instead of entering 0), it won't work with your current form.  Here's why.  If I'm understanding correctly, the "amount" fields all have the same name, so CF submits the values as a single, CSV list.  The problem is that approach doesn't work when 1 or more of the fields are left empty.  CF omits any empty values from the CSV list.  So you'll end up with a different number of elements in the year and amount arrays - and the INSERT code will break. (A similar problem occurs if the user happens to enter the delimiter, ie "," in the field value like $100,000). Run this example and you'll see what I mean:

<cfif structKeyExists(FORM, "FYList")>
	<cfset FundingYearArr = ListToArray(form.FYList)>
	<cfset AmountUseArr = ListtoArray(form.AmountList)>
	DEBUG: Notice the total elements do NOT match
	<cfdump var="#FundingYearArr#">
	<cfdump var="#AmountUseArr#">

<form method="post">
Year 1 <input type="text" name="FYList" value="2015">
Amount <input type="text" name="amountList" value="0"><br>
Year 2 <input type="text" name="FYList" value="2016">
Amount 2  <input type="text" name="amountList" value="100"><br>
Year 3 <input type="text" name="FYList" value="2017">
Amount <input type="text" name="amountList" value=""><br>
Year 4 <input type="text" name="FYList" value="2018">
Amount 4 <input type="text" name="amountList" value="95"><br>
<input type="submit">

Open in new window

The only way to avoid the empty field/delimiter problem is to generate unique field names like I mentioned above.  Then list handling isn't an issue. For example, use the year number as a suffix. Run this example.  Notice it works even when the amounts are left empty?

<!---- Simulate Action Page ---->
<cfif structKeyExists(FORM, "yearList")>
	<!--- Extract amounts for each year --->
	<cfloop list="#FORM.yearList#" index="yearNum">
		<cfset amount = FORM["amount"& yearNum]>

		DEBUG: Now processing Year #yearNum# / Amount = #amount#<br>
		<cfif isNumeric(Amount)>
			<!--- do something here --->

	Simulate Dynamic Form 
	Use year number as a suffix to relate "Amount" fields. 
	(Same concept would work if adding fields via javascript)
<cfset yearsToDisplay = [2015,2016,2017,2018]>

<form method="post">
	<cfloop array="#yearsToDisplay#" index="yearNum">
			Year #yearNum#
			<!--- Use year as a suffix to relate "Amount" fields. --->	
			Amount <input type="text" name="amount#yearNum#" value=""><br>
			<!--- Use same name for "year" fields, so values are submitted as CSV list --->
			<input type="hidden" name="yearList" value="#yearNum#">
	<input type="submit">

Open in new window

3. The form that the user is inserting the dollar figures into puts 0s in the text boxes I think so that it can call a javascript that adds the figures up as the user keys the entry so...

Well, you can always change the behavior. Just ignore invalid/non-numeric values when calculating the total.
Gurpreet Singh RandhawaCEOCommented:
if i did not missed your question and comments, did u tried using includeemptyvalues in a list to true
Unfortunately that won't work. The list won't have empty elements because CF drops empty fields when processing the same name form fields, ie

      <input name="AmountList" value="0">
      <input name="AmountList" value="">
      <input name="AmountList" value="">
      <input name="AmountList" value="">

will produce:   form.AmountList = "0"   <=== no empty elements

... and NOT:     form.AmountList = "0,,,"  <=== has empty element
Gurpreet Singh RandhawaCEOCommented:
i had this situation before and i used quantity as same field, and it did added comma's i was using ColdFusion 11, not sure what changed in 11 but i have seen this being worked, so just pointed out
mounty95Author Commented:
I get these three arrays based on the page that I have posted previously.  There is no way to just get the first value of each of the three arrays?
I'm a little confused about which suggestions you've tried so far. Backing up a step did you ever try my earlier suggestion?

By default, list functions ignore empty elements. CF9+ added the ability to preserve them.
B). Use listToArray(list, delim, includeEmptyFields=true):

If use that on each of the lists, it will create three arrays with the same number of elements.

         <cfset arr1 = listToArray("2014,2015,2016", ",", true)>
         <cfset arr2 = listToArray("1,1,1", ",", true)>
         <cfset arr3 = listToArray("0,,", ",", true)>

Just use arrayName[ position ] to grab whichever array position you want.

          #arr1 [ 1 ]#               <=== first element in each of the arrays
          #arr2 [ 1 ]#
          #arr3 [ 1 ]#
Gurpreet Singh RandhawaCEOCommented:
did u tried ListEach with using IncludeEmptyList set to as true
i think you want to match the values of one top list to relevant second list

if that is the case: try this

	result = [];
	listEach(form.getselection, function(value, index) {
		switch (value) {
			case "-2":
				result.append(listLast(ListGetAt(form.get_items, index), "~"));
			case "-1":
				result.append(ListGetAt(getselectiontext, index,',',true));

Open in new window

mounty95Author Commented:
OK so I have been fooling with these pages for a week now and I have tried the various things that have been offered and have broken the code and not known what exactly I needed to do to fix.  So I thought I would post all three pages that make up this process and see if I could get some clearer directions into what I should change.  The pages in order that they function are PostChangeOrder, PSCPConfirmChangeOrder, and I may have said in an earlier message...I inherited this application so I didn't come up with the names of the pages.
Gurpreet Singh RandhawaCEOCommented:
is this an intranet or internet application, this way it is not possible to check what is the actual proble, what is the error you are facing,

That is what n what needs a fix and if not facing an error what is the outcome now and what is the outcome expected
I think there's a page missing, the modal window : ChangeOrderList.cfm
@mounty95 - Can you tell us specifically what the current code is doing wrong/incorrectly? That's a lot of code, but looking at the PSCPProcessPostChangeOrder.txt page you definitely need to fix the calculations here:

       <cfset EntryLen = ListLen(form.AmountList)>
       <cfset AmountUseArr = ListtoArray(form.AmountList)>

As mentioned earlier, that code ignores empty elements in lists like "0,," and will mess up your cfloop code. Instead try changing it to this:

    <cfset AmountUseArr = ListtoArray(form.AmountList, ",", true)>
    <cfset EntryLen = arrayLen(AmountUseArr)>
mounty95Author Commented:
When I make the change to the two lines of code and try to post a transaction with 0 for one of the three choices for years, I get the error screen shot in the attached JPG.  I also included a snapshot of the screenshot that displayed the various arrays before they post on the final page.
Hard to tell without the full error message, but at a guess, one of the queries is trying to insert a blank where a number is expected.  What's the generated SQL statement from error message ? ie INSERT INTO Table (Columns) VALUES (....)
mounty95Author Commented:
I believe that this is the insert statement that is causing the error:
Insert into Source_Of_Fund_Transaction
		Current_Contract,Current_Expenditure,Transaction_Type, Transaction_Descn,
	values (#client.Project_ID#,'#client.PSC_No##FundingYearArr[i]#',#SOFIDArr[i]#,
		0,#AmountUse#,0,'C/O Posting','#CONumber#','#client.UserID#',current_timeStamp,'#client.UserID#',current_TimeStamp)

Open in new window

based on this loop:

<cfloop from="1" to="#EntryLen#" index="i">
No, I meant the final sql that gets sent to the db (with the actual values instead of CF variables). It's usually included in the error message. ie It should look like this:

          values ( 123, 'Some Value', ...)

... instead of this:

             values (#client.Project_ID#,'#client.PSC_No##FundingYearArr[x]#',...)
Without seeing the SQL, it sounds like one of the numeric variables is empty. If that's the problem query, I'd guess it's one of these variables:  #client.Project_ID#, #SOFIDArr[x]# or #AmountUse#. If any of them are empty, it could create invalid sql. For example:

             VALUES ( 123, 'aaa', 456, , 0 )              <=== value expected between two commas ",,"

... instead of this (valid):

         VALUES ( 123, 'aaa', 456, 0, 0 )
mounty95Author Commented:
AGX is absolutely awesome!!!!!  500 points isn't even close to the proper repayment for the help provided!!!!!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.