vandersk
asked on
Concatenating a field value in Crystal Reports 10 if more than one duplicate row is retrieved.
Dear All
I have a situation where ITEMS can have more than one ITEM_STATUSes.
However I would like to concatenate the statused if more than 1 row of ITEM_STATUSes appear.
At the moment my query retrieves the following:
ITEM_BARCODE, TITLE, LOCATION_CODE, ITEM_STATUS_DESC
37212000495505|Courses of training and instruction in post-graduate medicine|MAIN|Missing
37212000495505|Courses of training and instruction in post-graduate medicine|MAIN|Not Charged
37212007361767|Gustave Flaubert : a documentary|MAIN|In Process
37212007361767|Gustave Flaubert : a documentary|MAIN|Not Charged
37212005183999|Dictionary of medieval Latin|MAIN|Not Charged
37212005183999|Dictionary of medieval Latin|MAIN|At Bindery
37212002572285|Hsien tai han|MAIN|Not Charged
37212002572285|Hsien tai han|MAIN|In Transit Discharged
What I was thinking of doing, is to have the ITEM_STATUS as one field (concatenated) and separate the values with a comma.
ITEM_BARCODE, TITLE, LOCATION_CODE, ITEM_STATUS_DESC
37212000495505|Courses of training and instruction in post-graduate medicine|MAIN|Not Charged, Missing
. . .OR. . .
If one can setup an output field that would do the following types of converts.
Missing AND Not Charged > Missing
In Process AND Not Charged > In Process
At Bindery AND NOT Charged > At Bindery
Damaged AND Not Charged > Damaged
Discharged > Discharged
Charged > Charged
The end result would be a MS Excell sheet or printed list that a staff memeber can use. Instead of seing the double lines the staff memeber can see that the item is Not Charged i.e suppose to be on the shelf, but missing or what ever.
Would something like this be possible?
Ideally it should be Crystal Reports based, since these could be scheduled to run when ever or Ad. Hoc.
Any ideas, or feedback are welcome.
Regards
I have a situation where ITEMS can have more than one ITEM_STATUSes.
However I would like to concatenate the statused if more than 1 row of ITEM_STATUSes appear.
At the moment my query retrieves the following:
ITEM_BARCODE, TITLE, LOCATION_CODE, ITEM_STATUS_DESC
37212000495505|Courses of training and instruction in post-graduate medicine|MAIN|Missing
37212000495505|Courses of training and instruction in post-graduate medicine|MAIN|Not Charged
37212007361767|Gustave Flaubert : a documentary|MAIN|In Process
37212007361767|Gustave Flaubert : a documentary|MAIN|Not Charged
37212005183999|Dictionary of medieval Latin|MAIN|Not Charged
37212005183999|Dictionary of medieval Latin|MAIN|At Bindery
37212002572285|Hsien tai han|MAIN|Not Charged
37212002572285|Hsien tai han|MAIN|In Transit Discharged
What I was thinking of doing, is to have the ITEM_STATUS as one field (concatenated) and separate the values with a comma.
ITEM_BARCODE, TITLE, LOCATION_CODE, ITEM_STATUS_DESC
37212000495505|Courses of training and instruction in post-graduate medicine|MAIN|Not Charged, Missing
. . .OR. . .
If one can setup an output field that would do the following types of converts.
Missing AND Not Charged > Missing
In Process AND Not Charged > In Process
At Bindery AND NOT Charged > At Bindery
Damaged AND Not Charged > Damaged
Discharged > Discharged
Charged > Charged
The end result would be a MS Excell sheet or printed list that a staff memeber can use. Instead of seing the double lines the staff memeber can see that the item is Not Charged i.e suppose to be on the shelf, but missing or what ever.
Would something like this be possible?
Ideally it should be Crystal Reports based, since these could be scheduled to run when ever or Ad. Hoc.
Any ideas, or feedback are welcome.
Regards
amit_g
You should probably try to do this in SQL query. What DB are you using and what does the query look like?
This is relatively easy to get
ITEM_BARCODE, TITLE, LOCATION_CODE, ITEM_STATUS_DESC
37212000495505|Courses of training and instruction in post-graduate medicine|MAIN|Not Charged, Missing
Add a group to the report
Group on the BarCode field
Add a formula to the report header
Name - DeclVars
Formula
WhilePrintingRecords;
Global StringVar strStatus := "";
In the group header
Name - ResetStatus
Formula
WhilePrintingRecords;
Global StringVar strStatus;
strStatus := "";
In the details section
Name - BuildStatus
Formula
WhilePrintingRecords;
Global StringVar strStatus;
strStatus := strStatus & ", " & {ITEM_STATUS_DESC};
""
In the group footer
Put the other fields and this formula
Name - ResetStatus
Formula
WhilePrintingRecords;
Global StringVar strStatus;
mid(strStatus ,3)
The other is a bit more complex and requires analysis of the potential pairs
Will you always have the status in a specified order as in MISSING, NOT CHARGED?
Something like this
WhilePrintingRecords;
Global StringVar strStatus;
If (InStr(strStatus, ", Charged") > 0) then
"Charged"
else If (InStr(strStatus,"Discharg
"Discharged"
Else If (InStr(strStatus,"Missing"
"Missing"
Else If (InStr(strStatus,"In Process")) then
"In Process"
Else If (InStr(strStatus,"At Bindery")) then
"At Bindery"
Else If (InStr(strStatus,"Damaged"
"Damaged"
Else
strStatus
mlmcc
ITEM_BARCODE, TITLE, LOCATION_CODE, ITEM_STATUS_DESC
37212000495505|Courses of training and instruction in post-graduate medicine|MAIN|Not Charged, Missing
Add a group to the report
Group on the BarCode field
Add a formula to the report header
Name - DeclVars
Formula
WhilePrintingRecords;
Global StringVar strStatus := "";
In the group header
Name - ResetStatus
Formula
WhilePrintingRecords;
Global StringVar strStatus;
strStatus := "";
In the details section
Name - BuildStatus
Formula
WhilePrintingRecords;
Global StringVar strStatus;
strStatus := strStatus & ", " & {ITEM_STATUS_DESC};
""
In the group footer
Put the other fields and this formula
Name - ResetStatus
Formula
WhilePrintingRecords;
Global StringVar strStatus;
mid(strStatus ,3)
The other is a bit more complex and requires analysis of the potential pairs
Will you always have the status in a specified order as in MISSING, NOT CHARGED?
Something like this
WhilePrintingRecords;
Global StringVar strStatus;
If (InStr(strStatus, ", Charged") > 0) then
"Charged"
else If (InStr(strStatus,"Discharg
"Discharged"
Else If (InStr(strStatus,"Missing"
"Missing"
Else If (InStr(strStatus,"In Process")) then
"In Process"
Else If (InStr(strStatus,"At Bindery")) then
"At Bindery"
Else If (InStr(strStatus,"Damaged"
"Damaged"
Else
strStatus
mlmcc
ASKER
Hi There mlmcc
I have created a Formula for the more complex bit called "ItemStatus2"
When i try to run the report I get the message: "A boolean is required here"
Also a straight listing of the data will always provide a "Not Charged" and "Missing" or what ever as in:
37212000495505|Courses of training and instruction in post-graduate medicine|MAIN|Not Charged
37212000495505|Courses of training and instruction in post-graduate medicine|MAIN|Missing
37212007361767|Gustave Flaubert : a documentary|MAIN|Not Charged
37212007361767|Gustave Flaubert : a documentary|MAIN|In Process
37212005183999|Dictionary of medieval Latin|MAIN|Not Charged
37212005183999|Dictionary of medieval Latin|MAIN|At Bindery
Hopes this helps
Thanks for the help so far.
Regards,
vandersk
I have created a Formula for the more complex bit called "ItemStatus2"
When i try to run the report I get the message: "A boolean is required here"
Also a straight listing of the data will always provide a "Not Charged" and "Missing" or what ever as in:
37212000495505|Courses of training and instruction in post-graduate medicine|MAIN|Not Charged
37212000495505|Courses of training and instruction in post-graduate medicine|MAIN|Missing
37212007361767|Gustave Flaubert : a documentary|MAIN|Not Charged
37212007361767|Gustave Flaubert : a documentary|MAIN|In Process
37212005183999|Dictionary of medieval Latin|MAIN|Not Charged
37212005183999|Dictionary of medieval Latin|MAIN|At Bindery
Hopes this helps
Thanks for the help so far.
Regards,
vandersk
Can you show the formula? Where are you using it?
mlmcc
mlmcc
ASKER
The formula does not show coz of the error message: "A boolean is required here"
Added formula to the Group Footer
Formula named: ItemStatus2
Global StringVar strStatus;
If (InStr(strStatus, ", Charged") > 0) then
"Charged"
Else If (InStr(strStatus,"Discharg
"Discharged"
Else If (InStr(strStatus,", Not Charged, Missing")) then
"Missing"
Else If (InStr(strStatus,",Not Charged, In Process")) then
"In Process"
Else If (InStr(strStatus,",Not Charged, At Bindery")) then
"At Bindery"
Else If (InStr(strStatus,",Not Charged, Damaged")) then
"Damaged"
Else strStatus
Added formula to the Group Footer
Formula named: ItemStatus2
Global StringVar strStatus;
If (InStr(strStatus, ", Charged") > 0) then
"Charged"
Else If (InStr(strStatus,"Discharg
"Discharged"
Else If (InStr(strStatus,", Not Charged, Missing")) then
"Missing"
Else If (InStr(strStatus,",Not Charged, In Process")) then
"In Process"
Else If (InStr(strStatus,",Not Charged, At Bindery")) then
"At Bindery"
Else If (InStr(strStatus,",Not Charged, Damaged")) then
"Damaged"
Else strStatus
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Just to finish off and add some value for you guys I will recap as to what i did in the end.
The SQL:
SELECT
BIB_ITEM.BIB_ID,
ITEM_BARCODE.ITEM_BARCODE,
ITEM_VW.CALL_NO,
ITEM_VW.NORMALIZED_CALL_NO
ITEM_VW.ENUMERATION,
ITEM_VW.CHRONOLOGY,
BIB_TEXT.TITLE,
BIB_TEXT.EDITION,
ITEM_VW.GOV_LOCATION_CODE,
CALL_NO_TYPE.CALL_NO_DESC,
ITEM_STATUS_TYPE.ITEM_STAT
FROM (ITEM_BARCODE INNER JOIN ((BIB_TEXT INNER JOIN BIB_ITEM ON BIB_TEXT.BIB_ID = BIB_ITEM.BIB_ID) INNER JOIN ((ITEM_STATUS INNER JOIN ITEM_VW ON ITEM_STATUS.ITEM_ID = ITEM_VW.ITEM_ID) INNER JOIN ITEM_STATUS_TYPE ON ITEM_STATUS.ITEM_STATUS = ITEM_STATUS_TYPE.ITEM_STAT
WHERE ITEM_VW.GOV_LOCATION_CODE = '{?LocatioCode}'
ORDER BY ITEM_VW.CALL_NO, ITEM_VW.ENUMERATION
Add a group to the report
Group on the BarCode field
Add a formula to the report header
Name - DeclVars
Formula:
WhilePrintingRecords;
Global StringVar strStatus := "";
In the group header
Name - ResetStatus
Formula:
WhilePrintingRecords;
Global StringVar strStatus;
strStatus := "";
In the details section
Name - BuildStatus
Formula
WhilePrintingRecords;
Global StringVar strStatus;
strStatus := strStatus & ", " & {ITEM_STATUS_DESC};
In the group footer
Put the fields from the SQL and this formula in the group footer:
Name - ResetStatus2
Formula
WhilePrintingRecords;
Global StringVar strStatus;
mid(strStatus, 3)
Also in the group footer (NOTE: Status are Case Sensitive):
Name - ItemStatus2
Formula:
WhilePrintingRecords;
Global StringVar strStatus;
If
(InStr(strStatus,"At Bindery") > 0) then "At Bindery"
Else If (InStr(strStatus,"Missing"
Else If (InStr(strStatus,"Damaged"
Else If (InStr(strStatus,"In Process") > 0) then "In Process"
Else If (InStr(strStatus,"In Transit Discharged") > 0) then "In Transit Discharged"
Else If (InStr(strStatus,"Hold Request") > 0) then "Hold Request"
Else If (InStr(strStatus,"Discharg
Else If (InStr(strStatus,"Not Charged") > 0) then "Not Charged"
Else If (InStr(strStatus,"Charged"
Else If (InStr(strStatus,"In Transit On Hold") > 0) then "In Transit on Hold"
Else strStatus
Thanks,
The SQL:
SELECT
BIB_ITEM.BIB_ID,
ITEM_BARCODE.ITEM_BARCODE,
ITEM_VW.CALL_NO,
ITEM_VW.NORMALIZED_CALL_NO
ITEM_VW.ENUMERATION,
ITEM_VW.CHRONOLOGY,
BIB_TEXT.TITLE,
BIB_TEXT.EDITION,
ITEM_VW.GOV_LOCATION_CODE,
CALL_NO_TYPE.CALL_NO_DESC,
ITEM_STATUS_TYPE.ITEM_STAT
FROM (ITEM_BARCODE INNER JOIN ((BIB_TEXT INNER JOIN BIB_ITEM ON BIB_TEXT.BIB_ID = BIB_ITEM.BIB_ID) INNER JOIN ((ITEM_STATUS INNER JOIN ITEM_VW ON ITEM_STATUS.ITEM_ID = ITEM_VW.ITEM_ID) INNER JOIN ITEM_STATUS_TYPE ON ITEM_STATUS.ITEM_STATUS = ITEM_STATUS_TYPE.ITEM_STAT
WHERE ITEM_VW.GOV_LOCATION_CODE = '{?LocatioCode}'
ORDER BY ITEM_VW.CALL_NO, ITEM_VW.ENUMERATION
Add a group to the report
Group on the BarCode field
Add a formula to the report header
Name - DeclVars
Formula:
WhilePrintingRecords;
Global StringVar strStatus := "";
In the group header
Name - ResetStatus
Formula:
WhilePrintingRecords;
Global StringVar strStatus;
strStatus := "";
In the details section
Name - BuildStatus
Formula
WhilePrintingRecords;
Global StringVar strStatus;
strStatus := strStatus & ", " & {ITEM_STATUS_DESC};
In the group footer
Put the fields from the SQL and this formula in the group footer:
Name - ResetStatus2
Formula
WhilePrintingRecords;
Global StringVar strStatus;
mid(strStatus, 3)
Also in the group footer (NOTE: Status are Case Sensitive):
Name - ItemStatus2
Formula:
WhilePrintingRecords;
Global StringVar strStatus;
If
(InStr(strStatus,"At Bindery") > 0) then "At Bindery"
Else If (InStr(strStatus,"Missing"
Else If (InStr(strStatus,"Damaged"
Else If (InStr(strStatus,"In Process") > 0) then "In Process"
Else If (InStr(strStatus,"In Transit Discharged") > 0) then "In Transit Discharged"
Else If (InStr(strStatus,"Hold Request") > 0) then "Hold Request"
Else If (InStr(strStatus,"Discharg
Else If (InStr(strStatus,"Not Charged") > 0) then "Not Charged"
Else If (InStr(strStatus,"Charged"
Else If (InStr(strStatus,"In Transit On Hold") > 0) then "In Transit on Hold"
Else strStatus
Thanks,
Glad i could help
mlmcc
mlmcc