[RESOLVED] Formula working only on some records?

[blakemckenna]

I'm using CR 10 and have a report where the formula only works on some records. Here is my formuala below:

WhilePrintingRecords;

Global BooleanVar blnProductXref;

if {tblProducts.AdventProductCD} > "" then
    (
        blnProductXref := true;
        {tblBatchDetail.prodCode} & "/(" & {tblProducts.AdventProductCD} & ")"
    )
else
    (
        blnProductXref := false;
        {tblBatchDetail.prodCode} & "/(Unk)"
    )

Problem is for the record in question....it should be taking the else path and printing the value in {tblBatchDetail.prodCode} (which contains a value) but it's not showing on the report. For other records that contain a value in the same field....they WILL show up on the report. It doesn't make sense to me.

Any ideas?

Thanks,

[si_the_geek]

What is the value for AdventProductCD on that row?

My guess is that you think it is blank, but it is actually Null.

Rather than just checking > "" (why not <> "" ?) you should be checking for Nulls, I would personally have something like this (not sure if 'IsNull' is valid for CR!):

if IsNull({tblProducts.AdventProductCD}) or {tblProducts.AdventProductCD} = "" then
    (
        blnProductXref := false;
        {tblBatchDetail.prodCode} & "/(Unk)"
    )
else
    (
        blnProductXref := true;
        {tblBatchDetail.prodCode} & "/(" & {tblProducts.AdventProductCD} & ")"
    )

[blakemckenna]

The value is "010995"

[si_the_geek]

In that case it shouldn't be taking the Else path as you posted (as "010995"> "").

What is the value for prodCode on that row?

[blakemckenna]

Si,

It does take the else path because I verified the data in the DB. The field {tblBatchDetail.prodCode} has a value in it and it's not showing up on the report.

[blakemckenna]

Can there be a reason CR reports a record but not all of it. This sounds funny but it will only do this for 2 records out of my database. Every other record it reports it correctly. The 2 records in question are the same customer. It will report the record but only certain data will display on the report even though the empty fields contain data in the DB.

[si_the_geek]

In cases like that the problem is usually due to some kind of difference in the data.. is there anything at all in those rows that are different to other rows?

It's been a long time since I used Crystal, but I remember that you can set parts of the report to "hide" based on fields/formulas.. have you perhaps got anything like that set up in this report?

[blakemckenna]

No, I've checked all that. The records in question are being read, but it's not showing ALL the data that should show on the report. I ran a test and put a statement in my Formulas where it WILL display a value and when I ran the report it didn't show up, which tells me the formula didn't get executed. So, now my question is....what would cause a formula NOT to execute for specific records only? I've included my formulas for review!

REM Formula 1

WhilePrintingRecords

Global blnConsigneeXref as Boolean

If {tblConsignees.AdventCustomerID} <> "" Then
    If {tblConsignees.AdventCustLocNumber} > "" Then
        blnConsigneeXref = true
        Formula = {tblBatchDetail.destCustNumber} & "/(" & {tblConsignees.AdventCustomerID} & "-" & {tblConsignees.AdventCustLocNumber} & ")"
    Else
        blnConsigneeXref = true
        Formula = {tblBatchDetail.destCustNumber} & "/(" & {tblConsignees.AdventCustomerID} & ")"
    End If
Else
    blnConsigneeXref = false
    Formula = {tblBatchDetail.destCustNumber} & "/(Unk)"
End If


REM Formula 2

WhilePrintingRecords

Global blnCarrierXref as Boolean

If {tblBatchDetail.carrierSCAC} <> "" Then
    If {tblBatchDetail.fein} > "" Then
        Formula = {tblBatchDetail.carrierSCAC} & "/" & {tblBatchDetail.fein}
        If {tblCarriers.AdventCarrierCD} > "" Then
            blnCarrierXref = true
            Formula = {tblBatchDetail.carrierSCAC} & "/" & {tblBatchDetail.fein} & "/(" & {tblCarriers.AdventCarrierCD} & ")"
        Else
            blnCarrierXref = true
            Formula = {tblBatchDetail.carrierSCAC} & "/" & {tblBatchDetail.fein} & "/Unk)"
        End If
    Else
        blnCarrierXref = false
        Formula = {tblBatchDetail.carrierSCAC} & "/(Unk)"
    End If
Else
    Formula = "Unkown"
End If


REM Formula 3

WhilePrintingRecords

Global blnProductXref as Boolean

If {tblProducts.AdventProductCD} <> "" Then
    blnProductXref = true
    Formula = {tblBatchDetail.prodCode} & "/(" & {tblProducts.AdventProductCD} & ")"
Else
    blnProductXref = false
    Formula = {tblBatchDetail.prodCode} & "/(Unk)"
End If

[si_the_geek]

One thing that jumps out at me is that you still aren't checking for Null. If Nulls can appear in these fields, you must deal with them appropriately.

Null is very different to an empty string.. most programs (including Crystal if I remember correctly) will not display anything if there is a Null, even if you append it to a valid string.

Also comparisons (like {tblProducts.AdventProductCD} <> "") do not work as you would expect - if AdventProductCD is Null, does the condition evaluate to True or False? I could be wrong, but I think it evaluates to True in Crystal - so you get the problem of not being displayed as mentioned above.

[blakemckenna]

Si,

I see what your saying and that could be true if the conditions existed, however, I have checked rows in the table that are in question and they do contain data. Also, the entire formula (both branches of the IF ELSE construct) work.

[si_the_geek]

Well I'm not sure what to suggest.. perhaps add the fields separately to the report (rather than just in the formula) to see if Crystal has a problem with any of them, and (temporarily) changing the fields in the report to fixed strings (eg: Formula = "{tblBatchDetail.destCustNumber}" & "/(Unk)") to see that the correct part is actually showing.

[blakemckenna]

I'll give that a shot Si...thanks!

[blakemckenna]

Si,

Checking for Nulls worked. Wow, amazing what the difference between <> "" and IsNulls can make.

Thanks for your help!

[si_the_geek]

No problem.

Wow, amazing what the difference between <> "" and IsNulls can make.

The difference is a similar scale in almost everything that can work with databases, hence why it was the first thing I noticed.

The biggest problem is getting your head around what Null means, and many people struggle with it as it is very different to most things in the programming world. A string is a value (eg: "a"), and an empty string is a value (""), but a Null isn't - it is the absence of a value.

This meant that you were basically asking Crystal to compare an empty string to something, but weren't telling it what!


A good example of the reason for Nulls is a table containing the name of a person in three fields "FirstName", "LastName", and "MiddleName". If you know somebody has a middle name (but aren't sure what it is yet) you can leave it Null, and if you know they haven't got one you can make it blank (an empty string). Doing this allows you to deal with them differently if needed (eg: you could easily list all the people who need their details updated).