Good morning,

Im building a financial report in WEBFOCUS 5.3 and trying to create two defined fields using the following function. The formula below works if the "multiply by" is the same.

IF BTL.BT_LINE.A1_LineWeight_ActualUOM EQ 'TD' AND BTL.BT_LINE.Origin_City EQ 'LEMOORE' OR 'YUMA' OR 'SAN DIEGO' OR 'NORFOLK' OR 'VIRGINIA BEACH' OR 'CHERRY POINT' OR 'JACKSONVILLE' OR 'CHINA LAKE' OR 'LEMOORE' OR 'PUGET SOUND' OR 'FALLON' OR 'BREMERTON' THEN BTL.BT_LINE.A1_LineWeight_Actual * 5000 ELSE 0

1. I need change each cities "multiply by" to a different number. Can anyone offer a solution to my issue? How can I change my functions to perform these different calculations in the same defined field? I tried the following but get the error below:

FUNCTION/FORMULA THAT ERROR'D:

IF BTL.BT_LINE.A1_LineWeight_ActualUOM EQ 'TD' AND BTL.BT_LINE.Origin_City EQ 'LEMOORE' THEN BTL.BT_LINE.A1_LineWeight_Actual * 5000 ELSE 0 OR BTL.BT_LINE.Origin_City EQ 'YUMA' THEN BTL.BT_LINE.A1_LineWeight_Actual * 8500 OR
BTL.BT_LINE.Origin_City EQ 'NORFOLK' THEN BTL.BT_LINE.A1_LineWeight_Actual * 10500 ELSE 0

-ERROR CODE:
0 ERROR AT OR NEAR LINE 13 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC266) IF .. THEN .. ELSE .. SYNTAX ERROR
0 NUMBER OF ERRORS= 0
NUMBER OF SEGMENTS= 1 ( REAL= 1 VIRTUAL= 0 )
NUMBER OF FIELDS= 285 INDEXES= 0 FILES= 1
TOTAL LENGTH OF ALL FIELDS= 6032

2. The second defined field is to identify weight ranges i.e. 1-150, 151-300 etc. How do I change my functions to perform multiple calculations in the same defined field? My initial formula below ran however it populated all the rows with the same results regardless of of the weight.

FUNCTION/FORMULA THAT RAN:
IF BTL.BT_LINE.A1_LineQuantity_BillingUnitsUOM EQ 'LB' AND BTL.BT_LINE.A1_LineQuantity_BillingUnits LE 150 THEN '1-150' ELSE 0

FUNCTION FORMULA THAT ERROR'D: Same error as above.

IF BTL.BT_LINE.A1_LineQuantity_BillingUnitsUOM EQ 'LB' AND BTL.BT_LINE.A1_LineQuantity_BillingUnits LE 150 THEN '1-150' OR BTL.BT_LINE.A1_LineQuantity_BillingUnits GE 151 AND LE 300 THEN '151-300' OR BTL.BT_LINE.A1_LineQuantity_BillingUnits GE 301 AND LE 600 THEN '301-600' ELSE 0

Please help.

Mike

This message has been edited. Last edited by: Kerry,

The error message is telling you that your syntax is incorrect. Try something like this:

IF BTL.BT_LINE.A1_LineQuantity_BillingUnitsUOM EQ 'LB' AND BTL.BT_LINE.A1_LineQuantity_BillingUnits LE 150 THEN '1-150' ELSE IF BTL.BT_LINE.A1_LineQuantity_BillingUnits GE 151 AND LE 300 THEN '151-300' ELSE IF BTL.BT_LINE.A1_LineQuantity_BillingUnits GE 301 AND LE 600 THEN '301-600' ELSE 0;

Leave your ELSE 0 to the end and replace your OR's with IF.

You're not constructing your IF-THEN-ELSE logic correctly and throwing some OR's in there where they don't belong. The OR is part of the equivalency test, NOT part of the IF-THEN-ELSE statement.

Your first statement should be

IF BTL.BT_LINE.A1_LineWeight_ActualUOM EQ 'TD' THEN 
  (IF BTL.BT_LINE.Origin_City EQ 'LEMOORE' THEN BTL.BT_LINE.A1_LineWeight_Actual * 5000 ELSE 
   IF BTL.BT_LINE.Origin_City EQ 'YUMA' THEN BTL.BT_LINE.A1_LineWeight_Actual * 8500 ELSE 
   IF BTL.BT_LINE.Origin_City EQ 'NORFOLK' THEN BTL.BT_LINE.A1_LineWeight_Actual * 10500 ELSE 0) 
ELSE 0;

Your second statement has the same issue. Following the same logic as above (and using FROM-TO to simplify it):

IF BTL.BT_LINE.A1_LineQuantity_BillingUnitsUOM EQ 'LB' THEN 
  (IF BTL.BT_LINE.A1_LineQuantity_BillingUnits LE 150 THEN '1-150' ELSE
   IF BTL.BT_LINE.A1_LineQuantity_BillingUnits FROM 151 TO 300 THEN '151-300' ELSE
   IF BTL.BT_LINE.A1_LineQuantity_BillingUnits FROM 301 TO 600 THEN '301-600' ELSE 0)
ELSE 0;

Simple rules for IF..THEN..ELSE..

Make it readable
Use () to group logical tests


A suggested replacement for EQ OR OR OR would be IN ()

Thanks everyone for the guidance, it works!!! Appreciate all your quick responses.

Happy Holidays to all!

R/Mike