Missing Data using Select Expert

[Former Member]

Post Author: fireman204

CA Forum: General

Fairly new to Crystal, using Crystal 11. I'll do my best to describe my problem, but don't know if I can do it adequately, but here goes.

I have a report with 4 linked tables. I can get all the data with the linking without a problem. A code is entered in a field, and the description for that code is in another table. Within that table, the code is within a particular "category". There are several code numbers within the table that are the same, just the description is different. If I use Select Expert and limit the category, I get the correct descriptions, but not those fields with missing codes. If I don't limit the category, I get all kinds of descriptions that may or may not match. I have the following Basic statement that should handle the missing codes, but it doesn't work at all.

If {EMSAdvanced.complaintcode} = "" Then Formula = "No Complaint Code Entered" Else Formula = {EMSAdvanced.complaintcode}" - "{CodesEMS.descrip}End If

Anybody got any ideas? I've tried to change the joins, and used both left and full outer joins, both enforced both and not enforced. Not sure where to go from here, short of using the "is one of" choice in select expert. If possible, would like to avoid that, because if codes change, the statement would have to change to get an accurate report.

Any ideas are greatly appreciated!

Lloyd

[Former Member]

Post Author: fireman204

CA Forum: General

That sounds correct to me. The previous post about checking for null values worked. But it seems there's no way to check for null values in Select Expert without going into the formula and creating the formula itself. Pretty new to Crystal, but learning a lot on this forum! I've had the issue before on reports whenever there are null values and I'm trying to associate them with a field in another table. If I don't associate them with a field in another table, I get all values. I think the way I had it in Select Expert probably limited it to the category field, and if there was no value to look for (because the field was empty, so nothing to refer to in the category field) so it wouldn't return data. I know now to check for null values in these instances.

[Former Member]

Post Author: Jagan

CA Forum: General

Just to be clear1) You've got a 'left outer join enforced from' from a table (I'll call it TableA) to CodesEMS2) There can be more than one joined record in CodesEMS for a single record in TableA3) These multiple records in CodesEMS can have different values in the 'category' field.If all the above are true then the code in the select expert that checks if CodesEMS.category is either NULL or 'COMPLAIN' means that you will lose the records in TableA that have records in CodesEMS where none of them are 'COMPLAIN'. Is this what you want?

[Former Member]

Post Author: fireman204

CA Forum: General

That worked! Thanks for all the patience in getting this. This place is great!

[Former Member]

Post Author: synapsevampire

CA Forum: General

I'm a tad lost as you mix terms and incorrectly use others.

"Within that table, the code is within a particular "category". There are several code numbers within the table that are the same, just the description is different. If I use Select Expert and limit the category, I get the correct descriptions, but not those fields with missing codes. "

Are you using the term code and category synonymously, or are they different fields?

Also get away from using the select expert, manually edit the code there.

Also you state that this is a select statement:

{incident.incidenttype} startswith "3" and{incident.alarmdate}in{?Date Range} and{CodesEMS.category} in "COMPLAIN"

Obviously it is not a select statement, it doesn't even have a SELECT in it...

It looks like part of the Report->Selection Formula->Record

Try changing it to:

{incident.incidenttype} startswith "3" and{incident.alarmdate}in{?Date Range} and(isnull({CodesEMS.category})or{CodesEMS.category} = "COMPLAIN")

-k

[Former Member]

Post Author: fireman204

CA Forum: General

As far as I know. I'm using a left outer join, with both enforced. I've tried a full outer join, and didn't get anywhere, either.

[Former Member]

Post Author: Bandit07

CA Forum: General

Are your tables linked correctly?

B

[Former Member]

Post Author: fireman204

CA Forum: General

I get all the data, including data missing codes, before I put in a field from the codesems table. Once I put that in, I get the data, but it's not correctly associated. Once I limit it the category, it gives me the correct data, but is missing data without a code entered.

[Former Member]

Post Author: Bandit07

CA Forum: General

You say that you are able to get all the data before you start to add criteria to the record selection, so I would remove all criteria and just add in 1 at a time add test it for each criteria. Then you will be able to see which one is causing the issue. Try that and then we'll try to figure it out.

B

[Former Member]

Post Author: fireman204

CA Forum: General

Thanks! Tried it, but to no avail. I'm thinking it may have something to do with limiting the category in Select Expert. If there is no code, it obviously won't be able to associate anything with the category. But I don't know how to limit it to the category, yet still return data. The category would be another field to limit returns to only those codes that are also in the category. My select statement is

{incident.incidenttype} startswith "3" and{incident.alarmdate}in{?Date Range} and{CodesEMS.category} in "COMPLAIN"

I can remove the last line, and get data, but it's incorrect data.

Thanks for the assistance!

[Former Member]

Post Author: Bandit07

CA Forum: General

I would also check for null values along with checking for blanks.

If(IsNull({EMSAdvanced.complaintcode}) Or {EMSAdvanced.complaintcode} = ""

Then "No Complaint Code Entered"

Else {EMSAdvanced.complaintcode} + " - " + {CodeEMS.descrip})

B