How to import invoices using the Data Loader?

← Billing & Invoice Management FAQ

Your business may require to import invoices from external systems to JustOn. Generally, the invoice import is project-specific as it depends on your business needs and your system environment. A common approach, however, is preparing CSV files, which are to be inserted using the Data Loader.

Usually, this involves the following major tasks:

Preparing and importing invoice data (without invoice line items)
Preparing and importing invoice line item data
Optionally, preparing and importing invoice PDF files
Fixing the imported invoices

Preparing and Importing Invoice Data

In order to create an invoice using the Data Loader, you need a CSV file that provides the invoice records. The CSV file includes a header row and any number of rows that specify the invoice records to be created. The header row defines how the columns in the CSV file map to Salesforce fields. Be aware that each invoice record row must include the same number of columns as the header row. Preferably, the CSV file is UTF-8 encoded.

Info

The invoice records to be uploaded first do not contain the invoice line items.

JustOn Billing & Invoice Management requires the following fields to insert an invoice record:

Field	Possible Values	Notes
Status	`Pre-Draft` `Pre-Open`	The status must be prefixed with `Pre-` in order to allow the import. `Pre-Draft` invoices will have the status `Draft` after fixing. `Pre-Open` invoices will have the status `Open` after fixing.
Name	existing invoice number	The invoice number of `Pre-Open` invoices will not be replaced. Make sure that the invoice number is correct and unique.
Account	Salesforce account ID or External ID of the Salesforce account	An invoice must be associated with an existing account in Salesforce. If the account ID is is not available, you can use external IDs instead.
Template	JustOn template ID or External ID of the JustOn template	An invoice must be associated with an existing template. You can associate the template by its ID or by using external IDs.
Date	empty \| existing date Format: `YYYY-MM-DD`	If the date is empty, JustOn will use the current date when finalizing the invoice.
Currency ISO Code	one of the active currencies, like EUR, USD	This is only applicable in multi-currency organizations. If no currency is set, the system will copy the currency from the account.

All other invoice fields are optional. Commonly used information include, for example, service period start/end date, related subscription, related invoice run, business entity, related balance, payment due as well as bank information or address data.

Typical structure of an invoice import CSV file

Info

The invoice fix covers many functions like copying ON fields. It is therefore sufficient to populate the mandatory fields only.

To import the invoices, use the Data Loader as described in Insert, Update, or Delete Data Using Data Loader in the Salesforce Developer Documentation:

Click Insert.
Select the Invoice object.
Click Browse to select your invoice import CSV file, then proceed as prompted.

The Data Loader generates two CSV log files: a success file and an error file. The success file contains the successfully loaded invoices, including the generated IDs for each record. You need these IDs to associate the invoice line items with the corresponding invoice. The error file contains the rejected records, including a message that describes why the import has failed. Usually, the error file is empty.

Preparing and Importing Invoice Line Item Data

The invoice import produces "empty" invoices, which are assigned to your Salesforce accounts. In order to import and associate invoice line items, you prepare a second CSV file and execute another import.

The CSV file includes a header row and any number of rows that specify the invoice line item records to be created. The header row defines how the columns in the CSV file map to Salesforce fields. Be aware that each invoice line item record row must include the same number of columns as the header row. Preferably, the CSV file is UTF-8 encoded.

Info

You use the invoice record ID (as generated with the invoice import and written to the success file) to associate the invoice line items with the corresponding invoice. Make sure to add the correct invoice ID to each invoice line item row in the invoice line item import CSV file.

JustOn Billing & Invoice Management requires the following fields to insert invoice line item records:

Field	Possible Values	Notes
Invoice	JustOn invoice ID or External ID of the invoice (preferably the existing unique invoice number)	Invoice line items must be associated with an invoice record. This can be done by using the Salesforce ID of the invoice record or by using external IDs instead. `Pre-` type invoice line items can only be associated with `Draft`/`Pre-Draft`/`Pre-Open` invoices.
Type	`Pre-Product` `Pre-Shipping Fees`	The type must be prefixed with `Pre-` in order to allow the import. `Pre-Product` invoice line items will have the type `Product` after fixing. `Pre-Shipping Fees` invoice line items will have the type `Shipping Fees` after fixing.
Title	the title of the invoice line item	The title is displayed on the invoice PDF and names the sold product or service.
Unit Price	the price for one unit of the sold product or service	The unit price is interpreted as a net value unless the invoice is marked as a gross invoice.
Quantity	the sold quantity
Tax Rate	the tax rate of the invoice line item	If there is a tax rule applicable to the invoice line item, the fixing process will modify the tax rate accordingly.
Currency ISO Code	one of the active currencies, like EUR, USD	This is only applicable in multi-currency organizations. If no currency is set, the system will copy the currency from the invoice.

All other invoice line item fields are optional. Commonly used information include, for example, product group or service period start/end date.

Info

