A simple way to load Salesforce Data to Planful is to use a Salesforce Custom Report. This approach avoids having to write complex SOQL queries to combine data together from one or more Salesforce Objects.

In this tutorial, fetch the data of a simple Salesforce report called "Opportunity Report" using the Salesforce connection setup previously. This report is a tabular report that combines three Salesforce objects including Opportunity, Account, and Contact.

Step 1: Create a OneCloud Chain

If necessary, navigate to the "Sample Workspace for Planful".

💡 Workspaces is on the left-side navigation panel.

  • Click on the "DEV" Environment, prompting the Chains pallet to appear.

    🏆 Alternatively, click "Build" on the left-side navigation panel.

  • Click on the "DEV" Environment.

  • Hover over the blue (+) icon near the lower-right corner. Click "Create a Chain".

    • Name the Chain "Upload Salesforce Report to Planful".

  • Save the Chain.

Step 2: Add a Command to Run a Salesforce Report

Add the first Command, selecting Salesforce ➡️ Download Report operation.
💡 Use the Find tool to search for "Salesforce" and "Download Report".

  • Choose the Download Report Command by double-clicking the icon, which will auto-load the Command into the Chain Start circle.

    📓 Alternatively, click-and-drag the icon onto the pallet. Then move the icon in the Chain Start circle. The Chain Start will show a plus (+) icon indicating that the command may be placed at the start of the Chain.

  • Double-click (or click + pencil icon) to edit the Command.

  • Rename the Command: "Salesforce Opportunity Report".

  • Input the Salesforce Report Name or Salesforce Report ID.

    • For this example, this Report Name parameter is "Opportunity Report".

Step 3: Add a Command to Preview the Data

Add a second command to the Chain.

  • Select the File Utilities ➡️ List File Content Command.
    💡 Use the Find tool to search for "File Utilities" and "List File Content".

  • Connect the "Get Data" Command to the "List File Content" Command.

  • Edit the "List File Content" Command.

    • Name the Command: "Preview Data".

    • Scroll down to set the File Name:

    • Set the Input File to be the output of Format Month:

      • From the left-side panel, choose "Salesforce Opportunity Report" 🔽 .

      • Click "Custom Report CSV".

    • In the "Preview Lines" parameter below, set the value to "10".

  • Save the Command.

Step 4: Transform - Format Month

The month string needs to be in a MM format to load into Planful. This formatting requires the use of Regular Expression (regex) syntax. Please visit the brief regex example following the gif below for an explanation on deriving the desired format.

Add the Find and Replace Command.

  • Choose Tabular Transformation ➡️ Transform ➡️ Find and Replace

    💡 Use the Find tool to search for "Tabular" and then for "Find and Replace".

  • Edit the "Find and Replace" Command.

    📓 Either double-click the Command or click the Command, then click the pencil icon

    • Rename the Command: "Format Month".

    • Set the Input File to be the output of Salesforce Opportunity Report:

      • From the left-side panel, click 🔽 to the right of "Salesforce Opportunity Report".

      • Choose "Custom Report CSV".

    • Enable Preview Results.

    • Set the Find Pattern to ^(\d)\/

    • Set the Match Pattern Type to Regular Expression.

    • Set the Replacement Value to 0$1/

    • Enable Replace Matches Only.

  • Save the Command.

What is Regular Expression syntax?

Regular expressions are powerful sequences of symbols and characters expressing a string or pattern to be searched for within a longer piece of text. Here is an example, that translates the Month element in the date to a format that is compatible with Planful.

To get started, let's break the syntax down in the Find Pattern while looking for a sample string such as: 8/4/2019. With Tabular Transformation, each column is split by the delimiter, so each column is an independent string.

  • ^ means to start at the beginning of the string.

  • (\d) means to find a single-digit such as 8 and the parenthesis indicate to select the text which will be used in the subsequent Replace parameter.

  • \ means to escape the next character

  • / means to find a forward slash (which requires the prior escape)

Applying the syntax ^(\d)\/ (the Find Pattern established above) to the string 8/4/2019 will find the string 8/ and will be temporarily stored as $1 (the Replacement Value from above) used in the subsequent Replace parameter. The syntax for the string replacement breaks down as follows:

  • 0 indicates to pad the string replacement with a physical 0

  • $1 inserts the captured value into the string replacement (from the Find string)

  • / indicates to complete the string replacement with a backslash

