CODEWITHSUNDEEP
crystal-reports
user28014
user28014

Reputation: 21

Crystal reports 11 : blank field bombs the report

I'm creating a invoice crystal report for sage mas 500 AR module. In it, I'm attempting to add the tarinvoice.balance field with the following formula: 

if {tarPrintInvcHdrWrk.Posted} = 1 then 
    ToText({tarInvoice.Balance})

I'm assuming that when the {tarPrintInvcHdrWrk.Posted} = 1 conditional statement holds FALSE, it doesn't attempt to pull the invoice field because when I remove the formula from the report, the form displays correctly without it.

When the conditional statement renders true in the report, the balance fields behaves correctly. However, with the formula renders FALSE in the CR form, the entire crystal report bombs and displays blank. Any ideas why or what I'm doing wrong?

Just tried setting everything to zero and the report still bombs. I'm starting to think its more of a query error in the report. I wish there was a way to exclude the field in the query when posted = 0.

With tarinvoice.balance removed when the posted = 0, the report works fine.
With tarinvoice.balance included and posted = 1, report works fine.

With tarinvoice.balance included and posted =0, report bombs.

Upvotes: 2

Views: 10113

Answers (7)

vice
vice

Reputation: 605

if isnull({tarPrintInvcHdrWrk.Posted}) or {tarPrintInvcHdrWrk.Posted}=0 then
" "
else
if {tarPrintInvcHdrWrk.Posted} = 1 then 
    ToText({tarInvoice.Balance})
else
" "

I have trouble with this kind of field when making reports to export to excel. A field with no data in will pull all columns to the right of it over to "fill the gap".

Upvotes: 0

Michael Buen
Michael Buen

Reputation: 39393

Maybe you have Suppress If Blank section on your report. Try to put: Else " "

Upvotes: 0

Arvo
Arvo

Reputation: 10570

If you put {tarInvoice.Balance} directly on report (into details "debug" section - often needed, don't forget supress it in production :)), what values it displays or does report become empty?

Upvotes: 0

Anthony K
Anthony K

Reputation: 2603

I saw a suggestion on Exp.Exch to try putting the field into a variable before converting it to text.
e.g. 

NumberVar InvoiceBalance;  
If isnull({tarInvoice.Balance}) then
    InvoiceBalance := 0
Else
    InvoiceBalance := {tarInvoice.Balance};

If {tarPrintInvcHdrWrk.Posted} = 1 then
    ToText(InvoiceBalance);

I also tried to recreate your problem, since I have see similar things before.
No luck though trying with CR 8.5 & XI R2. Perhpas it has to do with linked tables as well, since I only tried on a simple single table.
I have also seen similar behaviour when using a formula within a Running Total - they do not like nulls at all!

Upvotes: 0

user28014
user28014

Reputation: 21

I modified the formula to this: 

if isnull({tarPrintInvcHdrWrk.Posted}) = FALSE then 
    if {tarPrintInvcHdrWrk.Posted} = 1 then 
        if isnull({tarInvoice.Balance}) = FALSE then 
            ToText({tarInvoice.Balance})
        else 
            "0.00" 
    else 
        "0.0"
else 
"0"

The crystal report still bombs.. Nevertheless, it does show "0" in the appropriate space.

Upvotes: 0

Anthony K
Anthony K

Reputation: 2603

You can change the way Crystal handles a null value for a value in a formula. At the top of the Formula Workshop there is a drop down box that usually says "Exceptions For Nulls".
Change this to the other option "Default Values For Nulls" and your formula should no longer bomb out. You used to be able to specify the what the default values applied were, but more recent versions of Crystal have these hard coded. Search the help for "Null Treatment" for a table showing them.

Upvotes: 0

micahwittman
micahwittman

Reputation: 12476

I believe the conditional statement fails immediately if you encounter a NULL, so your formula needs to test IsNull({tarPrintInvcHdrWrk.Posted}) before it tests equality with "1".

Upvotes: 2

Related Questions