Salesforce

The Salesforce plugin provides a way to interact with Salesforce using SQL. You can query Salesforce objects, create new Salesforce objects, update Salesforce objects, and delete Salesforce objects.

Usage

The Salesforce plugin provides tables for the following objects: account, contact, lead, opportunity, case, task, event, campaign, user, campaignmember, asset, contract, contractlineitem, servicecontract, solution, pricebook2, product2, productitem, pricebookentry, quote, quotelineitem, order, orderitem, invoice, invoiceline, report, dashboard, document, payment, paymentlineinvoice

If you need to query a different object, you can use the salesforce_custom plugin available here.

-- Query all accounts
SELECT * FROM salesforce_account;

-- Query all contacts whose email is like 'john.doe%'
SELECT * FROM salesforce_contact WHERE email LIKE 'john.doe%';

-- Join accounts and contacts
SELECT a.name, c.email FROM salesforce_account a
JOIN salesforce_contact c ON a.id = c.accountid;

-- Create a new account
INSERT INTO salesforce_account (name, phone) VALUES ('My Account', '1234567890');

-- Update an account
UPDATE salesforce_account SET phone = '0987654321' WHERE name = 'My Account';

-- Delete an account
DELETE FROM salesforce_account WHERE name = 'My Account';

-- Sum of the amount charged in all invoices
SELECT SUM(TotalChargeAmount) FROM salesforce_invoice;

If you want to query across multiple organizations, you can create multiple profiles and configure each profile with the credentials of a different organization.

Configuration

To get started, install the plugin:

anyquery install salesforce

To use this plugin, you will need a Salesforce organization with the REST API enabled. Currently, there are four ways to authenticate with the Salesforce plugin:

• Username and Password
• Access Token using the sf CLI
• OAuth 2.0 Web Server Flow
• OAuth 2.0 Client Credentials Flow

All flows require the following configuration:

• domain: The domain of your Salesforce organization. For example, mydomain.my.salesforce.com. You need to left out the https:// part. You can find this in the URL of the login page.
  In https://mycompany.lightning.force.com/lightning/setup, your domain is mycompany.my.salesforce.com. For more information, see Finding Your Salesforce Domain.
• cache_ttl: The time-to-live (TTL) for the cache in seconds. The default is 0, which means no cache.
• encryption_key: The encryption key used to encrypt the cache for sensitive data. The key must either be 16, 24, or 32 bytes long. Make sure you are using ascii characters only. If you don't provide an encryption key, the plugin will not be able to start.

Access Token using the sf CLI

1. Install the sf CLI by following the instructions
2. Run sf org login web to authenticate with Salesforce
3. Run sf org display --target-org <username> and replace <username> with your username
4. Copy the Access Token from the output

When anyquery will ask for the access token, paste the token you copied. Fill in the domain, encryption_key, and cache_ttl in the configuration, and leave the other fields empty.

Note that the access token expires after a certain amount of time (usually 1 hour). You can refresh the access token by running again sf org login web and copying the new access token. Then run anyquery profiles update default salesforce <profile name> (default is the main profile name) and paste the new access token in the access token field. Leave the other fields empty.

OAuth 2.0 Web Server Flow

Step 1: Create a Connected App

1. Go to Setup > App Manager > New Connected App
2. Fill in the required fields
3. In the API (Enable OAuth Settings) section, check Enable OAuth Settings
4. In the Callback URL field, enter http://localhost:8080/callback
5. In the Selected OAuth Scopes section, add the required scopes
   • Access and manage your data (api)
   • Perform requests on your behalf at any time (refresh_token, offline_access)
6. Click Save

Step 2: Get the Consumer Key and Consumer Secret

1. Go to Setup > App Manager > Manage Connected Apps
2. Click on the app you created
3. Open OAuth Settings accordion
4. Click on Consumer Key, pass the security check, and copy the consumer key and consumer secret

Step 3: Authenticate with Salesforce

Open in your browser the following URL and replace the placeholders with your values:

https://<domain>/services/oauth2/authorize?response_type=code&client_id=<consumer_key>&redirect_uri=http://localhost:8080/callback

After you authenticate, you will be redirected to http://localhost:8080/callback?code=<code>. Copy the code from the URL.

Step 4: Get the refresh token

Run the following command in your terminal and replace the placeholders with your values:

curl -X "POST" "https://<domain>/services/oauth2/token" \
     -H 'Content-Type: application/x-www-form-urlencoded; charset=utf-8' \
     --data-urlencode "grant_type=authorization_code" \
     --data-urlencode "code=<the code you copied from the URL>" \
     --data-urlencode "client_id=<consumer_key>" \
     --data-urlencode "client_secret=<consumer_secret>" \
     --data-urlencode "redirect_uri=http://localhost:8080/callback"

Copy the refresh_token from the response. When anyquery will ask for the refresh token, paste the token you copied. Fill in the domain, consumer_key, consumer_secret, encryption_key, and cache_ttl in the configuration, and leave the other fields empty.

