Updating Price Definitions in Bulk

You can alter a large number of price definitions for resources at the same time by exporting them to a CSV file, altering them and importing the file again.

Before You Start

Before you carry out the steps below, there are a few things to bear in mind:

• It’s only possible to update existing price definitions. You can’t add or delete any price definitions.
• You can’t alter any prices of ad hoc resources with these steps.
• You can’t update any selling prices or cost prices that vary in line with day parts.
• By default, only the prices for resources will be altered, and not the prices for booked resources.
• If you want to alter prices for resources booked on events anyway, you must indicate this when importing.

What Can You Alter?

The resource export file contains many columns, but not all of these are relevant to the import. Your import file must contain certain columns, while others are optional. You’ll find a list below.

Mandatory Columns

The columns below are mandatory:

• resource_id: The Yesplan ID of the resource.

• resource_name: The name of the resource. The name is for informational purposes. When importing, a warning will appear if the resource with the given Yesplan ID has a different name, but this field will otherwise be ignored.

• costmodel_name: The name of the price definition, exactly as it appears in Yesplan.

• costmodel_sellingprice_amount: The new figure for the selling price of the price definition. For instance, if the amount is ‘99’, then you can enter it as 99, 99.00 or 99,00.

• costmodel_sellingprice_vat: Whether the selling price amount is including or excluding VAT. Use Incl. VAT or Excl. VAT here.

• costmodel_cost_amount: The figure for the cost of the price definition. For instance, if the amount is ‘99’, then you can enter it as 99, 99.00 or 99,00.

• costmodel_cost_vat: Whether the cost price amount is including or excluding VAT. Use Incl. VAT or Excl. VAT here.

• costmodel_vat: The VAT percentage for the price definition. For instance, if the percentage is ‘21’, then you can enter it as 21, 21.00 or 21,00.

Optional Columns

Optional columns can also be added to the import file:

• If they are missing in the import file, you can still import it.
• If you don’t enter a value in these columns, the existing value won’t be updated in Yesplan.

Remark

Yesplan will inform you that the columns or values are missing by issuing a warning when verifying the file, but you may ignore this.

This concerns the columns below:

• costmodel_sellingprice_customcalculationquantity: The number of units that serve as the basis for the adjusted calculation of the selling price:

  • For a price per hour, the unit is minutes.
  • For a price per day, the unit is hours.
  • For a price per week or month, the unit is days.

  For example, for a price per hour, you can indicate that it must be calculated based on every started quarter of an hour (instead of the default: every started hour). In that case, you enter ‘15’ here (the number of minutes).

• costmodel_sellingprice_customcalculationminimum: The minimum number of units that we will take into account when determining the selling price.

  For example, for a price per day, you can indicate that it must be calculated based on every started hour, but with a minimum of 3 hours. In that case, you enter ‘3’ here (the minimum number of hours). Please note: the minimum number of units that must be taken into account can’t be smaller than the basis upon which the calculation is based (i.e. not smaller than the value in “costmodel_sellingprice_customcalculationquantity”).

• costmodel_cost_customcalculationquantity: The number of units that serve as the basis for the adjusted calculation of the cost price:

  • For a price per hour, the unit is minutes.
  • For a price per day, the unit is hours.
  • For a price per week or month, the unit is days.

  For example, for a price per hour, you can indicate that it must be calculated based on every started quarter of an hour (instead of the default: every started hour). In that case, you enter ‘15’ here (the number of minutes).

• costmodel_cost_customcalculationminimum: The minimum number of units that we will take into account when determining the cost price.

  For example, for a price per day, you can indicate that it must be calculated based on every started hour, but with a minimum of 3 hours. In that case, you enter ‘3’ here (the minimum number of hours). Please note: the minimum number of units that must be taken into account can’t be smaller than the basis upon which the calculation is based (i.e. not smaller than the value in “costmodel_cost_customcalculationquantity”).

Export the Resources

Resources can easily be exported using the search function in Yesplan.

Warning

If you perform this export as a normal user, the file may contain cells with the value “---” because you don’t have permission to view the prices in question.

For that reason, we advise to always export the resources as an administrator.

1. Log in as administrator.
2. Click the “Search” navigation menu item in Yesplan.
3. Run the following query in the search window: resource:name:*. This will display all your resources in the search results.
4. In the bottom right, select the operating system you’re working on, “MS-DOS” or “Mac”.
5. Click the button “Export” > “Export all resources”.
6. Click the button “Export” > “Download all resources” once your export is available.
7. Save the file.

Tip

If desired, you can filter the resources here using the search query. If you do this now, you don’t have to do so again in the next step. See Query Language for more information.

Copy the Relevant Resources to a New File

