Help with duplicates/row inflation

[Trogg]

Hi all and thanks in advance for your help. I am using CR 2011 having trouble supressing duplicates or row inflation. I'm puling allergies
on patients by allergy type id (ie. Med allergy type = 4714, Food = 4715, etc.).I am using formula fields in the detail section to pull the allergies for the different types like so:

if{TSM250_ALLERGY_Meds.allergy_type_id}=4715 then
{TSM255_PERSON_ALLERGY_Meds.alg_desc} else ""

if{TSM250_ALLERGY_Food.allergy_type_id}=4714 then
{TSM255_PERSON_ALLERGY_Food.alg_desc} else ""

I want to display them side by side as below but as you can see the food allergy gets repeated for each instance of a Med allergy.
Med Allergy[tab]Food Allergy
Bactrim[tab][tab] MISC-FOOD
Latex[tab][tab][tab] MISC-FOOD
[tab][tab][tab][tab][tab] MISC-FOOD
Opana[tab][tab][tab] MISC-FOOD
Penicillins[tab][tab] MISC-FOOD
Thorazine[tab][tab] MISC-FOOD

It should look like this:
Med Allergy[tab]Food Allergy
Bactrim[tab][tab] MISC-FOOD
Latex
Opana
Penicillins
Thorazine

I'll have another one to add(Environmental Allergies) but didn't want to complicate the explanation. Only one group so far on Med Rec number so that it prints one patient per page. If I can provide any more info please let me know and thanks.

 

[IanWaterman]

Just right click miscfood field and select format and check supress if duplicated

Ian

 

[Trogg]

Sorry.. should have mentioned that has already been done... also the detail section has been supressed if blank and the report is set for Select Distinct records. So I'm pretty sure it is not a duplicate issue but a row inflation issue.

 

[IanWaterman]

Group by Food allergy and then get group header field to underlay following fields, remove food allergy from details, move your Med Allergy field over so food does not overlap Med allergy text

Ian

 

[Trogg]

Remove Food allergy from details and put it where? If I put it in the header I will only get one allergy if they have multiple food allergies. Also if there are multiple food allergies the row inflation affects Med allergies... hope this is making some sense.

 

[IanWaterman]

I think you might have to sort this out using a SQL command. Please show SQL generated by Crystal.

The simple joins available in Crystal are probably causing your dupes, and trying to eliminate them in report could be impossible

Ian

 

[Trogg]

Here is the SQL before making the changes you suggested...

SELECT DISTINCT "TPM300_PAT_VISIT"."vst_ext_id", "TPM300_PAT_VISIT"."pat_cat_cd", "TPM300_PAT_VISIT"."adm_ts", "TPM300_PAT_VISIT"."vst_sta_cd", "TPM300_PAT_VISIT"."med_rec_no", "TSM040_PERSON_HDR"."bth_ts", "TSM040_PERSON_HDR"."lst_nm", "TSM040_PERSON_HDR"."fst_nm", "TSM040_PERSON_HDR"."mid_nm", "TSM180_Sex"."cod_dtl_ds", "TSM180_PatType"."cod_dtl_ds", "TSM950_LOCATION_Nur_Station"."loc_ds", "TSM180_Cgvr_Func"."cod_dtl_ext_id", "TSM040_PERSON_Cgvr"."lst_nm", "TSM040_PERSON_Cgvr"."fst_nm", "TSM040_PERSON_Cgvr"."mid_nm", "TSM950_LOCATION_Room"."loc_ds", "TPM300_PAT_VISIT"."adm_diag_note", "TSM255_PERSON_ALLERGY_Meds"."alg_desc", "TSM250_ALLERGY_Meds"."allergy_type_id", "TSM250_ALLERGY_Food"."allergy_type_id", "TSM255_PERSON_ALLERGY_Food"."alg_desc"
FROM ((((((((((((("paragon"."dbo"."TSM040_PERSON_HDR" "TSM040_PERSON_HDR" INNER JOIN "paragon"."dbo"."TPM300_PAT_VISIT" "TPM300_PAT_VISIT" ON "TSM040_PERSON_HDR"."psn_int_id"="TPM300_PAT_VISIT"."psn_int_id") INNER JOIN "paragon"."dbo"."TSM180_MST_COD_DTL" "TSM180_Sex" ON "TSM040_PERSON_HDR"."sex_cd"="TSM180_Sex"."cod_dtl_int_id") INNER JOIN "paragon"."dbo"."TSM255_PERSON_ALLERGY" "TSM255_PERSON_ALLERGY_Meds" ON "TSM040_PERSON_HDR"."psn_int_id"="TSM255_PERSON_ALLERGY_Meds"."psn_int_id") INNER JOIN "paragon"."dbo"."TSM255_PERSON_ALLERGY" "TSM255_PERSON_ALLERGY_Food" ON "TSM040_PERSON_HDR"."psn_int_id"="TSM255_PERSON_ALLERGY_Food"."psn_int_id") INNER JOIN "paragon"."dbo"."TSM250_ALLERGY" "TSM250_ALLERGY_Food" ON "TSM255_PERSON_ALLERGY_Food"."allergy_int_id"="TSM250_ALLERGY_Food"."allergy_int_id") INNER JOIN "paragon"."dbo"."TSM250_ALLERGY" "TSM250_ALLERGY_Meds" ON "TSM255_PERSON_ALLERGY_Meds"."allergy_int_id"="TSM250_ALLERGY_Meds"."allergy_int_id") INNER JOIN "paragon"."dbo"."TSM180_MST_COD_DTL" "TSM180_PatType" ON "TPM300_PAT_VISIT"."pat_cat_cd"="TSM180_PatType"."cod_dtl_int_id") INNER JOIN "paragon"."dbo"."TSM950_LOCATION_REF" "TSM950_LOCATION_Nur_Station" ON "TPM300_PAT_VISIT"."loc_lvl_3_id"="TSM950_LOCATION_Nur_Station"."loc_int_id") INNER JOIN "paragon"."dbo"."TPM315_VISIT_CARE_GIVER" "TPM315_VISIT_CARE_GIVER" ON "TPM300_PAT_VISIT"."vst_int_id"="TPM315_VISIT_CARE_GIVER"."vst_int_id") INNER JOIN "paragon"."dbo"."TSM950_LOCATION_REF" "TSM950_LOCATION_Room" ON "TPM300_PAT_VISIT"."loc_lvl_5_id"="TSM950_LOCATION_Room"."loc_int_id") INNER JOIN "paragon"."dbo"."TPM114_CAR_GVR_FUNC" "TPM114_CAR_GVR_FUNC" ON "TPM315_VISIT_CARE_GIVER"."func_aso_int_id"="TPM114_CAR_GVR_FUNC"."func_aso_int_id") INNER JOIN "paragon"."dbo"."TPM100_CARE_GIVER" "TPM100_CARE_GIVER" ON "TPM114_CAR_GVR_FUNC"."car_gvr_int_id"="TPM100_CARE_GIVER"."car_gvr_int_id") INNER JOIN "paragon"."dbo"."TSM180_MST_COD_DTL" "TSM180_Cgvr_Func" ON "TPM114_CAR_GVR_FUNC"."func_int_id"="TSM180_Cgvr_Func"."cod_dtl_int_id") INNER JOIN "paragon"."dbo"."TSM040_PERSON_HDR" "TSM040_PERSON_Cgvr" ON "TPM100_CARE_GIVER"."psn_int_id"="TSM040_PERSON_Cgvr"."psn_int_id"
WHERE "TPM300_PAT_VISIT"."vst_sta_cd"=4742 AND "TPM300_PAT_VISIT"."pat_cat_cd"=4760 AND "TSM180_Cgvr_Func"."cod_dtl_ext_id"='ATTND'
ORDER BY "TPM300_PAT_VISIT"."med_rec_no"

 

[IanWaterman]

Just off home now, but I would consider building a command which joins two queries together. One showing Food allergies and one for Med allergies. Do you have a query tool best to do it there and you can then see results before going to effrt of adding to Crystal.

Hopefully that will eliminate duplications.

Ian