We can't see the Average Cost field in the IM_ItemWarehouse file when using Visual Integrator. It shows up if we do an excel query but not in VI so we can't import it. Has anyone else run in to this? Am I missing something? Any input would be appreciated.
Gosh Bernie don't take it so personal. This is a calculated field so the business object rules do not allow importing into this field is my guess. If it is not present in VI then it is a pretty good guess that the business object rules is the reason.
Big Louie No MBA but BMOC -------------------------------------------------------------------------- You have enemies? Good. That means you've stood up for something in your life.
If Sage allowed you to import into the Average Cost field, the Inventory Valuation would be out of balance with the GL since average cost is adjusted each time you post a transaction to an inventory account.
Thanks for the feedback and don't worry, I have been around long enough not to take things too personally. I did appreciate the tone and information of the subsequent posts a little more than the first one though :-). The client that wants to do this uses FIFO\Serial so the average cost only comes in to play in the accounting area if something goes negative. However, they use the average for reporting and comparative purposes and it can get messed up if someone fat fingers a transaction.
They recently had this happen on a rather large purchase order and were looking to make a mass correction, that's what was driving the request for the import. Beth's method will work for them though and I guess they should have the person who did the fat fingering enter the necessary transactions to get things back in line.
Starting with version 4.40, the AverageCost bucket (in most cases) is more of a memo field that hard data. If you were to import into the field, or otherwise change the field, the changes would be lost the next time the record was updated. The fields that are more important in IM_ItemWarehouse are "QuantityOnHand" and "TotalWarehouseValue". When a transaction occurs and the record is updated, it is those two fields that are updated. At that point, the average cost is recalculated using those two buckets. The same process happens in CI_Item with TotalQuantityOnHand & TotalInventoryValue, while IM_ItemCost uses QuantityOnHand & ExtendedCost. The purpose for using these buckets is to try and balance to the penny.
