Is there a recommended workflow to update all database line item cost/sells from an old product database to a new product database?
My scenario is cost/sells change annually so I have a XYZ-2018, XYZ-2019, XYZ-2020, databases of product costs/sells. If i have outstanding quotes from previous years, I would like to easily update all matching line items to a new product database.
some examples of medic that i tested and work fine...
Updates List price by taking cost and multiplying by 3.
update products set list = format( Cost * 3, "0.00")
then you could round those numbers like this:
UPDATE Products SET List = Round(List,0)
[Note that the Round function will round both up and down (i.e. a list price of $1.60 will become $2.00 and a list price of $1.40 will become $1.00). This could make a large different if you are working with small monetary amounts. source: Dan T - old forum]
Kindly provided by Terry @ QW a few years back:
Increase List Price:
update products set list = format( list * 1.05, "0.00")
Increase Price 5%:
update products set Price = format( Price * 1.05, "0.00")
Increase Cost 5%:
update products set Cost = format( Cost * 1.05, "0.00")
Increase Volume Prices:
The following Exmples will increase the Prices by 5%
update products set pricinglevel01 = format( pricinglevel01 * 1.05, "0.00")
update products set pricinglevel02 = format( pricinglevel02 * 1.05, "0.00")
update products set pricinglevel03 = format( pricinglevel03 * 1.05, "0.00")
update products set pricinglevel04 = format( pricinglevel04 * 1.05, "0.00")
update products set pricinglevel05 = format( pricinglevel05 * 1.05, "0.00")
Continue for each pricing level through 10
Make Cost a Percentage of List:
update products set cost = format( List * 0.65, "0.00")
few screenshots of some medic queries that i tested...
some prefix vendor part numbers with "Zzz's"
so people like me dont end up using the wrong out of date product
or can sort them fast.
This way I also know which ones to 'selectively' delete once all the new data
is imported and working nice...