Concatenate fields 3

[ekta22]

Hi,

I have 5 fields on my report out of which 3 are formula fields. How can I concatenate them into one field? Also how can I hide a field on a report?

Thanks,

Ekta

 

[dtempleton]

To hide it:
Right-click on the field.
Choose Format Field
Click Suppress
Click OK.

Of course, It might be worth deleting the field off of your report if you never use it.

 

[bdreed35]

You can hide a field by suppressing it.
Right click the field on the report, and choose Format Field from the pop up.
In the Format Field dialog box, click the Common tab, and select the suppress option.

To concatenate fields you can create another formula that will perform the concatenation.

Here is some example code that you will need to update with your fields:

{table.field1} & {table.field2} & {@formula1} & {@formula2} & {@formula3}

The above example will concatenate them without anything seperating them. If you need to sperate them with a character, you can do the following:

{table.field1} & ", " & {table.field2} & ", " & {@formula1} & ", " & {@formula2} & ", " & {@formula3}

You can modify these as necessary to achieve exactly what you want.

~Brian

 

[kskid]

At least with CR 8.5 and Oracle 8i, I ran into the problem of coming up with a null concatenated record if any one of the fields were null so I started doing the following.

stringvar n;

if isnull{table.field1}) then
n := n
else
n := {table.field1};
if isnull{table.field2}then
n := n & ","
else
n := n & ", " & {table.field2};
if isnull{{@formula1}then
n := n & ","
else
n := n & ", " & {@formula1};
if isnull{{@formula2}then
n := n & ","
else
n := n & ", " & {{@formula2};
if isnull{{@formula3}then
n := n & ","
else
n := n & ", " & {{@formula3};

 

[knitwit]

I found that you can also create a blank text field and use it to hold your concatenated fields. Open this text field and then drag the formula field into it. You can then add any text before or after any number of formulas.

 

[ekta22]

Thanks everyone for the information. I used Brain's solution. The concatenation works fine but I have one problem. I have concatenated 3 fields but the formatting doesn't look right. For example, these are the values for the 3 fields- field1=E, field2=???, field3=DME. After concatenating the fields it looks like this

E???DME

This is fine but if field1 is empty it looks like this
???SDU

Together they look like this-

E???DME
???SDU

I want if field1 is empty field2 should be lined up. So it should look like this

E???DME
???SDU

How can I fix this?

Thanks,

Ekta

 

[kskid]

Then do this

stringvar n;

if isnull{table.field1}) then
n := " " <---- 1 blank space here
else
n := {field1};
if isnull{field2}then
n := n & " " <---- 3 blank spaces here
else
n := n & {table.field2};
if isnull{{table.field3}then
n := n & " " <---- 3 blank spaces here
else
n := n & {table.field3};
n;

 

[ekta22]

Thanks for your reply wichitakid. I created a new formula field and added the code below. But it doen't seem to do anything. One other thing. User runs this report on the web and the output is generated as a text file. But I ran the report on Crystal Reports as well but same thing.

stringvar n;

if isnull({pPC60403.CAUSE_CODE}) then
    n := "  "   
else
    n := ({pPC60403.CAUSE_CODE});
if isnull({@start_dd})then
    n := n & "  "   
else 
    n := n & ({@start_dd});
if isnull({@outage_time})then
    n := n & "    "   
else 
    n := n & ({@outage_time});
n;

Any ideas what I am doing wrong?

Thanks,

Ekta

 

[kskid]

Please post the @start_dd and @outage_time formulas you are using.

 

[ekta22]

@start_dd

Local StringVar outString := "";

if {pPC60403.HDR_RECORD_STATUS}="O"
then (if {pPC60403.HDR_START_DATE}<{?START_DATE} then outstring := "0*"
        else outstring := Cstr({pPC60403.HDR_START_DATE},"dd") )
 else (if {pPC60403.LIR_START_DATETIME}<{?START_DATE} then outstring := "0*"
        else outstring := Cstr({pPC60403.HDR_START_DATE},"dd") );

outString

@outage_time

Dim outage_hour As Number 

if IsNull({@outage_secs}) or {@outage_secs} < 360 then 
  outage_hour = .1
else 
  outage_hour = ({@outage_secs} / 3600)

end if

formula = ToText(outage_hour,"000.0",1,"","")

 

[kskid]

Ah Ha. Change the formula. For some reason, the results of the formula is not a null value. Instead it has a value of "".

stringvar n;

if isnull({pPC60403.CAUSE_CODE}) then
n := " "
else
n := ({pPC60403.CAUSE_CODE});
if {@start_dd} = "" then
n := n & " "
else
n := n & ({@start_dd});
if {@outage_time} = "" then
n := n & " "
else
n := n & ({@outage_time});
n;

 

[ekta22]

Same thing. This is driving me nuts now.

 

[ekta22]

I added * instead of space to see if it doing anything. If seems to be working fine in some of them. Wonder why it does not work in others. It looks something like this.

???FCOM
*???FCOM
*???FCOM
B???ASDE
???RTDS
???RTDS
*???BRIT

 

[kskid]

Then let's cover both scenarios

stringvar n;

if isnull({pPC60403.CAUSE_CODE}) or {pPC60403.CAUSE_CODE} = "" then
n := " "
else
n := ({pPC60403.CAUSE_CODE});
if isnull({@start_dd}) or {@start_dd} = "" then
n := n & " "
else
n := n & ({@start_dd});
if isnull({@outage_time}) or {@outage_time} = "" then
n := n & " "
else
n := n & ({@outage_time});
n;

 

[lbass]

You might try adding the trim function. If there were two spaces entered into the field, two spaces would be returned, while if it were blank or had one space, one space would be returned. With a proportional font, this might look like one space (when really it's two), or no space, when it's really one. So I would also try changing the font to Courier (a non-proportional font) and then adding the trim function to the first clause:

stringvar n;

if isnull({pPC60403.CAUSE_CODE}) or
trim({pPC60403.CAUSE_CODE}) = "" then
n := " "
else
n := ({pPC60403.CAUSE_CODE});
if isnull({@start_dd}) or {@start_dd} = "" then
n := n & " "
else
n := n & ({@start_dd});
if isnull({@outage_time}) or {@outage_time} = "" then
n := n & " "
else
n := n & ({@outage_time});
n;

-LB

 

[ekta22]

Thanks wichitakid and lbass. lbass your solution works just fine.

 

[ekta22]

Just one more question. What if the field has a variable data. I mean a field can accept a maximum of 3 characters but can show 2 or just 1 character. How should I handle that?

 

[lbass]

Let's say you it is the first element that varies in length. Try this, substituting the maximum field length for "3":

stringvar n;

if isnull({pPC60403.CAUSE_CODE}) or
trim({pPC60403.CAUSE_CODE}) = "" then
n := space(3)
else
n := ({pPC60403.CAUSE_CODE}) + space(3-length({pPC60403.CAUSE_CODE});

Again, you would want to use a nonproportional font like Courier.

-LB

 

[ekta22]

Hi lbass,

The above soltion kinda works. I have concatenated a total of 10 fields. And field number eight can have less than a max of 3 characters allowed.

This is how the fields looked before concatenation
000 000 3 3 1 7 E 36C MEM 1K
000 000 3 3 1 6 3 32 1A

After concatenation
0000003317E36CMEM1K
0000003316332 1A

You will see that last field(1A) is not lined up because field 8 has just 2 characters instead of 3. After applying your code to field 8, fields 1 through 7 do not show up on the report. Though field 1A is lined up correctly. This is how it looks.

36CMEM1K
32 1A

I changed code only for field 8 as below:

if isnull({pPC60403.FFA_REMOTE_BASE_IDENT}) or {pPC60403.FFA_REMOTE_BASE_IDENT} = "" then
    n := space(3)  
else 
    n := ({pPC60403.FFA_REMOTE_BASE_IDENT}) + space(3-length({pPC60403.FFA_REMOTE_BASE_IDENT}));

Any idea why this might be happening?

Thanks again,

Ekta

 

[kskid]

You forgot the n & after n :=

if isnull({pPC60403.FFA_REMOTE_BASE_IDENT}) or {pPC60403.FFA_REMOTE_BASE_IDENT} = "" then
n := n & space(3)
else
n := n & ({pPC60403.FFA_REMOTE_BASE_IDENT}) + space(3-length({pPC60403.FFA_REMOTE_BASE_IDENT}));