You have now exported all the resources to a CSV file, but you might only want to alter some of them. We therefore recommend that you copy the relevant resources to a new file, which will serve as the import file.

Tip

Don’t save the changes in the downloaded file: you can use it as a backup.

Filtering

The easiest way to limit the number of resources in Excel is to use a filter. Say you only want to alter the price definitions with a VAT rate of 6 %:

1. Use Excel to open the file you just downloaded.
2. Click the “Data” tab in the ribbon and then “Filter”. See also Filtering data in a range or table from Office Support.
3. Search for the relevant cell with the term “costmodel_vat” in the first row of the table and click the arrow in that cell.
4. Select only the checkbox next to “6.0” in the filter window and close the window.

Check whether the filtered data is correct. You can do this using the name (“resource_name”), the type (“resource_type”), the group (“resource_group”) etc.

Copying

Once the resources have been filtered correctly, you can copy the relevant columns to a new file.

Tip

See What Can You Alter? above to find out more about the columns and the values they must contain.

The import file must contain the columns below:

- resource_id
- resource_name
- costmodel_name
- costmodel_sellingprice_amount
- costmodel_sellingprice_vat
- costmodel_cost_amount
- costmodel_cost_vat
- costmodel_vat

The import file may also contain the columns below:

- costmodel_sellingprice_customcalculationquantity
- costmodel_sellingprice_customcalculationminimum
- costmodel_cost_customcalculationquantity
- costmodel_cost_customcalculationminimum

Follow the steps below to copy the columns:

1. Select the mandatory columns (along with any of the optional columns if you wish to alter these fields). You select by pressing the Ctrl key (Windows) or Cmd key (macOS) for each column and then clicking the column letter, e.g. the “A” above “resource_id”. See Selecting cell content in Excel from Office Support.
2. Copy the data:
   • Use the key combination Ctrl+C (Windows) or Cmd+C (macOS).
   • Or click “Edit” > “Copy” in the menu.
3. Create a new file:
   • Use the key combination Ctrl+N (Windows) or Cmd+N (macOS).
   • Or click “File” > “New” in the menu.
4. Paste the data into the new file:
   • Use the key combination Ctrl+V (Windows) or Cmd+V (macOS).
   • Or click “Edit” > “Paste” in the menu.
5. Save the new file as a CSV file, with a recognizable name:
   • Use the key combination Ctrl+S (Windows) or Cmd+S (macOS).
   • Or click “File” > “Save” in the menu.
   • The file format must be “Comma-Separated Values (.csv)”.
6. Close the file you downloaded without saving your changes. This can serve as a backup.

Alter the Prices

Once you’ve created the import file, you can alter the prices:

• You can find more information on the columns and desired values in What Can You Alter? above.
• Save the file once you’ve carried out the desired changes.

Import the File into Yesplan

Now you can import the file into Yesplan:

1. In Yesplan, go to “System Settings” > “Import” > “Resources”.
2. Select “Update Resource Price Definitions” in the drop-down menu.
3. In the drop-down menu next to “Encoding”, select the one for your operating system, “Latin1” for Windows or “MacRoman” for macOS.
4. Choose your import file.

Error Messages

Following the selection, Yesplan will verify the file and display any warnings or errors. You can only import the file once there are no more errors:

• Is there a red icon with a cross in front of the file name? Then you can’t import the file, for instance because some columns are missing.
• Is there a green icon with a tick? Then you can import the file. Click “Update Now” or “Schedule for Later”.
• Any warnings appear under the heading “Results of file verification”.

Tip

Does the file have a green icon? Then we recommend that you review the warnings anyway before carrying out or scheduling the actual update.

Altering Booked Resources

By default, updating price definitions in bulk won’t have an impact on the prices on existing events.

Do you want to alter the prices for resources that have already been booked on events in the future anyway? You can do so by using the option “Also update bookings on events on or after future date”:

1. Select the option.
2. Enter a date. This date must be in the future, and after the date when the update may be scheduled.

Remark

If you adjust the VAT rate on the resource and apply the changes to bookings on future events, then the following happens:

• The VAT rate changes on the applied price definition.
• The VAT rate doesn’t change on an overridden price.

See Overriding the Selling Price for a specific example.

Scheduling

Do you want to schedule the price update for a date in the future? To do so:

1. Click “Schedule for Later”.
2. Choose the date on which the price definitions are to be updated. Yesplan will carry out the alterations at night.

Attention

• You can only schedule one update at a time.
• If an update is scheduled, you can’t update any other price definition immediately either.
• You can delete a scheduled update at any time.
• You can download the CSV file that belongs with the scheduled update, e.g. as a backup before deleting the planned update from Yesplan.