Linking to a table causes duplicate lines to apear on report

[markajem]

Crystal v8.5
MSSQL

I am listing the inventory locations of each inventory item in each detail line. This works fine until I link a table to it. The table I am linking to it has multiple records for that item for that location which shows individual transactions for that item. What is happening is that when I link the other table to my report and insert fields from the INVHST file I get a detail line for every transaction for that item and location which of course inflates all of my totals. If I remove the table link then I again just get one detail line for each inv item for each location. Is there a way to link the file so this does not happen. My goal is to be able to link to the table, not get multiple lines of duplicate data for each record and then in the table Ihave just linked to pull only one record that matches my criteria.

Example:

[tt]

INVLOC INVHST

|------------| |--------------|
|itemnumber | ======> |itemnumber |
|location | ======> |location |
|qtyonhand | |receiptdate |
|description | |receiptnumber |
|------------| |receiptqty |
|--------------|

[/tt]

Before I link the INVHST table to the INVLOC table and insert any fields my detail looks like this.

[tt]

ITEM # DESCRPT LOCATION QTYONHND RCTDTE RCTQTY

gh1 38273 Widget
det WHRS201 3,245
det WHRS202 3,298
gf1 Total ==> 6,543


[/tt]

If I link the INVHST table to my INVLOC table then as I said for ever record in the INVHST table there is for the item and that location it will duplicate the detail lines that many times.

So if there have been 4 receipts for item #38273 in location WHRS201 and 3 for WHRS202 I will get these results:

[tt]

ITEM # DESCRPT LOCATION QTYONHND RCTDTE RCTQTY

gh1 38273 Widget
det WHRS201 3,245 01/30/01 837
det WHRS201 3,245 03/28/01 472
det WHRS201 3,245 05/15/01 776
det WHRS201 3,245 07/23/01 773
det WHRS202 3,298 03/17/01 998
det WHRS202 3,298 04/16/01 553
det WHRS202 3,298 07/15/01 554
gf1 Total ==> 22,874


[/tt]

Now keep in mind I am only interested in one of the many records in the INVHST table. But I can't even use a formula to pull only that one record I want becaues the moment I link the tables I get the multiple lines of detail. I also have not even inserted any fields such as RCTQTY or RCTDTE yet.

Thanks for any help
Mark
Email: markanas333@hotmail.com

 

[skuhlman]

This becomes a question of what you want to show in your Receipt Date and Receipt Quantity columns. There are several ways to look at it. I'll take a guess at one. Let's say you wanted to show the most recent receipt date and the total quantity received:

GH1 - ITEM
GH2 - Location
Detail - SUPRESSED
GF2 - Location, FmlMaxReceiptDate, FmlSumQtyReceived
GF1 - Item, FmlItemSum

The formulas for Maximum Date, and Sum of Quantity Received should be pretty straight forward. The Formula for Sum of Items without repeating values could be a little trickier, but is doable. Need more help... just reply.

 

[markajem]

Okay skuhlman, now it is starting to clear up a little since your post. Thank you. What I am attempting to do is grab the very first record only. This is because the purchase receipts had been done by the integration manager. Instead of the receipts from the old system going in as purchase receipts they went in as inventory adjustments. I do not know if they could have gone in as purchase receipts but I had anticipated them going in as purchase receipts and prepared the report to pull from a specific table and only needed to use one record because that all that would have been written is one record. Now the receipts from the old system went in as inventory adjustments while the new receipts are going in as purchase receipts in a totally different table.

Example:

This table INVLOC will have only one record for each item and location whereas the INVHST table will have many records. When a purchase order is received thru the Purchase order module it updates the fields lstrcptdate and lstrcptqty from the INVLOC table. Since the integration entered the old receipts as inventory adjustments it did not hit the table INVLOC and the values are 0 for qty and date so there for have to go to the INVHST table and grab the first record from there instead. Luckly though there were two batchs done only for all receipts from the old system. They were 12/31/01 and 10/20/02.

[tt]
INVLOC INVHST

|------------| |--------------|
|itemnumber | ======> |itemnumber |
|location | ======> |location |
|qtyonhand | |trnsdate |
|description | |trnsqty |
|lstrcptdate | |rrnsmodule |
|lstrcptqty | |trnstype |
|------------| |--------------|

[/tt] Mark
Email: markanas333@hotmail.com

 

[markajem]

Anyone have any further thoughts on this?

Thanks
Mark
Mark
Email: markanas333@hotmail.com

 

[skuhlman]

I doubt this is the best way, but it works:

(RH)
GH1 Item Description
(GH2) (Location) (@FmlFirstDate)
[Detail] Location trnsdate @fmlTrnsQty (@fmlTQRunningTot)
(GH2)
(GH1)
RF

Sections/fields in parenthesis can be suppressed. The Detail section gets a conditional suppression.

Formulas:

@FmlFirstDate--------------------------------
whileprintingrecords ;
Global datetimevar varFirstDate ;
varFirstDate := {transdate}

@fmlTrnsQty----------------------------------
whileprintingrecords ;
global datetimevar varFirstDate ;
global numbervar varFirstQty ;
varFirstQty := iif({transdate}=varFirstDate,trnsqty,0)

@fmlTQRunningTot-----------------------------
whileprintingrecords;
global numbervar varFirstQty ;
global numbervar varFQRunningTotal ;
varFQRunningTotal := varFQRunningTotal + varFirstQty

[Conditional Suppression on Detail Line]-----
WhilePrintingRecords ;
global Numbervar varFirstDate ;
varFirstDate<>{transdate}

More running total formulas can be added if subtotals are needed

 

[markajem]

Skuhlman - Thanks for your replies.

I am not looking for totals, just the most recent receipt date and most recent receipt qty. But since I have been concentrating so much on this I did fail to mention that not only do I need to display the most recent receipt date and qty but this is based on the selection criteria also. Another words the user will enter a parameter date. For example he would only want the report to display records for an item and show the most recent receipt if that most recent receipt was prior to the two dates range. So if he entered Oct 1, 2002 he would only want the items where there have been receipts prior to that date even if he was running that report on Dec 31, 2002. he wouldnot want to see any items where there was receiving on that item between Oct 1, 2002 and Dec 31, 2002. Thisis an inventory aging report and items where there has been receipt activity would not interest him. he is looking for old and slow moving inventory.

Maybe this throws a monkey wrench in this whole thing now. Also if I were to add more groups than I have already I would also be dealing with resetting many of my manual formulas in other areas of the report because I have a subreport in the main report and using Shared Variables in the main and subreports. For this reason I had to create formulas for sub totals and grand totals for 3 columns. Approximately 10 formulas becuse of the many groups i already have. The report breaks down by ItemClass, Subclass and then finally item number with the different warehouse locations.

If the integration was done correctly, not meaning if the person did it correctly but if the proper fields were populated correctly this portion of the report should have been the easiest, instead it is now themost difficult part. I have also discussed this with another crystal consultant and his feelings are that maybe the fields in the INVLOC file should be populated using MS Access or some other program so that the report can just be designed the way it was originally intended to be. Just pulling from that one file and one record. I am in the process of discussing that now with the company.


Thanks Mark
Email: markanas333@hotmail.com

 

[skuhlman]

These additional requirements DO significantly change the specifications and therefor the aproach in getting the needed results. Were I the one doing this project, the first thing I would want to do is to correct the data to the way it should have been in the original design if that is possible. If that is not possible, I would take the aproach of generating a temporary working file. I've only done that with SQL Server 2000 stored procedures so far, so this may be the end of my usefullness on this issue. Good luck.