The invoice fix to be executed covers many functions like copying ON fields. It is therefore sufficient to populate the mandatory fields only.

To import the invoice line items, use the Data Loader as described in Insert, Update, or Delete Data Using Data Loader in the Salesforce Developer Documentation:

Click Insert.
Select the Invoice Line Item object.
Click Browse to select your invoice line item import CSV file, then proceed as prompted.

Preparing and Importing Invoice PDF Files

Your external system may have generated invoice PDF files. Depending on your requirements, you must import them to Salesforce, too, and associate them with the invoice records produced with the import. Doing so involves three steps:

Importing the PDF files
Retrieving the ID of the created document records
Associating the invoice records with the uploaded PDF documents

Importing Invoice PDF

You can use the Data Loader to upload the existing PDF files as Content Version records into libraries in Salesforce CRM Content. To this end, you prepare a specific CSV file, which includes a header row and any number of rows that specify the document records to be created. The header row defines how the columns in the CSV file map to the Content Version fields. Be aware that each document record row must include the same number of columns as the header row.

The CSV file must provide the following information:

Column	Description
`Title`	Specifies the name of the PDF file.
`VersionData`	Specifies the complete file path on the local drive.
`PathOnClient`	Specifies the complete file path on the local drive.
`ONB2__Type__c`	Specifies the JustOn-specific document type. Must be `Invoice PDF`.

Note

Salesforce requires the file path to be set in both the VersionData and PathOnClient fields in the CSV file. VersionData identifies the location and extracts the format, and PathOnClient identifies the type of document being uploaded.

Info

You need the Content Version record ID (as generated with the PDF import and written to the success file) to retrieve the Content Document ID in the next step.

Typical structure of a PDF import CSV file

To import the PDF files, use the Data Loader as described in Upload Content with the Data Loader in the Salesforce Developer Documentation:

Click Insert.
Select the Content Version object.
Click Browse to select your PDF import CSV file, then proceed as prompted.

The Data Loader generates two CSV log files: a success file and an error file. The success file contains the successfully loaded PDF files, including the generated IDs for each Content Version record. You need these IDs to retrieve the Content Document ID.

Retrieving Document ID

In order to associate the uploaded PDF files with the previously generated invoice records, you need the IDs of the file records. To retrieve them, you export the generated Content Version records using the Data Loader.

The SOQL query for the data export could be (assuming <id> specifies the IDs of the generated Content Version records, as taken from the PDF import's success file):

SELECT Id, Title, ContentDocumentId FROM ContentVersion WHERE Id IN ('<id>','<id>',...)

To export the generated Content Version records, use the Data Loader as described in Export Data in the Salesforce Developer Documentation:

Click Export.
Select the Content Version object.
Select a CSV file as the export target.
Define your SOQL query for the data export, then proceed as prompted.

From the exported CSV file, you need the Content Document IDs to associate the PDF file records with the corresponding invoices. Fields like Title help to find the correct invoice.

Associating PDF Files with Invoice Records

To relate the PDF file records with the corresponding invoices, you create Content Document Links – again through importing a specific CSV file. The CSV file must provide the following information:

Column	Description
`LinkedEntityId`	Specifies the ID of the generated invoice record.
`ContentDocumentId`	Specifies the ID of the content document record.
`ShareType`	Specifies the Content Document Link's access permission. Must be `I`.
`Visibility`	Specifies the availability of the link. Must be `AllUsers`.

Once the CSV file is prepared, you use the Data Loader as described in Insert, Update, or Delete Data Using Data Loader in the Salesforce Developer Documentation to import the Content Document Links:

Click Insert.
Select the Content Document Link object.
Click Browse to select your Content Document Link import CSV file, then proceed as prompted.

This associates the uploaded invoice PDF files with the corresponding invoice records.

In addition, you can update the PDF links on the invoice records. To do so, reuse the Content Document Link import CSV file, adding a new column for the URL of the invoice PDF file:

Column	Description
`ONB2__PDF__c`	Specifies the URL of the invoice PDF file.

The URL is composed of the home URL of your Salesforce org and the ID of the Content Version record, like

<mydomain>.salesforce.com/<ContentVersionId>

To update the invoices, you use the Data Loader as described in Insert, Update, or Delete Data Using Data Loader in the Salesforce Developer Documentation:

Click Update.
Select the Invoice object.
Click Browse to select your Content Document Link import CSV file, then proceed as prompted.

Fixing the Imported Invoices

Once the data import processes are completed, you execute the invoice fix. JustOn provides several options for invoking the invoice fixing process. Proceed according to your requirements:

Related information:

Invoice Import and Fix
Insert, Update, or Delete Data Using Data Loader in the Salesforce Developer Documentation
Upload Content with the Data Loader in the Salesforce Developer Documentation
Export Data in the Salesforce Developer Documentation
How to import data into Salesforce Salesforce video series