Skip to content

Tag: OData

22.04.2023
Ben
Leave a Comment

Working with FetchXML aliases in Power Automate

Posted in Power Automation, Dynamics 365, and Power Platform

For my private project (Key Box), I’ve built a FetchXML statement to group data already during the query, instead of building a logic in Power Automate. For that, I had to set an alias in the query.

FetchXML aliases in Power Automate

When I tried to access the “myOwner” alias, I did not find it in the Dynamics Content list of Power Automate. It only provided me the default Owner choices.

FetchXML aliases in Power Automate

Hoping that this is only a question of visualization, I tried to insert the “Owner (Value)” and got nothing. Therefore, I looked into the result of the Fetch query and found “myOwner”.

FetchXML aliases in Power Automate

tl;dr; How to access now FetchXML aliases in Power Automate?

  1. Select the original owner (or whatever you have) from the dynamic content list
  2. Place the cursor inside your action and copy the inserted formula
  3. Delete the inserted formula
  4. Paste the copied formula inside the expression area of the dynamics content
  5. Replace ‘@{‘ at the start and ‘}’ at the end
  6. Replace the original attribute name with your alias and press “OK”
FetchXML aliases in Power Automate
05.10.2014
Ben
3 Comments

Workflow ExecutionTimeSpan

Posted in Dynamics 365, Power Platform, and Revive

Last week I had a problem as part of a service request at Microsoft. I should test how long it takes to create a record for a particular entity by a workflow. The whole thing had to be very precise, because the request has been caused by an excessive execution time of a plug-in chain when such a record has been created.

Experimental Setup

The workflow should create a new contact, based on the data of an existing contact record. Let’s name it a Light-copy without relations. It is so configured that an entry in the systemjobs will be created.

Workflow-Log

Unfortunately, the workflow log is not as meaningful as intended. It contains the only times to the minute.


Workflow ExecutionTimeSpan

But that brings me at least an idea. Dynamics CRM stores the data internally with a higher accuracy than it displays it on the surface. Now I just have to get that data, mind you in a CRM Online system.

Workflow ExecutionTimeSpan

Since it is well known fact that you can not access the database in CRM Online directly, I planned to create a query that returns “Created On” and “Modified on” of my workflow. I used the OData Query Designer from the Dynamics XRM Tools to create that query.
When selecting the fields for my query I found a field named “Zeitspanne für Ausführung” field, or in English “ExecutionTimeSpan”. BINGO! That sounds good!

The Query

YOURSERVERURL/xrmservices/2011/OrganizationData.svc/AsyncOperationSet?$select=Name,StartedOn,CreatedOn,CompletedOn,ModifiedOn,ExecutionTimeSpan&$filter=OperationType/Value eq 10&$orderby=CreatedOn desc

To make the URL more understandable for you:

  • The Organization Data Service (OData) URL of your CRM.
    YOURSERVERURL/xrmservices/2011/OrganizationData.svc
     
  • The table from which I need the data.
    AsyncOperationSet?
     
  • The columns from the table that I would like to see as result.
    $select=Name,CreatedOn,StartedOn,CompletedOn,ModifiedOn,ExecutionTimeSpan
     
  • There should be only system jobs of type “Workflow” being returned.
    $filter=OperationType/Value eq 10
     
  • Finally, I would like to have the results sorted by creation date, so that the most recent are on top.
    Who wants to look for a long time.
    $orderby=CreatedOn desc

The Result

<xml version="1.0" encoding="utf-8" standalone="yes">
    <feed xml:base="http://od4u-crm2013.cloudapp.net/FC/XRMServices/2011/OrganizationData.svc/" xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" xmlns="http://www.w3.org/2005/Atom">
      <title type="text">AsyncOperationSet</title>
      <id>http://od4u-crm2013.cloudapp.net/FC/xrmservices/2011/OrganizationData.svc/AsyncOperationSet</id>
      <updated>2014-10-05T08:07:34Z</updated>
      <link rel="self" title="AsyncOperationSet" href="AsyncOperationSet" />
      <entry>
        <id>http://od4u-crm2013.cloudapp.net/FC/XRMServices/2011/OrganizationData.svc/AsyncOperationSet(guid'40107141-ab4b-e411-80df-00155d572098')</id>
        <title type="text">LightCopy</title>
        <updated>2014-10-05T08:07:34Z</updated>
        <author>
          <name />
        </author>
        <link rel="edit" title="AsyncOperation" href="AsyncOperationSet(guid'40107141-ab4b-e411-80df-00155d572098')" />
        <category term="Microsoft.Crm.Sdk.Data.Services.AsyncOperation" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme" />
        <content type="application/xml">
          <m:properties>
            <d:Name>LightCopy</d:Name>
            <d:CreatedOn m:type="Edm.DateTime">2014-10-04T09:46:04Z</d:CreatedOn>
            <d:StartedOn m:type="Edm.DateTime">2014-10-04T09:46:17Z</d:StartedOn>
            <d:CompletedOn m:type="Edm.DateTime">2014-10-04T09:46:22Z</d:CompletedOn>
            <d:ModifiedOn m:type="Edm.DateTime">2014-10-04T09:46:22Z</d:ModifiedOn>
            <d:ExecutionTimeSpan m:type="Edm.Double">1.7</d:ExecutionTimeSpan> 
            <d:ModifiedOn m:type="Edm.DateTime">2014-10-04T09:46:22Z</d:ModifiedOn>
          </m:properties>
        </content>
      </entry>
    </feed>

In the marked area you can see the columns that we have retrieved and in line 23 the value of workflow ExecutionTimeSpan.
Thus, so the workflow had an execution time of 1.7 seconds.

With Power Query for Excel you can you view the data a little more beautiful, or a whole series of workflows to evaluate and calculate averages with Excel or be generated diagrams. Simply Excel.

Hope it helps!