CFIf Syntax and Logic

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

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?

when you populate the array to a check if the field empty or not . If empty write 0 otherwise whatever they entered.

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?

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

Some additional examples and the expected results would help.

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
PSCPProcessPostChangeOrder.txt

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

        <cfset theNewID = insertResult.GENERATEDKEY>

Select allOpen in new window

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#">
</cfif>

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

Select allOpen 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]>

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


<!---- 
	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">
		<cfoutput>
			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#">
		</cfoutput>
	</cfloop>
	<input type="submit">
</form>

Select allOpen 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.

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

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

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?
Capture.JPG

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

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

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

Select allOpen in new window

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 PSCPProcessPostChangeOrder...as I may have said in an earlier message...I inherited this application so I didn't come up with the names of the pages.
PostChangeOrder.txt
PSCPConfirmChangeOrders.txt
PSCPProcessPostChangeOrder.txt

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

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.
Error-message.JPG
Posting-Attempt.JPG

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 (....)

I believe that this is the insert statement that is causing the error:

Insert into Source_Of_Fund_Transaction
		(Project_ID,PSC_No,SOF_ID,Current_Allocation,
		Current_Contract,Current_Expenditure,Transaction_Type, Transaction_Descn,
		Created_By,Creation_Date,Modified_By,Modified_Date)
	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)

Select allOpen 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 )

AGX is absolutely awesome!!!!!  500 points isn't even close to the proper repayment for the help provided!!!!!!