Saturday 16 July 2016

Force CLI Part 3 - Accessing Data

Force CLI Part 3 - Accessing Data



In part 2 of this series, Extracting Metadata, I covered how to extract configuration data from your Salesforce instance, which it is something pretty much every other deployment tools offer. A key difference of the Force CLI is that you aren’t limited to configuration - you can retrieve and update your Salesforce data from the command line or a script. Very useful if you have to carry out some data migration as part of a deployment for example.

NOTE: In the examples below the commands are split over multiple lines for readability, but should be entered on a single line if you are trying them out for yourself.

Executing SOQL

The command for executing SOQL is force query, followed by the SOQL string enclosed in quotes:

> force query "select id, Name, Industry from Account 
order by CreatedDate limit 5"

The default format is ascii table style:

 Id                 | Industry       | Name
 0018000000Q9v1VAAR | Construction   | Pyramid Construction Inc.
 0018000000Q9v1WAAR | Consulting     | Dickenson plc
 0018000000Q9v1XAAR | Hospitality    | Grand Hotels & Resorts Ltd
 0018000000Q9v1YAAR | Transportation | Express Logistics and Transport
 0018000000Q9v1ZAAR | Education      | University of Arizona
 (5 records)

which is fine for displaying data on the screen, but not that easy for processing. Luckily the output format can be changed via the -format switch.

CSV Formatting

Specifying csv as the value of the format switch outputs the results of the query in the familiar comma separated format, along with a list of headers:

> force query "select id, Name, Industry from Account 
order by CreatedDate limit 5" -format:csv
"0018000000Q9v1VAAR","Construction","Pyramid Construction Inc."
"0018000000Q9v1WAAR","Consulting","Dickenson plc"
"0018000000Q9v1XAAR","Hospitality","Grand Hotels & Resorts Ltd"
"0018000000Q9v1YAAR","Transportation","Express Logistics and Transport"
"0018000000Q9v1ZAAR","Education","University of Arizona"

 while this is more useful for processing, it doesn’t work well with subqueries:

> force query "select id, Name, (select id, FirstName from Contacts) 
from Account order by CreatedDate limit 5" -format:csv
"Contacts","Id","Name" "[map[Id:0038000000asT8TAAU FirstName:Pat]]","0018000000Q9v1VAAR","Pyramid Construction Inc." "[map[Id:0038000000asT8UAAU FirstName:Andy]]","0018000000Q9v1WAAR","Dickenson plc" "[map[Id:0038000000asT8VAAU FirstName:Tim] map[Id:0038000000asT8WAAU FirstName:John]]","0018000000Q9v1XAAR","Grand Hotels & Resorts Ltd" "[map[Id:0038000000asT8ZAAU FirstName:Babara] map[FirstName:Josh Id:0038000000asT8aAAE]]","0018000000Q9v1YAAR","Express Logistics and Transport" "[map[Id:0038000000asT8bAAE FirstName:Jane]]","0018000000Q9v1ZAAR","University of Arizona"

JSON Formatting

Specifying json as the format switch outputs the result in JavaScript Object Notation:

force query "select id, Name, (select id, FirstName from Contacts) 
from Account order by CreatedDate limit 5" -format:json
[{"Contacts":{"done":true,"records":[{"FirstName":"Pat","Id":"0038000000asT8TAAU","attributes":{"type":"Contact","url":"/services/data/v36.0/sobjects/Contact/0038000000asT8TAAU"}}],"totalSize":1},"Id":"0018000000Q9v1VAAR","Name":"Pyramid Construction Inc.","attributes":{"type":"Account","url":"/services/data/v36.0/sobjects/Account/0018000000Q9v1VAAR"}},{"Contacts":{"done":true,"records":[{"FirstName":"Andy","Id":"0038000000asT8UAAU","attributes":{"type":"Contact","url":"/services/data/v36.0/sobjects/Contact/0038000000asT8UAAU"}}],"totalSize":1},"Id":"0018000000Q9v1WAAR","Name":"Dickenson plc","attributes":{"type":"Account","url":"/services/data/v36.0/sobjects/Account/0018000000Q9v1WAAR"}},{"Contacts":{"done":true,"records":[{"FirstName":"Tim","Id":"0038000000asT8VAAU","attributes":{"type":"Contact","url":"/services/data/v36.0/sobjects/Contact/0038000000asT8VAAU"}},{"FirstName":"John","Id":"0038000000asT8WAAU","attributes":{"type":"Contact","url":"/services/data/v36.0/sobjects/Contact/0038000000asT8WAAU"}}],"totalSize":2},"Id":"0018000000Q9v1XAAR","Name":"Grand Hotels \u0026 Resorts Ltd","attributes":{"type":"Account","url":"/services/data/v36.0/sobjects/Account/0018000000Q9v1XAAR"}},{"Contacts":{"done":true,"records":[{"FirstName":"Babara","Id":"0038000000asT8ZAAU","attributes":{"type":"Contact","url":"/services/data/v36.0/sobjects/Contact/0038000000asT8ZAAU"}},{"FirstName":"Josh","Id":"0038000000asT8aAAE","attributes":{"type":"Contact","url":"/services/data/v36.0/sobjects/Contact/0038000000asT8aAAE"}}],"totalSize":2},"Id":"0018000000Q9v1YAAR","Name":"Express Logistics and Transport","attributes":{"type":"Account","url":"/services/data/v36.0/sobjects/Account/0018000000Q9v1YAAR"}},{"Contacts":{"done":true,"records":[{"FirstName":"Jane","Id":"0038000000asT8bAAE","attributes":{"type":"Contact","url":"/services/data/v36.0/sobjects/Contact/0038000000asT8bAAE"}}],"totalSize":1},"Id":"0018000000Q9v1ZAAR","Name":"University of Arizona","attributes":{"type":"Account","url":"/services/data/v36.0/sobjects/Account/0018000000Q9v1ZAAR"}}]

 suitable for processing by a myriad variety of tools.

