month to month inventory comparison

[randva]

Macola 7.5.102e, Pervasive

We have about 8500 part numbers at 6 locations. We use BOM and very simple POP.

We would like to compare extended values by part number month to month to help us track inventory errors. Has anyone done something like this? If so, can you give me any suggestions on how to go about it?

All replies are greatly appreciated.

 

[kscheeler]

This would be done through Access since you are on Pervasive. Even if you use the freeze inventory function, Macola only saves the information since the last freeze. What I've done for a client is to create my own table with the information they wanted. date (or seperate fields for year and month), item, loc, qty on hand, cost and another for extended value if you want. You would append to the table monthly with the current values.

The reports would then be done off the access table. If you convert to SQL, I would move the table into a custom table within the live database which will make reporting on it easier.

Kevin Scheeler

 

[NEmacGuy]

INIMVTRX joined to IMINVLOC - sum trx in previous month range, and subtract / add to current on hand perhaps

 

[NEmacGuy]

previous idea was just start, you would want to do an outerjoin (IMINVLOC left join IMINVTRX) in crystal. You will also have to deal with null in your formula for the change that will be computed by summing the ininvtrx. (other that equals join leads to null (items that remained the same)

 

[NEmacGuy]

Hell,
some things are never easy, you also should audit your item master and make sure trx audit is turned on, any item with it not on does not get posted to the iminvtrx file.

 

[crystalreporting]

I'm thinking you could also do your main report on IMITMIDX and/or IMINVLOC then use a subreport on IMINVTRX for each period (linked on item). Each subreport would use selection criteria to return transactions in a specific period. If the subreports were sorted in date order, then the last transaction would give you the 'old_qty' at the end of the month and the first transaction would give the the 'old_qty' at the beginning of the month. Maybe.

Peter Shirley

http://www.srhconsulting.com

 

[dgillz]

A much easier way to do this is to set beginning balance records at the end of each month under IM, Processes, Beg Balance Records. This creates a doc_type H record in the IMINVTRX_SQL table with the qty on hand at that time.

Then you can easily write a crystal cross tab with items as the rows, the date as the columns, and qty as the field to be summarized. Just be sure to limit the report to doc_type H transactions.

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

http://www.gainfocus.biz

"making predictions is tough, especially about the future" - Yogi Berra

 

[ultrav]

Not sure if it would help you but IMINVHST_SQL holds up to 24 periods of usage ptd, qty sold ptd, $ sold PTD and cost PTD. We use it to track sales monthly of items. The limitation is that it represents the accumulators so if you reset the accumulator and put in usage for the previous month it will not be accurate.

Celeste

 

[kscheeler]

Don's method is the least intrusive, especially since you are on Pervasive. I keep forgetting about the beginning balance records but you would need to make sure that your inventory was done for the month since like he said; it's going to capture the qty on hand at the time you run the function.


Kevin Scheeler