null problem in formula

[ajdesigns]

I have a sales invoice report where I need to specify total no of boxes sent to a customer. These boxes (despatch units)are a different size ie, 1, 6PK ,36PK etc.hence the formulars below to get the boxes per sales item and total boxes.The trouble is if the despatch units is a null value I get an error which pops up the Get_no_of_boxes formula saying the string is non numeric how can this be avoided.

Split units -split({stockm.despatch_units},"PK")[1] Splits the field {stockm.despatch_units} from 36PK, 72PK etc, to 36, 72.

Get_no_of_boxes-ToNumber ({@split units}) changes the split field created by the formula “Split units” to a numeric value.

Boxes - {opdetm.order_qty} / {@Get_no_of_boxes}divides order qty by the numeric field created to get total boxes .

Roundupboxes_Local NumberVar RoundUp := {@boxes};
If Int(RoundUp)/2 = RoundUp/2 then RoundUp else Truncate(Roundup) + 1; This rounds up to next highest integer.

Then I grand total Roundupboxes to get total boxes

 

[ajdesigns]

Sorry I am using CR 8.5

 

[danausten]

Go to File, Options then Reporting Tab then check 'convert NULL values to default'. This will convert all NULL values to the fields default type i.e. if {stockm.despatch_units} is a string field it will convert it to "" if it is NULL

HTH.

Dan

 

[GJParker]

Dan

Thsi will still cause an error with the ToNumber() function as "" is non numeric

AJdesighns

you will need to test for NULL in the split Units formula i.e.

//@SplitUnits

If Isnull({stockm.despatch_units}} Then
1 //whatever you want here
Else
split({stockm.despatch_units},"PK")[1]

If you change null values to display 0 you will also need to amend the Boxes formula to stop divsiosn bt zero errors i.e.

//@Boxes

If {@Get_no_of_boxes} <> 0 Then
{opdetm.order_qty} / {@Get_no_of_boxes}
Else
0


HTH


Gary Parker
MIS Data Analyst
Manchester, England

 

[ajdesigns]

I tried this but it is asking for a number after the Else


//@SplitUnits

If Isnull({stockm.despatch_units}} Then
1 //whatever you want here
Else
//( Asking for number here)

split({stockm.despatch_units},"PK")[1]

 

[GJParker]

Covert the 1st value to a string i.e.

If Isnull({stockm.despatch_units}} Then
"1" //whatever you want here
Else
split({stockm.despatch_units},"PK")[1]


Gary Parker
MIS Data Analyst
Manchester, England

 

[ajdesigns]

I tried this but if the value is a string we get back to the error saying "the string is non numeric

 

[GJParker]

If this problem is occurring in the Get_No_of_Boxes formula, then this must mean that values from @SplitUnits are not all numbers,

We know that Null database values are converted to 1 so it can't be these, therefore the split({stockm.despatch_units},"PK")[1] part of the formula must be returning non numeric values.

You will need to check the data in stockm.despatch_units.

You could also use the IsNumeric() function to check the @SplitUnit value before converting to a number i.e.

If IsNumeric({@SplitUnits}) Then
ToNumber({@splitunits})
Else
1

HTH

Gary Parker
MIS Data Analyst
Manchester, England