Importing Data from Salesforce Opportunities to QuickBooks Invoices

In this tutorial, we will show how to configure Salesforce - QuickBooks integration: import data from Salesforce Opportunities to QuickBooks Online Invoices. The main problem of such an import operation is that when you import data from Salesforce Opportunities into QuickBooks Invoices, there are no fields in Salesforce that correspond to the Invoice Line field in QuickBooks, and, to successfully load data, you need to map the required Invoice Line field to a constant in the form of an array. The Invoice Line field is not represented as separate fields, it has an array data structure.

However, when you select the Nested Objects checkbox in your import package, the nested Line object is divided into separate fields, and it becomes possible to map its columns to the corresponding columns from the Salesforce OpportunityLineItem. When you map fields of a nested object, you do not need to define how many lines should be for each Invoice. Skyvia automatically detects it. For example, if the Opportunity object has three line items, then Skyvia generates 3 lines for Invoice in QuickBooks Online.

To find out how to create an import package and configure mapping settings of nested objects correctly, read our step-by-step instruction below.

Please note that the Nested Objects functionality is available when the new data integration runtime is selected.

Make sure you have selected the Use new runtime checkbox in your import package before adding a task.

Creating Connections

First, you need to create connections in Skyvia, using Salesforce and QuickBooks connectors. You will use them as source and target in your import package. For our scenario, we create connections to Salesforce (source) and QuickBooks (target) respectively.

Read the Salesforce and QuickBooks topics to find out how to configure settings of necessary cloud apps as well as view the Connections topic to know how to create a connection following several simple steps.

Creating Import Package

To successfully create and run a new package, you need to adjust general package settings and configure task editor settings. Follow these simple steps to adjust general package settings:

1. Click +NEW in the top menu;

2. In the Integration column, click Import;

   

3. In the open package editor, rename your package by clicking and editing the package name. The default package name is Untitled. Please note that if you omit this step, the package name will remain Untitled in the list of created packages;
4. Select the Use new runtime checkbox to activate the Nested Objects option;
5. As you import data from the cloud app, click Data Source database or cloud app and select Salesforce as source from the Connection drop-down list;

6. In the next drop-down list, select QuickBooks as target;

   

7. Select the Nested Objects checkbox to be able to map the columns of the nested QuickBooks Line object to the corresponding columns from the Salesforce OpportunityLineItem;
8. Click the Add new link to open the Task Editor.

Configuring Task Editor Settings

Each import package can contain one or more import tasks, which are configured in the convenient task editor. For our scenario, we need to create and configure settings for one import task.

Source Definition Tab

On this task editor tab, you select a source object to load data from and specify filter settings to filter source data the way you need. To configure settings, perform the following quick steps:

1. When the task editor opens, select the task editor mode you want to work with — Simple or Advanced. You can read more about task editor modes here.
2. Next select the Opportunity object from the Source drop-down list.

3. Optionally, below you can add a filter by clicking +Condition and setting a specific condition. For example, if you need to insert only specific Opportunities, you can filter them by ID as shown below.

   

   Please note that here you need to filter only Opportunities, which have OpportunityLineItem. It is important in order to map correctly the Invoice Line field. If some Opportunities do not have OpportunityLineItem, records will fail in the import package.

   You can read more on how to configure filters and conditions in the Filter Settings topic.

4. Optionally, if you want this package to only sync new invoices from Salesforce to QuickBooks, you can select Inserted to load only new opportunities every time the package is run.
5. Click the Next step button at the bottom of the window to switch to the next editor tab. You can also switch between the editor tabs by clicking the corresponding icons: Source Definition, Target Definition, and Mapping Definition;

Target Definition Tab

1. On the Target Definition tab, select Invoice from the Target drop-down list.

2. Select an operation type you want to use (for our scenario, we select Insert).

   

3. Click the Next step button to continue.

Mapping Definition Tab

On the Mapping Definition tab, you need to use lookup to map the columns of the nested Line object to the corresponding columns from the Salesforce OpportunityLineItem. If you selected the Nested Objects checkbox for the package, the Invoice Line field provides access to fields of its nested object, which currently you can easily map.

To apply Lookup mapping for the Line column, follow the steps below:

1. Click QuickBooks Invoice Line column and, in the Salesforce Column drop-down list on the left, select Source Lookup.
2. In the Lookup Object drop-down list, select OpportunityLineItem.
3. Under Lookup Key Column, select OpportunityId.

4. Next, select the Column mapping and map by Id.

   

Proceed with other fields beneath:

1. Map the QuickBooks Amount field to the Salesforce TotalPrice field, using Column mapping.
2. Map the QuickBooks DetailType field using Constant mapping. Select SalesItemLineDetail field This field must be mapped to run the package successfully.

3. Map the QuickBooks SalesItemLineDetail_UnitPrice field to the Salesforce UnitPrice field. You can read more about different types of mapping here.

   

Start mapping the required CustomerRefId column:

Please note that the CustomerRefId column is one of the main columns of the Invoice object. It must be mapped correctly to run the package successfully. You can map this column, using either a constant or a lookup.

1. When you use Сonstant mapping, you get less flexible data import because the constant limits us to one customer. But if you need to import Opportunities of one customer only, constant will be a good option for it.

2. When you use Lookup mapping, you should remember that Salesforce Accounts should correspond to the QuickBooks Customers because the Opportunity object has an AccountId column. Since the CustomerRefId column is required in QuickBooks, you need to specify the AccountId column for the Opportunity object to perform a successful import operation. Otherwise an error appears stating that the value is not found by lookup. You also need to take into account that the name of the QuickBooks customer should correspond to the Salesforce account. Map the CustomerRefId column, using Lookup settings as shown below.

   

Saving Import Task

When everything is ready, click Save to save the task. You will see the created task in your package settings.

Running Import Package

Сlick Create to create the package and run it to import data from Salesforce to QuickBooks. You can check whether results are successful or not on the Monitor tab. For this, click the Run ID to open the History Details window. The Opportunity object had three line items, Skyvia detected it and generated 3 lines for Invoice in QuickBooks.