cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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
Highlighted

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
Highlighted
Solution Sage
Solution Sage

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.

 

Highlighted

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

Highlighted

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.

Highlighted

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

Highlighted

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
Frequent Visitor

[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
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

Top Solution Authors
Users online (7,501)