Multiple Occurences of the same Item ID

[WinterDragon]

I know the the answer to this was posted here long ago, but for the life of me i can not locate it.

I have an Inventory report. ItemID - Qty Oh Hand - Etc Etc

The report will show the same ItemID multiple times.

158-10 1 description
158-10 1 description
158-10 2 description

I need to find out how to make each item id appear only once in the report.

Thanks in Advance

 

[synapsevampire]

Try posting technical information as well:

Crystal version
Database/connectivity used

You can group via the itemid and then place the fields in the group header or footer and suppress the details. Or you can select Database->Select Distinct Records, however I suspect the latter won't work because you probably didn't post a complete list of the fields being used.

-k

 

[WinterDragon]

Fields: ItemID, Qty On Hand, Description

these are the only 3 fields I am using. I am not Grouping fields or anything like that.

Example.

Item ID Qty On Hand Description
110-12 12 Adapter
111-15 1 Hose
158-10 1 Bearing
158-10 1 Bearing
158-10 2 Bearing


This example show 3 occurences of the same ItemID..

 

[synapsevampire]

A shame you decided not to answer my questions.

Try the menu option Database->Select Distinct Records

If that isn't available, you can create a group (Insert Group) and insert the item ID as the grouping.

You didn't mention what the output should be, should it sum the qty on hand field? If so, suppress the details, then right click the qty in the details and select insert->summary-Sum and select for the item id grouping.

-k

 

[WinterDragon]

I thought that I did answer your questions. In case I did not here is the jist.

I need a Piece of paper (report) with 3 things of information. The Item ID, The Qty On Hand, and the Part Description. I do not need a GROUP, or SUM, or Running Total. JUST those 3 pieces of information.

I do not want multiple listings of the same Part Number (ItemID)

Output is Nothing more than a List of Part numbers, The qty fro that part number and the description. I know this is very basic for you guys, but I dont need something elaborate, no need in over thinking this...

 

[synapsevampire]

I had requested:

"Try posting technical information as well:

Crystal version
Database/connectivity used"

Did you try what I have already suggested yet?

When you say you want ONLY one part number, note that the qty is different for each row:

158-10 1 Bearing
158-10 1 Bearing
158-10 2 Bearing

Which one do you want then, the last? If so, group on the part number (Insert->Group) and place the fields in the group footer, and then suppress the detail;s section.

-k

 

[WinterDragon]

My apologies. Crystal Reports 9. Connectivity is BTRIEVE (for Peachtree)

Make this simple. I do not want grouping, I do not want anything other than the 3 fields of information. I want the report to contain 3 (THREE) pieces of information.

ITEM ID, QTY ON HAND, and DESCRIPTION.

(NO GROUPS, NO TOTALS, NO ANYTHING ELSE)

I DO want it show ONLY ONE OCCURENCE of the ITEM ID (WHICH is NOT GROUPED with anything else.)

 

[synapsevampire]

...

A grouping is simple as I've demonstrated and doesn't change the data, and since your data isn't duplicates, you must filter the other roiws in some fashion.

One alternative is to select the section expert in the details section and use a suppression formula which would be something like:

{table.itemid} = previous({table.itemid))

This would limit it to the first occurence of each itemid

If you want the last of each itemid , try:

if not(onlastrecord) then
{table.itemid} = next({table.itemid))

Note that my last post asked which QTY you wanted which you also ignored.

-k

 

[WinterDragon]

There should be only one qty. When I first started Crystal, someone had posted a much simpler way of doing this, which basically eliminated the duplicate item ids. Something like Lineitem.itemid=0 or something to that effect which is what I was looking for in this forum, cuz thats where I found it before.

Apparently crystal when used with peachtree, grabs all of the raw data, and displays it.

 

[synapsevampire]

This is gettiung frustrating because you ignore my questions.

I'm asking WHICH qty you want, have done so twice, and each time you ignore it.

It's obvious that you want just one qty, but given the above example which would you want?

Yet another approach is to group by the itemid and then in the Report->Edit Selection Formula->Group place something like:

{table.lastdate} = maximum({table.lastdate},{table.itemid})

This assumes that there is a date which indicates the date of the transactions, and that you want the latest. Again, something you refuse to share.

Again, your records show 3 different quantities, and since you state that you don't want a sum of them, which row do you need to show?

-k

 

[WinterDragon]

I am not ignoring your questions.


There is only one QTY on Hand.

I want the number of pieces on hand at the moment I run the report.

There is no Sum, or Group, or table. Its as basic of a report as there can be. I can pull the INVENTORY LIST report from Peachtree and it shows exactly the data I want, with the exception that peachtree shows the items that have 0 qty.

Ok, Im going to try this from a Different Angle.

I need a report to show me The Item ID(Part Number), a Description of the part, and the Qty on hand. Qty should be accurate with Peachtree at any given time. If you want to show me how to write this as Simply as possible I will be happy to listen.

 

[synapsevampire]

You are mistaken. Your example data shows 3 quantites for the same itemid.

The most current is probably based on a date field, which my postings have clearly covered.

I think that you need to learn your datasource structure and understand how tables work.