OAuth 2.0 Client Credentials Flow

Step 1: Create a Connected App

1. Go to Setup > App Manager > New Connected App
2. Fill in the required fields
3. In the API (Enable OAuth Settings) section, check Enable OAuth Settings
4. In the Callback URL field, enter whatever you want
5. In the Selected OAuth Scopes section, add the required scopes
   • Access and manage your data (api)
   • Perform requests on your behalf at any time (refresh_token, offline_access)
6. Click Save

Now you need to enable the Client Credentials OAuth Flow for the connected app:

1. Go to Setup > App Manager > Manage Connected Apps
2. Click on the app you created
3. Click on Edit Policies
4. Under OAuth Policies, select "Enable Client Credentials OAuth Flow"
5. In the input field just below, enter the email adress of the user you want to impersonate for Anyquery
6. Click Save

Step 2: Get the Consumer Key and Consumer Secret

1. Go to Setup > App Manager > Manage Connected Apps
2. Click on the app you created
3. Open OAuth Settings accordion
4. Click on Consumer Key, pass the security check, and copy the consumer key and consumer secret

When asked by Anyquery, paste the consumer key and consumer secret. Fill in the domain, encryption_key, and cache_ttl in the configuration, and leave the other fields empty.

Username and Password

This flow is not recommended for security reasons. If you can, prefer the Client Credentials Flow.

Step 1: Create a Connected App

1. Go to Setup > App Manager > New Connected App
2. Fill in the required fields
3. In the API (Enable OAuth Settings) section, check Enable OAuth Settings
4. In the Callback URL field, enter http://localhost:8080/callback
5. In the Selected OAuth Scopes section, add the required scopes
   • Access and manage your data (api)
   • Perform requests on your behalf at any time (refresh_token, offline_access)
6. Click Save

Step 2: Enable the Username and Password OAuth Flow

1. Go to Setup > Identity > OAuth and OpenID Connect Settings
2. Check Allow users to use the username/password OAuth flow and click Save

Once done, when asked by Anyquery, paste the consumer key (client_id) and consumer secret (client_secret). Fill in the domain, encryption_key, and cache_ttl in the configuration. You'll need to enter your username and password when Anyquery asks for them. Your password is the concatenation of your password and your security token. Learn how to get your security token here.

Additional Information

