Tutorial: Upsert and delete records using web request jobs

This tutorial provides step-by-step instructions for using Web Request jobs to perform upsert (update or insert) and delete operations on Salesforce records.

For details on the REST API endpoints used in this tutorial, please refer to the following documentation pages:

• Salesforce: Insert or Update (Upsert) a Record Using an External ID

• Salesforce: Delete a Record

Prerequisites

Before continuing with this tutorial, ensure you have completed the following prerequisites:

• Tutorial: Create and configure a new Project.

• Set up a Developer Sandbox in Salesforce.

• Set up a Salesforce connection in Snoweaver.

Create a web request PATCH job

1. Open the LEARN_DEV project in Snoweaver with the LEARN_DEV_DEVELOPER role.

2. Open the Jobs page

3. Apply the following configuration:

   Name:                   sfdc_upsert_object_record
   Job Type:               Web Request
   Instance Type:          Function
   Request Type:           PATCH
   Payload Format:         Json
   Response Format:        Json
   External Integration:   sfdc_learn_dev_access_integration
   Enable Headers:         checked
   Secrets:
       Alias:  token
       Secret: sfdc_learn_dev_oauth_access_token

   

4. Specify the following job vairables:

   Name	Type	Test Value
   object	Text	Account
   external_id_field	Text	customExtIdField__c
   external_id	Text	11999
   record	Object	{ "Name" : "California Wheat Corporation", "Type" : "New Customer" }

   

5. Use the following Jinja template for specifying the job endpoint URL:

   https://{{_proj.sfdc_account}}.my.salesforce.com/services/data/v61.0/sobjects/{{_vars.object}}/{{_vars.external_id_field}}/{{_vars.external_id}}
   

6. Test the template to validate the output.

7. Use the following Jinja template for specifying the job headers:

   {
       "Authorization":"Bearer {{_secrets.token}}"
   }
   

8. Test the template to validate the output.

9. Use the following Jinja template for specifying the payload of the job:

   {{ _vars.record|tojson }}
   

10. Test the template to validate the output. The payload will be rendered in JSON format as defined in the metadata.

    

Test and save the PATCH job

1. Click Preview Request to examine the web request generated by the job.

2. Click Make a Test Call to initiate a web request to the specified endpoint URL.

3. The job will output the Salesforce response in JSON format as previously specified.

   

4. Save the job.

5. You can locate the newly added account in Salesforce by performing a search.

   

Build and execute the job instance

1. Click Build to build a new job instance.

2. Open a Snowflake worksheet and Run the following statements. These commands will create a temporary table used to store a list of accounts intended for upload into Salesforce:

   USE SCHEMA SNOWEAVER.LEARN_DEV;
   USE ROLE LEARN_DEV_DEVELOPER;
   USE SCHEMA LEARN_SW.DEV;
   
   CREATE OR REPLACE TABLE UPSERTED_ACCOUNTS
   AS
   WITH ACCOUNTS AS (
   SELECT '789' AS EXTERNAL_ID, 'Grand Hotels & Resorts Ltd' AS ACCOUNT_NAME
   UNION
   SELECT '890','Express Logistics and Transport'
   UNION
   SELECT '901','University of Arizona'
   UNION
   SELECT '1350','United Oil & Gas Corp.'
   UNION
   SELECT '2690','University of The Terrific'
   )
   SELECT
   'Account' AS OBJECT,
   'customExtIdField__c' AS EXTERNAL_ID_FIELD,
   ACCOUNTS.EXTERNAL_ID,
   OBJECT_CONSTRUCT('Name',accounts.account_name,'Type','New Customer') as RECORD
   FROM ACCOUNTS;
   
   SELECT * FROM UPSERTED_ACCOUNTS;
   

1. Execute the following statements to insert the new accounts into Salesforce and store the results.

   CREATE OR REPLACE TABLE UPSERTED_ACCOUNTS_WITH_SFDC_ID
   AS
   SELECT
   EXTERNAL_ID,
   RECORD:Name::VARCHAR AS NAME,
   SNOWEAVER.LEARN_DEV.SFDC_UPSERT_OBJECT_RECORD(
      OBJECT,EXTERNAL_ID_FIELD,EXTERNAL_ID,RECORD
   ):id::VARCHAR as sfdc_id
   FROM UPSERTED_ACCOUNTS;
   
   SELECT * FROM UPSERTED_ACCOUNTS_WITH_SFDC_ID;
   

   

2. Locate the SFDC_ID associated with EXTERNAL_ID 890 and record the value.

Create a web request DELETE job

1. Return to the Jobs page within the LEARN_DEV Project.

2. Click Save as to duplicate the upsert job under a new name.

   Name:  sfdc_delete_object_record

   

3. Apply the following configuration:

   Job Type:               Web Request
   Instance Type:          Table Function
   Request Type:           DELETE
   Response Format:        Text
   External Integration:   sfdc_learn_dev_access_integration
   Enable Headers:         checked
   Secrets:
       Alias:  token
       Secret: sfdc_learn_dev_oauth_access_token

   

4. Specify the following job vairables:

   Name	Type	Test Value
   object	Text	Account
   id	Text	<SFDC_ID of EXTERNAL_ID 890>

   

5. Use the following Jinja template for specifying the job endpoint URL:

   https://{{_proj.sfdc_account}}.my.salesforce.com/services/data/v61.0/sobjects/{{_vars.object}}/{{_vars.id}}
   

6. Test the template to validate the output.

7. Use the following Jinja template for specifying the job headers:

   {
       "Authorization":"Bearer {{_secrets.token}}"
   }
   

8. Click Preview Request to examine the web request generated by the job.

9. Click Make a Test Call to initiate a web request to the specified endpoint URL.

10. The server responds with a status code 204 indicating successful completion. No content is returned in the response body.

    

11. Save the job.

12. Click Build to build a new job instance.

13. Open a Snowflake worksheet and run the following statements to delete all previously created accounts:

    SELECT EXTERNAL_ID, NAME, SFDC_DELETE_OBJECT_RECORD.*
    FROM UPSERTED_ACCOUNTS_WITH_SFDC_ID,
    TABLE(SNOWEAVER.LEARN_DEV.SFDC_DELETE_OBJECT_RECORD('Account',SFDC_ID));
    

14. The results indicate successful deletion of all accounts, except for the one identified by EXTERNAL_ID 890, which was already removed.