Avatar of mounty95
mounty95
Flag for United States of America asked on

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.
ColdFusion Language

Avatar of undefined
Last Comment
mounty95

8/22/2022 - Mon
erikTsomik

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
mounty95

ASKER
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?
erikTsomik

when you populate the array to a check if the field empty or not . If empty write 0 otherwise whatever they entered.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
mounty95

ASKER
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?
erikTsomik

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
_agx_

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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
mounty95

ASKER
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
ASKER CERTIFIED SOLUTION
_agx_

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
_agx_

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

Open in new window

mounty95

ASKER
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.
Your help has saved me hundreds of hours of internet surfing.
fblack61
_agx_

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>

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

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

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.
Coast Line

if i did not missed your question and comments, did u tried using includeemptyvalues in a list to true
_agx_

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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Coast Line

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
mounty95

ASKER
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
_agx_

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 ]#
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Coast Line

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>

Open in new window

mounty95

ASKER
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
Coast Line

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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
_agx_

I think there's a page missing, the modal window : ChangeOrderList.cfm
_agx_

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

ASKER
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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
_agx_

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

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

Open in new window


based on this loop:

<cfloop from="1" to="#EntryLen#" index="i">
_agx_

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]#',...)
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
_agx_

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

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