search

OpenFn / Miracle-Feet

Jobs for Miracle Feet's CommCare to Salesforce integration
1 stars 1 forks source link

Filter fetching Clininc (`Account`-s) by LastModifiedDate date #111

Closed ritazagoni closed 2 years ago

ritazagoni commented 2 years ago

Background, context, and business value

Original issue: https://github.com/OpenFn/Miracle-Feet/issues/80

The getInactiveClinincs.js job runs daily at 12.05AM, fetching Account-s from Salesforce. In the subsequent run we only want to fetch Clinics (Account-s) that have been modified since the last run.

The specific request, in as few words as possible

In the getInactiveClinics job we would like to limit the query so that we only fetch the Account-s where LastModifiedDate > "12.05AM the day before" (ie. since the last run of the job).

state.json

See LP for MiracleFeet Salesforce SMS Sandbox and Infobip Portal (MiracleFeet)

adaptor

language-salesforce, language-http

aleksa-krolls commented 2 years ago

@ritazagoni @lakhassane here I wonder if we need to worry about time zones again because the SF system time will be different than the OpenFn time. To avoid having to think about this, we could set a relative date filter. For example, this SF query will fetch anything updated in the last day... 

SELECT xyz FROM Account WHERE LastModifiedDate >= LAST_N_DAYS:1

If we assume this runs at the same time daily, do you think that will work? Maybe we adjust the OpenFn job to run exactly at midnight or a minute after to be sure?

Rita, if this will work... you can just add AND LastModifiedDate >= LAST_N_DAYS:1 to the existing SF query in the job.

lakhassane commented 2 years ago

@aleksa-krolls @ritazagoni Let's discuss this monday please. I can't really tell if LAST_N_DAYS:1 is a thing (literally) or a placeholder to be replaced by something else.

Also yes completely agree that we should worry about timezone otherwise we lose any change that occurred in the time difference span.

aleksa-krolls commented 2 years ago

@lakhassane yes, LAST_N_DAYS:1 is literally a filter you can add. I've left links below to SF docs, including how to convert SF timezones in case this is needed.

@ritazagoni can you please weigh in on the approach w.r.t. time zone and determine next steps with Mamadou, or whether changing the time of the cron trigger will be enough.

https://developer.salesforce.com/forums/?id=906F00000008mk6IAA https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_convert_time_zone.htm

ritazagoni commented 2 years ago

This worked great, thanks both!