Non-numeric string error for formula

[blueboyz]

I am using Sage 50 (formerly Peachtree) Quantum 2013, Crystal Reports 2008 and a pervasive database.

I created a report to convert the number of units sold to ounces.
One field in the inventory item record stores the number of ounces and another field stores the number of units in a case.

My formula to convert the units sold to ounces is:
(If isnull({LineItem.CustomField1}) then 0
else If {LineItem.CustomField1} = "can" then 1 * ToNumber({LineItem.CustomField2})
else [highlight #FCE94F]ToNumber({LineItem.CustomField1})[/highlight] * ToNumber({LineItem.CustomField2}))

When the report is processing, an error occurs: The string is non-numeric
and highlights ToNumber({LineItem.CustomField1}) in the 3rd line of the formula shown above.[highlight #FCE94F][/highlight]

Some Custom Field #1's do not have a value in them, others have "can" and others have a numeric number.

Does anyone know how I can correct the formula so the error message doesn't appear?

 

[CoSpringsGuy]

If isnull({LineItem.CustomField1}) then 0
else If isnumeric({Dbtr_Status.Disposition}) then ToNumber({LineItem.CustomField1}) * ToNumber({LineItem.CustomField2})
else 1 * ToNumber({LineItem.CustomField2})

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[CoSpringsGuy]

whups

If isnull({LineItem.CustomField1}) then 0
else If isnumeric({LineItem.CustomField1}) then ToNumber({LineItem.CustomField1}) * ToNumber({LineItem.CustomField2})
else 1 * ToNumber({LineItem.CustomField2})

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[blueboyz]

I tried the formula that CoSpringsGuy suggested:

If isnull({LineItem.CustomField1}) or isnull({LineItem.CustomField2}) then 0
else If isnumeric({LineItem.CustomField1}) then ToNumber({LineItem.CustomField1}) * ToNumber({LineItem.CustomField2})
else 1 * [highlight #FCE94F]ToNumber({LineItem.CustomField2})[/highlight]

but now the error, "the string is non-numeric" is on the other field in the formula, {LineItem.CustomField2}.

Is there a way to address both fields in the formula so the error, "the string is non-numeric" doesn't appear?

 

[pmax9999]

Just to explain why CoSpringsGuy's solution should work, {LineItem.CustomField1} obviously contains something other than the string "can" and numbers.

If I was you, I would be investigating the contents of that field thoroughly to ensure all possibilities have been covered. Otherwise you risk missing data where the quantity unity is "box", "pallet", "tin", "bottle" or similar.

Cheers
Pete

 

[CoSpringsGuy]

I would do a couple things ... take the formula out and run your data and see what else is in {LineItem.CustomField1} to make sure there isnt something in there you may want to account for as pmax explained....

Then I would do the same for {LineItem.CustomField2} ... if there are null values in that field then create a formula to convert those to something you want to use as a multiplier .. I guess if there is something other than null or numeric you could convert that as well but I think maybe you should evaluate that field first .. but if you are ok with it do this ... create a formula ..

If isnull({LineItem.CustomField2}) then 1000
else If isnumeric({LineItem.CustomField2}) then ToNumber({LineItem.CustomField2})
else 1000

(Replace 1000 with whatever you want that default multiplier to be)

Use this formula in the formula I suggested above replacing {LineItem.CustomField2}

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[blueboyz]

{LineItem.CustomField1} is either blank (null), has the word "can" or has a numeric value.
{LineItem.CustomField2} is either blank (null) or has a numeric value.

I changed the formula to the best of my ability to:

If isnull({LineItem.CustomField1}) then 0
else if isnull({LineItem.CustomField2}) then 0
else If isnumeric({LineItem.CustomField1}) then ToNumber({LineItem.CustomField1})
else if isnumeric({LineItem.CustomField2}) then [highlight #FCE94F]ToNumber({LineItem.CustomField2})[/highlight] and
ToNumber({LineItem.CustomField1}) * ToNumber({LineItem.CustomField2})
else 1 * ToNumber({LineItem.CustomField2})

An error appears on the 4th line of the formula on the field ToNumber({LineItem.CustomField2}): a boolean is required here.

If Custom Field 1 = "can" the formula should multiply 1 * the numeric value in Custom Field 2.
If Custom Field 1 = " " the formula should multiply 0 * the numeric value in Custom Field 2.
If Custom Field 1 = a numeric value the foumula should multiply Custom Field 1 * the numeric value in Custom Field 2.
If Custom Field 2 = " " the formula should set Custom Field 2 = 0.
If Custom Field 2 = a numeric value the formula should multiply Custom Field 1 * Custom Field 2.

Is there a way I can create a formula to handle this?

 

[CoSpringsGuy]

Create two formulas

formula1
If isnull({LineItem.CustomField2}) then 0
else If isnumeric({LineItem.CustomField2}) then ToNumber({LineItem.CustomField2})
else 0

second formula

If isnull({LineItem.CustomField1}) then 0
else If isnumeric({LineItem.CustomField1}) then ToNumber({LineItem.CustomField1}) * {@formula1}
else {@formula1}

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[blueboyz]

Thank you, CoSpringsGuy! I think the two formulas in your last post did the trick.