Display Empty String Problem 1

[Corres]

Hi to all!

CRXI, SQL Server

I have a string field that has values, no entries and NULL values. I want empty string be displayed as Internal on my report. I created formula and inserted it by right click on the field, Format Field, as Display String formula:
if {newdem.Customer} ="" or isnull({newdem.Customer})then
"Internal"
else
{newdem.Customer}

But the values (Names of Customers) are displayed, but the word Internal is not displayed for the empty fields. I used this method of displaying different abbreviations in my other reports. I can't figure out what is wrong.

please help. thank you

 

[synapsevampire]

Code seems good, so you may have characters in the field, try:

if TRIM({newdem.Customer}) = ""
or
isnull({newdem.Customer})then
"Internal"
else
{newdem.Customer}

If that still fails, then you need to figure out what is in the field that falls out of the above criteria, something like:

whileprintingrecords;
stringvar output:="";
numbervar counter;
For counter := 1 to len(trim({table.field})) do(
if asc(mid({table.field},counter,1)) < 32
or
asc(mid({table.field},counter,1)) > 126 then
output := Output + "!=" & totext(asc(mid({table.field},counter,1)),0,"")
);
output

Now if output shows any exclamation points, you have extended ascii within the field.

-k

 

[Corres]

Thank you very much, synapsevampire, for your reply.

I did as you suggested, first with trim - it failed and I created formula @output, I put this formula in my details section and did not receive any exclamations points, it is also empty.

Actually, I have to group by this field, if it is empty; it means that “Internal” customer and I also tried to make it for the group display:

if GroupName ({newdem.Customer}) = "" or isnull(GroupName ({newdem.Customer})) then
"Internal" else
GroupName ({newdem.Customer})

But it does not work…

 

[synapsevampire]

Then I would say that you do not have any NULL nor empty fields,at least that Crystal recognize as null or empty..

What makes you think that you have empty fields? Have you placed the field itself alongside and see no output?

I've never heard of Crystal not recognizing a NULL or empty string correctly, but anything is possible.

-k

 

[lbass]

You have to test for null first. The formula should be:

if isnull({newdem.Customer}) or
{newdem.Customer} ="" then
"Internal"
else
{newdem.Customer}

-LB

 

[Corres]

synapsevampire, thank you.

I see NULL and empty fields if I open table in SQL Server (Through Enterprise Manager). And yes, of course, I have placed the field itself in details section in Crystal and saw no output - empty. I did it before posting my question. I agree, it is simple, but strange…

Oh, I remember that in Report Options I checked regarding NULL values as DEFAULT values. I will try tomorrow to uncheck it.

Anyway, thank you and maybe someone encountered such a problem? Thanks.

 

[lbass]

See my earlier post, and yes, you need to uncheck the convert null to default setting.

-LB

 

[synapsevampire]

naw, my formula sucked, I'm an idjut, null checks must come first in a crystal formula, sorry, just use:

if isnull({newdem.Customer})
or
TRIM({newdem.Customer}) = "" then
"Internal"
else
{newdem.Customer}

-k

 

[Corres]

Thanks once again lbass and synapsevampire for your follow ups.

I failed to make it through format field, display string option.

So, I created formula @Internal

if isnull({newdem.Customer})
or
TRIM({newdem.Customer}) = "" or {newdem.Customer}= ""
then
"Internal"
else
{newdem.Customer}

that you suggested and grouped on this formula and it works great. I don.t know why display string option does not work.

But I got into another issue. I received in my report:


Source Total Q/A Others

Honeywell 83 25 58
Applications
Documentation 20 15 5
Internal 83 25 58
Applications
Documentation 20 15 5
Smart
Predefined Resources 21 2 19



Where Internal is a group that empty or with NULL values. How can I sort groups to “separate” Internal group from other sources to show such as:

Source Total Q/A Others

Internal 83 25 58
Applications
Documentation 20 15 5
Honeywell 83 25 58
Applications
Documentation 20 15 5
Smart
Predefined Resources 21 2 19


I have to use in Group Expert formula option, but which formula to use to sort? I can’t find in previous threads.

Thanks.

 

[synapsevampire]

the or {newdem.Customer}= "" is redundant, delete it.

Not sure why you decided to change my formula other than wanting to think that you improved it, you didn't, you simmply slowed it down.

Do you mean that you want Internal to be the first one to show in the group?

You have different ways to do this, I suggest changing your formula to:

if isnull({newdem.Customer})
or
TRIM({newdem.Customer}) = ""
then
"aaaaaaInternal"
else
{newdem.Customer}

Now it will always show up first, thn instead of displaying the group formula, create another formula for diplay as:

if {@MyGroupFormula} = "aaaaaaInternal" then
"Internal"
else
{@MyGroupFormula}

You can also manually designate the order when creating/changing the group, but if new ones come along later, they won't be in the list.

-k

 

[Corres]

Thanks synapsevampire and lbass for your suggestions!

synapsevampire, my star to you, I received what my user wants.