Create a Reference Data in Integration


Reference data is data that defines the set of permissible values to be used by other data fields. It is a collection of key-value pairs, which can be used to determine the value of a data field based on the value of another data field. Integration allows you to upload reference data from a text file containing tabular data separated by a character, for example, a comma, semicolon, and so on. The uploaded file should not have an empty column heading or space in the first row, and the first row cannot be empty.

Reference data appears under the Services category in the FlowServices workspace. You can access the uploaded reference data in FlowServices as a list of documents by using the reference data service. You can filter the documents returned into the pipeline by the reference data service. If a reference data is used in a FlowService, you will not be able to delete that reference data. You must remove the reference data from the FlowService and then delete the reference data.

Reference Data Signature

Reference data signature is derived from the column names of the uploaded text file. You can filter the reference data by providing an appropriate matchCriteria. The output of the reference data is a list of documents that match the specified matchCriteria.

Input Parameters

matchCriteria: Document. Criteria on which documents from the Reference data are matched.

Parameters for matchCriteria are:

  • path: Column names of the Reference data.

  • compareValueAs: Optional. Allowed values are string, numeric, and datetime. The default value is string.

  • datePattern: Optional. Pattern is considered only if compareValueAs is of type datetime. Default value is MM/dd/yyyy hh:mm:ss a.

joins: List of join criteria.

Each join criteria consists of:

  • operator: Allowed values are equals, doesNotEqual, greaterThan, greaterThanEqual, lessThan, lessThanEqual, equalsIgnoreCase, contains, doesNotContain, beginsWith, doesNotBeginWith, endsWith, doesNotEndWith.

  • value: Allowed values are string, numeric, and datetime. The default value is string.

  • joinType: Specifies the way two joins can be linked. Values are “and” or “or”. Default value is “and”.

Output Parameters

Reference Data Name: Document List. List of documents that match the retrieve criteria.

Creating a Reference Data

Let us now see how to create reference data and use it in a FlowService.
In the following example, we will upload a file that contains a list of courier vendors as reference data in Integration, and then import the list of vendors as contacts in Salesforce CRM.

Before you begin

  • Log in to your tenant and enable FlowServices.
  • Check if you have the Developer and Admin roles assigned from the Settings > Roles page in Integration.
  • Obtain the credentials to log in to the Salesforce CRM back end account.
  • Create a Salesforce CRM account in Integration.

Basic Flow

1. Select the project where you want to create the reference data. You can also create a new project.

2. Click Configurations > FlowService > Reference Data > Add Reference Data.

3. Provide a name, CourierVendors, and an optional description for the reference data.

4. Click Browse file and select the Reference Data file. Both csv and a text file having tabular data are supported. The maximum file size you can upload is 1 MB. As shown in the below sample, the file should not have an empty column heading or space in the first row and that row cannot be empty. This is because the first row of data is read as column headings.

5. Click Next to define the reference data. Select the Field separator and the Text qualifier. Determine the encoding of the reference data file and from the File Encoding drop down list, select the same encoding.

6. Click Next to preview the data. If you select an incorrect encoding, garbage characters may appear in the preview pane.

7. Click Done to create the reference data. The new reference data appears in the Reference Data page. The reference data also appears under the Services category in the FlowServices workspace.

8. Go to FlowServices and create a new FlowService. Provide a name, ImportCourierVendorsAsContacts, and description of the FlowService. In the first step, type reference data and select Reference Data.

9. Select CourierVendors. This is the reference data that you created. Click the icon to add a new step.

We need to create a contact for each vendor, so type Repeat to select the Repeat step, and then select the CourierVendors option.

10. Select Salesforce CRM, select the action as createcontact, and associate the SalesforceCRM_1 account.

11. Click the mapping icon and map the input and output fields as shown below.

12. Save and run the FlowService, and view the results.