• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 357
  • Last Modified:

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.
previous({SALES_HISTORY_DETAIL.NUMBER}) = {SALES_HISTORY_DETAIL.NUMBER}
and previous({SALES_HISTORY_DETAIL.RECNO}) = {SALES_HISTORY_DETAIL.RECNO}

Open in new window


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
0
Gerhardpet
Asked:
Gerhardpet
  • 4
  • 3
  • 3
1 Solution
 
GerhardpetAuthor Commented:
btw the database is a Pervasive SQL version 10
0
 
ajaytempCommented:
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.
0
 
ajaytempCommented:
I saw the database version and I was using CR 2007 Full with Service Packs.
0
[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

 
James0628Commented:
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
0
 
GerhardpetAuthor Commented:
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
0
 
James0628Commented:
Another possibility might be to make a copy of the report and change it to show more fields in the detail section - Basically, every field that's currently being used on the report.  What I call a "data dump".  The idea is to look for something different in the duplicate records that might tell you where they're coming from.  The first trick there is finding some duplicate records to check.  And it's possible that all of the fields that you're using, or even all of the fields in the records, are duplicated, in which case that may not tell you anything, but it's a place to start.

 Oh, and as for the problem coming back when you customize the report, I would think that the Previous suppression formula would still work, unless you're changing the order so that the records aren't sorted by NUMBER and RECNO.  Or, of course, if you're changing that suppression formula, that might be the problem.

 James
0
 
GerhardpetAuthor Commented:
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.
0
 
ajaytempCommented:
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.
0
 
James0628Commented:
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
0
 
GerhardpetAuthor Commented:
Yes my scenario is your last comment

Apparently not even the developers have been able to replicate the issue
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 4
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now