Inventory Report

[Borvik]

My boss wants a report on our inventory. And as per usual, he's got to have a field in there that makes running the report difficult (ie. must use SQL instead of SmartList).

There are four fields he wants that are super simple (Item Number, Qty On Hand, etc...), but the last field he wants is "Last Date of Sale."

Is there by chance an easy way to get that field or will I just be better off making an iterative program to find it for all our items?

Thanks.

 

[dgillz]

Is the date of last sale stored somewhere, or do you have to figure it out from am inventory transaction table?

I think either way this is a very easy crystal report. Do you have crystal?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry

 

[Borvik]

I'm hoping it's stored somewhere - as that would make it rather easy to grab.

I have a feeling though that it will have to be figured out - as you say from the inventory transaction table, or possibly the sales transaction tables.

We have crystal - in a limited capacity (came with VS2003).

 

[dgillz]

Either way this is a 20 minute crystal report at most. 5 minutes if the value is stored somewhere.

Doesn't Great Plains come with its own report writer? If so then you should be able to use this as well.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry

 

[Borvik]

Yes GP does come with it's own report writer.

Maybe I should re-phrase the question: Is the "Last Date of Sale" field (whatever the real name would be) stored in the database somewhere? If so where?

If it's not in the database it would be trivial to get around it, but I'm hoping I don't have to.

 

[dgillz]

Maybe a GP expert will come around. I work with GP infrequently and do not have access to the database schema.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If the phone doesn't ring, it's me".....Jimmy Buffet

 

[VictoriaYudin]

The closest you can come to 'last date of sale' stored in a field is Last Order Date in IV00102. The only way I can think of to get the last 'invoiced' date is to find the last invoice for that item using SOP30300 (and possibly SOP30200 if you want the invoice date and not the ship date).

Victoria
Flexible Solutions, Inc.

 

[Borvik]

That's what I figured - oh well, I was hoping to avoid that sort of lookup as that is a huge lookup for us, but it looks like that's what I'm going to have to do.

 

[RobertC22]

You can use either the SOP30300 or IV30300 to get the history of the item. Below uses the IV30300 in a subquery to get your last date the item appeared from SOP (could be a return...)

select 
  ITEMNMBR, 
  QTYONHND, 
( select max(DOCDATE) from IV30300 T where T.ITEMNMBR = Q.ITEMNMBR and T.HSTMODUL = 'SOP' ) AS DateLastTrx

from IV00102 Q

where RCRDTYPE = 1

If you have Smartlist Builder, this could be converted to a view and then create your own Smartlist object for easy access.

------
Robert

 

[Borvik]

Yeah, that's pretty much what I ended up doing (via SOP30300 and SOP30200 and JOINS). It's not like it was difficult - I was just wondering if there was an easier way.