Adding a Source Based on a Salesforce Query

When defining the Salesforce objects to extract on the Application Connectors page, you can use the Add Query Object feature to edit the SOQL query for an object in order to select the specific columns you want to extract from the object. You can also add a WHERE clause to the SOQL query which is useful for performing incremental extracts. A Birst variable can be used to filter the data being extracted from the source. For example, in order to extract data from Salesforce incrementally, you could extract only the records that have changed on or after a certain date/time. For example, to extract all the Opportunities with SystemModstamp on or after the LastLoadLastExtractDate (where LastLoadLastExtractDate is a Birst variable containing the date and time), you could use the following SOQL query:

SELECT Id,CreatedDate,StageName,Amount,ExpectedRevenue,CloseDate,Probability,ForecastCategory FROM Opportunity WHERE SystemModstamp >= V{LastLoadLastExtractDate}

To add a source based on a Salesforce query:

Configure a Salesforce extraction on the Application Connectors page as described in Extracting Data from Salesforce.
To add a source based on a Salesforce.com query, click Add Query Object.
For Name, type in the name of the object exactly as it appears in the list of data sources (catalog).
Click the Get Query Object link to retrieve the SOQL query for the specified object. The Query and Mapping fields will be automatically populated.

Note: After retrieving the SOQL query for the object by clicking the Get Query Object link, change the name of the object to a user-friendly name you would like to use in Infor. Make sure the name is not the same name as an already saved object otherwise the existing object will be overwritten. Alphanumeric characters, spaces and underscores can be used in the name.
The Query field will contain the SOQL SELECT statement that retrieves all of the columns in the object. Modify this query as applicable. You can restrict the columns that are extracted to just the columns you need for reporting by deleting ones you don't need. You can also add a WHERE clause to the end of the SELECT statement. This is useful for performing incremental extracts.

Note: Birst does not support extracting columns from multiple Salesforce sources in a single SOQL query.

Note: Birst allows you to embed a variable in a SOQL query. A Birst variable can be used to filter the data being extracted from the source. The date/datetime variable referenced in the SOQL query needs to be a Query session variable. Also the FORMAT function must be used in the variable expression in order to format the date/datetime to a format supported by Salesforce. The variable must be created before referencing it here. See Creating Variables for more information.

Example of using a Birst variable in a SOQL query:

You can also use a hard-coded datetime value in UTC as in the following example:
After modifying the Query field, click Validate Query Object to ensure the SOQL SELECT statement uses the proper syntax.

Note: Variables cannot be validated. Variables are replaced with the value at runtime.
The Mapping field contains the name of each column in the source, an equal sign, then the name it will be mapped to in Infor. To change the name of a column in Infor, change the name after the equal sign. For example, the column Id is mapped to Account ID in Infor by default. To change the column name to Acct ID, you would change Id=Account ID=true to Id=Acct ID=true. The column name will appear as Acct ID in the Columns tab of the Manage Sources page.

Note: Do not modify the =true or =false part.

Note: Once a column name has been changed in the Mapping field for an object, and the column has been extracted and created in Infor (and appears in the Columns tab of the Manage Sources page), it cannot be changed again. If you subsequently change the name to map the column to in the Mapping field then extract again, the column name in Infor will not be changed. (The column name can be changed in the Columns tab in Manage Sources if necessary.)
If you would like the extraction to include records that have been deleted or archived in Salesforce, check the Include Deleted and Archived Records checkbox. Note that the extraction may take longer when this option is selected. (Refer to Salesforce documentation for information about Salesforce’s policies regarding deleted and archived records.)
When you finish defining the object, click the Save button. After saving the object, it will be added to the list of objects to be extracted in the Data Sources table. It is now ready for extraction.

Important: Always remember to validate the query object before saving it. An object can be saved even if it is not valid.

Tip: To edit the object, click on the hyperlinked name.