+ Start a Discussion
emale999emale999 

SOQL: where [field1] > [field2] doesn't work

I have to write a script to synchronize some data in salesforce with our MIS. This should be done when new records are inserted or existing records are edited on both sides. Therefore I have a new field Synced__c which holds the timestamp of the last synchronisation with our MIS.
To get all changed records wouldn't be any great problem for someone who knows SQL (and really isn't a problem in our MIS):

select [...] where SystemModstamp > Synced__c

Unfortunately this doesn't work in salesforce because I cannot use fieldnames on the right side of logical statements - I was told that in another thread and the solution was to use an Apex Trigger to set Synced__c = null when the record was modified so that I can write

select [...] where Synced__c = null

So I prepared the whole synchronisation skripts tested it in my testaccount and when I wanted to go live it doesn't work because we have a "Professional Edition+API" which doesn't support Apex Triggers. **bleep**!

After being miffed for two months with the support which only has the solution to upgrade to a Developer Account I'm opening that thread (should have done that before!). Has anyone an idea how to get this working WITHOUT upgrading to a Developer Account?
Parameters are:
- Select all records that have been modified since they were synchronized the last time
- Timestamp of synchronisation ist stored per record in Synced__c
- Synced__c will be updated from my script when synchronized - that works anyway

This thing is getting me mad...
SFFSFF

I think you want the getUpdated() method:

 

http://www.salesforce.com/us/developer/docs/api/Content/sforce_api_calls_getupdated.htm

 

I'm not 100% clear on why you are maintaining the information on when the SFDC record was last synchronized in SFDC - doesn't that cause a synch loop? When I've done this in the past, I just used the SystemModStamp value on my SQL Server record to determine when the last synch happened, and called getUpdated() with the name of the object to be synched, the start date/time, and the end date/time.

 

(Note that getUpdated() has some funny rules on how much data it will process - you have to architect your solution around them.)

 

 

emale999emale999
Thanks for the reply but that's not really what I've wanted.

I wanted to get an item that was modified since the last synchronisation.
Using getUpdated() presumes that I know when the last synchronisation happened. Maybe I could get this to work but at the one hand it's not safe enough for me (the time of the salesforces database differs some seconds from our server; there would be a gap between the time our server thinks the records were synced and the time the salesforce server thinks they were synced) and at the other hand I had to store the "last synced"-date somewhere.

Maybe I just have to reduce synchronisation to one time a day. Then I could use that function. That's not very nice for us but at least doable.