Nested if then else statement (I think)

[kwirky]

Hi,
I am trying to figure out how to get a field to change depending on the value of another field.
I have 2 tables: boxlabel & deliverynote & fields: weight, boxes, jobno.
A job will have a total weight and total number of boxes, depending on what has to be delivered. Each box will have a label for each box. In order to show these label numbers, I have created a bunch of box formulas (box1 to box12 at this stage) and then I have added these to the equivalent number of details sections. I am suppressing details depending on how many boxes; if I have 4 boxes, I am suppressing where box number is 1,2,3,5,6,7,8,9,10. if I have 7 boxes, I am suppressing where box number is 1,2,3,4,5,6,8,9,10.
Anyway, each label needs to have a different prefix depending on the weight of the total order. If the total weight is 34.91kg and I have 8 boxes, I want the first label to have the prefix "PML" and the remaining labels to have "PTR".
If the total weight is 104.91kg and I have 5 boxes, I want the first 4 labels to have the prefix "PML" and the remaining label to have "PTR".

I thought I would need to create a nested if than else statement for each of the box formulas similar to this:

if {DeliveryNote.Weight} in 0 to 25 then "WWPML0" + {BoxLabel.JobNo}
else
(if {DeliveryNote.Weight} in 26 to 50 then "WWPML0" + {BoxLabel.JobNo} else
(if {DeliveryNote.Weight} in 51 to 75 then "WWPML0" + {BoxLabel.JobNo} else
(if {DeliveryNote.Weight} in 76 to 100 then "WWPML0" + {BoxLabel.JobNo} else
(if {DeliveryNote.Weight} in 101 to 125 then "WWPML0" + {BoxLabel.JobNo} else
(if {DeliveryNote.Weight} in 126 to 150 then "WWPML0" + {BoxLabel.JobNo} else
(if {DeliveryNote.Weight} in 151 to 175 then "WWPML0" + {BoxLabel.JobNo} else
(if {DeliveryNote.Weight} in 176 to 200 then "WWPML0" + {BoxLabel.JobNo} else
(if {DeliveryNote.Weight} in 201 to 225 then "WWPML0" + {BoxLabel.JobNo} else
"WWPTR0" + {BoxLabel.JobNo}))))))))

 

[Madawc]

DUPLICATE, I answered the first post.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP