Crystal Reports XI Arrays

Are you sure the records with no end date have a NULL in the date field?

mlmcc

Yes, I believe it is because if I run this simple query in sql it returns the correct records.

Select * from relation
where
relisa = 'STEW' and relstopdat is null

Move the IsNull() check to a higher-level IF.  You can't mix IsNull with other checks on the same column because that would yield a False when the column is NULL.

Can you show me the syntax for that since I tried that but was unsuccessful.  I'm not sure I had the syntax right.

The checks are on 3 different columns.

You might try making it first

if isnull({relation_contact.relstopdat}) and {relation_contact.relisa} = "STEW" and (not({relation_contact.relkey} in contactkeys) )


Where do you have the formula in the report?
If I read what you are doing it should be in the detail section.

mlmcc

Check the report options to see if you have the option set to CONVERT DATABASE NULLS TO DEFAULT

Click FILE --> REPORT OPTIONS

Also ensure you don't have the option for DEFAULT VALUE FOR NULLS selected in the formula editor

mlmcc

I tried putting the isnull selection first but that didn't make a difference.  The formula is in the details section of the report.  The Report options are set to Convert Database Nulls to Default and the formula is not set to Default value for nulls.  

None of these have made a difference.  I still get nothing returned in my array.

You should _not_ have "Convert Database NULL Values to Default" checked under File > Report Options.  With that checked, the formula will never see any null values.

 James

When I uncheck that I get absolutely no data.  Not even the detail that was showing before.

How are you selecting data for the report (report filter)?

mlmcc

Yes, there is selection criteria in the report.

Can you post the record selection formula?  Also, are you using formulas to suppress any sections/fields, or using group selection?

 James

Here is the record selection.

{name_fund.nametype} = "a" and
{GENERIC.id_number} <> "" and
{name_contact.nametype} = "a" and
{name_full.nametype} = "a" and
{relation_full.relisa} = "SCRE" and
{corebio_full.coreconst} = "STUD" and
{relation_to_fund.relisa} = "Fund" and
{relation_contact.relisa} = "STEW" and
{@Dead} >= 1.00 and
{@Contact_Name} <> "" and
({relation_contact.relrelatid} = {relation_to_fund.relid})

I am not using group selection.  The detail row is suppressed but the group footer row that has the output from the array, is not suppressed and none of the fields in the group footer have suppressions.

Can you unsuppress the details and put the 3 fields involved in the test on the line{relation_contact.relisa}, relation_contact.relstopdat}, and {relation_contact.relkey}

DO you see records that meet the criteria?

mlmcc

Yes, I do.  I see those that have a null in the stopdate field and those that have a value in the stopdate field.

How are you displaying the data in the array?

mlmcc

Can you upload the rpt file?

mlmcc

Do you need the rpt or can I upload a screenshot of the design layout?

I need the rpt file.  I don't need data.  I just want to see how the various formulas are used and where they are on the report.

mlmcc

Ok, here it is.
Export-For-Letters.rpt

You supressed the sections with the formulas.  Supprressed sections don't get evaluated

I think I fixed it

mlmcc
Export-For-Letters.rpt

I just tested the revised report and none of the array fields are populated.  They are all blank.

If I remove the following: isnull({relation_contact.relstopdat}) from the updatecontact formula, the array populates but I also get the values where the stop date is not null.  Is there a better way to evaluate the null in the datefield, rather than using isnull?

The report that you posted has the "Convert Database NULL Values to Default" option checked under Report > Options, so the UpdateContacts formula will never see any nulls.

 That is, unless the null handling option in the formula editor overrides that setting under Report > Options.  I'm using CR 10 and it doesn't have that option in the formula editor, so I don't know how it interacts with the setting under Report > Options.


 Oh, and contrary to what mlmcc said, in my experience, formulas are still evaluated in suppressed sections.  I've done that many times and never had a problem.

 James