The isolated string replacement will now hold the value 08/ which is proper month format for loading into Planful.

Apply the same principals in Step #5 and the syntax should be able to be deciphered.

Step 5: Transform - Format Day

The day string needs to be in a dd format to load into Planful. Add another Find and Replace Command

  • Choose Tabular Transformation ➡️ Transform ➡️ Find and Replace

    💡 Use the Find tool to search for "Tabular" and then for "Find and Replace".

    🏆 Since the Command is the same as the previous one, copy it and make edits.

  • Edit the "Find and Replace" Command.

    📓 Either double-click the Command or click the Command, then click the pencil icon

    • Rename the Command: "Format Day".

    • Set the Input File to be the output of Format Month:

      • From the left-side panel, click 🔽 to the right of "Format Month".

      • Choose "Find & Replace Output".

    • Enable Preview Results.

    • Set the Find Pattern to (\d\d)\/(\d)\/.

    • Set the Match Pattern Type to Regular Expression.

    • Set the Replacement Value to $1/0$2/

      • Gif shows $/0$2/

    • Enable Replace Matches Only.

  • Save the Command.

What should the date string look like now?

The date string should now look like this example: 08/04/2019. This is the proper format for loading into Planful.

Step 6: Convert "Contact: Title" to "Contact Title"

The : in the header "Contact: Title" needs to be removed prior to loading into Planful. Create a Command that will perform this action. Add a third Find and Replace Command.

  • Choose File Utilities ➡️ General ➡️ Find and Replace.

    • Alternatively, copy the previous Find and Replace Command and make the necessary edits (shown).

  • Give the command the name: "Format Contact Title".

  • Set the Source to be the output of Format Day.

  • Set the Input File parameter:

    • From the left-side panel, click 🔽 to the right of "Format Day".

    • Choose "Find & Replace Output".

  • Set the Find to be Contact: Title

  • Set the Find Syntax to be Exact.

  • Set the Replace to be Contact Title

  • Enable Replace Matches Only.

  • Save the Command.

Step 7: Preview Converted Data

Add the List File Content Command.

  • Choose File Utilities ➡️ General ➡️ List File Content.

  • Give the command the name: "Preview Data".

  • Ensure Connection is a CloudRunner for this example

  • Set the File name parameter:

    • From the left-side panel, click 🔽 to the right of "Format Contact Title"

    • Choose "Find & Replace Output".

  • Set the Preview Lines parameter to 10.

  • Save the Command.

Step 8: Upload Converted Data to Planful

Add the Load Data Command to create a Planful rule configured to load data in the following format:

Preview of Converted Data

  • Search for Planful ➡️ Load Data ➡️ Load Data, dragging Load Data to the pallet.

  • Rename the Command: "Load Salesforce Data".

  • Set the Rule parameterto the name created in Planful: "GL Summary Data".

  • Set the Data parameter:

    • From the left-side panel, click 🔽 to the right of "Format Contact Title"

    • Choose "Find & Replace Output".

  • Set the File Name to the Response from the Format Contact Title output.

  • Set the Delimiter to ,.

  • Save the Command.

Step 9: Transfer the Salesforce Data

Add the Transfer Data Command.

  • Add the Command Planful ➡️ Load Data ➡️ Transfer Data.

  • Give the Command the name: "Transfer Opportunity Data".

  • Set the Rule to the name created in Planful.

    • Input GL Summary Data per previous lessons from this tutorial.

  • Save the Command.

Step 10: Publish & Run the Chain

Publishing is an important feature in OneCloud. Chains must be published, before they can be run. A Chain and its respective Commands will not be executed otherwise. Whether making one alteration or many, the Chain must be published every time a Chain or Command is edited.

Publish ➡️ Execute ➡️ Run Chain.

  • To publish a Chain, click "Publish" near the upper-right corner.

  • Optionally, add a Publish Chain Comment. For this example, leave it blank.

  • Once published, the "Execute" button becomes active. Click it.

  • Once executed, the "Run Chain" button is activated. Click it.

  • The Chain will animated when it is running.

  • Click on the Anaplan Upload File Command.

  • When the Chain has been run, the outputs will be available to view.


📚 Related Topics from this Section:

Environment
Chain Management

Chain Variable

Connections

The Salesforce BizApp

The File Utilities BizApp

The Tabular Transformation Biz App

Regular Expressions (regex)


Did this answer your question?