I need the supplier cost field to update item cost. I have a sql script that updates the supplier cost from the Item cost field, but he wants a script that does the opposite. Thanks Dave
- posted
16 years ago
I need the supplier cost field to update item cost. I have a sql script that updates the supplier cost from the Item cost field, but he wants a script that does the opposite. Thanks Dave
Update item set cost = ...Oh, wait, how are you going to deal with the fact that you can have multiple suppliers for one item? The REPLACEMENT Cost of an item is not the same as the COST you have incurred. You should suggest that your customer discuss this with their accountant - changing cost without receiving a PO at a different cost is sort of questionable. The most common practice in RMS is to allow the system to calculate weighted average cost upon receipt of each PO. This gives the most realistic picture of your cost of inventory.
If they still insist on doing this (which you should really advise against), reply with the answer as to how they want to deal with multiple suppliers and someone will most likely complete the statement above.
Glenn Adams Tiber Creek C> I need the supplier cost field to update item cost. I have a sql script that
Why not use the options built into the system.
Open Store Operations Manager File Configuration Select Options Tab Purchase Order Options Tick "Update supplier cost and tax rate from Purchase Order" Select Inventory Tab Select Last Cost if you want the last cost of the item updated each time you enter a purchase order.
This will update the Item Cost regardless of if you enter a PO from the Primary Supplier or an alternative Supplier.
(I prefer the Weighted Average cost as this is more accurate for stores that have items with slow turn over and high turnover)
Regards Michael (Brisbane, Australia)
I still don't think this is a good idea. The instructions Michael gave to use Last Cost method was where I was going. Anyway here it is...
ALWAYS BACKUP YOUR DATABASE BEFORE RUNNING any Update, Insert or Delete statement!
update item set cost = sl.cost from item i inner join supplierlist sl on i.id = sl.itemid and i.supplierid=sl.supplierid where i.cost sl.cost
Glenn Adams Tiber Creek C> I did discuss with him and he would like to have the sql script written for
BeanSmart website is not affiliated with any of the manufacturers or service providers discussed here. All logos and trade names are the property of their respective owners.