trouble with Description of a field from a description table

[mchambers]

Hello,

I am having some issues getting the correct description of fields from another table. I have several fields that I am trying to pull the description of. For ex:

I need to pull the description of race, gender, marital status etc. from a global code table. I have tried to add the category name to the selection expert but it only pulls all the clients that actually have something in each field.

So in the selection expert I did {Global_Code_Sex.category} = 'SEX'
for Gender. But if any patient has this as null in their record it does not pull their record.

I have also tried to remove that from the selection expert and use his formula for the field itself:
Whileprintingrecords;
(if{Patient.sex} = {Global_Code_Sex.code} and {Global_Code_Sex.category} = 'SEX'
then
{Global_Code_Sex.name}
else "Missing")

The problem with that is there is a code of M in that table and I get that field name instead of Male.

Can anyone help me with this?

 

[lbass]

You need to add the global code table once for each field you need to decode. The alias tables will appear with a number extension like globalcodes_1. You can right click on the tables in the database expert and rename them so you know what they refer to, e.g., gender. Then in the linking expert, link the code from the original table to the corresponding lookup table on the codes field and then display the description in the report.

-LB

 

[mchambers]

Thanks lbass,

I have tried to do that but the only records retrieved are the ones that are not null. We are trying to determine whose records are not filled out completely.

 

[lbass]

Use left joins for each alias table added.

-LB

 

[mchambers]

I am using Left Joins. Here is the sql from crystal
SELECT "Patient"."patient_id", "Patient"."fname", "Patient"."lname", "Patient"."addr_1", "Patient"."mrn", "Patient"."ssn", "Patient"."episode_id", "Patient"."dob", "Patient"."addr_2", "Patient"."city", "Patient"."state", "Patient"."zip", "Patient"."county", "Patient"."home_phone", "Patient"."work_phone", "Patient"."other_phone", "Patient"."occ_1", "Patient"."employer_name", "Patient"."no_of_dependents", "Patient"."num_in_household", "Patient"."household_income", "Global_Code_Sex"."name", "Global_Code_Sex"."category", "Patient"."case_status"
FROM "PsychProd"."dbo"."Global_Code" "Global_Code_Sex" LEFT OUTER JOIN "PsychProd"."dbo"."Patient" "Patient" ON "Global_Code_Sex"."code"="Patient"."sex"
WHERE "Global_Code_Sex"."category"='SEX' AND "Patient"."case_status"='A'
ORDER BY "Patient"."patient_id"

 

[lbass]

No. You have it backwards, and you should not be selecting on the code you want to decode. Your From clause should look like this:


FROM (("PsychProd"."dbo"."Patient" "Patient"
left outer join "PsychProd"."dbo"."Global_Code" "Global_Code_Sex"
ON "Patient"."sex" = "Global_Code_Sex"."code"
)

left outer join "PsychProd"."dbo"."Global_Code" "Global_Code_Race"
on "Patient"."race" = "Global_Code_race"."code"
) //etc.

WHERE "Patient"."case_status"='A'
ORDER BY "Patient"."patient_id"

You need to add the codes table multiple times and use a left join FROM the patient table to each of the code tables, and it will generate the correct SQL.

-LB

 

[mchambers]

Ok this worked I get way more results but for clients that are missing data in some of the fields they are not showing in my results.

 

[lbass]

It sounds like you are not using a left outer join or that you are selecting on the code tables. Please paste your new SQL query into this thread so we can troubleshoot it.

-LB

 

[mchambers]

Here is the new sql from statement:

