Components to add price delta to all products

[NickD]

I sell wine. Wine taxation consists, normally, of (a) duty - a fixed amount per bottle - and (b) VAT.

Every time the duty rate changes it involves having to manually reprice every product, which is a long and tedious process.

It occurred to me that we might be able to use components to semi-automate this.

So each product would consist of (1) a base price, and (2) a duty amount. There would be probably 6 duty amounts - they might vary because of bottle size, wine type (sparkling has higher rates), and origin (a surcharge applies to duty charged on wines originating from outside the EU).

Each product would use one of these 6 duty "components".

All the stuff in the UG talks about components being used to offer users product options - but of course there are no user options here - they don't get to choose whether or not they pay the duty or which rate is charged for a given product. The advantage for me is simply that I have to change 6 values instead of several hundred.

I don't want this use of components to be in any way visible to the users - all they see, whether on screen or on their invoice, is a product which costs a certain amount of money, which is (base price+duty)x(1+VAT%).

To compound matters further, I'd like to be able to offer mixed cases which are made up of other products as components of the mixed case. Each product in the mixed case would itself be made up of a base product plus the duty "component". So we would need Actinic to support multiple-levels of components.

Is all this possible? Any comments/ideas? Has anyone done anything similar?

Nick

[Duncan Rounding]

The simplest way to achieve this would be to use Excel and create a flat file to calculate the prices and then simply import that file into Actinic to update.

[NickD]

Probably right. I already have such a file, which is used to generate a pricelist, so that's probably the way to go. It would have the added advantage of ensuring both website and pricelist use identical prices.

[NickD]

Actually on reflection the excel route is still by no means straightforward. The reason is that there is no mechanism withion the database for recording a parameter which defines which of the 6 different duty rates would apply. So there is still a large manual component to the process as each product has to be examined and the duty "type" set manually. Moreover the order in which products appear in the database is defined by Actinic and is quite different to the order they appear in my existing pricelist spreadsheet.

So I wonder - is there a way of using an otherwise unused database column to define duty type? If there were, the Excel route then becomes simple.

I can't use WEIGHT as that's used for calculating carriage - but maybe ALTERNATIVE WEIGHT?

Nick

[leehack]

There is nothing to stop you having a field or fields in your spreadsheet for your use, they don't have to be actinic fields. Have a column for duty type and then some simple calculations using that will do what you want. Look up the 'if' function, you can have 7 if/elses so sounds perfect for you.

[leehack]

Duty Types
1 - sparkling
2 - red
3 - etc
4 - etc
5 - etc
6 - etc

then a table, with corresponding duties:

1 = £0.50
2 = £0.75
etc

then an if:

=if(A1="1","0.50",if(A1="2","0.75",if(etc.)))

Have that in a cell next to base price, so the price field that you import into actinic is base price + duty added together.

There is also nothing stopping you from creating a variable in actinic for 'duty type' and recording this against each product in the database, you can then use that also.

[NickD]

The fields have to be in the database - from there they will be copied to a spreadsheet along with the price. This (simple) spreadsheet can calculate new price and create a new column which can be pasted back into the database.

The spreadsheet is the easy bit and the way of using one is well understood.

My point is that the "duty type" parameter MUST live in the database, because as new products are added and old ones removed, the columns in which the product price and "duty type" must be the same height and these parameters, for each product, must live on the same row.

So the process is...

1. copy "price" column from database & paste in spreadsheet
2. copy "duty type" column & paste in spreadsheet
3. new prices are calculated in new price column in spreadsheet using appropriate method
4. copy new price column from spreadsheet & paste back in database
5. save database & import into Actinic

For this to work easily and without having to edit the database each time a product is added, it must be possible to define the "duty type" parameter (as a number 0-5 or 1-6) from within Actinic. Hence my idea to use an otherwise unused parameter which results in a database entry - such as "ALTERNATIVE WEIGHT". Maybe there's a better one. But it must be definable as part of the product, from within Actinic.

Nick

[leehack]

Why do you need to find an unused parameter, you can set as many variables up yourself, to record whatever you like against a product. Once you've done that, it will export with your products. Create a variable 'DutyType' add in the options, so it's a list and if you have your setup so that people can order by wine type for instance and all those products in those sections are the same duty, you can make use of the 'use parent' facility to define them en mass. Even if that's not possible, decide on the most popular duty, define that as the standard one applied to all products and then you only have to change those that are not that duty.

[NickD]

Clearly I don't - wasn't aware you could do that - I will investigate.

[leehack]

Creating a variable in the help should do it, you'll smile when you see how easy it is. Make sure you get your 'place of setting' as product and if you can take advantage of the 'use parent' facility, make sure you have section as place of setting too.

[NormanRouxel]

Illustrated tutorial in the Starter Guide which is available via Windows Start menu. See Adding Custom Fields.

[NickD]

Thanks both.

It's certainly true that it's easy to create a new variable. I've created one called DutyType and have used "product" as the "place of setting". All that seems fine - a user data entry field for "DataType" appears in the "properties" section of every product and can be set as I wish. So far so good.

However, things then don't work quite as I expected. I assumed (maybe wrongly) that having done that, the relevant table in the .mdb file would now contain a column for the new variable, with a title "DutyType". I opened a copy of the .mdb file - in it there are lots of sub-tables, one of which is called "product". I opened that and saw that each row relates to a specific product, and that there are columns for lots of different variables associated with each product. But not my new variable. There appear to be many tables within the .mdb file and frankly I don't know what they are all for - but a quick root around some of the more likely prospects did not reveal what I was looking for.

The data is definitely stored somewhere, as it is retained in Actinic. Also when I enter data for a product's DutyType, the timestamp on the .mdb file is updated so I'm betting it's stored in there somewhere. I just can't see where.

Any ideas?

[leehack]

I'd steer clear of the DB personally, a file > export will give you a hierarchical export file, which will include your variable. Strip that file down to just prod ref, price and your variable and then you then have a master list in excel.

[NickD]

That works, and has the DutyType column. Prices are shown in column F.

So we have what we want and the next stage is to generate the new price & replacing the new price data in the "Price" column - that'll be easy using Excel; beyond that it's presumably just re-importing the same file (with changed price data) back into Actinic using File->Import-> Hierarchical import.

We're getting there.

Nick

[leehack]

Sounds about right Nick, do yourself a favour and snapshot before you start playing, particularly important while you are getting used to how you will do it. If i were you, i'd setup your own spreadsheet that you paste the export data into, your spreadsheet then automatically does the price updates (according to a master table of duties you have setup) and produces the final spreadsheet you need to import. You will be 'updating' when you import, don't forget that part.