• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 649
  • Last Modified:

Crystal Reports XI Arrays

I need some help with a formula in an array.  The data I want represented in the array is based on a datetime field in a sql table being NULL.  If the value is not null, I want to exclude that record.  I've tried positioning the ISNULL in it's own if statement but it still doesn't work.  When I add the isnull criteria, the array returns no results.  If I remove it it returns all records including those with an end date.  Any thoughts on how to write this?


WhilePrintingRecords;
stringvar array contacts;
numbervar array contactkeys;
numbervar contactcounter;

if {relation_contact.relisa} = "STEW" and isnull({relation_contact.relstopdat})and not({relation_contact.relkey} in contactkeys)
 then (contactcounter := contactcounter + 1;
          if contactcounter < 5 then
              (redim preserve contacts[contactcounter];
                redim preserve contactkeys[contactcounter];
                contacts[contactcounter] := {name_contact.nameformn};
                contactkeys[contactcounter] := {relation_contact.relkey};))
0
zknoll
Asked:
zknoll
  • 21
  • 17
  • 6
  • +1
3 Solutions
 
mlmccCommented:
Are you sure the records with no end date have a NULL in the date field?

mlmcc
0
 
zknollAuthor Commented:
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
0
 
Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
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.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
zknollAuthor Commented:
Can you show me the syntax for that since I tried that but was unsuccessful.  I'm not sure I had the syntax right.
0
 
mlmccCommented:
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
0
 
mlmccCommented:
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
0
 
zknollAuthor Commented:
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.
0
 
James0628Commented:
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
0
 
zknollAuthor Commented:
When I uncheck that I get absolutely no data.  Not even the detail that was showing before.
0
 
mlmccCommented:
How are you selecting data for the report (report filter)?

mlmcc
0
 
zknollAuthor Commented:
Yes, there is selection criteria in the report.
0
 
James0628Commented:
Can you post the record selection formula?  Also, are you using formulas to suppress any sections/fields, or using group selection?

 James
0
 
zknollAuthor Commented:
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.
0
 
mlmccCommented:
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
0
 
zknollAuthor Commented:
Yes, I do.  I see those that have a null in the stopdate field and those that have a value in the stopdate field.
0
 
mlmccCommented:
How are you displaying the data in the array?

mlmcc
0
 
mlmccCommented:
Can you upload the rpt file?

mlmcc
0
 
zknollAuthor Commented:
Do you need the rpt or can I upload a screenshot of the design layout?
0
 
mlmccCommented:
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
0
 
zknollAuthor Commented:
Ok, here it is.
Export-For-Letters.rpt
0
 
mlmccCommented:
You supressed the sections with the formulas.  Supprressed sections don't get evaluated

I think I fixed it

mlmcc
Export-For-Letters.rpt
0
 
zknollAuthor Commented:
I just tested the revised report and none of the array fields are populated.  They are all blank.
0
 
zknollAuthor Commented:
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?
0
 
James0628Commented:
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
0
 
mlmccCommented:
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
0
 
zknollAuthor Commented:
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.
0
 
mlmccCommented:
If you put the datefield on the report, what date is displayed when you expect it to be NULL?

mlmcc
0
 
James0628Commented:
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.
0
 
zknollAuthor Commented:
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.
0
 
mlmccCommented:
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
0
 
zknollAuthor Commented:
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.
0
 
mlmccCommented:
Did you run the report I uploaded?

mlmcc
0
 
zknollAuthor Commented:
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.
0
 
James0628Commented:
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
0
 
mlmccCommented:
Try this version.  Instead of testing for a NULL date field, it tests for 1 Jan 1900 0:0:0

If that doesn't work, can you unsuppress the dedtails and verify you are getting data and what the default date is.

mlmcc
0
 
zknollAuthor Commented:
mlmcc,
did you mean to attach a file for me to try or do you want me to change the formula in the array?
0
 
mlmccCommented:
Here it  is

mlmcc
Export-For-Letters--3-.rpt
0
 
zknollAuthor Commented:
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.
0
 
mlmccCommented:
Can you show the new Crystall SQL?

mlmcc
0
 
James0628Commented:
Can you post the record selection formula with the IsNull test?

 James
0
 
zknollAuthor Commented:
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"
0
 
zknollAuthor Commented:
I downloaded the Export3.rpt and ran it but it still returns records where the stop date is populated and the array fields are all blank .  If I uncheck the Report Options to Convert Null values, it returns nothing.  

However, I went into the formula you created for the @UpdateContact and changed the = to <,  I got exactly what I need.  Arrays are populating but only with those folks that have a NULL in the stop date field.

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

Honestly, this shouldn't be so hard.  You'd think CR would be able to handle the ISNULL in the selection criteria.
0
 
mlmccCommented:
Try changing your filter to this

 "GENERIC"."id_number"<>'' AND  "relation_full"."relisa"='SCRE'

Crystal will treat left outer joins as INNER when you start testing/filtering on the joined tables.  If you get data for the above filter then you can start adding the other filters in 1 at a time to see which one causes the data to disappear.

mlmcc
0
 
zknollAuthor Commented:
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))
0
 
zknollAuthor Commented:
With a minor tweak to the formula, the report returns exactly what I need.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 21
  • 17
  • 6
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now