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}))))))))

This formula is not working as I want it to.

Any ideas how to get the end result. (I am using CRXI)

 

[Madawc]

It should work, though you don't need the brackets, and could have something like

if {DeliveryNote.Weight} <= 25 then "WWPML0" + {BoxLabel.JobNo} 
else 
if {DeliveryNote.Weight} <= 50 then "WWPML0" + {BoxLabel.JobNo} else 
...

I also notice the the first nine tests produce exactly the same output, I'd suppose the second should be WWPML1 etc.

If that's not it, please say exactly what you are getting. For debugging, it is good to have an extra display line which shows the raw data plus the formula field result.


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

 

[kwirky]

The reason we need the labels to be identified by weight is for invoicing purposes. We need to have a PML on every 25kg amount, so if we have a total weight of 50kg, we would have 2 PML labels. In addition we need to note how many boxes in total are being shipped per order. In the 50kg example, we may have say 7 boxes going in total (the total weight of the order is 50kg) and the first 2 boxes need to be labelled PML. The remaining boxes need to have PTR (5 boxes in total).
Therefore the reporting would show the following, with the labels listed
Order #123456; total weight 50; total boxes 7;
PML0123456
PML1123456 [PML0 will become PML1 etc for each additional record]
PTR0123456
PTR1123456 [PTR0 will become PTR1 etc for each additional record]
PTR2123456
PTR3123456
PTR4123456

 

[kwirky]

I think I over-thought the whole thing. I have been able to do the formula by creating a formula which says totalweight divided by 25.
I then created a formula for box2 to be
if {@weightcalculator} >=2 then "WWPML1" + {BoxLabel.JobNo} else
"WWPTR0" + {BoxLabel.JobNo}
I added this formula to each box# (amending the values as applicable) and my labels are doing what I wanted.

Thank you all for your help!