Power Automate Filter formatting incorrect for timestamps

[Roland80]

When you're filtering by dates, the resulting FetchXML is correct, but upon converting to Power Automate parameters, the dates are missing single quotes. For example the following FetchXML:

<fetch top="25">
  <entity name="new_mytable">
    <attribute name="new_mytableid" />
    <filter type="or">
      <filter type="and">
        <condition attribute="createdon" operator="ge" value="2-8-2024 07:06:47" />
        <condition attribute="createdon" operator="le" value="2-8-2024 07:16:25" />
      </filter>
      <filter type="and">
        <condition attribute="modifiedon" operator="ge" value="2-8-2024 07:06:47" />
        <condition attribute="modifiedon" operator="ge" value="2-8-2024 07:16:25" />
      </filter>
    </filter>
  </entity>
</fetch>

results in an OData filter of ((createdon ge 2024-08-02T05:06:47Z and createdon le 2024-08-02T05:16:25Z) or (modifiedon ge 2024-08-02T05:06:47Z and modifiedon ge 2024-08-02T05:16:25Z))

This doesn't work, as Power Automate expects single quotes around the timestamps: ((createdon ge '2024-08-02T05:06:47Z' and createdon le '2024-08-02T05:16:25Z') or (modifiedon ge '2024-08-02T05:06:47Z' and modifiedon ge '2024-08-02T05:16:25Z'))

I discovered this as I'm trying to list records in Dataverse based on the start and end times of a Dataflow refresh 'run'. This is in FetchXML Builder version 1.2024.4.1

[rappen]

I hope this is a format issue... When I try with your query (just changed to another custom table) and I can't make it not working.

<fetch top='25'>
  <entity name='rapp_rocket'>
    <attribute name='rapp_name' />
    <filter type='or'>
      <filter type='and'>
        <condition attribute='createdon' operator='ge' value='2-8-2024 07:06:47' />
        <condition attribute='createdon' operator='le' value='2-8-2024 07:16:25' />
      </filter>
      <filter type='and'>
        <condition attribute='modifiedon' operator='ge' value='2-8-2024 07:06:47' />
        <condition attribute='modifiedon' operator='ge' value='2-8-2024 07:16:25' />
      </filter>
    </filter>
  </entity>
</fetch>

Power Automate accepts both with/without single quotes. I'm sorry that I can't get this issue.

Tried also with the "full OData", same there, I can't get it wrong.

https://jonasspace.crm4.dynamics.com/api/data/v9.2/rapp_rockets?$select=rapp_name&$filter=((createdon ge 2024-02-08T06:06:47Z and createdon le 2024-02-08T06:16:25Z) or (modifiedon ge 2024-02-08T06:06:47Z and modifiedon ge 2024-02-08T06:16:25Z))&$top=25

Do you have any more info for this issue, so I can force the problem?

Another thing, format related, is that my query with for me unknow format (d-m-yyyy or m-d-yyyy) converts to date 2024-02-08 (eight of Feb) instead of your (second of Aug). For next version of FXB everything is hard code to ISO8601 format (which is the international format, just like numbers thousand-hundreds-tens-ones-tenths-etc, biggest parts first, smallest at the end: yyyy-mm-dd HH:MM:ss.fff) so there will never be a question on which date are we really meaning :)

[Roland80]

Sorry for the delay in reponding, but I've been on much needed vacation :)

The date formatting is possibly due to the environment I'm testing this in, has its base language set to Dutch and Dutch date formatting is d-m-yyyy. When I try to run a FetchXML builder query on the Account table with a date chosen using the date picker, I get this error:

Error Time: 2024-08-21 09:29:33.774
System.ServiceModel.FaultException<Microsoft.Xrm.Sdk.OrganizationServiceFault> (0x80040239)
The date-time format for 21-8-2024 09:29:16 is invalid, or value is outside the supported range.
FetchXML Builder
Server stack trace: 
   at System.ServiceModel.Channels.ServiceChannel.HandleReply(ProxyOperationRuntime operation, ProxyRpc& rpc)
   at System.ServiceModel.Channels.ServiceChannel.Call(String action, Boolean oneway, ProxyOperationRuntime operation, Object[] ins, Object[] outs, TimeSpan timeout)
   at System.ServiceModel.Channels.ServiceChannelProxy.InvokeService(IMethodCallMessage methodCall, ProxyOperationRuntime operation)
   at System.ServiceModel.Channels.ServiceChannelProxy.Invoke(IMessage message)

