cancel
Showing results for 
Search instead for 
Did you mean: 

Excel True/False Field with Nulls displaying as False

0 Kudos

Using CRXI with Excel spreadsheet as my database. The Excel file has a field with entries of either True, False, or Null (blank). When displaying this field in CR, the Null values are displaying as False. How do I display the Nulls as blank. I tried the following formula, but the Nulls still are False:

If(IsNull(field)) then ""

else ToText(field)

Thanks,

JSR

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi

First convert all null values to default as below:

From File -> Report Options and File -> Options -> Reporting check the option for :

- "Convert database null values to default

- Convert other null values to default.

- Then right on the field-> Format field -> Common tab click on X-2 for Display string and type the below formula:

if IsNull(field) then "Null value here"
else ""& currentfieldvalue;

- Click on save and close. Click on Ok.

Hope this helps!!

Regards

Sourashree

0 Kudos

Sourashee,

Thanks for the reply! Unfortunately, when entering your formula, I get the following message:

The formula result must be a number.

Former Member
0 Kudos

Hi

You need to make sure that you are entering the formula in formula editor for "DISPLAY STRING".

- Right Click on the field -> format field -> Under Common tab there is Display String and a X-2 button just besides that.

Click on that and put the exact formula (that is mentioned in the above post) in the formula editor.

Hope this helps!!

Regards

Sourashree

0 Kudos

Sourashree,

Thanks for the quick reply again!!

I'm sorry...I did fail to go to the Common tab before. However, I did go to the Common tab this time and enter your formula in the Display String (after marking the Convert Database NULL values to Default and Convert Other NULL values to Default in the Report Options), but the NULL values still show as FALSE.

Former Member
0 Kudos

Hi

Can you provide the formula that you have used?

Regards

Sourashree

0 Kudos

Sure:

if IsNull() then "Null value here"

else ""& currentfieldvalue;

Former Member
0 Kudos

Hi JSR,

When you create Crystal Reports using True / False from excel, it is taking as Boolen data type in the report. As Boolen will have only two values either true or false. Try to enter True / False values in excel starting with (') single cote.

For example 'True / 'False.

Thanks,

Sastry

Former Member
0 Kudos

Hi

You can resolve your issue as below:

1. Create a formula field as Right click on formula fields on field explorer -> click on new ->

2. put a name -> click on ok.

3. Put the below formula in the formula editor :

if (field) = "true" then "true"

else if (field) = "false" then "false"
else "Null value here";

Use this formula field on the report instead of using the database field.

Hope this helps!!

Regards

Sourashree

0 Kudos

Sastry,

Thanks for the info! I will try this, however, I was hoping to not change/alter the Excel source file, since I am getting it from a vendor and I don't want to be accused of changing anything. If I must, I must...there is not any way to remedy this in Crystal?

0 Kudos

Hi Sourashree,

I tried your suggestion and the Nulls still display as FALSE.

I did have to alter your formula a bit by adding ToText:

if ToText(field) = "true" then "true"

else if ToText(field) = "false" then "false"

else "Null value here";

I also tried taking out the quotes:

if (field) = true then "true"

else if (field) = false then "false"

else "Null value here";

Thanks,

Jeff

0 Kudos

I did find some sort of solution thanks to Sastry! However, instead of changing the data in the field by adding a ', I formatted the whole field as TEXT in Excel.

When I now bring the Excel file in Crystal, the field is still listed as BOOLEAN in the Field Explorer, but when I place the field on the report, it now shows the NULLS as blanks.

I am still wishing of not having to do the extra step in Excel, so if anyone has a suggestion/solution in CR, please let me know.

Thanks,

Jeff

Former Member
0 Kudos

hi

I actually tried the same for a field which i have in my sample database. The field has true or false values or empty.

I applied the formula in the formula field and used that formula field in my report instead of using the database field and it worked for me.

Regards

Sourashree

0 Kudos

Sourashree,

In Field Explorer, what type of field does CR list the True/False field as?

0 Kudos

Sourashree,

How is the field formatted in Excel?

Former Member
0 Kudos

Hi

CR lists all the database fields together under datbase fields in field explorer.

I created a new formula field with the above formula and used that in the report, it worked for me..

Regards

Sourashree

0 Kudos

When in Field Explorer, right-click on the field that corresponds to the True/False/Empty field in your test database and select Show Field Type. What does CR list as the field type?

former_member260594
Active Contributor
0 Kudos

Jeff,

I was able to recreate the behaviour you were seeing but only when the report option Convert Database NULL values to default was enabled. Do you have this report option (under the file menu) enabled? What happens if you disable it?

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi Jeff,

Go in formula and select Default Values for Nulls on top drop down and also edit the fomul like :

If (IsNull(field)) or Field = " "

then ""

else ToText(field)

Thanks,

Sastry

0 Kudos

Sastry,

Thanks for the reply! Unfortunately, I am still getting the Nulls as False. I did however, have to change your formula a little:

If (IsNull(field)or ToText(Field) = " ")

then ""

else ToText(field)