How to ask for one of several records with repeating values??

[CarolineS]

Here's a problem I can't seem to get my head around.
I am creating a two lists of the orders placed each day, for the Shipping Dept. to pull the stock and send it.

For the overview version, I need to show just a count of products ordered, as in:
Item # Ordered (across all invoices)
ABC 3
EFG 14
MNO 10 etc.

So I've been grouping by Item#, counting on Item#, and hiding the details for the overview version of the report.
The detailed version looks like this: (Each invoice is allowed only one item)

Item Invoice No. Date
ABC 10009 12.13.01
ABC 10026 12.13.01
ABC 10028 12.13.01
total: 3

EFG 10014 12.13.01 etc……

HOWEVER. An Item# can represent a kit. An Item called XXK, which is a kit, may consist of one ABC and one EFG, for example. When this kit is ordered, my report is showing this:

Item Invoice No. Date
ABC 10012 12.13.01
total: 1
EFG 10012 12.13.01
total: 1
XXK 10012 12.13.01
total: 1

……which of course isn't accurate. I want to show just the XXY and show the count as one XXK. However, someone can also order an ABC or EFG on its own, so I can't just ignore invoices with those Item#'s attached to them.

Basically, what I need to say is that "if there is more than one record for Invoice No. 10012, only show me the one that has an Item# with a K at the end" (this is the only distinguishing feature of the kits). It would be easier if I was grouping on Invoice No., but grouping on Item# allows me to lay out the report as Shipping wants, and provides a neat way to do the overview report.

Maybe I'm missing an obvious solution? It seems so simple on paper …

 

[Ngolem]

There must be something that relates "ABC" , "EFG" and "XXK"

In your Invoice detail table (or equivalent) is there a field that Identifies this item as a part of a kit??

IE. When you print invoices...you must be able to make the distiction ortherwise you would be billing the client for stuff he didn't recieve.

If this flag exists then place something like

{table.ItemFlag} <> &quot;Kit&quot;

then this would remove the offending records

Jim

 

[CarolineS]

Two things - firstly, it is the record with the kit that I *do* want to print, not omit.

And as to there being some info in the invoice table... I don't think that there is. It's kind of an odd way this company works. When an order for a kit is entered, the data entry person goes into the &quot;card&quot; for that item (say XXK), enters the components for that *particular order*, saves it, and creates an invoice. Once those components are written to the SQL table as related to that invoice, the next data entry person can go into that same item card, delete what's there, and enter in new, different components for that same (same name anyway) item, and create a different invoice for a different customer.

The invoices for this company are generated in Crystal Reports also, and are also experiencing this problem - a kit with two components will produce 3 invoices: one for XXY, one for ABC, and one for EFG. The one for XXY has the correct price, and the ones for the components have 0.00.
(We're talking about Great Plains here - a sometimes nonsensical piece of software, from which I'm trying to craft reports which make sense.)

Thanks in advance for any help you can provide.

Caroline

 

[Nuffsaid]

Hi CarolineS,

So, both &quot;Kit&quot; and &quot;Kit Details&quot; are stored in the same table? I think a &quot;Kit&quot; should be a unique item within the main table with a link to a &quot;Kit Detail&quot; table. Wouldn't that make life easier!

An interesting problem.... just haven't got a solution yet..

Nuffsaid. x-)

 

[mbarron]

You state that &quot;a kit with two components will produce 3 invoices: one for XXY, one for ABC, and one for EFG. The one for XXY has the correct price, and the ones for the components have 0.00.&quot;

If this is always true, and there are no occasions that a 0.00 priced item needs to be returned, you could request only records where the price is not equal to zero.

Mike

 

[Ngolem]

I like mbarron's solution...you can also suppress the detail line when the item's value is 0.00...don't do it for each field (though you could) but do it in the Conditional suppress for the section.

As far as totals go....use a formula to do the totals with the same criteria for suppressing the section

{@unitCount}

numberVar unitcount;

if {table.unitPrice} <> 0 then
unitcount = unitcount + 1;

Hope this helps
Jim

 

[Ngolem]

Hmmm Got carried away....mbarron's solution solves everything...but in case it dosn't then perhaps I said something useful

 

[CarolineS]

Sometimes it's just sooo worthwhile to step back and have someone else look at your problem. mbarron, your answer is, of course, bang-on. I was very busy looking for a way to ask for the Kit, when the uniqueness (0.00 amounts) of the components that I wanted to omit was staring me in the face.

Thanks!!