Exception rethrown at [0]: 
   at Microsoft.Xrm.Tooling.Connector.CrmServiceClient.RetrieveMultiple(QueryBase query)
   at Rappen.XRM.Helpers.Extensions.ServiceExtensions.RetrieveMultipleAll(IOrganizationService service, QueryBase query, BackgroundWorker worker, DoWorkEventArgs eventargs, String message, Boolean showMessageOnFirstPage)
   at Rappen.XTB.FetchXmlBuilder.FetchXmlBuilder.<>c__DisplayClass127_0.<RetrieveMultiple>b__0(BackgroundWorker worker, DoWorkEventArgs eventargs)
   at System.ComponentModel.BackgroundWorker.OnDoWork(DoWorkEventArgs e)
   at System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument)

That's with the following query:

When I change August 21 to August 2, the error goes away, which makes me believe that Dataverse is rejecting the 8th day of the 21st month.

The flow I'm using, starts when a Dataflow has finished refreshing. I then List rows to get all records created or modified by the Dataflow in a specific table:

This gives the following input, which works:

{
  "entityName": "ilx_kwetsbaarhedens",
  "$select": "ilx_kwetsbaarhedenid, ilx_pad_volledig",
  "$filter": "(createdon ge '2024-08-21 07:30:16Z' and createdon le '2024-08-21 07:34:51Z')"
}

But when the quotes are removed, the action fails with the following error:

{
  "error": {
    "code": "0x80060888",
    "message": "')' or operator expected at position 25 in '(createdon ge 2024-08-21 07:30:16Z and createdon le 2024-08-21 07:34:51Z)'."
  }
}

This is based on this action input

{
  "entityName": "ilx_kwetsbaarhedens",
  "$select": "ilx_kwetsbaarhedenid, ilx_pad_volledig",
  "$filter": "(createdon ge 2024-08-21 07:30:16Z and createdon le 2024-08-21 07:34:51Z)"
}

Perhaps the quote requirement is also the result of the base language of the environment?

[rappen]

Thanks Roland for all details! To me it seems like there are two different issues:

1. Date formatting issue local vs server
2. Quotes sometimes required in Power Automate

I'm now working on it. Stay tuned...

[rappen]

Okay, the convert to OData/WebAPI/Power Automate is done by FetchXmlToWebAPI by @MarkMpn. These lines may need to add single-quotes around the data time... I do not entirely understand it or why, and in my environments, it works without quotes, but when testing with quotes, it works, too. @MarkMpn, can you help us?

FYI - the code for FXB now forces the date time format to ISO 8601, if that affects anything.

[MarkMpn]

It seems to require the quotes if the value contains a space, so createdon gt 2024-08-28 or createdon gt 2024-08-28T12:00:00Z work without a quote, but createdon gt '2024-08-28 12:00:00Z' requires the quote. I can add some logic to handle this in the conversion, but if FXB will enforce ISO 8601 format then that should avoid the problem as well.

[rappen]

Right, thanks @MarkMpn So I guess next release with my force will solve it, but I also guess you want that your code will always work, so you will add this extra logic anyway... ;)

[MarkMpn]

I've just been looking into this some more. There is already logic in the conversion to remove the spaces, so it should always produce a filter in the format modifiedon ge 2024-09-02T01:02:03Z. This format works for me, but the original report by @Roland80 indicated this did not work.

@Roland80 can you please clarify if your original query as reported:

((createdon ge 2024-08-02T05:06:47Z and createdon le 2024-08-02T05:16:25Z) or (modifiedon ge 2024-08-02T05:06:47Z and modifiedon ge 2024-08-02T05:16:25Z))

still fails in your environment, or does the issue only occur if you replace these datetime literal values with variables in Power Automate?