Set WooCommerce price from a custom field: copy, multiply, add VAT

To set a WooCommerce price from a custom field, you need a calculation that reads one field of each product and writes another: the regular price copied from a supplier price field, the regular price as cost times two, or a feed price that is the regular price plus 21% VAT. WooCommerce has no such operation, and a CSV round trip through a spreadsheet works but is easy to get wrong. BEAR does it in the Bulk Edit tab, product by product, with two tools: a formula block for number fields and the {meta:key} variable for text fields. Below is which one to use, the exact settings for the common cases, and the traps: empty source fields, variations and rounding.

Two tools: a formula for numbers, a variable for text

The regular price, the sale price and every meta field of the type number are number fields. In the Bulk Edit tab they accept only digits, so a calculation goes through the block under the numeric rows called Selected num fields: an operator (+, -, *, /) and a list of numeric fields. For each product BEAR does four things in this order:

1. It applies the row's own operation to the field you ticked: set new, increase by % and so on.
2. It applies the operator with the value of the field chosen in Selected num fields, read from the same product.
3. It applies the rounding chosen in the drop-down under the numeric rows.
4. It saves the result.

So set new 2 with * and a cost field gives 2 × cost, and set new 0 with + and a field copies that field. The list in Selected num fields shows the regular and sale price, the other built-in numbers, and the number meta fields whose columns are switched on. A custom field therefore has to be attached as a meta field of the type number before it can be a source, as described in the Meta Fields documentation. The type only tells BEAR how to show and edit the value; switching a field from string to number does not change the stored data.

Text fields work differently. In a text row of the Bulk Edit tab, or in a text cell, you can type variables that BEAR replaces per product: {meta:_supplier_code} inserts the value of any meta key, and the variable drop-down next to the row adds {REGULAR_PRICE}, {SALE_PRICE}, {SKU} and others. This copies values but does not calculate, and it cannot be used in price fields. The FAQ on copying data from meta fields shows the syntax.

Set the WooCommerce price from a custom field

1. In the Meta Fields tab attach the source key (find it with Get meta keys from any product by its ID), give it a title such as Cost, choose the type number, press Save meta fields, and switch its column on in the Settings tab.
2. In the Filters tab enter 0.01 in Cost from. This leaves out every product without a value, and it matters: an empty source counts as 0, and a regular price of 0 makes the product free.
3. In the Bulk Edit tab tick only the regular price row, choose set new and enter 0 to copy, or the multiplier from the table above.
4. In Selected num fields choose the operator and Cost, check the line Bulk editing will be applied to, and press Do Bulk Edit.

The formula is applied to every numeric row that is ticked, so tick nothing else in the same run. Source values must use a dot as the decimal separator: a field that holds 12,50 is read as 12. The same steps fill the sale price, with one difference: BEAR does not write a sale price that is not lower than the regular price, and a result of 0 or less removes the sale price.

Variations

Variable products have no price of their own; the prices are on the variations. Switch on variations on the tools panel before the run, and BEAR writes to the variations and ignores the parents. The formula reads the custom field of the variation itself, so the source must be filled on the variations; a field that exists only on the parent product gives the variations nothing to calculate from.

The empty-source check from step 2 does not work here: meta field filters look at the parent product, not inside its variations. With variations on, look through the source column before the run instead. A variation with an empty source gets a regular price of 0.

With variations on, the Bulk Edit tab also offers select combination of attributes. Select the attributes the variations are built on, for example Size, press Add attributes combination to apply on and set Size to XL in the row that appears: the run then changes only the XL variations, which suits a supplier price that applies to one size. The variations documentation covers this mode.

Fill a custom price field from the regular price

Feeds and marketplaces often need a separate price field, for example the price with 21% VAT when the shop stores prices without tax. Attach that field as a meta field of the type number (a text field cannot take a formula), then:

1. Tick only the feed price row, choose set new and enter 1.21.
2. In Selected num fields choose * and regular price, and run. A product at 100 gets a feed price of 121.

If the feed must carry the price customers actually pay, sale prices included, use WooCommerce's current price as the source instead. WooCommerce keeps it in the key _price: the sale price while a sale runs, the regular price otherwise. Get meta keys from any product by its ID does not list WooCommerce's own keys, so add _price with Add Custom key by hands as a number field, switch its column on, and choose it in Selected num fields instead of regular price. Use that column only as a source and never edit it: WooCommerce calculates _price from the regular and sale prices, and a value typed into it is overwritten at the next save of the product.

Run variable products with variations on, for the same reason as above. If the feed field has to stay a text field, new with {REGULAR_PRICE} copies the price into it unchanged; the VAT then has to be added by the feed tool.

Calculations that need two steps

One run combines one operation with one field. Anything longer is done in several runs, and the order matters because each run starts from the result of the previous one:

• Cost × 2 + 5. Run 1: regular price, set new 2, * cost. Run 2: regular price, increase by value 5, with Selected num fields set back to the empty line.
• (Cost + 3) × 1.5. Run 1: set new 3, + cost. Run 2: increase by % 50, formula off.

Do not forget to empty Selected num fields for the second run; otherwise the formula is applied again on top of the new price. Some relations need no formula at all: the sale price row has regular price minus %, the regular price row has sale price plus %, and doubling a price is increase by % 100, as explained in the FAQ on multiplying prices. For the opposite direction, a cost calculated as a share of the regular price, see bulk editing cost of goods.

Round the result

Multipliers produce fractions such as 47.6345. The rounding drop-down under the numeric rows is applied to the formula result before it is saved: 00 rounds to whole numbers, 5 and 10 round up to the next 0.05 or 0.10, and 9 to 99 set the cents to that ending (47.99). The options are described in price rounding; they work only when the shop shows prices with decimals. Regular and sale prices are saved with the shop's number of decimals anyway, but number meta fields keep the whole fraction unless you round them.

Test first, then roll back if needed

• Filter two or three products by SKU or title, run the operation, and compare the result with the source field in the product editor and on the product page.
• Check that only the target row is ticked and that Selected num fields shows the right operator and field.
• Exclude products without a source value before every price run.
• Variable products: variations on, source filled on the variations.
• A wrong run can be undone in the History tab; revert it instead of running an opposite formula.

If a calculation you need cannot be built from these steps, describe it with an example product on the BEAR support forum.