update all line items from existing quote with new database cost/sell : QuoteWerks Support Portal

S

Stumpf, Tom

started a topic about 4 years ago

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.

S

Steve (ML)

said about 4 years ago

Hi Tom, If the parts are not changing totally and you just want to know what year they are in and/or you want to use the latest prices all the time then.... My recommended workflow 'could be' to use the product databases like this... keep the manufacture part numbers the same and then use the power of quotewerks native features like "latest" prices to use the latest prices if needed...(see 2) https://www.quotewerks.com/helpfilelatest/refreshingitemswithlatestorbestpricing.htm?zoom_highlightsub=latest Idea... 2. Duplicate the vendor database each year. So if your new database is the latest one call it "2020" Keep manf part numbers the same. You could change any other fields like "vendor part number" to reflect the "latest year" part of your product part syntax. To change the products in the product database on lots of items quickly consider use excel where the original data is always in excel and you update that then re-import into qw when needed or, if you are brave use medic utility. Medic can easily update and set product fields but it would be best you are really specific before positing here about Medic. Changes using medic are irreversible And should be used with caution (on a test database) Examples of medic... I use it to: Update a field called "notes" everytime I do major work on a database and bring it upto date, so I then get medic to write a value in a custom notes field with a "date". Update price margins all in one go. for example you could easily increase your prices by 1.1 (or 10%) Move / copy values from one field to another. E.g. copy manufacture part number to Vendor part number. or copy "availability" field to custom text xx

S

Steve (ML)

said about 4 years ago

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")

S

Steve (ML)

said about 4 years ago

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...