cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jamesefraserbb
Level: Powered On

MS Flow + Salesforce Connector: Flow changes date to a datetime and causes filters on Opportunities -> CloseDate field to fail

When attempting to use the Salesforce "Get Records" action on Opportunities, and specifying a date to filter CloseDate (Est. Close Date) on, it appears that Flow or the Salesforce connector is manipulating the query - converting the date to a datetime - which causes the query to fail as Salesforce expects a date for this field.

 

This occurs when you calculate a date using an expression, or when hard-coding it.   Encapsulating the value in quotes also gives an error as it expects the value unquoted.

 

I'm not sure if this error is in Flow or the Salesforce connector (or even in our specific instance of Salesforce), perhaps a schema issue somewhere.

 

Errorvalue of filter criterion for field 'CloseDate' must be of type date and should not be enclosed in quotes

Details:

{
"status": 400,
"message": "Salesforce failed to complete task: Message: \nx_bkb_Unused_License_Credit__c FROM Opportunity WHERE (CloseDate < 2019-05-10T00:00:00.0000000+00:00)\n ^\nERROR at Row:1:Column:7716\nvalue of filter criterion for field 'CloseDate' must be of type date and should not be enclosed in quotes\r\nclientRequestId: 415e97b0-76ae-4ca7-a6c8-acd3c8c6d161",
"source": "Microsoft.Azure.Connectors.Salesforce",
"errors": []
}

Picture of simple test flow:

image (7).png

 

Workaround (in some cases): In my case, I was able to work around this issue using the "LastModifiedDate" field, which is a datetime. Flow still changes the date to a datetime, but no error occurs as Salesforce is expecting a datetime.   (My use case is to find Won opportunities closed in the last 7 days, and Won opportunities last modified in the last 7 days is close enough in my case)

1 ACCEPTED SOLUTION

Accepted Solutions
v-zhos-msft
Level 10

Re: MS Flow + Salesforce Connector: Flow changes date to a datetime and causes filters on Opportunities -> CloseDate field to fail

Hi @jamesefraserbb ,

Obviously, your post is correct.

Thanks for your posting here, it would be helpful to anyone who encounters similar issues.

Best Regards,

Community Support Team _ Zhongys

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
v-zhos-msft
Level 10

Re: MS Flow + Salesforce Connector: Flow changes date to a datetime and causes filters on Opportunities -> CloseDate field to fail

Hi @jamesefraserbb ,

As the tips shown in the 'Filter Query' field: An ODATA filter query to restrict the entries returned (e.g. stringColumn eq 'string' OR numberColumn lt 123).

The format of the CloseDate is string, so you can compare  two string value in the Filter Query.

Here I suggest you use the 'Filter array' to get the records you want.

Sorry I don't has the permission to use Salesforce, so I use a similar action to test for you.

Capture1.PNG

Best Regards,

Community Support Team _ Zhongys

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

jamesefraserbb
Level: Powered On

Re: MS Flow + Salesforce Connector: Flow changes date to a datetime and causes filters on Opportunities -> CloseDate field to fail

Thanks for the alternative workaround suggestion @v-zhos-msft .  That may help, but we don't really want to be retrieving hundreds of records and then filter them down, that's what I was trying to avoid. To confirm - CloseDate is a date, not a string. 

 

 

I already have a viable workaround - I just wanted to flag up this bug so that it can be fixed in Flow/Salesforce (wherever it originates). The bug is that the perfectly valid date (not enclosed in quotes, as required) is being manipulated into a datetime which is invalid input for the DateClose field.

 

Thanks,

 

James

v-zhos-msft
Level 10

Re: MS Flow + Salesforce Connector: Flow changes date to a datetime and causes filters on Opportunities -> CloseDate field to fail

Hi @jamesefraserbb ,

I am glad to hear that your issue was successfully resolved.

I konw the CloseDate is a date.

But when the data transfer from the Salesforce to Flow, the format will change to string.

For example, I set a Date column in the SharePoint list, the data will change to string in the flow.

Capture2.PNG

If there is anything else we can do for you, please feel free to post in the forum.

Best Regards,

Community Support Team _ Zhongys

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

jamesefraserbb
Level: Powered On

Re: MS Flow + Salesforce Connector: Flow changes date to a datetime and causes filters on Opportunities -> CloseDate field to fail

Thanks - I just want to confirm if this bug has been reported correctly or if I need to submit a different way.

v-zhos-msft
Level 10

Re: MS Flow + Salesforce Connector: Flow changes date to a datetime and causes filters on Opportunities -> CloseDate field to fail

Hi @jamesefraserbb ,

Obviously, your post is correct.

Thanks for your posting here, it would be helpful to anyone who encounters similar issues.

Best Regards,

Community Support Team _ Zhongys

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
GKS
Level: Powered On

Re: MS Flow + Salesforce Connector: Flow changes date to a datetime and causes filters on Opportunities -> CloseDate field to fail

[updated - tested on opp close date instead of custom object, like OP issue]

 

I was absolutely shocked but soql date literals worked fine - even on Opportunity Close Date.  I confirm your experience that filters that work on other date fields ie createdDate do NOT work on closeDate.  

 

On created date this filter would work CreatedDate ge 2019-08-01

That would not work on CloseDate

 

But this worked fine in the filter : CloseDate ge LAST_90_DAYS

 

here is reference to date literals

https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_sel...

 

however - I cant get last N days to work, because that includes a colon which gets escaped and that gets escaped and the query fails.  I tried urlencoded stuff etc but could not ge that to work

 

so LAST_N_DAYS:7 would not work

 

and since you cant even pass a hard-coded date value, i dont see anything other than the date literals without a colon, which is a bug, but that is what it is.

 

 

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Users Online
Currently online: 277 members 5,312 guests
Please welcome our newest community members: