Need help with sccript with blank value

[richardurban]

This script runs correctly when there is a value in coupon_cod:

discount=if(coupon_cod=coupcode1,if(disctype1="percent",cart_total*coupdisc1,coupdisc1),if(coupon_cod=coupcode2,if(disctype2="percent",cart_total*coupdisc2,coupdisc2),if(coupon_cod=coupcode3,if(disctype3="percent",cart_total*coupdisc3,coupdisc3),if(coupon_cod=coupcode4,if(disctype4="percent",cart_total*coupdisc4,coupdisc4),if(coupon_cod=coupcode5,if(disctype5="percent",cart_total*coupdisc5,coupdisc5),if(coupon_cod=coupcode6,if(disctype6="percent",cart_total*coupdisc6,coupdisc6),if(coupon_cod=coupcode7,if(disctype7="percent",cart_total*coupdisc7,coupdisc7),0)))))))

However, if coupon_cod is blank, I receive the error:
"Script Error
Error:Script:" /CheckoutUX2.a5w" line:302
discount=if(coupon_cod=coupcode1,if(disctype1="percent",cart_total*coupdisc1,cou
Argument is incorrect data type "

The script ran okay before when I was using DBF tables. Now that I am using SQL tables, there is a problem. Could it be because the blank field creates a NULL value that xbasic does not like. If so, how do I fix the problem?

[Davidk]

Re: Need help with sccript with blank value

Richard,

Where do you have this script running?

[richardurban]

Re: Need help with sccript with blank value

It's running on an .a5w page.

[masteralone]

Re: Need help with sccript with blank value

Having used Alpha since A4V2, I have found that this error is either caused by:

a) table x, field y is numeric and table y, field y, is character.

or, more likely

in one of your other portions of the script, abc var is defined as C instead of N

However, I am still trying to download the trial version so I can decide whether or not to upgrade, so this is kind of a swag. :)

[peteconway]

Re: Need help with sccript with blank value

YOu need to use this function. http://wiki.alphasoftware.com/CONVERT_TYPE+Function

[masteralone]

Re: Need help with sccript with blank value

After think about this some more, I think the problem is the 'null' value return. You will have to determine whether it is returned as Character ("") or Numeric (0). Once that is determined, you could have a simple if statement to see if there is a discount, if so, run the rest of the script as is. If not, you could use goto() and skip over this portion of the script.

I would also think about considering using Case() as an option. Case allows you to be specific but is faster. It simply checks through Case statements til it finds one that meets the criteria set, then ends.

Ex:
Case
Case(1) coupon_cod=coupcode1.AND.disctype1="percent"
put calculation here
Case(2) coupon_cod=coupcode2.AND.disctype2="percent"
put calculation here
Case End

This could avoid the null return entirely. Been a while since I used Case/Case End (or End Case) but it processes faster instead of evaluating numerous ifs, it just goes down the list til it meets a specified criteria, executes the code for that criteria and then ends. If it doesn't find anything that meets any of the criteria, it ends. Quick and simple and it will work fine if every other item listed has a percent discount (assuming that if there is a discount for a specified amount disctype would not be percent.)

It will mean a little revamping of your calculations for cart total, but that shouldn't be very much.

Hope this helps

[masteralone]

Re: Need help with sccript with blank value

Just looked it up, it is Select/End Select with Case statements.

From an older xbasic manual: A conditional control structure comprised of one or more CASE statement. The Logical Expression of each CASE statement is evaluated until a TRUE result is reached. The statements for TRUE CASE are executed, and program flow continues after the END SELECT. The statements listed after the CASE ELSE are executed as the default action if no other CASE expresision is TRUE.

SELECT
[CASE Logical Expression1 [Statements1]]
Several more Case lines then
[CASE ELSE] [Statements]
END SELECT

Example:

'Associate a grade with a score. Any score below 60 is an F.
FUNCTION test_grade AS C(score as N)
SELECT
CASE score >= 90
grade = "A"
CASE score >= 80
grade = "B"
CASE score >= 70
grade = "C"
CASE score >= 60
grade = "D"
CASE ELSE
grade = "F"
END SELECT
test_grade = grade
END FUNCTION

Took me a while to figure this particular structure out but it suddenly hit me, if you made your first CASE statement using 60 instead of 90, everyone with a score of 60 or greater would get a D even if they scored 100. So, you have to be a little careful if you are using greater than or equal to as part of your CASE.

The benefit of this is you don't have to make sure you have all the correct number of closing ')' for if() statements and would avoid the null return problem.

[richardurban]

Re: Need help with sccript with blank value

Thanks so much Pete & Oran. Actually, I see that the value when there is no coupon_cod is ""(blank), not "Null" when I look in the SQL db. I thought about the logic on the .a5w page for checkout, and I just decided to add a random value that would not be used as a coupon_cod to avoid the blank value being used in the script

sqlCommand = "select coupon_cod from ord_head_web where Uuid = :Uuid"
flag = cn.execute(sqlCommand,args)
dim rs as sql::ResultSet
rs = cn.ResultSet
flag = rs.NextRow()
if flag= .t.
sqlCommand = "select coupon_cod from ord_head_web where Uuid = :Uuid"
flag = cn.execute(sqlCommand,args)
dim rs7 as sql::ResultSet
rs7 = cn.ResultSet
coupon_cod=rs7.data(1)
end if
coupon_cod="a"

I test to see if there is a value for coupon_cod, because if there is not, then that causes an error, as there will be not data for the result set to find.

[richardurban]

Re: Need help with sccript with blank value

Update. Actually, when the dialog first opens, the value of coupon_cod is "null", and that does cause an error in the calculation of "discount". Also, if you check for a null value, and the value is blank, instead, that causes an error, too. Rather than using the val function in each instance of coupon_cod,, I test for a null value and a blank value and then skip the discount calculation, setting it to 0 (zero):

dim rscoup as sql::ResultSet
rscoup = cn.ResultSet
flag = rscoup.NextRow()
if rscoup.dataisnull("Coupon_cod")=.t. then
	discount=0
else if rscoup.data("Coupon_cod")="" then
discount=0
else 
discount=if(coupon_cod=coupcode1,if(disctype1="percent",cart_total*coupdisc1,coupdisc1),if(coupon_cod=coupcode2,if(disctype2="percent",cart_total*coupdisc2,coupdisc2),if(coupon_cod=coupcode3,if(disctype3="percent",cart_total*coupdisc3,coupdisc3),if(coupon_cod=coupcode4,if(disctype4="percent",cart_total*coupdisc4,coupdisc4),if(coupon_cod=coupcode5,if(disctype5="percent",cart_total*coupdisc5,coupdisc5),if(coupon_cod=coupcode6,if(disctype6="percent",cart_total*coupdisc6,coupdisc6),if(coupon_cod=coupcode7,if(disctype7="percent",cart_total*coupdisc7,coupdisc7),0)))))))
end if

If you specify that coupon_cod is not null, like this:

select coupon_cod from ord_head_web where Uuid = :Uuid and coupon_cod is not null

that does not work because that now returns no result set at all, which causes an error. Rather, return a result set with the null value, and then use the .DataIsNull() method to test for that value.