0

I'm using Python with simple-salesforce package.

Eventually I'm running queries like:

make_query("SELECT Id, Email, Name, Title  FROM Contact")

What I want to do is getting all records of the object that were added/deleted/modified since TIMESTAMP X

Should be something like:

SELECT Id, Email, Name, Title  FROM Contact WHERE date_updated>= TIMESTAMP '2020-02-05 09:54:22.070144'

How can this be done in SalesForce query?

3 Answers 3

0

Sure, you need to set your datetime to SOQL in this format:

SELECT Id, Email, Name, Title  FROM Contact WHERE SystemModStamp >= 2020-02-05T09:54:22Z ALL ROWS

Note, that there are no quotes.

UPDATE:

If you need to track updates made with automated processes, too (https://help.salesforce.com/articleView?id=000336532&language=en_US&type=1&mode=1) replace LastModifiedDate with SystemModStamp.

To track delete records: https://developer.salesforce.com/forums/?id=906F0000000BIZ4IAO

UPDATE: (ALL ROWS and IsDeleted explanation)

ALL ROWS modifier allows you to query both existing and deleted records (that are left in recycle bin). Assume you have 5 accounts and 3 accounts you have deleted previously.

SELECT Id FROM Account ALL ROWS will give you 8 records, whereas SELECT Id FROM Account WHERE IsDeleted = true ALL ROWS will give you only that 3 records that are placed in recycle bin.

Also, these 2 queries are equivalent, they will return 5 existing rows:

SELECT Id FROM Account

SELECT Id FROM Account WHERE IsDeleted = false ALL ROWS

UPDATE:

If you are getting:

MALFORMED_QUERY: ALL ROWS not allowed in this context

Try to use queryAll() through your connection: https://developer.salesforce.com/docs/atlas.en-us.api.meta/api/sforce_api_calls_queryall.htm

1
0

Yes, you can write the SOQL with Date and DateTime with where condition. I would suggest please read the documentation of Date and Datetime format, In this format, you can see lot of date/DateTime format.

You can also use Date literals accordingly to your need, please go through the documentation.

Example: enter image description here

SOQL Example with a datetime value

SELECT Id
FROM Account
WHERE CreatedDate > 2005-10-08T01:02:03Z
0

Answer for 2023

With Python and simple_salesforce, use the following:

import simple_salesforce

# This example uses security token authorization
sf = simple_salesforce.Salesforce(
    username=[USERNAME],
    password=[PASSWORD],
    security_token=[SECURITY_TOKEN],
)

# Make sure to format your timestamp correctly
# '%Y-%m-%dT%H:%M:%SZ'
timestamp = [YOUR_TIMESTAMP]
query = f"""
select      Id
            , Email
            , Name
            , Title
from        Contact
where       SystemModstamp >= {timestamp}
"""

response = sf.query_all(query, include_deleted=True)

A few things to note:

  • The SystemModstamp field can be leveraged as a catch-all for most recent create/update/delete for a given record. According to Salesforce:

'SystemModstamp' is the date and time when a record was last modified by a User or by an automated process (such as a trigger.)

  • The ALL ROWS keyword will not work with simple_salesforce query methods, even though it is the standard way which you would get deleted (and archived) records from Salesforce using a SOQL query. According to every Salesforce forum post I've found, "It's not currently available with the API." If you include it in your query, it will raise the following error:

MALFORMED_QUERY: ALL ROWS not allowed in this context

  • The query_all() function in simple_salesforce does NOT behave the same as the query_all() function in the API. Rather, it is "a convenience wrapper around query() and query_more()". This can be very confusing, for sure.
  • Under the hood, include_deleted=True changes the behavior of query_all() in simple_salesforce to pass 'queryAll/' to the request url instead of the default behavior which is to pass 'query/'. This means you will get all records regardless of whether they are deleted* and/or archived** or not. If you need to filter one of these categories out, that can be done in the query's where-clause (IsArchived = false, IsDeleted = true, etc)
    • *By "deleted", I mean records which are currently in the "Recycle Bin" in Salesforce. These records stay in the Recycle Bin for 15 days (unless otherwise extended) before they are permanently deleted by Salesforce. If there is some method by which records can be retrieved after being permanently deleted, I am not familiar with it.
    • **By "archived", I mean records for Activity (i.e. Task, Event) which are automatically archived by Salesforce. These remain accessible indefinitely unless otherwise deleted.
  • Passing include_deleted=True to sf.query_all() can potentially increase the amount of records scanned (particularly with Activity records which can be archived), so applying a filter to narrow your query will help with speed. Filtering date fields helps Salesforce be efficient in such a case.

Disclaimer: I can verify that this works in my environment:

  • Python 3.7.3
  • simple_salesforce 1.12.3

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .