| Author |
Message |
BIM75
Newbie
Joined: 17 Jul 2008
Location: United States
Online Status: Offline
Posts: 7
|
  
Topic: Duplicate values in a string
Posted: 21 Jan 2009 at 10:47am |
Hello, I am using CR 10 with Oracle 9i. I am attempting to display multiple records in one line.
Original Data
Case # Products__
123 Product A
123 Product B
123 Product C
I would like Crystal to display it like this:
Case # Products__
123 Product A, Product B, Product C
or if there are no products for the case then it should state 'N/A' in the products column.
I am using 'Case #' as a group and have the following formulas:
//DETAILS
whileprintingrecords;
if {PRODUCT.DRUG_ROLE} = "CONC"
then stringvar cp := cp + {PRODUCT.GENERIC} +", "
else if length(cp) = 0
then stringvar cp := cp + "N/A; "
else stringvar cp := cp
//HEADER
whileprintingrecords;
stringvar cp := "";
//FOOTER
whileprintingrecords;
stringvar cp;
left(cp,len(cp)-2)
Cases that do not have a product are properly displaying 'N/A' however if a case has at least one product the report is displaying as follows:
Case # Products
123 N/A; Product A, Product A, Product A, Product A........etc.
I need to eliminate the N/A if a product exists and only display each unique product one time per case. Any suggestions are greatly appreciated. Thanks!!
|
 IP Logged |
|
|
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 21 Jan 2009 at 11:37am |
Try changing your detail formula like this:
whileprintingrecords;
stringvar cp;
if {PRODUCT.DRUG_ROLE} = "CONC"
then
if length(cp) > 0 then
cp := cp + ', ' + {PRODUCT.GENERIC}
else
cp := {PRODUCT.GENERIC}
This will prevent the trailing comma in your string.
Then change your footer formula as follows: whileprintingrecords;
stringvar cp;
if {table.CASE#} <> next({table.CASE#}) then
if length(cp) = 0 then
cp := "N/A"
else cp := cp;
cp
This way you're not checking for a zero length until you're on the last record of the group, so you're only setting the "N/A" if there really are no records.
-Dell Edited by hilfy - 21 Jan 2009 at 11:38am
|
|
|
| IP Logged |
|
BIM75
Newbie
Joined: 17 Jul 2008
Location: United States
Online Status: Offline
Posts: 7
|
Posted: 21 Jan 2009 at 12:21pm |
Thanks for your fast response. This worked partially as I no longer have the 'N/A' displayed with products. Unfortunately I still have the problem where a single product is duplicated multiple times for the case;
also the last record on the report doesn't display any data at all (no product or N/A) under products when it had one listed before (I also double checked the database to verify that this case has a product)
Here is an example of what the it looks like now:
Case # Products
1 N/A
2 A, A, A, A, A,
A, A, A, A, A,
A, A, A, A, A
3 N/A
4
Thanks again!!
|
| IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 21 Jan 2009 at 1:18pm |
Ok, here's another update to the detail formula:
whileprintingrecords;
stringvar cp;
if {PRODUCT.DRUG_ROLE} = "CONC"
then
if length(cp) > 0 then
if InStr(cp, {PRODUCT.GENERIC}) > 0
then cp
else cp := cp + ', ' + {PRODUCT.GENERIC}
else cp := {PRODUCT.GENERIC}
-Dell
|
|
|
| IP Logged |
|
BIM75
Newbie
Joined: 17 Jul 2008
Location: United States
Online Status: Offline
Posts: 7
|
Posted: 21 Jan 2009 at 1:24pm |
That worked. No more duplicates  Now if I can figure out why the last record has no data it will be perfect. Any thoughts on why this formula would cause the final record to be blank?
Thanks for all your help so far, this has been a great learning experience!
|
| IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 21 Jan 2009 at 1:41pm |
I know why...I missed something. When you're in the footer of the last record, the next record is null. Comparison of a value to a null is null, which is neither true nor false so the rest of the if statement is not being evaluated. Try this:
whileprintingrecords;
stringvar cp;
if NextIsNull({table.CASE#} or ({table.CASE#} <> next({table.CASE#}))
then if length(cp) = 0 then
cp := "N/A"
else cp := cp;
cp
-Dell
|
|
|
| IP Logged |
|
BIM75
Newbie
Joined: 17 Jul 2008
Location: United States
Online Status: Offline
Posts: 7
|
Posted: 21 Jan 2009 at 6:31pm |
Ah, that makes sense now. Thanks again!!!
|
| IP Logged |
|
|
|
