Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Need help in formula !! Post Reply Post New Topic
<< Prev  Page  of 2
Author Message
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 24 Dec 2008 at 8:12am
what formual did you use to replace column 5 data?
IP IP Logged
karthizen
Newbie
Newbie
Avatar

Joined: 10 Dec 2008
Location: India
Online Status: Offline
Posts: 17
Quote karthizen Replybullet Posted: 24 Dec 2008 at 9:09am

Hi Dblank,

Let me post the entire formulae I have used.

Barbie count: - This one works fine

B2:

if isnull ({PRODUCT_INFORMATION.PRODUCT_DESC_2}) then 0

else if 'Barbie' in {PRODUCT_INFORMATION.PRODUCT_DESC_2} then {@B22} 

else 0

 

B22:

if isnull ({PRODUCT_INFORMATION.PRODUCT_AMT_2}) then 0

else {PRODUCT_INFORMATION.PRODUCT_AMT_2}

  

B3:

if isnull ({PRODUCT_INFORMATION.PRODUCT_DESC_3}) then 0

else if 'Barbie' in {PRODUCT_INFORMATION.PRODUCT_DESC_3} then {@B33} 

else 0

 

B33:

if isnull ({PRODUCT_INFORMATION.PRODUCT_AMT_3}) then 0

else {PRODUCT_INFORMATION.PRODUCT_AMT_3}

 

B4:

if isnull ({PRODUCT_INFORMATION.PRODUCT_DESC_4}) then 0

else if 'Barbie' in {PRODUCT_INFORMATION.PRODUCT_DESC_4} then {@B44} 

else 0

 

B44:

if isnull ({PRODUCT_INFORMATION.PRODUCT_AMT_4}) then 0

else {PRODUCT_INFORMATION.PRODUCT_AMT_4}

 

B:

 {PRODUCT_INFORMATION.PRODUCT_AMT_1}+{@B2}+{@B3}+{@B4}

----------------
Desc:2
 

if

{PRODUCT_INFORMATION.PRODUCT_DESC_2} ="Barbie" or

{PRODUCT_INFORMATION.PRODUCT_DESC_3} ="Barbie" or

{PRODUCT_INFORMATION.PRODUCT_DESC_4}="Barbie" then " "

else if

{PRODUCT_INFORMATION.PRODUCT_DESC_2} ="Barbie" or

{PRODUCT_INFORMATION.PRODUCT_DESC_3} ="Barbie" then

{PRODUCT_INFORMATION.PRODUCT_DESC_4}

else if

{PRODUCT_INFORMATION.PRODUCT_DESC_2}= "Barbie" then

{PRODUCT_INFORMATION.PRODUCT_DESC_3}

else

{PRODUCT_INFORMATION.PRODUCT_DESC_2}

Desc:3:

if

{PRODUCT_INFORMATION.PRODUCT_DESC_3} ="Barbie" or

{PRODUCT_INFORMATION.PRODUCT_DESC_4} ="Barbie" then " "

else if

{PRODUCT_INFORMATION.PRODUCT_DESC_3}= "Barbie" then

{PRODUCT_INFORMATION.PRODUCT_DESC_4}

else

{PRODUCT_INFORMATION.PRODUCT_DESC_3}

 
Desc:4
if

{PRODUCT_INFORMATION.PRODUCT_DESC_4} ="Barbie" then " "

else

{PRODUCT_INFORMATION.PRODUCT_DESC_4}

---------------------------------------
For calculating the Tax amounts:
 

Amt:2

 

if

{PRODUCT_INFORMATION.PRODUCT_DESC_2} ="Barbie" or

{PRODUCT_INFORMATION.PRODUCT_DESC_3} ="Barbie" or

{PRODUCT_INFORMATION.PRODUCT_DESC_4}="Barbie" then 0

else if

{PRODUCT_INFORMATION.PRODUCT_DESC_2} ="Barbie" or

{PRODUCT_INFORMATION.PRODUCT_DESC_3} ="Barbie" then

{PRODUCT_INFORMATION.PRODUCT_AMT_4}

else if

{PRODUCT_INFORMATION.PRODUCT_DESC_2}= "Barbie" then

{PRODUCT_INFORMATION.PRODUCT_AMT_3}

else

{PRODUCT_INFORMATION.PRODUCT_AMT_2}

Amt:3:

if

{PRODUCT_INFORMATION.PRODUCT_DESC_3} ="Barbie" or

{PRODUCT_INFORMATION.PRODUCT_DESC_4} ="Barbie" then 0

else if

{PRODUCT_INFORMATION.PRODUCT_DESC_3}= "Barbie" then

{PRODUCT_INFORMATION.PRODUCT_AMT_4}

else

{PRODUCT_INFORMATION.PRODUCT_AMT_3}

 

Amt:4

if

{PRODUCT_INFORMATION.PRODUCT_DESC_4} ="Barbie" then 0

else

{PRODUCT_INFORMATION.PRODUCT_AMT_4}

---------------------------

Please let me know if you need any other inputs from my side....
Regards,
Karthik...
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 24 Dec 2008 at 9:47am