The selection in the formula editor will not override the report options setting since the data will be converted on reading.

Try this one

mlmcc
Export-For-Letters.rpt

The array still remains blank unless I remove the isnull line in the formula.  If I uncheck Convert Database NULL Values to Default the report returns nothing. None of the fields populate with this box unchecked.

If you put the datefield on the report, what date is displayed when you expect it to be NULL?

mlmcc

If the report doesn't show anything when you uncheck the Convert option, then I'd have to guess that you're checking a field in your record selection formula that's null, and that's throwing off the test.  To make sure that it's that, and not records being suppressed or something, you could put RecordNumber (under Special Fields) in the report footer and see how many records the report is reading (with and without the Convert option checked).

 With the Convert option checked, the IsNull test in your Update formula simply isn't going to work, because the field will never be seen as null.

 The field will be seen as the "default" value, whatever that is for that type of field.  If you can determine what that value is (which is what mlmcc was getting at in his last post), and you don't have that value in any other records, then you can replace the IsNull test with a test for that value.

 If you really need to check for nulls in that field, you'll have to uncheck the Convert option, in which case you'll need to try to figure out why you're not seeing records when you don't use that option.  If the problem is that one or more fields in the record selection formula are null, you'll need to add IsNull tests on those fields, and the IsNull test needs to be before you do any other test on that field.

 BTW, the reason why nulls can be a problem is because CR doesn't handle nulls very well in formulas.  Here's a quote from the CR 10 Help:

 > In general, when Crystal Reports encounters a null valued field in a formula,
 > it immediately stops evaluating the formula and produces no value. If you
 > want to handle null field values in your formula, you must explicitly do so
 > using one of the special functions designed for handling them: IsNull,
 > PreviousIsNull or NextIsNull.

 James

 PS: Thanks for the info about the two null options mlmcc.  I suspected that it would be something like that, but really didn't know.

If I add the stopdate to the detail line and run the report, for those records that have a NULL in the stopdate, the column looks blank, but the rest of the fields do display data as they should.  If I then uncheck the convert option box, my report returns no data.  I've looked in the selection criteria and I am not filtering on NULLs.  I've check all the suppressions and none have a formula.  It's a straight suppression in the section expert.  It makes no sense to me why the report returns nothing when the convert option is left unchecked.

You are filtering on a several of the joined tables so Crystal essentially turns the join to INNER.  If the change is done before doing the join then the fields have values and can be joined.

Are you joining tables on fields that could be NULL?

Part of the problem when you uncheck is you are testing for "" or " " but NULL is not a value so those tests fail and the formula doesn't evaluate.

I made a couple of changes in the report.  Run this and see if you get data.  Run it both ways.

mlmcc
Export-For-Letters--2-.rpt

No, I don't believe any of my links are on fields that could be NULL.

In either case, none of the array fields display.  If I check the Convert option box, I get data in all the fields except the arrays.  If I uncheck the convert option box, my report is blank.

Did you run the report I uploaded?

mlmcc

Yes I did.  I responded in a previous post but here are the results:

In either case, none of the array fields display.  If I check the Convert option box, I get data in all the fields except the arrays.  If I uncheck the convert option box, my report is blank.

Something that didn't hit me before.  You said:

 > I've looked in the selection criteria and I am not filtering on NULLs.

 If you mean that you're not specifically excluding nulls, that's not the only issue.  If a field is null and CR tries to use it in a formula (except with the IsNull function), the formula will not be evaluated properly.  If that formula is the record selection formula, the record will not be included.

 It might help if you went to Database > "Show SQL Query" and checked the query that CR is sending to the db.  Does it include the tests from your record selection formula?

 If it does include them, then CR should be sending the tests to the server, and CR's problem with nulls shouldn't be an issue (as far as those tests are concerned).

 If it does not include them, then CR is doing the filtering locally and nulls will be a problem.  In that case, you need to add an IsNull check to your record selection formula for any of those fields that could be null.

 James

