Hi there,
I've been trying to add a percentage increase to some 14,000 lines/permutations in Actinic but it was proving rather tedious so I've listed what I did below as there doesn't seem to be a detailed post anywhere on how best to do this. I saw a few posts about export from Access to Excel and copying and pasting back but if you have a large number of products/permutations this doesn't always work so I found this to be the best method. Bearing in mind ALL products on my website have their price worked out by component permutations, but this method should apply to anyone who isn't afraid to update any prices in bulk directly into MS Access and it should apply to any version of Actinic I imagine:
1) In Actinic export a snapshot of your site and save it somewhere as a backup
2) Close Actinic completely
3) Go into your site root folder which contains the file ActinicCatalog.mdb
4) I would right click on the .mdb file and paste it into the same folder several times as simple database backups. I thought this was silly when I did it five times but I ended up using three of them!! Better safe than sorry!
5) Open ActinicCatalog.mdb (no other file, it must be this one as this drives your Actinic site)
6) When you see the long list of tables in the database you need to decide which one you need to update. If you have specified the simple ONE price per product method (i.e. no choice dependant pricing) then open the table 'Product' and the field you need to update is simply the 'Price' field...OR if, like me, your pricing is based upon choices then you'll need the table 'ProductProperties' and the field to update in this table is 'nValue1' (fourth column) which contains all the price permutations from your Actinic Catalog.
7) Go back to the list of tables you saw when you first opened the database in point 5 above and in the options down the left-hand side select 'Queries' and then 'New' just above this. Then select 'Design view' and 'ok'
8) You then need to select which table you're working on to update the prices. In my case it was 'ProductProperties' as I have choice dependant pricing - select the table, click 'Add' and then 'Close'
9) Your cursor will be flashing in the 'field' box. Select the field containing your prices (again in my case this was 'nValue1' as i'm in the 'ProductProperties' table but if you're in the 'Product' table then select 'Price') and then the table name should appear in the box below also.
10) In the menu bar select 'Query' and then 'Update Query' - this will amend the look of your query slightly.
11) In the 'Update To' box this is where you input your calculation to update your prices. In my case I was updating prices by 9% so you put the name of your field in square brackets followed by the mathematical formula, which looks like this: [nValue1]*1.09 (if you're in the 'Product' table then the your formula is slightly different: [Prices]*1.09) - just substitute the 9 in this formula for whatever percentage increase you're applying
12) If you simply need to update ALL prices to the same percentage then all you need to do now is click 'Query' on the menu bar and then 'Run' - it will ask you are you sure you wish to update x records/rows, if you are sure select yes and it will update everything there and then. You can go into your relevant table and take a look at the column to check.
Further notes:
13) If you want to update your prices by simply adding an amount (lets say we want to add £22.75 to every item) then your formula changes to [nValue1]+2275 (or [Prices]+2275 if you're in the 'Product' table)
14) Some of the products in my 'ProductProperties' table has the values '0' or '1' in the 'nValue1' column so I didn't want to update these. To do this just add '>1' to the criteria field in your query before you run it.
15) If you want to apply price increases only to products or a certain type then you'll probably need to add a second part to your query. In my case I wanted to update all products where the product reference began with 'KY' so beside your query go to the next column and select 'sProductRef' and in the 'criteria' field enter 'Like KY*' or whatever it is you want to update. This will mean when you run the query it will only apply the update to those particular products rather than the whole lot. This method can be applied to any field where there is a common theme to the field. E.g if you want to update all items that are priced at £100 then the second query column would be set to 'nValue1' and the criteria would be 10000, then run the query...
I hope this helps and people find it useful. It has certainly saved me DAYS, if not WEEKS of time inputting every new price manually within Actinic!!
I've been trying to add a percentage increase to some 14,000 lines/permutations in Actinic but it was proving rather tedious so I've listed what I did below as there doesn't seem to be a detailed post anywhere on how best to do this. I saw a few posts about export from Access to Excel and copying and pasting back but if you have a large number of products/permutations this doesn't always work so I found this to be the best method. Bearing in mind ALL products on my website have their price worked out by component permutations, but this method should apply to anyone who isn't afraid to update any prices in bulk directly into MS Access and it should apply to any version of Actinic I imagine:
1) In Actinic export a snapshot of your site and save it somewhere as a backup
2) Close Actinic completely
3) Go into your site root folder which contains the file ActinicCatalog.mdb
4) I would right click on the .mdb file and paste it into the same folder several times as simple database backups. I thought this was silly when I did it five times but I ended up using three of them!! Better safe than sorry!
5) Open ActinicCatalog.mdb (no other file, it must be this one as this drives your Actinic site)
6) When you see the long list of tables in the database you need to decide which one you need to update. If you have specified the simple ONE price per product method (i.e. no choice dependant pricing) then open the table 'Product' and the field you need to update is simply the 'Price' field...OR if, like me, your pricing is based upon choices then you'll need the table 'ProductProperties' and the field to update in this table is 'nValue1' (fourth column) which contains all the price permutations from your Actinic Catalog.
7) Go back to the list of tables you saw when you first opened the database in point 5 above and in the options down the left-hand side select 'Queries' and then 'New' just above this. Then select 'Design view' and 'ok'
8) You then need to select which table you're working on to update the prices. In my case it was 'ProductProperties' as I have choice dependant pricing - select the table, click 'Add' and then 'Close'
9) Your cursor will be flashing in the 'field' box. Select the field containing your prices (again in my case this was 'nValue1' as i'm in the 'ProductProperties' table but if you're in the 'Product' table then select 'Price') and then the table name should appear in the box below also.
10) In the menu bar select 'Query' and then 'Update Query' - this will amend the look of your query slightly.
11) In the 'Update To' box this is where you input your calculation to update your prices. In my case I was updating prices by 9% so you put the name of your field in square brackets followed by the mathematical formula, which looks like this: [nValue1]*1.09 (if you're in the 'Product' table then the your formula is slightly different: [Prices]*1.09) - just substitute the 9 in this formula for whatever percentage increase you're applying
12) If you simply need to update ALL prices to the same percentage then all you need to do now is click 'Query' on the menu bar and then 'Run' - it will ask you are you sure you wish to update x records/rows, if you are sure select yes and it will update everything there and then. You can go into your relevant table and take a look at the column to check.
Further notes:
13) If you want to update your prices by simply adding an amount (lets say we want to add £22.75 to every item) then your formula changes to [nValue1]+2275 (or [Prices]+2275 if you're in the 'Product' table)
14) Some of the products in my 'ProductProperties' table has the values '0' or '1' in the 'nValue1' column so I didn't want to update these. To do this just add '>1' to the criteria field in your query before you run it.
15) If you want to apply price increases only to products or a certain type then you'll probably need to add a second part to your query. In my case I wanted to update all products where the product reference began with 'KY' so beside your query go to the next column and select 'sProductRef' and in the 'criteria' field enter 'Like KY*' or whatever it is you want to update. This will mean when you run the query it will only apply the update to those particular products rather than the whole lot. This method can be applied to any field where there is a common theme to the field. E.g if you want to update all items that are priced at £100 then the second query column would be set to 'nValue1' and the criteria would be 10000, then run the query...
I hope this helps and people find it useful. It has certainly saved me DAYS, if not WEEKS of time inputting every new price manually within Actinic!!
Comment