You need to make your formula fields account for each possibility in sequence to determine what data will show up. This also means accounting for data that you have already "shifted" via you other formula fields prior to getting to that field.
I am getting a little lost in my own logic on this one but try these for Desc:3 and amt:3

"if {PRODUCT_INFORMATION.PRODUCT_DESC_2}="Barbie" and ({PRODUCT_INFORMATION.PRODUCT_DESC_3}="Barbie" or {PRODUCT_INFORMATION.PRODUCT_DESC_4}="Barbie") then " " else if {PRODUCT_INFORMATION.PRODUCT_DESC_3}="Barbie" and {PRODUCT_INFORMATION.PRODUCT_DESC_4}="Barbie" then " " else {PRODUCT_INFORMATION.PRODUCT_DESC_3}="Barbie and {PRODUCT_INFORMATION.PRODUCT_DESC_4}<>"Barbie" then {PRODUCT_INFORMATION.PRODUCT_DESC_4} else {PRODUCT_INFORMATION.PRODUCT_DESC_3}"
Replace your colum 5 with this field.
 

"if {PRODUCT_INFORMATION.PRODUCT_DESC_2}="Barbie" and ({PRODUCT_INFORMATION.PRODUCT_DESC_3}="Barbie" or {PRODUCT_INFORMATION.PRODUCT_DESC_4}="Barbie") then 0 else if {PRODUCT_INFORMATION.PRODUCT_DESC_3}="Barbie" and {PRODUCT_INFORMATION.PRODUCT_DESC_4}="Barbie" then 0 else {PRODUCT_INFORMATION.PRODUCT_DESC_3}="Barbie and {PRODUCT_INFORMATION.PRODUCT_DESC_4}<>"Barbie" then {PRODUCT_INFORMATION.PRODUCT_AMT_4} else {PRODUCT_INFORMATION.PRODUCT_AMT_3}"
Replace your column 6 with this field.
These are the same logic so if I missed a possible option you need to account for add it into the sequence and on one and test it. If it works update the other one to account for the same option.

for desc4 and amount 4 you have to also look at desc2 and amount 2 to account for the data "shifting".
Desc:4
if
{PRODUCT_INFORMATION.PRODUCT_DESC_2} ="Barbie"
or
{PRODUCT_INFORMATION.PRODUCT_DESC_3} ="Barbie"
or {PRODUCT_INFORMATION.PRODUCT_DESC_4} ="Barbie"
then ""
else
{PRODUCT_INFORMATION.PRODUCT_DESC_4}
Amt:4
if
{PRODUCT_INFORMATION.PRODUCT_DESC_2} ="Barbie"
or
{PRODUCT_INFORMATION.PRODUCT_DESC_3} ="Barbie"
or {PRODUCT_INFORMATION.PRODUCT_DESC_4} ="Barbie"
then 0
else
{PRODUCT_INFORMATION.PRODUCT_AMT_4}
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 24 Dec 2008 at 9:50am

Missed an if in the last post:

"if {PRODUCT_INFORMATION.PRODUCT_DESC_2}="Barbie" and ({PRODUCT_INFORMATION.PRODUCT_DESC_3}="Barbie" or {PRODUCT_INFORMATION.PRODUCT_DESC_4}="Barbie") then " " else if {PRODUCT_INFORMATION.PRODUCT_DESC_3}="Barbie" and {PRODUCT_INFORMATION.PRODUCT_DESC_4}="Barbie" then " " else if{PRODUCT_INFORMATION.PRODUCT_DESC_3}="Barbie and {PRODUCT_INFORMATION.PRODUCT_DESC_4}<>"Barbie" then {PRODUCT_INFORMATION.PRODUCT_DESC_4} else {PRODUCT_INFORMATION.PRODUCT_DESC_3}"
Replace your colum 5 with this field.
 
"if {PRODUCT_INFORMATION.PRODUCT_DESC_2}="Barbie" and ({PRODUCT_INFORMATION.PRODUCT_DESC_3}="Barbie" or {PRODUCT_INFORMATION.PRODUCT_DESC_4}="Barbie") then 0 else if {PRODUCT_INFORMATION.PRODUCT_DESC_3}="Barbie" and {PRODUCT_INFORMATION.PRODUCT_DESC_4}="Barbie" then 0 else if{PRODUCT_INFORMATION.PRODUCT_DESC_3}="Barbie and {PRODUCT_INFORMATION.PRODUCT_DESC_4}<>"Barbie" then {PRODUCT_INFORMATION.PRODUCT_AMT_4} else {PRODUCT_INFORMATION.PRODUCT_AMT_3}"
IP IP Logged
karthizen
Newbie
Newbie
Avatar

Joined: 10 Dec 2008
Location: India
Online Status: Offline
Posts: 17
Quote karthizen Replybullet Posted: 24 Dec 2008 at 5:28pm
Thank You very much DBlankSmile.
 
Suddenly there was a change in the requirement and I worked on that accordingly & completed the task.
 
Thanks again for all your help !
 
Merry X-MasSmile to all Forum Members !
 
 
Regards,
Karthik...
IP IP Logged
<< Prev  Page  of 2
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.