Creating Item Prices for a new price period using Jitterbit – Step 2: Inserting Item Prices

This step explains how to insert Item Prices using Jitterbit to create new prices for a new Price Period.

Creating a file in Kaptio Travel to export Item prices

In order to create a file to export Item prices, you will first need to create a new report type that allows you to export the Item prices displaying one on each line.

1. How to create the Item Price 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 Item Price from the picklist.
  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.
  9. On the following screen click Edit Layout:Custom Report Type_ All Item Prices ~ Salesforce - Enterprise Edition - Google Chrome 2017-10-17 14.58.19.png
  10. On the following screen click Add fields related via lookup on the right hand side of your screen:Edit Custom Report Type_ All Item Prices ~ Salesforce - Enterprise Edition - Google Chrome 2017-10-17 16.48.20.png
  11. In the popup window, click the Item >> link.
  12. Edit Custom Report Type_ All Item Prices ~ Salesforce - Enterprise Edition - Google Chrome 2017-10-17 15.11.36.png
  13. Then scroll down to Item ID and check the checkbox
  1. Edit Custom Report Type_ All Item Prices ~ Salesforce - Enterprise Edition - Google Chrome 2017-10-17 15.12.41.png
  2. Click OK at the bottom of this popup window.
  3. You can now see the Item ID field under the Field Layout Properties:Edit Custom Report Type_ All Item Prices ~ Salesforce - Enterprise Edition - Google Chrome 2017-10-17 15.13.57.png
  4. Repeat the same process (steps 10. to 13.) for Price Category ID.
  5. Once complete, click Save on the following screen.
2. How to create the Item Price report
  1. Click the Reports tab at the top of your screen and the following will appearReports ~ Salesforce - Enterprise Edition - Google Chrome 2017-10-13 15.27.38.png
  2. Click New Report.
  3. Type Item Price in the Select Report Type search box and select the relevant report from the displayed options.
  4. Highlight the relevant report and click Create.Select Report Type ~ Salesforce - Enterprise Edition - Google Chrome 2017-10-17 14.44.38.png
  5. The report should show the following required price fields as columns:
    1. Currency
    2. Net Price
    3. Sales Price
    4. Item Id
    5. Price Category Id.
  6. You can add columns to a report as follows:
    1. Type the relevant field name, for example, Currency in the field search box on the left hand side of your screen and you will see the field name appear in the list of available fields.Unsaved Report ~ Salesforce - Enterprise Edition - Google Chrome 2017-10-17 14.50.17.png
    2. Click and drag the relevant field 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-17 14.55.16.png
  7. Once you have added all required columns, click Run Report at the top of your screen.Unsaved Report ~ Salesforce - Enterprise Edition - Google Chrome 2017-10-17 15.20.06.png
  8. Export the data by clicking Export Details, selecting Excel as the Export File Format and clicking Done.
3. How to manipulate the file in Excel
  1. Keep the following columns in the excel workbook:
    1. Currency (rename to CurrencyIsoCode)
    2. Net Price (rename to KaptioTravel__Net_Price__c)
    3. Sales Price (rename to KaptioTravel__Sales_Price__c)
    4. Item ID (rename to KaptioTravel__Item__c)
    5. Price Category (rename to KaptioTravel__Price_Category__c)
    6. Add an external Id Column with a header name of KaptioTravel__ExternalId__c
    7. Add a column for the Price Period External Id, name the header PricePeriodExternalId
  2. All the values in the columns listed above should come from the Salesforce report, except for the following two fields:
    1. The KaptioTravel__ExternalId__c field, which should use the following formula: CONCATENATE(D2;E2;”CY2018”) (assuming that the Item Id is in column D and Price Category is in column E).
    2. The PricePeriodExternalId field, which uses the formula CONCATENATE(D2;”CY2018”) (assuming that the Item Id is in column D).
  3. The workbook should now look as follows. NOTE If for some reason there are any Price Categories missing in the data, it would be best to fix these manually and then run the report until there are no Price Categories missing.
4. How to upsert the file into Kaptio Travel using Jitterbit
  1. Follow the same steps to set up a new Upsert Operation in Jitterbit as described in the Creating new Price Periods article, except for the following:
    1. Select Item Price instead of Price Period in the Object selection.
    2. Use the standard Id field as an the external Upsert Id.
  2. The mapping should look as follows. NOTE: Missing from the screenshot is the KaptioTravel__Sales_Price__c field.
  3. Then run the operation.
5. How to edit the Item price values you have upserted

In case there is a need to add a field/column or change any values after running the operation, then the KaptioTravel__ExternalId__c will help you run the operation again without having to delete all the data and start again from scratch. Since there is a record in Salesforce that has the External ID, the upsert operation will figure out that you are updating the record instead of creating a new one. Because of this feature it is handy to store the csv file you used to upsert Item prices so that you can modify any data at a later stage if needed.

  1. Create a new Jitterbit Upsert Operation.
  2. Follow the same steps as before, but select the KaptioTravel__ExternalId__c instead of the standard Id field as the upsert ID. 
  3. In case you are adding a new field, then you also need to map that field to the corresponding KaptioTravel__Item_Price__c object.