Problem with Crystal Report
I have an invoice and order confirmation in Crystal which is duplicating some of the items on the order but only sometimes. When the same invoice is reprinted then there is no duplication.
The application is Sage BusinessVision with Crystal run-time engine version 10
There is a long history behind this issue going back to 2007. The issue started to appear sometime in 2007 and was reported by some customers. BusinessVision QA at the time and later on attempted to replicate the issue but so far to this day no one has ever be able to replicate this. I have attempted to replicate the issue for countless hours with no success.
I'm not sure why but someone from QA discovered that by adding the following formula to the report for suppression in the detail section the problem went away.
Now the problem is that as soon as I start customizing the report the problem comes back. in my case I have a custom report for one of my customers where I'm grouping the items on an invoice. (attached). Sage is telling me that they can't help me because of the customization I have.
I've been insisting with the Sage QA team to get the issue resolve but they claim that it is a Crystal bug but are not sure because no one has ever been able to replicate it.
Has someone else ever come across this? I have attached my custom report and also the standard report for you to look at.
Invoice---Plain-Paper.rpt
Invoice.rpt
The application is Sage BusinessVision with Crystal run-time engine version 10
There is a long history behind this issue going back to 2007. The issue started to appear sometime in 2007 and was reported by some customers. BusinessVision QA at the time and later on attempted to replicate the issue but so far to this day no one has ever be able to replicate this. I have attempted to replicate the issue for countless hours with no success.
I'm not sure why but someone from QA discovered that by adding the following formula to the report for suppression in the detail section the problem went away.
previous({SALES_HISTORY_DETAIL.NUMBER}) = {SALES_HISTORY_DETAIL.NUMBER}
and previous({SALES_HISTORY_DETAIL.RECNO}) = {SALES_HISTORY_DETAIL.RECNO}Now the problem is that as soon as I start customizing the report the problem comes back. in my case I have a custom report for one of my customers where I'm grouping the items on an invoice. (attached). Sage is telling me that they can't help me because of the customization I have.
I've been insisting with the Sage QA team to get the issue resolve but they claim that it is a Crystal bug but are not sure because no one has ever been able to replicate it.
Has someone else ever come across this? I have attached my custom report and also the standard report for you to look at.
Invoice---Plain-Paper.rpt
Invoice.rpt
Please list database information.
I had similar problem in CR2007 where II started a new report and linked to the Access 2007 data table. I restarted the report completely, and clicked finish in the UI screen for picking database and data access layer technology (ADO.NET 2) before I clicked next. if I clicked next, it adds an option for data table that caused data view problems. the problem went away.
I had similar problem in CR2007 where II started a new report and linked to the Access 2007 data table. I restarted the report completely, and clicked finish in the UI screen for picking database and data access layer technology (ADO.NET 2) before I clicked next. if I clicked next, it adds an option for data table that caused data view problems. the problem went away.
I saw the database version and I was using CR 2007 Full with Service Packs.
Disclaimer: I know nothing about Sage, so I'm speaking in generalities here.
The reason for the duplicate items is quite simple. You're getting more than one record for those items. That's why that suppression formula fixes (hides) the problem. It says if this record has the same NUMBER and RECNO as the previous record, suppress this record. The extra records are probably coming from one of the secondary tables, like a product table that has two records for the same product, so any line item for that product shows up twice.
You said that if the invoice is reprinted, you don't get the duplication. Is the invoice reprinted immediately, or some time later? If it's reprinted immediately, then it may be that the printing process itself is eliminating the extra records, like there's a temporary record that is removed when the invoice is printed. If it's reprinted some time later, then something is removing the extra records in between the two printings.
FWIW, if you go to Database > "Show SQL Query", CR should show you the query that it's using. If you have the capability to run a query in Sage manually, you could try running that query and looking at the "raw" data. If nothing else, you could post the query here and we could take a look at it, assuming that there is nothing "sensitive" in it.
James
The reason for the duplicate items is quite simple. You're getting more than one record for those items. That's why that suppression formula fixes (hides) the problem. It says if this record has the same NUMBER and RECNO as the previous record, suppress this record. The extra records are probably coming from one of the secondary tables, like a product table that has two records for the same product, so any line item for that product shows up twice.
You said that if the invoice is reprinted, you don't get the duplication. Is the invoice reprinted immediately, or some time later? If it's reprinted immediately, then it may be that the printing process itself is eliminating the extra records, like there's a temporary record that is removed when the invoice is printed. If it's reprinted some time later, then something is removing the extra records in between the two printings.
FWIW, if you go to Database > "Show SQL Query", CR should show you the query that it's using. If you have the capability to run a query in Sage manually, you could try running that query and looking at the "raw" data. If nothing else, you could post the query here and we could take a look at it, assuming that there is nothing "sensitive" in it.
James
ASKER
The strange thing is that this only happens once in a while. My customer calls me about every 2 to 3 weeks with one invoice and they generate 150 to 200 invoices a day.
When this happens the reprints are done a day later or after the customer calls back complaining about items being duplicated. No one handle these invoice manually, they are automatically emailed and faxed without user intervention.
I have a PSQL Btrieve connection and I don't have the option to show the SQL query
When this happens the reprints are done a day later or after the customer calls back complaining about items being duplicated. No one handle these invoice manually, they are automatically emailed and faxed without user intervention.
I have a PSQL Btrieve connection and I don't have the option to show the SQL query
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
In my case I think it is because I'm grouping the details in the report and that is why the suggested formula does not work.
I will try you suggestions to see if I can find out where the problem is.
I will try you suggestions to see if I can find out where the problem is.
Upon report creation, when you attach the report to a database is where the problem occurs.
On a UI, there if you press Next button, then CR 2008 Enterprise will AUTOMATICALLY, JOIN DATA TABLES after FINISH button.
So there are two screens,
1) Pick Data Source and Data Adapter (ADO for example)
2) JOIN DATA TABLES
The second one cause problems.
On a UI, there if you press Next button, then CR 2008 Enterprise will AUTOMATICALLY, JOIN DATA TABLES after FINISH button.
So there are two screens,
1) Pick Data Source and Data Adapter (ADO for example)
2) JOIN DATA TABLES
The second one cause problems.
FWIW, my basic assumption has been that the "duplicate" records are not really duplicates, per se. For example, we had some software that stored the quantity for each item on an invoice in a separate table. Some items were sold in pounds and some were sold in other units. The software would create one quantity record in the sold units, and one in pounds. So, if you simply linked to that quantity table, you'd get some items "duplicated", once for each quantity record. I've been assuming that you're running into something like that.
However, it's also possible that you have some problem that is actually causing duplicate records to be created for some items. Hopefully it's not that, because that would probably be much harder to track down and fix.
Anyway, good luck with it.
James
However, it's also possible that you have some problem that is actually causing duplicate records to be created for some items. Hopefully it's not that, because that would probably be much harder to track down and fix.
Anyway, good luck with it.
James
ASKER
Yes my scenario is your last comment
Apparently not even the developers have been able to replicate the issue
Apparently not even the developers have been able to replicate the issue
ASKER