0

Due to limited budget, I am writing my own integration tools using python3, beatbox and soap api. I was wondering if someone can share their knowledge while answering my questions.

I will import all data at one point of time and after that I just want to import only those records which have been changed since previous day execution of the script. The script will run every day at 11:00 PM EST. So I want to capture all those data which has been created or modified between yesterday 11:00 PM EST to today 11:00 PM EST.

  1. Is it good idea to import data like below?

    SELECT id,name from account where lastmodifieddate = yesterday
    
  2. Will lastmodifieddate capture creation and modification of record, both?

  3. What if I want to capture those records where only specific fields have changed? For example if an opportunity's stage has changed then I need that but if total amount has changed I don't?

1

SELECT id,name from account where lastmodifieddate = yesterday

This will span from midnight to midnight, local time. If you run it at 11 PM, you won't include the current day's activity, only records between 47 and 23 hours before the time the script ran.

Will lastmodifieddate capture creation and modification of record, both?

Yes, last modified date is set to the created date on initial creation, so simply filtering on that field would be sufficient (but there's a better way).

What if I want to capture those records where only specific fields have changed? For example if an opportunity's stage has changed then I need that but if total amount has changed I don't?

You can't do that. There are tools to help you replicate data that's specific to whatever criteria you want, but there's no direct query for that.


In the general case, you should use the Replication API to make sure you don't miss any updates. This API covers gaps that you might miss if you're too strict with your queries, without giving you overlapping data if you're too liberal with your queries. The reason why we recommend this API is because it also takes into account in-flight requests (requests that are being processed by the server but cannot be queried yet). It's generally recommended that you use this API more frequently than daily, and is designed to be run as frequently as 12 times an hour (every five minutes).

If you want notifications about certain updates, such as a specific field changing, you need to keep track of those changes somehow. If you want to keep the logic on the local side, consider setting up field tracking on the object's field; you can then query the history for specific field changes. If you want to have salesforce notify you, set up workflow outbound message, which will let you specify trigger conditions, and you'll receive asynchronous notifications when your criteria is met.

  • I can schedule midnight to midnight. So just to be sure, lastmodifieddate will capture anything between 12:00AM yesterday - 12:00 AM today(if I run the script at let's say 12:00 AM)? – r0xette Nov 17 '16 at 18:13
  • Can you point out to some code samples for Replication API? I couldn't find any code samples online. – r0xette Nov 17 '16 at 18:14
  • @r0xette There's examples in C# and Java on the pages in the sub-categories of the first link in my answer. Also, yes, if you were to query a few seconds after midnight using lastmodifieddate = yesterday, you'd get all of the changes in the past 24 hours. – sfdcfox Nov 17 '16 at 18:31
0

Take a look at the existing [inexpensive] Salesforce data replication solutions:

  • StitchData (starting $100/mo)

    ETL your Salesforce data to the data warehouse of your choice:

    • Amazon Redshift
    • Google BigQuery
    • PostgreSQL
  • CopyStorm ($1,260, optional $630 annual update/support plan afterwards)

    Backup of your production Salesforce! Copying the structure and data to one of these databases:

    • SQLServer
    • Oracle
    • PostgreSQL
    • MySQL
    • H2
  • DBAmp ($1,295/year)

    Replicate all your Salesforce.com data into MS SQL Server with a single stored procedure. Bulk insert and update MS SQL Server tables into Salesforce.com

  • Due to restricted budget and newly implementation of SFDC, we won't be able to go with third party tools at this point. But your input is valuable. – r0xette Nov 17 '16 at 18:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.