Programmatically Obtaining All Fields on an Object
Its possible to programmatically obtain a list of all fields on an object using Python and the simple_salesforce library.
Credentials below are for a trailhead org created for purposes of this note, only. It contains only default data provided by Salesforce.
from simple_salesforce import Salesforce
import pandas as pd
pd.options.display.max_rows = 999
sf = Salesforce(username='[email protected]',
password='',
security_token='8OkoE0bfWfTR14nakoP5i0V43')
pd.options.display.max_rows = 999 sets options such that Pandas will print as many as 999 rows in a single returned table.
Obtain All Objects
Iterate through the ordered dict returned by sf.describe() to build a list of objects on the org. Use it to create a DataFrame.
desc = sf.describe()
objects = []
obj_labels = [obj['label'] for obj in desc['sobjects']]
obj_names = [obj['name'] for obj in desc['sobjects']]
obj_custom = [obj['custom'] for obj in desc['sobjects']]
for label, name, custom in zip(obj_labels, obj_names, obj_custom):
objects.append((label, name, custom))
objects = pd.DataFrame(objects,
columns = ['label','name','custom'])
print(str(objects.shape[0]) + ' objects')
objects.head(10)
411 objects
| label | name | custom | |
|---|---|---|---|
| 0 | Accepted Event Relation | AcceptedEventRelation | False |
| 1 | Account | Account | False |
| 2 | Account Change Event | AccountChangeEvent | False |
| 3 | Account Clean Info | AccountCleanInfo | False |
| 4 | Account Contact Role | AccountContactRole | False |
| 5 | Account Feed | AccountFeed | False |
| 6 | Account History | AccountHistory | False |
| 7 | Account Partner | AccountPartner | False |
| 8 | Account Share | AccountShare | False |
| 9 | Action Link Group Template | ActionLinkGroupTemplate | False |
Obtain All Fields on a Specific Object
.describe() returns an ordered dictionary of attributes on the specified object including a list of all fields and their attributes. Use the object name (like those in the table above) in the following call to .describe(). Iterate through the ordered dict getting a list of fields on the object. Use it to create a DataFrame.
This example uses the Account object.
desc = sf.Account.describe()
fields = []
field_labels = [field['label'] for field in desc['fields']]
field_names = [field['name'] for field in desc['fields']]
field_types = [field['type'] for field in desc['fields']]
for label, name, dtype in zip(field_labels, field_names, field_types):
fields.append((label, name, dtype))
fields = pd.DataFrame(fields,
columns = ['label','name','type'])
print(str(fields.shape[0]) + ' fields')
fields.head(10)
65 fields
| label | name | type | |
|---|---|---|---|
| 0 | Account ID | Id | id |
| 1 | Deleted | IsDeleted | boolean |
| 2 | Master Record ID | MasterRecordId | reference |
| 3 | Account Name | Name | string |
| 4 | Account Type | Type | picklist |
| 5 | Parent Account ID | ParentId | reference |
| 6 | Billing Street | BillingStreet | textarea |
| 7 | Billing City | BillingCity | string |
| 8 | Billing State/Province | BillingState | string |
| 9 | Billing Zip/Postal Code | BillingPostalCode | string |
Obtain All Data Across All Fields on a Specific Object
Append the name column in the fields DataFrame together such that it can be added to a SQL query string.
obj = 'Account'
field_list = ', '.join(list(fields['name']))
query = 'SELECT ' + field_list + '''
FROM ''' + obj
print(query)
SELECT Id, IsDeleted, MasterRecordId, Name, Type, ParentId, BillingStreet, BillingCity, BillingState, BillingPostalCode, BillingCountry, BillingLatitude, BillingLongitude, BillingGeocodeAccuracy, BillingAddress, ShippingStreet, ShippingCity, ShippingState, ShippingPostalCode, ShippingCountry, ShippingLatitude, ShippingLongitude, ShippingGeocodeAccuracy, ShippingAddress, Phone, Fax, AccountNumber, Website, PhotoUrl, Sic, Industry, AnnualRevenue, NumberOfEmployees, Ownership, TickerSymbol, Description, Rating, Site, OwnerId, CreatedDate, CreatedById, LastModifiedDate, LastModifiedById, SystemModstamp, LastActivityDate, LastViewedDate, LastReferencedDate, Jigsaw, JigsawCompanyId, CleanStatus, AccountSource, DunsNumber, Tradestyle, NaicsCode, NaicsDesc, YearStarted, SicDesc, DandbCompanyId, CustomerPriority__c, SLA__c, Active__c, NumberofLocations__c, UpsellOpportunity__c, SLASerialNumber__c, SLAExpirationDate__c
FROM Account
q = sf.query_all(query)
a = (pd.DataFrame(q['records'])
.drop(columns=['attributes']))
print(str(a.shape) + ': records, fields')
a.info()
#a.head()
#pd.DataFrame(a.iloc[0].T)
(12, 65): records, fields
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 12 entries, 0 to 11
Data columns (total 65 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 Id 12 non-null object
1 IsDeleted 12 non-null bool
2 MasterRecordId 0 non-null object
3 Name 12 non-null object
4 Type 11 non-null object
5 ParentId 0 non-null object
6 BillingStreet 12 non-null object
7 BillingCity 11 non-null object
8 BillingState 11 non-null object
9 BillingPostalCode 4 non-null object
10 BillingCountry 4 non-null object
11 BillingLatitude 0 non-null object
12 BillingLongitude 0 non-null object
13 BillingGeocodeAccuracy 0 non-null object
14 BillingAddress 12 non-null object
15 ShippingStreet 10 non-null object
16 ShippingCity 2 non-null object
17 ShippingState 1 non-null object
18 ShippingPostalCode 2 non-null object
19 ShippingCountry 2 non-null object
20 ShippingLatitude 0 non-null object
21 ShippingLongitude 0 non-null object
22 ShippingGeocodeAccuracy 0 non-null object
23 ShippingAddress 10 non-null object
24 Phone 12 non-null object
25 Fax 12 non-null object
26 AccountNumber 11 non-null object
27 Website 12 non-null object
28 PhotoUrl 12 non-null object
29 Sic 11 non-null object
30 Industry 11 non-null object
31 AnnualRevenue 8 non-null float64
32 NumberOfEmployees 11 non-null float64
33 Ownership 11 non-null object
34 TickerSymbol 8 non-null object
35 Description 6 non-null object
36 Rating 7 non-null object
37 Site 0 non-null object
38 OwnerId 12 non-null object
39 CreatedDate 12 non-null object
40 CreatedById 12 non-null object
41 LastModifiedDate 12 non-null object
42 LastModifiedById 12 non-null object
43 SystemModstamp 12 non-null object
44 LastActivityDate 0 non-null object
45 LastViewedDate 0 non-null object
46 LastReferencedDate 0 non-null object
47 Jigsaw 0 non-null object
48 JigsawCompanyId 0 non-null object
49 CleanStatus 12 non-null object
50 AccountSource 0 non-null object
51 DunsNumber 0 non-null object
52 Tradestyle 0 non-null object
53 NaicsCode 0 non-null object
54 NaicsDesc 0 non-null object
55 YearStarted 0 non-null object
56 SicDesc 0 non-null object
57 DandbCompanyId 0 non-null object
58 CustomerPriority__c 9 non-null object
59 SLA__c 11 non-null object
60 Active__c 10 non-null object
61 NumberofLocations__c 11 non-null float64
62 UpsellOpportunity__c 11 non-null object
63 SLASerialNumber__c 11 non-null object
64 SLAExpirationDate__c 11 non-null object
dtypes: bool(1), float64(3), object(61)
memory usage: 6.1+ KB
pd.DataFrame(a.iloc[0].T)
| 0 | |
|---|---|
| Id | 0014W000026oyWmQAI |
| IsDeleted | False |
| MasterRecordId | None |
| Name | Express Logistics and Transport |
| Type | Customer - Channel |
| ParentId | None |
| BillingStreet | 620 SW 5th Avenue Suite 400\nPortland, Oregon ... |
| BillingCity | Portland |
| BillingState | OR |
| BillingPostalCode | None |
| BillingCountry | None |
| BillingLatitude | None |
| BillingLongitude | None |
| BillingGeocodeAccuracy | None |
| BillingAddress | {'city': 'Portland', 'country': None, 'geocode... |
| ShippingStreet | 620 SW 5th Avenue Suite 400\nPortland, Oregon ... |
| ShippingCity | None |
| ShippingState | None |
| ShippingPostalCode | None |
| ShippingCountry | None |
| ShippingLatitude | None |
| ShippingLongitude | None |
| ShippingGeocodeAccuracy | None |
| ShippingAddress | {'city': None, 'country': None, 'geocodeAccura... |
| Phone | (503) 421-7800 |
| Fax | (503) 421-7801 |
| AccountNumber | CC947211 |
| Website | www.expressl&t.net |
| PhotoUrl | /services/images/photo/0014W000026oyWmQAI |
| Sic | 8742 |
| Industry | Transportation |
| AnnualRevenue | 9.5e+08 |
| NumberOfEmployees | 12300 |
| Ownership | Public |
| TickerSymbol | EXLT |
| Description | Commerical logistics and transportation company. |
| Rating | Cold |
| Site | None |
| OwnerId | 0054W00000CKU8EQAX |
| CreatedDate | 2020-10-19T13:31:40.000+0000 |
| CreatedById | 0054W00000CKU8EQAX |
| LastModifiedDate | 2020-10-19T13:31:40.000+0000 |
| LastModifiedById | 0054W00000CKU8EQAX |
| SystemModstamp | 2020-10-19T13:31:40.000+0000 |
| LastActivityDate | None |
| LastViewedDate | None |
| LastReferencedDate | None |
| Jigsaw | None |
| JigsawCompanyId | None |
| CleanStatus | Pending |
| AccountSource | None |
| DunsNumber | None |
| Tradestyle | None |
| NaicsCode | None |
| NaicsDesc | None |
| YearStarted | None |
| SicDesc | None |
| DandbCompanyId | None |
| CustomerPriority__c | Medium |
| SLA__c | Platinum |
| Active__c | Yes |
| NumberofLocations__c | 150 |
| UpsellOpportunity__c | Maybe |
| SLASerialNumber__c | 4724 |
| SLAExpirationDate__c | 2020-05-15 |
col_rename = {'Id' : 'AccountId',
'Name' : 'AccountName'}
a = (a
.rename(columns=col_rename)
[col_rename.values()])
a.head()
| AccountId | AccountName | |
|---|---|---|
| 0 | 0014W000026oyWmQAI | Express Logistics and Transport |
| 1 | 0014W000026oyWoQAI | United Oil & Gas, UK |
| 2 | 0014W000026oyWjQAI | Dickenson plc |
| 3 | 0014W000026oyWqQAI | GenePoint |
| 4 | 0014W000026oyWkQAI | Grand Hotels & Resorts Ltd |
Single Code Snippet
desc = sf.Contact.describe()
fields = []
field_labels = [field['label'] for field in desc['fields']]
field_names = [field['name'] for field in desc['fields']]
field_types = [field['type'] for field in desc['fields']]
for label, name, dtype in zip(field_labels, field_names, field_types):
fields.append((label, name, dtype))
fields = pd.DataFrame(fields,
columns = ['label','name','type'])
print(str(fields.shape[0]) + ' fields')
obj = 'Contact'
field_list = ', '.join(list(fields['name']))
query = 'SELECT ' + field_list + '''
FROM ''' + obj
q = sf.query_all(query)
c = (pd.DataFrame(q['records'])
.drop(columns=['attributes']))
print(str(c.shape) + ': records, fields')
col_rename = {'Id' : 'ContactId',
'Name' : 'ContactName',
'Phone' : 'ContactPhone'}
c = (c
.rename(columns=col_rename)
[col_rename.values()])
c.head()
61 fields
(20, 61): records, fields
| ContactId | ContactName | ContactPhone | |
|---|---|---|---|
| 0 | 0034W00002FamUeQAJ | Jack Rogers | (336) 222-7000 |
| 1 | 0034W00002FamUrQAJ | Liz D'Cruz | (650) 450-8810 |
| 2 | 0034W00002FamUpQAJ | Ashley James | +44 191 4956203 |
| 3 | 0034W00002FamUlQAJ | Babara Levy | (503) 421-7800 |
| 4 | 0034W00002FamUfQAJ | Pat Stumuller | (014) 427-4427 |
Ref
Org-Level Desc Dictionary Deep Dive
desc = sf.describe()
list(desc.keys())
['encoding', 'maxBatchSize', 'sobjects']
desc['sobjects'][0]
OrderedDict([('activateable', False),
('createable', False),
('custom', False),
('customSetting', False),
('deletable', False),
('deprecatedAndHidden', False),
('feedEnabled', False),
('hasSubtypes', False),
('isSubtype', False),
('keyPrefix', None),
('label', 'Accepted Event Relation'),
('labelPlural', 'Accepted Event Relations'),
('layoutable', False),
('mergeable', False),
('mruEnabled', False),
('name', 'AcceptedEventRelation'),
('queryable', True),
('replicateable', False),
('retrieveable', True),
('searchable', False),
('triggerable', False),
('undeletable', False),
('updateable', False),
('urls',
OrderedDict([('rowTemplate',
'/services/data/v42.0/sobjects/AcceptedEventRelation/{ID}'),
('defaultValues',
'/services/data/v42.0/sobjects/AcceptedEventRelation/defaultValues?recordTypeId&fields'),
('describe',
'/services/data/v42.0/sobjects/AcceptedEventRelation/describe'),
('sobject',
'/services/data/v42.0/sobjects/AcceptedEventRelation')]))])
Object-Level Desc Dictionary Deep Dive
desc = sf.Account.describe()
list(desc.keys())
['actionOverrides',
'activateable',
'childRelationships',
'compactLayoutable',
'createable',
'custom',
'customSetting',
'deletable',
'deprecatedAndHidden',
'feedEnabled',
'fields',
'hasSubtypes',
'isSubtype',
'keyPrefix',
'label',
'labelPlural',
'layoutable',
'listviewable',
'lookupLayoutable',
'mergeable',
'mruEnabled',
'name',
'namedLayoutInfos',
'networkScopeFieldName',
'queryable',
'recordTypeInfos',
'replicateable',
'retrieveable',
'searchLayoutable',
'searchable',
'sobjectDescribeOption',
'supportedScopes',
'triggerable',
'undeletable',
'updateable',
'urls']
desc['name']
'Account'
desc['fields'][0]
OrderedDict([('aggregatable', True),
('autoNumber', False),
('byteLength', 18),
('calculated', False),
('calculatedFormula', None),
('cascadeDelete', False),
('caseSensitive', False),
('compoundFieldName', None),
('controllerName', None),
('createable', False),
('custom', False),
('defaultValue', None),
('defaultValueFormula', None),
('defaultedOnCreate', True),
('dependentPicklist', False),
('deprecatedAndHidden', False),
('digits', 0),
('displayLocationInDecimal', False),
('encrypted', False),
('externalId', False),
('extraTypeInfo', None),
('filterable', True),
('filteredLookupInfo', None),
('groupable', True),
('highScaleNumber', False),
('htmlFormatted', False),
('idLookup', True),
('inlineHelpText', None),
('label', 'Account ID'),
('length', 18),
('mask', None),
('maskType', None),
('name', 'Id'),
('nameField', False),
('namePointing', False),
('nillable', False),
('permissionable', False),
('picklistValues', []),
('polymorphicForeignKey', False),
('precision', 0),
('queryByDistance', False),
('referenceTargetField', None),
('referenceTo', []),
('relationshipName', None),
('relationshipOrder', None),
('restrictedDelete', False),
('restrictedPicklist', False),
('scale', 0),
('searchPrefilterable', False),
('soapType', 'tns:ID'),
('sortable', True),
('type', 'id'),
('unique', False),
('updateable', False),
('writeRequiresMasterRead', False)])