Creating Item Prices for a new Price Period using Jitterbit – Step 1: Creating new price periods

This guide explains how to create a new price period for all Items in one step. It assumes that for each Item there will only be one price period, for example, a whole calendar year.

NOTE: At the time of writing, Price Periods are still in use, but in future Price Seasons will replace this object. ***link to new doc when ready***

Creating a file in Kaptio Travel to export the Item Id from the Price Period Object

In order to create a file to export the Item ID from the price period object, you will first need to create a new report type that allows you to export the price periods displaying one on each line.

1. How to create the Price Period report type
  1. In Kaptio Travel, click Setup in the top right hand corner of your screen:Salesforce - Enterprise Edition - Google Chrome 2017-10-13 15.11.07.png
  2. Type Report Type in the Quick Find/ Search box in the top left hand corner of your screen and you will see Report Types appear in the left hand sidebar: Force.com Home Page ~ Salesforce - Enterprise Edition - Google Chrome 2017-10-13 15.15.39.png
  3. Click on Report Types and the following screen will appear:All Custom Report Types ~ Salesforce - Enterprise Edition - Google Chrome 2017-10-13 15.17.32.png
  4. Click New Custom Report Type and the following screen will appear:New Custom Report Type ~ Salesforce - Enterprise Edition - Google Chrome 2017-10-13 15.18.41.png
  5. In the Primary Object field, select Price Periods from the picklist: Untitled - Paint 2017-10-13 15.19.36.png
  6. In the Identification and Deployment sections, set the values similar to the following:
  7. Click Next on the right hand side of your screen.
  8. On the following screen, click Save and the following will appear:Custom Report Type_ Price Periods ~ Salesforce - Enterprise Edition - Google Chrome 2017-10-13 16.08.13.png
  9. Click Edit Layout and the following screen will appear: Edit Custom Report Type_ Price Periods ~ Salesforce - Enterprise Edition - Google Chrome 2017-10-13 16.10.18.png
  10. Click Add fields related via lookup on the right hand side of your screen.
  11. In the popup window, click the Item >> link: Edit Custom Report Type_ Price Periods ~ Salesforce - Enterprise Edition - Google Chrome 2017-10-13 16.13.37.png
  12. Then scroll down to Item ID and check the checkbox:Edit Custom Report Type_ Price Periods ~ Salesforce - Enterprise Edition - Google Chrome 2017-10-13 16.15.12.png
  13. Click OK at the bottom of the popup window:Edit Custom Report Type_ Price Periods ~ Salesforce - Enterprise Edition - Google Chrome 2017-10-13 16.16.40.png
  14. You can now see the Item ID field under the Field Layout Properties:Edit Custom Report Type_ Price Periods ~ Salesforce - Enterprise Edition - Google Chrome 2017-10-13 16.17.18.png
  15.  Click Save towards the bottom of your screen.
2. How to create the Price Periods report
  1. Click the Reports tab at the top of your screen and the following will appear:Reports ~ Salesforce - Enterprise Edition - Google Chrome 2017-10-13 15.27.38.png
  2. Click New Report.
  3. Type Price Periods in the Select Report Type search box and select the relevant report from the displayed options: Reports ~ Salesforce - Enterprise Edition - Google Chrome 2017-10-13 15.27.38.pngor expand the folder where you saved the report and select the relevant report from there: Select Report Type ~ Salesforce - Enterprise Edition - Google Chrome 2017-10-13 15.34.44.png
  4. Highlight the relevant report and click Create Select Report Type ~ Salesforce - Enterprise Edition - Google Chrome 2017-10-13 15.35.49.png
  5. Ensure that you set the filters as follows:
  6. Add the Item ID as a column as follows:
    1. Type the word Item in the field search box on the left hand side of your screen and you will see the Item ID field appear in the list of available fields:Unsaved Report ~ Salesforce - Enterprise Edition - Google Chrome 2017-10-13 16.23.14.png
    2. Click and drag Item ID to the column section, you will see a green checkmark when you are dropping the field in the correct area as a column:Untitled - Paint 2017-10-13 16.25.16.png
  7. Click Run Report at the top of your screen.
  8. Export the data by clicking Export Details, selecting Excel as the Export File Format and clicking Done Price Periods ~ Salesforce - Enterprise Edition - Google Chrome 2017-10-13 16.52.03.png
3. How to manipulate the file in Excel

The only column that is necessary for this action is the Item ID column.

  1. Open the file in Excel.
  2. Rename the header for the Item ID column to KaptioTravel__Item__c.
  3. Add the following columns:
    1. Name
    2. KaptioTravel__DaysOfWeek__c
    3. KaptioTravel__Start_Date__c
    4. KaptioTravel__End_Date__c
    5. KaptioTravel__ExternalId__c
  4.  Set the value of the Name column to the price period you are about to add, for example, CY 2018. 
  5. Set the value of the KaptioTravel__DaysOfWeek__c column to Mon;Tue;Wed;Thu;Fri;Sat;Sun in order to to enable the price for all days of the week. If you only require the weekend or certain weekdays, remove the values you do not wish to use. NOTE: Ensure you keep the semi-colon in between the values and do not add any spaces. The format should be exactly as shown here.
  6. Set the value of the KaptioTravel__Start_Date__c column to the first day of the price period year, for example, 2018-01-01. In order to ensure that Excel does not convert this value, right click the column header, select Format Cells, then select Text and click OK. 
  7. Set the value of the KaptioTravel__End_Date__c column to the last day of the price period year, for example, 2018-12-31 . Here you also need to right click the column header, select Format Cells, then select Text and click OK. 
  8. It is important to set to a unique value for the KaptioTravel__ExternalId__c column so that you can use this as a reference when you insert the Item prices. To create a unique value you can, for example, use the following Excel formula CONCATENATE(A2;”CY2018″). With this formula you are creating a unique value based on the Item, assuming that the Item ID is stored in Column A and adding a fixed value, which in this case is the name of price period you are creating.
  9. Excel should now look as follows:
  10. as a csv and Upsert with Jitterbit as follows.
4. How to Upsert the file into Kaptio Travel using Jitterbit
  1. If you have not used Jitterbit before, please go to the following link first: http://info.jitterbit.com/Cloud-Data-Loader_Request-your-Free-Data-Loader_smart.html
  2. Within Jitterbit, create a new Upsert operation as follows
  3. :
  4. Set up the login to your org if it does not exists already:
  5. Select the Price Period object from the list. NOTE: At the time of writing, Price Periods are still in use, but in future Price Seasons will replace this object.
  6. Configure the External ID:
  7. In this example we will just use the ID field:
  8. On the following screen, select the csv file you created in the earlier step.
  9. Select CSV file, with header: 
  10. Set up the following mapping with the header names in the csv and following the same structure as the Salesforce object. It should be possible to do an Auto Match, but even then please verify the mapping.
  11. Once the mapping is done, run the Upsert operation and add a suitable name. Click Save and then click Run Upsert.
  12. The Upsert action should finish with 0 errors. Once it has completed you will have one new Price Period for each of your Items. Each of those Price Periods has an external ID that you can use to connect the Item price to the correct Price Period.