• The plugin uses the Salesforce REST API to interact with Salesforce. As a rule of thumb, querying 2000 records costs 1 API request. And 200 Insert, Update, or Delete operations cost 1 API request. Refer to the Salesforce documentation to find your monthly API request limit.
• Queries using sum, count or other aggregate functions may result in a lot of API requests because Anyquery has to fetch all the records to calculate the result. Avoid them on sobject that have a lot of records.
• The plugin uses the v61.0 version (Summer '24) of the Salesforce API.
• The plugin caches the results of the queries to reduce the number of API requests. The cache is encrypted using the encryption key provided in the configuration. You can change the cache TTL in the configuration.
• Internally, the plugin transforms the SQL queries into SOQL queries to interact with Salesforce.
• Any insert or update on fields that are not updatable will be ignored.
• The plugin doesn't support the Bulk API. If it's a requirement for you, feel free to open an issue
• The plugin doesn't supported sobjectFeed object. This means you cannot access the feed of an object like accountFeed, contactFeed, etc. As a workaround, you can use the salesforce_custom plugin to query the feed of an object.

Schema

The following contains the schema of the tables available in the Salesforce plugin. If you added any custom fields to your Salesforce objects, they will be available in the tables suffixed with __c.

salesforce_account

Represents an individual account, which is an organization or person involved with your business (such as customers, competitors, and partners).

salesforce_contact

Represents a contact, which is a person associated with an account.

salesforce_lead

Represents a prospect or lead.

salesforce_opportunity

Represents an opportunity, which is a sale or pending deal.

salesforce_case

Represents a case, which is a customer issue or problem.

salesforce_task

Represents a business activity such as making a phone call or other to-do items. In the user interface, Task and Event records are collectively referred to as activities.

salesforce_event

Represents an event in the calendar. In the user interface, event and task records are collectively referred to as activities.

salesforce_campaign

Represents and tracks a marketing campaign, such as a direct mail promotion, webinar, or trade show.

salesforce_user

Represents a user in your organization.

salesforce_campaignmember

The CampaignMember object represents the relationship between a campaign and either a lead or a contact. If the Accounts as Campaign Members setting is enabled in an org, CampaignMember can also represent the relationship between a campaign and an account.

salesforce_asset

Represents an item of commercial value, such as a product sold by your company or a competitor, that a customer has purchased.

salesforce_contract

Represents a contract (a business agreement) associated with an Account.

salesforce_contractlineitem

Represents a product covered by a service contract (customer support agreement).

salesforce_servicecontract

Represents a customer support contract (business agreement).

salesforce_solution

Represents a detailed description of a customer issue and the resolution of that issue.

salesforce_pricebook2

Represents a price book that contains the list of products that your org sells.

salesforce_product2

Represents a product that your company sells. This table has several fields that are used only for quantity and revenue schedules (for example, annuities). Schedules are available only for orgs that have enabled the products and schedules features. If these features aren’t enabled, the schedule fields don’t appear , and you can’t query, create, or update the fields.

Use this table to define the default product information for your org. This table is associated by reference with Pricebook2 table via PricebookEntry objects. The same product can be represented in different price books as price book entries. In fact, the same product can be represented multiple times (as separate PricebookEntry records) in the same price book with different prices or currencies. A product can only have one price for a given currency within the same price book. To be used in custom price books, all standard prices must be added as price book entries to the standard price book.

salesforce_productitem

Represents the stock of a particular product at a particular location in field service, such as all bolts stored in your main warehouse. Each product item is associated with a product and a location in Salesforce. If a product is stored at multiple locations, the product will be tracked in a different product item for each location.

Note that field service must be enabled in your org to use this table.

salesforce_pricebookentry

Represents a product entry (an association between a Pricebook2 and Product2) in a price book.

Use this table to define the association between your organization’s products (Product2) and your organization’s standard price book or to custom price books ( Pricebook2). Create one PricebookEntry record for each standard or custom price and currency combination for a product in a Pricebook2.

When creating these records, you must specify the IDs of the associated Pricebook2 record and Product2 record. Once these records are created, you can’t update these IDs.

This table is defined only for those organizations that have products enabled as a feature.

salesforce_quote

Represents a quote, which is a record showing proposed prices for products and services. Quotes can be created from and synced with opportunities, and emailed as PDFs to customers.

salesforce_quotelineitem

Represents a quote line item, which is a member of the list of Product2 products associated with a Quote, along with other information about those line items on that quote.

A Quote record can have QuoteLineItem records only if the Quote has a Pricebook2. A QuoteLineItem must correspond to a Product2 that is listed in the quote's Pricebook2.

salesforce_order

Represents an order associated with a contract or an account.

The Status field specifies the current state of an order. Status strings represent its current state (Draft or Activated).

When you create an order, the Status Code must be Draft and the Status must be any value that corresponds to a Status Code of Draft. The application can then activate an order by updating it and setting the value in its Status field to an Activated state; however, the Status field is the only field you can update when activating the order.

After an order is activated, you can change the Status back to the Draft state—but only if the order doesn’t have any child reduction order products. You can delete orders when the Status is Draft but not when its Status is Activated.

salesforce_orderitem

Represents an order product that your organization sells.

An order can have associated order product records only if the order has a price book associated with it. An order product must correspond to a product that is listed in the order’s price book.

salesforce_invoice

Represents a financial document describing the total amount a buyer must pay for goods or services provided. Users can edit non-posted invoices. Posted invoices can’t be deleted. After an invoice is posted, users can make payments against it to reduce its balance.

To access these entities, your org must have a Salesforce Order Management or D2C Commerce license.

salesforce_invoiceline

Represents the amount that a buyer must pay for a product, service, or fee. Invoice lines are created based on the amount of an order line.

This table is available when Order Management or Subscription Management is enabled.

The Salesforce plugin provides tables for the following objects: account, contact, lead, opportunity, case, task, event, campaign, user, campaignmember, asset, contract, contractlineitem, servicecontract, solution, pricebook2, product2, productitem, pricebookentry, quote, quotelineitem, order, orderitem, invoice, invoiceline, report, dashboard, document, payment, paymentlineinvoice

salesforce_report

Represents a report, a set of data that meets certain criteria, displayed in an organized way. Access is read-only.

salesforce_dashboard

Represents a dashboard, which shows data from custom reports as visual components. Access is read-only.

salesforce_document

Represents a file that a user has uploaded. Unlike Attachment records, documents are not attached to a parent object.

When creating or updating a document, you can specify a value in either the Body or Url fields, but not both.

salesforce_payment

Represents a single event when a shopper makes a payment. For credit cards, this event is a payment capture or payment sale, but it doesn't appear on the shopper's credit card statement.

salesforce_paymentlineinvoice

Represents a payment allocated to or unallocated from an invoice.

To access Commerce Payments entities, your org must have a Salesforce Order Management license with the Payment Platform org permission activated. Commerce Payments entities are available only in Lightning Experience.

Use a payment line to apply all or part of a payment’s balance to an invoice. The PaymentLineInvoice object represents the balance taken from the payment and applied toward the invoice. You can apply a payment’s balance when you create the payment record or afterward. The payment line must have the same currency as the parent payment.

A payment line has an amount, which represents the total amount taken from the payment, and balance, which represents the remaining amount after the payment line has been applied to an invoice. A payment’s amount can’t be less than the sum of all of its payment line amounts.

One payment can have multiple payment lines. A payment line must be related to only payment.

You can create multiple payment lines on a payment apply each line to different invoices on the same account, or to invoices on different accounts.