Also, you did ignore this request as I'd asked which one you wanted and you just now stated the latest qty.

-k

 

[kskid]

I want the number of pieces on hand at the moment I run the report.

This would imply totaling but you said you do not want total.

If you are not totaling it, then what is the significance of having any quantity on hand if you only want 1 record of each item?

If I want a list of items that still had quantity on hand, then I would place just the itemno and description in the detail section. That's it.

In my record selection, I would add the following selection criteria

{table.qtyonhand} > 0

-LW

 

[WinterDragon]

Ok I think I figured this out. The reason it was pulling multiple IDS, was that Peachtree has References for that Item ID, IE, Each PO that was entered.

So YES, your solution would be correct, It would require a SUM of the Item ID Qty's.

When I did a similar report, a couple of years back, there were no multiple references, as I mentioned.

My apologies if I seemed to be ignoring your request for data, I was not seeing the multiple references in Peachtree.

Thank you for your help.

(the Info that I was looking for was {JrnlRow.ItemType=0} which did not work in this scenario anyway.)

 

[davidwendelken]

I ran across this thread today, and it was really obvious right off the bat that it wasn't a CR problem, it was either a "bad data problem" or a "programmer doesn't understand the data problem".

I know it's a CR forum and all, but it's not a good idea to tell people how to use CR to suppress the fact that they are doing something wrong in the report!

The right question to ask, right away, was:

"How many item 158-10 records show up in the database?"

Followed up with:

"If three, why are there three records? Have you misunderstood the data definitions?"

David Wendelken

http://www.casetech.net

 

[synapsevampire]

David: I appreciate the thought, and you sound competent, however I think it a better idea to offer a better solution prior to suggesting that others are incorrect.

Since you don't understand the issue nor the database, how can you state with certainty that:

"The right question to ask, right away, was:

"How many item 158-10 records show up in the database?"

They showed 3, and if there were 10K of them, yet they weren't used in the report, what does it matter how many are in the database?

I think the more approriate questions to ask are for technical information, perhaps including the SQL generated, but even then, without an understanding of the capabilities and permissions of the poster, coupled with understanding the database, your question will probably serve to muddy the waters, they probably don't understand dupes nor row inflation as evidenced by the posters responses.

-k

 

[davidwendelken]

Synapsevampire,

I wasn't trying to be mean, and I'm sorry if it sounded that way.

I've been going thru a slew of these threads to better understand how to make use of CR features. I've seen a lot of your posts, and it's very clear you know CR very well.
My thanks for all the great advice you've given - it's helped me several times already!

I'm new to CR, but I've got 20+ years doing data modeling. I'm considered an international expert in that field. Glad to supply credentials if asked, otherwise I won't bore you.

First of all, I offered part of a solution: Ask the questions I listed. A full solution would have to await the answers to those questions.

As for how I knew where to look, here's an explanation:

The first post showed that the user expected 1 record but was getting 3 records.

The definition of what he was supposed to get was:

item id / qty on hand / description.

I'm new to CR, but I'm operating under the impression that it doesn't just invent data rows out of thin air - the SQL has to return the rows or the programmer has to code them into existence.

When someone gets more rows than they expect, there are several very likely reasons from a database point of view. All of these are common mistakes, by the way:

1) There really are more rows in the database than they suspected because the data is invalid.

This happens a lot! Usually because the data modeler didn't set up the database constraints correctly and the programmers misunderstood the data modeler's intent.

2) Although there really only is one record in the database, a programmer made an incorrect join and therefore extra records were created in the output dataset by the query.

This wasn't likely given the data that was displayed in the first posting. First of all, the quantities didn't match. This meant that there were probably at least 2 records in the database, one with a qty of 1 and another with a qty of 2. However, there were two records with a qty of 1 and only one record with a qty of 2. If it were just a simple join mistake, there would normally be the same number of "extra" records for each original source record. While it's possible to get 3 records in the output from two starting records, it's also much less likely.

3) The data is correct, but the programmer has misunderstood the definition of the data table.

In this case, there would be three legitimate rows in the database and the programmer's desire to remove two of them is the mistake. In an inventory system, it's common to have a many-to-many cross-reference between item and storage_location, each of which needs it's own qty on hand value.

My questions to ask were:

"How many item 158-10 records show up in the database?"

Followed up with:

"If three, why are there three records? Have you misunderstood the data definitions?"

These are the first things to check because it's a) easy and quick to do so and b) other programmers frequently create bad data, misunderstand the data they are querying, or incorrectly join the data.

The answers given to these questions will quickly pinpoint which (if any) of them is the source of the problem.

In this case, when he went back and checked the data he was querying, he discovered that there were indeed 3 records in the database and that there were supposed to be 3 records in the database. That's mistake #3, misunderstanding the definition of the data being queried.

Hope that helps explain my reasoning.

My intent was to illustrate how easy it is to answer the question someone asks instead of the question they should have asked.

Fundamentally, the original poster asked:

How do I get rid of these extra rows?"

They should have asked:

"Why do I have these extra rows?"




David Wendelken

http://www.casetech.net