Community Support for the Catalook Store

 

Catalook Support Forum

Changing prices of products in bulk using Host>SQL
Last Post 10/16/2016 2:23 PM by Tim. 4 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
Luke
New Member
New Member
Posts:15


--
01/05/2015 9:08 PM
    Hi,

    I'd like to be able to change the prices of all our products in one go via the Host>SQL control panel.

    I understand it is possible but I'm not familiar with the code. We would like to swap our Catalook Store settings from incl tax to excl tax but will then need the correct function to input into the SQL form. Currently we have UK VAT added which is 20% and we need to remove it.

    The equation as I would do it on a calculator is:

    new unit price = old unit price / 1.2

    I found the following text on the Catalook release notes which seems to answer my question, but can anybody explain how it works or help me please? What do I need to enter in replacement of "databaseOwner" ? and what needs to replace "XX" ? 20 or 0.2.

    Either way the maths doesn't make sense to me.

    -------

    5.9.6: CATALooK.netStore -> Store Settings -> Price Type. If ‘tax incl.’ display is selected, prices of the products and fixed prices in product price rules must be entered now without tax! 
    5.9.8: CATALooK.netStore -> Store Settings -> Price Type. If ‘tax incl.’ display is selected, fixed surcharges from option selections and combinations of options and selections, fixed prices in cart price rule formulas and shipping charges from custom shipping rates must be entered now without tax!
    To bulk update previously entered prices with tax included, you could execute the following script via Host -> SQL (‘Run As Script’ must be turned off)

    Update {databaseOwner}CAT_Products
    Set unitcost = unitcost / (1+XX/100)

    (Replace XX by the tax percentage amount)
    After that: CATALooK.netStore -> Store Settings -> click on the ‚Clear Cache’ link

    -----

    I don't won't to make a mistake and backtrack is possible. Has anybody here had any experience of using the Host>SQL function?

    Many thanks for your help.

    Nina Meiers
    I am the boss today!
    Veteran Member
    Veteran Member
    Posts:507


    --
    04/30/2015 10:38 PM
    Sorry for this delay - I didn't see this - use the spreadsheet - we rarely update by hand, in fact our clients are quite comfortable with updating using xls file.

    I would think that the 1+xx/100 is the price + % of 100 so if i had a product that was 10.00 and wanted to increase it by 20% it would be 1+20/100 - that's just my interpretation but we manage all our product stock via xls files and my clients update their products that way - whether its 10 products or 5,000

    Nina
    Give me a place to stand and a lever long enough... and I will change the world.
    Eric
    New Member
    New Member
    Posts:1


    --
    02/16/2016 4:45 AM
    Hi Nina,

     you mentioned to use the spreadsheet to update prices. Can you point me where to look up this procedure? I'd like to update prices and stock at the same time.

    Thank you.

    Eric
    Nina Meiers
    I am the boss today!
    Veteran Member
    Veteran Member
    Posts:507


    --
    03/01/2016 3:14 PM
    Hi Eriic
    In your package you should have a folder with catimport.xls which maps the information to the site.
    There is procedure to do this - it's an import function of the website.
    if you've got more than 20 or so items, I'd be using the spreadsheet to update the information.

    If you want to do it programmatically, then you can use the xml function there is to connect to the server and import the data.
    We've built out a whole shop using that function -one from xmls files from suppliers to create a drop ship store while testing the xml functionality.
    Do you have your module packages when you first purchased catatlook- there are several files there that relate to both the xls import and the xml function.
    Give me a place to stand and a lever long enough... and I will change the world.
    Tim
    New Member
    New Member
    Posts:1


    --
    10/16/2016 2:23 PM
    Would have a step by step on that ?   
    I need to increase the price of one category of products...about 76 items by 3%.  

    Is this done in the products area with Export options on the top of the page.  
    I have tried searching for the category I want to change, then I select the select all button and click the export all box.  Then I hit the XML export button the nothing happens.  I waited about 10 minutes.  I then tried selecting the Excel export but nothing happened there either. 

    I might need step by step for the upload after the prices are changed.

    You are not authorized to post a reply.


    Catalook Support Chapters