Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Nested if then else statement (I think) Post Reply Post New Topic
Author Message
Kwirky
Newbie
Newbie
Avatar

Joined: 21 Apr 2010
Online Status: Offline
Posts: 32
Quote Kwirky Replybullet Topic: Nested if then else statement (I think)
     Posted: 27 Oct 2010 at 6:55pm
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)
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 28 Oct 2010 at 3:41am
I don't think that there is enough information as to why you want the different labels on the boxes. 
 
Your if statement is only concerned with the complete weight and all labels will look the same. No account is made for the logic about changing labels, but I don't see a pattern of WHY the labels should be changed...it appears that 1 label is different, but which one and why?
 
 
IP IP Logged
Kwirky
Newbie
Newbie
Avatar

Joined: 21 Apr 2010
Online Status: Offline
Posts: 32
Quote Kwirky Replybullet Posted: 28 Oct 2010 at 11:05am
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
PML0123456
PTR0123456
PTR0123456
PTR0123456
PTR0123456
PTR0123456


Edited by Kwirky - 28 Oct 2010 at 11:08am
IP IP Logged
Kwirky
Newbie
Newbie
Avatar

Joined: 21 Apr 2010
Online Status: Offline
Posts: 32
Quote Kwirky Replybullet Posted: 28 Oct 2010 at 12:16pm

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!
IP IP Logged
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | KONACAD

© 2004-2026 Brian Bischof. All Rights Reserved.