FROM   ((((((((((((((((((((("psychprod"."dbo"."Patient" "Patient" LEFT OUTER JOIN "psychprod"."dbo"."Intake_Med_Hx" "Intake_Med_Hx" ON ("Patient"."patient_id"="Intake_Med_Hx"."patient_id") AND ("Patient"."episode_id"="Intake_Med_Hx"."episode_id")) LEFT OUTER JOIN "psychprod"."dbo"."Patient_Legal" "Patient_Legal" ON ("Patient"."patient_id"="Patient_Legal"."patient_id") AND ("Patient"."episode_id"="Patient_Legal"."episode_id")) LEFT OUTER JOIN "psychprod"."dbo"."Patient_Risk_Assessment" "Patient_Risk_Assessment" ON ("Patient"."patient_id"="Patient_Risk_Assessment"."patient_id") AND ("Patient"."episode_id"="Patient_Risk_Assessment"."episode_id")) LEFT OUTER JOIN "psychprod"."dbo"."Patient_Custom" "Patient_Custom" ON ("Patient"."patient_id"="Patient_Custom"."patient_id") AND ("Patient"."episode_id"="Patient_Custom"."episode_id")) LEFT OUTER JOIN "psychprod"."dbo"."Global_Code" "Global_Code_Sex" ON "Patient"."sex"="Global_Code_Sex"."code") LEFT OUTER JOIN "psychprod"."dbo"."Global_Code" "Global_Code_Stud" ON "Patient"."student_status"="Global_Code_Stud"."code") LEFT OUTER JOIN "psychprod"."dbo"."Global_Code" "Global_Code_Race" ON "Patient"."race"="Global_Code_Race"."code") LEFT OUTER JOIN "psychprod"."dbo"."Global_Code" "Global_Code_MStatus" ON "Patient"."marital_status"="Global_Code_MStatus"."code") LEFT OUTER JOIN "psychprod"."dbo"."Global_Code" "Global_Code_Sal" ON "Patient"."salary"="Global_Code_Sal"."code") LEFT OUTER JOIN "psychprod"."dbo"."Global_Code" "Global_Code_Occu" ON "Patient"."occ_status"="Global_Code_Occu"."code") LEFT OUTER JOIN "psychprod"."dbo"."Global_Code" "Global_Code_Educ" ON "Patient"."ed_level"="Global_Code_Educ"."code") LEFT OUTER JOIN "psychprod"."dbo"."Global_Code" "Global_Code_Milt" ON "Patient"."military_status"="Global_Code_Milt"."code") LEFT OUTER JOIN "psychprod"."dbo"."Global_Code" "Global_Code_RS" ON "Patient"."referral_source"="Global_Code_RS"."code") LEFT OUTER JOIN "psychprod"."dbo"."Global_Code" "Global_Code_LA" ON "Patient"."living_arrangement_1"="Global_Code_LA"."code") LEFT OUTER JOIN "psychprod"."dbo"."Psychiatric_History" "Psychiatric_History" ON ("Patient"."patient_id"="Psychiatric_History"."patient_id") AND ("Patient"."episode_id"="Psychiatric_History"."episode_id")) LEFT OUTER JOIN "psychprod"."dbo"."Global_Code" "Global_Code_FS" ON "Patient"."fin_status"="Global_Code_FS"."code") LEFT OUTER JOIN "psychprod"."dbo"."Global_Code" "Global_Code_primIn" ON "Patient_Custom"."pincosrc"="Global_Code_primIn"."code") LEFT OUTER JOIN "psychprod"."dbo"."Custom_Dropdowns" "Custom_Dropdowns_Eth" ON "Patient_Custom"."ethnicity"="Custom_Dropdowns_Eth"."field_value") LEFT OUTER JOIN "psychprod"."dbo"."Custom_Dropdowns" "Custom_Dropdowns_Phy" ON "Patient_Custom"."physical"="Custom_Dropdowns_Phy"."field_value") LEFT OUTER JOIN "psychprod"."dbo"."Chemical_Dependency" "Chemical_Dependency" ON "Patient_Risk_Assessment"."session_no"="Chemical_Dependency"."session_no") LEFT OUTER JOIN "psychprod"."dbo"."Global_Code" "Global_Code_Drug" ON "Chemical_Dependency"."drug"="Global_Code_Drug"."code") LEFT OUTER JOIN "psychprod"."dbo"."Global_Code" "Global_Code_Legal" ON "Patient_Legal"."crt_disp"="Global_Code_Legal"."code"

 

[lbass]

Please show the WHERE clause as well. I am assuming that your entire selection formula is passing to the SQL query. If not, you should also copy/paste in your selection formula (from report->selection formulas->record.

-LB

 

[mchambers]

I do not understand what you mean above.

Here is my where statement:

WHERE  "Global_Code_Sex"."category"='SEX' AND "Global_Code_Occu"."category"='OS' AND "Global_Code_Stud"."category"='SS' AND "Global_Code_Educ"."category"='ED' AND "Global_Code_LA"."category"='LA' AND "Global_Code_RS"."category"='RS' AND "Global_Code_Legal"."category"='CRTDISP' AND "Global_Code_primIn"."category"='PINCOSRC' AND "Global_Code_Drug"."category"='RISKDRUGS' AND "Global_Code_Race"."category"='R' AND "Global_Code_MStatus"."category"='MS' AND "Global_Code_Sal"."category"='SA'
 ORDER BY "Patient"."patient_id"

 

[lbass]

No, you should remove ALL where clauses. You have already linked on these fields, so the description in the code table will automatically only show the description for the field you linked on.

Adding the codes table multiple times should NOT have affected the number of records, since there is a one-to-one relationship between each field and the code/description, so if you are getting inflated record counts, you should take a look at your other joins.

-LB