Formula with String and Number

[mycrystalbaby]

Hi,
In my report one field is number and other is string I need to write a formula like this..please tell me how to do that?

// this is a number
NumberVar Pole :={NUM_POLES};
//this is a string
StringVar Phase :={PHASE};

If Pole="1" then Phase else
if Pole="2" and Phase="C; then "B","C"

PS; I am using CR X and SQL 8.0
Thanks
AA

 

[dgillz]

In your if-then formula, I have several questions:

1) POLE and PHASE areprevious referred to as database fields and as variables. Which are then in your if-then?

2) the last then part of your code says then [red]"B","C"[/red]. What does this mean? Are you trying to assign 2 values?

How about showing some raw data and desired results?



Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry

 

[mycrystalbaby]

Yes this is if then formula.
Well, Actual fied from the db is {NUM_POLES} and {PHASE}.
{PHASE} is string in the database and have only three values A,B and C.
{NUM_POLES} is number in the database and has three values always, 1,2 and 3.
So we have to write formula
if {NUM_POLES}=1 then
whatever is there in {PHASE} diplay that value
//next we need to check if {NUM_POLES}=2, it means we can have A,B or B,C or C,A in the {PHASE} field.
So we need to check for that by writing
if {NUM_POLES}=2 and {phase}="C"
then B,C
//(these will come from database and then we need to check for different situations..
I am not sure, if I answered properly or not.
Please let me know, if you have any further question

 

[dgillz]

Please read my red remarks:

Yes this is if then formula.
Well, Actual fied from the db is {NUM_POLES} and {PHASE}.
[red]{PHASE} is string in the database and have only three values A,B and C.[/red]
{NUM_POLES} is number in the database and has three values always, 1,2 and 3.
So we have to write formula
if {NUM_POLES}=1 then
whatever is there in {PHASE} diplay that value
//next we need to check [red]if {NUM_POLES}=2, it means we can have A,B or B,C or C,A in the {PHASE} field.[/red]
So we need to check for that by writing
if {NUM_POLES}=2 and {phase}="C"
then B,C

The 2 statements in red are mutually exclusive.

I notice that you did not take the time to post sample raw data and desired results. Is there a reason why?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry

 

[mycrystalbaby]

Hi,
I did not get, what you suggested.
Let me send you some data, actually I missed it..
If it is 1 pole breaker, it will be a single row in the database, if it is 2 pole breaker then two rows in the database, if 3 pole breaker then 3 rows in the database.



BREAKER_NAME NUM_POLE PHASE

10, 12, 14 3 B
10, 12, 14 3 C
10, 12, 14 3 A

29 1 C

30, 32, 34 3 C
30, 32, 34 3 A
30, 32, 34 3 B

31, 33 2 A
31, 33 2 B

38, 40 2 A
38, 40 2 B

 

[dgillz]

I am losing patience, Is the above the raw data, or the desired results? I asked for both.


Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry

 

[mycrystalbaby]

I really apologize for this.
Raw data

10, 12, 14 3 B,C,A
10, 12, 14 3 B,C,A
10, 12, 14 3 B,C,A

8,9 2 A,B
8,9 2 A,B

11 1 A

This was the raw data and I would like to Have the results in Crystal LIKE

BREAKER_NAME NUM_POLE PHASE
10, 12, 14 3 B,C,A
8,9 2 A,B
11 1 A

explaination

If a breaker is 3 phase, then it will be one of these three (A,B,C or B,C,A or C,A,B)
a two phase breaker can be (A,B or B,C or C,A)

The problem is that we need to know which phase the breaker starts on (or ends on), so we can pick the right string...


THE PROBLEM IS NUM_POLE IS A NUMBER AND PHASE IS A STRING

CRYSTAL FORMULA SHOULD BE LIKE THIS..

IF
{NUM_POLE}=1
THEN
{PHASE}
ELSE

IF
{NUM_POLE}=2 AND {PHASE} ="C"
THEN
"B","C"


ELSE

IF
{NUM_POLE}=2 AND {PHASE} ="B"
THEN
A,B

ELSE


IF
{NUM_POLE}=2 AND {PHASE} ="A"
THEN
C,A

ELSE
IF
{NUM_POLE}=3 AND {PHASE} ="C"
THEN
A,B,C


ELSE AND SO ON...

 

[dgillz]

Your results just show that you are hiding the additional detail lines. Is this what you want?

Your formula still makes zero sense to me, starting here:

IF
{NUM_POLE}=2 AND {PHASE} ="C"
THEN
"B","C"

This makes no sense because none of your sample data matches this criteria, or any of the other criteria that follows it.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry