Solved

Crystal Reports XI  Arrays

Posted on 2014-02-20
45
427 Views
Last Modified: 2014-03-24
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
Comment
Question by:zknoll
  • 21
  • 17
  • 6
  • +1
45 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 39875171
Are you sure the records with no end date have a NULL in the date field?

mlmcc
0
 

Author Comment

by:zknoll
ID: 39875195
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
 
LVL 22

Expert Comment

by:Ido Millet
ID: 39875435
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
 

Author Comment

by:zknoll
ID: 39875621
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 39875642
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 39875684
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
 

Author Comment

by:zknoll
ID: 39876946
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
 
LVL 34

Expert Comment

by:James0628
ID: 39877251
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
 

Author Comment

by:zknoll
ID: 39877333
When I uncheck that I get absolutely no data.  Not even the detail that was showing before.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 39877628
How are you selecting data for the report (report filter)?

mlmcc
0
 

Author Comment

by:zknoll
ID: 39877699
Yes, there is selection criteria in the report.
0
 
LVL 34

Expert Comment

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

 James
0
 

Author Comment

by:zknoll
ID: 39892633
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 39892745
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
 

Author Comment

by:zknoll
ID: 39892883
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 39893055
How are you displaying the data in the array?

mlmcc
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 39893062
Can you upload the rpt file?

mlmcc
0
 

Author Comment

by:zknoll
ID: 39893131
Do you need the rpt or can I upload a screenshot of the design layout?
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 39893379
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
 

Author Comment

by:zknoll
ID: 39901791
Ok, here it is.
Export-For-Letters.rpt
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 39902236
You supressed the sections with the formulas.  Supprressed sections don't get evaluated

I think I fixed it

mlmcc
Export-For-Letters.rpt
0
 

Author Comment

by:zknoll
ID: 39903253
I just tested the revised report and none of the array fields are populated.  They are all blank.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:zknoll
ID: 39903269
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
 
LVL 34

Expert Comment

by:James0628
ID: 39903684
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 39904087
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
 

Author Comment

by:zknoll
ID: 39904192
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 39904697
If you put the datefield on the report, what date is displayed when you expect it to be NULL?

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
ID: 39904918
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
 

Author Comment

by:zknoll
ID: 39906957
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 39907117
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
 

Author Comment

by:zknoll
ID: 39907152
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 39907541
Did you run the report I uploaded?

mlmcc
0
 

Author Comment

by:zknoll
ID: 39917829
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
 
LVL 34

Expert Comment

by:James0628
ID: 39918325
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
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
ID: 39918601
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
 

Author Comment

by:zknoll
ID: 39918826
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 39918896
Here it  is

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

Author Comment

by:zknoll
ID: 39918897
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 39919211
Can you show the new Crystall SQL?

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
ID: 39919553
Can you post the record selection formula with the IsNull test?

 James
0
 

Author Comment

by:zknoll
ID: 39920814
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
 

Assisted Solution

by:zknoll
zknoll earned 0 total points
ID: 39920978
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
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 500 total points
ID: 39921293
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
 

Author Comment

by:zknoll
ID: 39929982
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
 

Author Closing Comment

by:zknoll
ID: 39949778
With a minor tweak to the formula, the report returns exactly what I need.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now