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.
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.
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
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?
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
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
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
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").
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.AmountLis
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.AmountLis
C) When you loop through the amount array, verify the value IsNumeric as Erik described above
<cfloop ....>
<cfif isNumeric( currentAmount)> save to database </cfif>
</cfloop>
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
</cfif>
</cfloop>
OR use the year number to create a link with each amount, so you end up with:
form.yearList = 2014,2015,2016,2017
form.amount2014
form.amount2015
form.amount2016
form.amount2017
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
</cfif>
</cfloop>
>> 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.AmountLis
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.AmountLis
C) When you loop through the amount array, verify the value IsNumeric as Erik described above
<cfloop ....>
<cfif isNumeric( currentAmount)> save to database </cfif>
</cfloop>
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
</cfif>
</cfloop>
OR use the year number to create a link with each amount, so you end up with:
form.yearList = 2014,2015,2016,2017
form.amount2014
form.amount2015
form.amount2016
form.amount2017
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
</cfif>
</cfloop>
>> 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>
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.
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.
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:
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?
Well, you can always change the behavior. Just ignore invalid/non-numeric values when calculating the total.
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>
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>
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
<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
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?
If use that on each of the lists, it will create three arrays with the same number of elements.
<cfset arr1 = listToArray("2014,2015,201
<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 ]#
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,201
<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
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>
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
PostChangeOrder.txt
PSCPConfirmChangeOrders.txt
PSCPProcessPostChangeOrder.txt
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
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
@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
<cfset EntryLen = ListLen(form.AmountList)>
<cfset AmountUseArr = ListtoArray(form.AmountLis
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.AmountLis
<cfset EntryLen = arrayLen(AmountUseArr)>
<cfset EntryLen = ListLen(form.AmountList)>
<cfset AmountUseArr = ListtoArray(form.AmountLis
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.AmountLis
<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
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:
based on this loop:
<cfloop from="1" to="#EntryLen#" index="i">
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)
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#,'#cli
values ( 123, 'Some Value', ...)
... instead of this:
values (#client.Project_ID#,'#cli
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 )
VALUES ( 123, 'aaa', 456, , 0 ) <=== value expected between two commas ",,"
... instead of this (valid):
VALUES ( 123, 'aaa', 456, 0, 0 )
Premium Content
You need an Expert Office subscription to comment.Start Free Trial