asmyatt
asked on
Crystal Reports - Duplicate records
I'm using crystal reports 11. When I execute my report, I'm getting duplicate records. I have narrowed it down to a formula. It's only when the formula is added to the report, it creates many, many duplicates.
Here is my formula:
if {mwsCustFieldDef.MWFieldNu
Both of these fields are bit fields. I had to use True for the YNValue field because I kept getting a boolean is required here. The YNValue field is just 1 and 0.
Any ideas?
The code below is my joins. The problem might be in the joins.
Here is my formula:
if {mwsCustFieldDef.MWFieldNu
Both of these fields are bit fields. I had to use True for the YNValue field because I kept getting a boolean is required here. The YNValue field is just 1 and 0.
Any ideas?
The code below is my joins. The problem might be in the joins.
mwlLoanApp.ID Right Outer Join mwlCustomField.LoanApp_ID
mwlCustomField.CustFieldDef_ID Right Outer Join mwsCustFieldDef.ID
I don't see this at all.
Adding a formula field into a report can't create duplicate records.
There will be the same number of records in the report as there was before you added the formula field.
As for the joins, yes, the problem might lie there.
If there can be multiple mwlLoanApp records for each mwlCustomField record and/or multiple mwlCustomField records for each mwsCustFieldDef record then what you see in the report could appear as duplicate records, depending on the fields that are displayed.
Adding a formula field into a report can't create duplicate records.
There will be the same number of records in the report as there was before you added the formula field.
As for the joins, yes, the problem might lie there.
If there can be multiple mwlLoanApp records for each mwlCustomField record and/or multiple mwlCustomField records for each mwsCustFieldDef record then what you see in the report could appear as duplicate records, depending on the fields that are displayed.
I tend to agree that in general adding a formula shouldn't change the record selection. However if one of the fields in the formula is using a field that isn't on the report and is the first field from one of the tables then the join may not have been used until it was needed to get records from the joined table.
mlmcc
mlmcc
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I've posted the record selection criteria (Report|Selection Formulas|Record.) and then the SQL Query in CR for all the joins. The report selection returns the right number of records, but when I add in the formula it creates the duplicates. Basically in our application we have a custom field we created as a check box. When the user checks the box in the application, it post a 1 in mwlCustomfield.YNValue, but only for mwsCustFieldDef.MWFieldNum
{mwlAppraiserNote.UpdatedOnDate} in dateadd("n",-20,currentdatetime) to currentdatetime and
{mwlLoanData.FinancingType} <> "F" or
{mwlAppraiserNote.UpdatedOnDate} in dateadd("n",-20,currentdatetime) to currentdatetime and
{mwlLoanData.FinancingType} = "F" and
{mwlLoanApp.CaseNum} <> '""'
SELECT DISTINCT "mwlAppraiserNote"."UpdatedOnDate", "mwlLoanApp"."LoanNumber", "mwlLoanData"."FinancingType", "mwlLoanApp"."CaseNum", "mwlAppraiserNote"."ApprCompanyName", "mwlAppraiserNote"."ApprContactName", "mwlSubjectProperty"."Street", "mwlSubjectProperty"."City", "mwlSubjectProperty"."State", "mwlSubjectProperty"."ZipCode", "mwlSubjectProperty"."ShortLegal", "mwlBorrower"."LastName", "mwsCustFieldDef"."MWFieldNum", "mwlCustomField"."YNValue"
FROM (((("INTERLINQE3"."dbo"."mwlBorrower" "mwlBorrower" LEFT OUTER JOIN ("INTERLINQE3"."dbo"."mwlSubjectProperty" "mwlSubjectProperty" LEFT OUTER JOIN "INTERLINQE3"."dbo"."mwlLoanApp" "mwlLoanApp" ON "mwlSubjectProperty"."LoanApp_ID"="mwlLoanApp"."ID") ON "mwlBorrower"."LoanAPP_ID"="mwlLoanApp"."ID") LEFT OUTER JOIN "INTERLINQE3"."dbo"."mwlLoanData" "mwlLoanData" ON "mwlLoanApp"."ID"="mwlLoanData"."ObjOwner_ID") RIGHT OUTER JOIN "INTERLINQE3"."dbo"."mwlAppraiserNote" "mwlAppraiserNote" ON "mwlLoanApp"."ID"="mwlAppraiserNote"."ObjOwner_ID") RIGHT OUTER JOIN "INTERLINQE3"."dbo"."mwlCustomField" "mwlCustomField" ON "mwlLoanApp"."ID"="mwlCustomField"."LoanApp_ID") RIGHT OUTER JOIN "INTERLINQE3"."dbo"."mwsCustFieldDef" "mwsCustFieldDef" ON "mwlCustomField"."CustFieldDef_ID"="mwsCustFieldDef"."ID"
WHERE (("mwlAppraiserNote"."UpdatedOnDate">={ts '2009-04-15 05:42:42'} AND "mwlAppraiserNote"."UpdatedOnDate"<{ts '2009-04-15 08:22:43'}) AND "mwlLoanData"."FinancingType"<>'F' OR ("mwlAppraiserNote"."UpdatedOnDate">={ts '2009-04-15 05:42:42'} AND "mwlAppraiserNote"."UpdatedOnDate"<{ts '2009-04-15 08:22:43'}) AND "mwlLoanData"."FinancingType"='F' AND "mwlLoanApp"."CaseNum"<>'""')
ORDER BY "mwlBorrower"."LastName"
You might need to use ( ) to get the order correct in the filter.
(
{mwlAppraiserNote.UpdatedO
{mwlLoanData.FinancingType
)
or
(
{mwlAppraiserNote.UpdatedO
{mwlLoanData.FinancingType
{mwlLoanApp.CaseNum} <> '""'
)
mlmcc
(
{mwlAppraiserNote.UpdatedO
{mwlLoanData.FinancingType
)
or
(
{mwlAppraiserNote.UpdatedO
{mwlLoanData.FinancingType
{mwlLoanApp.CaseNum} <> '""'
)
mlmcc
ASKER
mlmcc, I 'm getting the correct number of records if you take out the duplicates. I added the () and received the same results.
Thanks.
Thanks.
Can any of the fields be NULL?
mlmcc
mlmcc
I think the problem stems from the fact you are selecting/filtering based on the joined tables. In that case Crystal converts the joins to INNER
mlmcc
mlmcc
I agree that it looks like your JOIN's are probably the problem, in one way or another. However ...
In your record selection and query you have {mwlLoanApp.CaseNum} <> '""' (two double-quotes inside two single-quotes). Are you really looking for the records where that field has two double-quotes in it, or are you looking for the records where that field is empty? If it's the latter, change the record selection to just use "" or ''.
James
In your record selection and query you have {mwlLoanApp.CaseNum} <> '""' (two double-quotes inside two single-quotes). Are you really looking for the records where that field has two double-quotes in it, or are you looking for the records where that field is empty? If it's the latter, change the record selection to just use "" or ''.
James
I would have expected some sharing of the points in this Q.
In general, it's definitely possible to get duplicate records when using outer joins, since you'll get one record back for every record that matches the primary table, unless you limit the recordset from the secondary tables. If we have a better idea of what your report is doing, we might be better equipped to provide a solution.
~Kurt