mlmcc,
did you mean to attach a file for me to try or do you want me to change the formula in the array?

Here it  is

mlmcc
Export-For-Letters--3-.rpt

James,

I added the isnull to my selection criteria in CR,  however, my report now returns no data.  I tried unchecking the Convert Null option but still the report is blank.  If I take the sql query from CR  (Show sql query) and run it directly against the database, the record selection is exactly what I want in my report.  The query converts the isnull({relstopdat]) to this: "relation_contact"."relstopdat" IS  NULL  and it works like a charm.  It's only CR that doesn't seem to know how to handle the isnull.

Can you show the new Crystall SQL?

mlmcc

Can you post the record selection formula with the IsNull test?

 James

SELECT DISTINCT "corebio_full"."coreid", "relation_to_fund"."relid", "name_full"."namefirst", "name_full"."namemiddle", "name_full"."namelast", "name_full"."namesuffix", "name_fund"."namefirst", "name_fund"."namemiddle", "name_fund"."namelast", "name_fund"."namesuffix", "name_contact"."namefirst", "GENERIC"."id_number", "GENERIC"."sortname", "name_fund"."nametype", "name_contact"."nametype", "name_full"."nametype", "relation_full"."relisa", "corebio_full"."coreconst", "relation_to_fund"."relisa", "relation_contact"."relisa", "relation_contact"."relrelatid", "death_full"."deathid", "corebio_full"."coreprefyr", "relation_contact"."relstopdat", "crh_pref"."pre_ind_name", "name_full"."namesalut", "name_fund"."nameformn", "name_contact"."nameformn", "relation_contact"."relkey"
 FROM   (((((((("milldata"."dbo"."relation_full" "relation_full" INNER JOIN "milldata"."dbo"."zkgeneric14" "GENERIC" ON "relation_full"."relkey"="GENERIC"."timekey") LEFT OUTER JOIN "milldata"."dbo"."corebio_full" "corebio_full" ON "relation_full"."relid"="corebio_full"."coreid") LEFT OUTER JOIN "milldata"."dbo"."relation_full" "relation_to_fund" ON "relation_full"."relrelatid"="relation_to_fund"."relid") LEFT OUTER JOIN "milldata"."dbo"."relation_full" "relation_contact" ON "relation_to_fund"."relrelatid"="relation_contact"."relid") LEFT OUTER JOIN "milldata"."dbo"."name_full" "name_fund" ON "relation_to_fund"."relid"="name_fund"."nameid") LEFT OUTER JOIN "milldata"."dbo"."name_full" "name_contact" ON "relation_contact"."relid"="name_contact"."nameid") LEFT OUTER JOIN "milldata"."dbo"."death_full" "death_full" ON "relation_contact"."relid"="death_full"."deathid") LEFT OUTER JOIN "milldata"."dbo"."crh_pref" "crh_pref" ON "relation_contact"."relid"="crh_pref"."pre_id") LEFT OUTER JOIN "milldata"."dbo"."name_full" "name_full" ON "corebio_full"."coreid"="name_full"."nameid"
 WHERE  "name_fund"."nametype"='a' AND "GENERIC"."id_number"<>'' AND "name_contact"."nametype"='a' AND "name_full"."nametype"='a' AND "relation_full"."relisa"='SCRE' AND "corebio_full"."coreconst"='STUD' AND "relation_to_fund"."relisa"='Fund' AND "relation_contact"."relisa"='STEW' AND "relation_contact"."relstopdat" IS  NULL  AND "relation_contact"."relrelatid"="relation_to_fund"."relid"
 ORDER BY "GENERIC"."id_number", "name_contact"."namefirst"

Thanks for the update.  I've solved my problem by using the following line in my criteria:

({relation_contact.relstopdat} < DateTime(1900,1,1,0,0,0))

With a minor tweak to the formula, the report returns exactly what I need.