Prettified JSON

For the best of both worlds - JSON formatted for automated processing but laid out so that humans can read it, specify json-pretty as the format switch:

> force query "select id, Name, (select id, FirstName from Contacts) 
from Account order by CreatedDate limit 5" -format:json-pretty
[ { "Contacts": { "done": true, "records": [ { "FirstName": "Pat", "Id": "0038000000asT8TAAU", "attributes": { "type": "Contact", "url": "/services/data/v36.0/sobjects/Contact/0038000000asT8TAAU" } } ], "totalSize": 1 }, "Id": "0018000000Q9v1VAAR", "Name": "Pyramid Construction Inc.", "attributes": { "type": "Account", "url": "/services/data/v36.0/sobjects/Account/0018000000Q9v1VAAR" } }
<removed for clarity>

Working with Records

If you know the ID(s) of the records you need to work with, the force record command allows you to access and manipulate record instances.

Retrieving Records

To retrieve a record, execute force record <type> <id> - this brings back all fields :

> force record get Contact 0038000000asT8TAAU
AccountId: 0018000000Q9v1VAAR AssistantName: Jean Marie AssistantPhone: (014) 427-4465 Birthdate: CreatedById: 00580000001ju2CAAQ CreatedDate: 2009-04-19T11:27:45.000+0000 Department: Finance Description: Fax: (014) 427-4428 FirstName: Pat

<removed for clarity>
SystemModstamp: 2013-01-10T16:17:39.000+0000 Title: SVP, Administration and Finance attributes: type: Contact url: /services/data/v36.0/sobjects/Contact/0038000000asT8TAAU

Updating Records

To update a record, execute force record update <type> <id> <field>:<value>

> force record update Contact 0038000000asT8TAAU FirstName:"Patrick"
Record updated

I can then confirm that the record has been updated by retrieving it again and extracting the FirstName field:

>force record get Contact 0038000000asT8TAAU | grep FirstName
FirstName: Patrick

(the grep command extracts any lines in the output containing the search term, FirstName in this case.

Creating Records

You aren’t just limited to existing records with the Force CLI - you can also created them, either individually on en-masse from a file.

To create a single record execute force record create <type> <field> <field> … :

> force record create Contact FirstName:"Keir" LastName:"Bowden"
Record created: 00380000023TUCxAAO > force record get Contact 00380000023TUCxAAO

<removed for clarity>
FirstName: Keir LastModifiedById: 00580000001ju2CAAQ LastModifiedDate: 2016-07-16T10:48:38.000+0000 LastName: Bowden
 <removed for clarity>

To create multiple records execute force record create:bulk <type> <file> - the default format is CSV, but you can specify the content type of the file if it is different. Thus the following file, named contacts.csv:

"First","CLI Blog"
"Second","CLI Blog"
"Third","CLI Blog"

can be loaded as follows

> force record create:bulk Contact contacts.csv

Batch 1 of 1 added with Id 75180000006NK0pAAG
Job created ( 75080000004fh7wAAA ) - for job status use
 force bulk batch 75080000004fh7wAAA 75180000006NK0pAAG

and I can verify the load using the query command introduced earlier

> force query "select FirstName, LastName from Contact where LastName ='CLI Blog'"

 FirstName | LastName
 First     | CLI Blog
 Second    | CLI Blog
 Third     | CLI Blog
 (3 records)

Related Posts


1 comment:

  1. Good post thank you for this.

    How can "extract all" or "all rows" be used with "force query"?