Displaying records in file with null datetime

[wiplash]

Using CR11.5.

I'm linking 2 tables and the secondary table can have more than 1 row. I need to display some fields in this secondary table ONLY IF a field (defined as a datetime) is null and not sure how to go about this. I tried simply adding this condition into the SELECT statement, but the report then returns 0 records. Take this out and the report returns many records (as it should), however, of course some of the fields I need aren't there.

Any help is much appreciated!

 

[BettyJ]

did you try the condition
[tt]isnull({YourDateField})[/tt]

 

[wiplash]

Thank you, and yes, and sorry, I guess I didn't ask the right question. That indeed works but how do I actually read and test for the right record? Say there are 2 matches on the secondary table and the first record has a date but the second record doesn't. I want to display some other fields from that second record and just disregard the first record.

Currently the data from that first record is displaying and the second record seems not to exist. I tried eliminating records in that secondary file (that actually had a datetime) but then get nothing. Not sure what's up so again, surely appreciate the help.

 

[Danster]

If I read your question correctly, you need to display records in the second table where the DATETIME is NULL?

In the record selection formula, as BettyJ suggested, put in IsNull({YourDateField}).

Crystal will only display records which match the above selection formula.

 

[BettyJ]

Can you please give some more info, like sample data from both tables and what you are trying to pull with examples?

 

[wiplash]

Sure and again, thanks!

Table A and Table B

Table A is my master table and has unique rows, meaning it only has one row per serial number.

Table B on the other hand can have one, two, or three records with that same serial number (each record indeed has a relationship to the master table) (table A).

However, of those two or three matching records in table B, there will only be one of those with a null Date (defined as datetime). I need to report this serial (with some data from table A (which currently works fine) and some data from table B (which is the issue here). Currently the information for that serial out of table B is showing me data from the first record, even though that Date (in table B) may not contain a null value.

The report currently shows many serial numbers (which is correct as there are many serial numbers in table A), however, again, the fields shown coming from table B are often incorrect (unless of course there was only one record for that serial number in table B).

If I add that ISNULL(date field from table B) statement to the report, then I get no records. (Not sure why as table A should still report but for some reason doesn't.) ???????

Example:
- Table A contain one row for Serial Number 12345.
- Table B contains 2 rows for Serial Number 12345.
Within Table B, the first row has a date in the Date field and the second row has a null in the Date field.
- I need to report the serial number (and other data) from Table A, along with a few fields found in Table B for that matching serial number (but only from that linked serial number record in Table B that had no date value).

Thanks!

 

[BettyJ]

Can you please confirm that you are linking the two tables on the Serial number field.

 

[wiplash]

Yes, the linked fields are indeed the serial numbers from both tables.

However, I removed the ISNULL(date) from the selection criteria (as it gave me 0 records for some reason) and created a formula with just that ISNULL(date). Then I added that new formula field to the selection criteria and was given the possible values as TRUE or FALSE.

When I selected TRUE, most of the serial numbers are indeed now showing up in the report, but the serial numbers that have more than one matching Table B record aren't.

So I then put the selection criteria back like it was and then simply display the date formula. Each serial number shown now has that formula value showing as either true or false. I need for those records that show 'false' to "skip down" to the next Table B record where the value is indeed 'true'. It doesn't appear that the table B record with no date is being read, and that's the record I need.

 

[BettyJ]

I am not sure why you are not getting the desired output. If you link table A and table B by serial number and then add isnull(YourDateField}) in the record selection criteria you should be getting what you are looking for.(unless there is something else I am missing. Is there anything else in the record selection criteria )

Also, I believer that you are displaying the fields in the Details section.

Try taking out the record selection criteria to display all the records. Run the report and make sure all records are being displayed.

You can also sort by serial number. Run the report again.

Then in details section add a suppress condition

[tt]not(isnull({YourDateField}))[/tt]

 

[wiplash]

Thanks again for your assistance! I'll try your latest suggestion.

Why it's not reading multiple records in table B is a mystery to me also (or at least it doesn't show anything but the first record from table B). Apparently something's wrong somewhere.

Really, all I want is for the matching table B records that have a null date to show, but if I include the formula in the selection criteria to say ISNULL({table B.date field}), then the linked table B records indeed show (now), but not any table A recs that have multiple table B recs. Strange! Meaning: Only table A records THAT HAVE a single matching table B record (with a null date) are displayed / reported. Nothing is shown for those table A recs that have multiple table B recs. It's as if they're never seen.

What's also strange to me is that if I create a subreport linking the serial number and share the date field, then multiple table B recs indeed show up. So I know they're there! (I can also query table B and see the multiples.) The subreport feature will work, but it seems the long way around.

Thanks