The great thing when you are working with Azure Logic Apps is that, if you have previously spent any amount of time understanding Power Automate flows, the knowledge gained in this area is instantly transferable. That’s mainly because Power Automate is utilising Logic Apps underneath the hood. In practice, therefore, you can anticipate that a lot of the same trigger actions, connectors and general functionality will be identical. So why would you consider using Logic Apps at all in the first place? There are a few reasons why:
- Logic Apps are a consumption-based service, meaning you are charged based on the number of monthly executions. Depending on the volumes involved, this can come in significantly cheaper when compared to buying a Power Automate license.
- Logic Apps support the ability to execute within a dedicated location as part of integration service environments (ISE’s).
- It is far easier to incorporate Logic Apps development as part of your Application Lifecycle Management (ALM) processes, with the ability to do one-click extracts of deployment templates. What’s more, you have a high degree of control over the parameterisation, deployment location and general functionality of Logic Apps when compared with Power Automate flows.
So in most senses, Logic Apps functionality can be argued as being on a par with that of Power Automate flows, often exceeding capabilities in some areas and being far better suited for large-scale, pro-developer integrations between systems.
Now, I use the workĀ argued, as I cannot reliably say that the functionality on offer is complete like-for-like, especially when it comes to working with Dynamics 365 / the Common Data Service. Now it is true that we have a Common Data Service connector within Logic Apps, that has a list of actions that is equivalent to what is available within Power Automate:
However, there are two issues I can specifically highlight here:
- Power Automate introduced the Common Data Service (current environment) connector a while back, which does exactly what it says on the tin. Also, this connector offers several additional features/benefits, such as the ability to execute change set requests or upload files/images to an entity record. This connector is not exposed at all within Logic Apps, even if you are in the same tenant where your Common Data Service environments reside. Logic App users, therefore, lose some degree of functionality here compared to Power Automate flows.
- When using theĀ List records action, you have access to all of the common OData filter queries, such as filter, top and orderby, thereby allowing you to return a specific subset of the data you require. However, you have no option here to indicate the precise fields you wish to return - via a select option or similar. This limitation can lead to your requests into the Common Data Service processing far more data than necessary and lengthen the execution time of your Logic Apps.
You can get around the second of these issues straightforwardly enough by using a Parse JSON action to consume and then remove any fields you don’t wish to process any further as part of your Logic App. But what if the data you wish to process from the Common Data Service is variable? For example, let’s assume that the entity and list of fields to be processed differs each time the Logic App runs - dictated by either the creation of a database record or by the details of a request passed to an HTTP endpoint setup on the Logic App. In this instance, it is not possible to use the Parse JSON action to parameterise this information satisfactorily. Indeed, to the best of my knowledge, it is not possible to utilise any of the collection/data operation functions available via the Workflow Definition Language to perform this action instead. Fortunately, where there is a will - and a bit of knowledge of C# - there is a way ;)
Logic Apps have had the longstanding capability to integrate alongside Azure Functions, thereby allowing developers to off-load any complex processing to a programming interface that can do almost anything you can imagine. So, in this case, Azure Functions becomes our rescuer and allows us to build out a basic Function App to support us. We can design the function app to accept two core bits of information:
- A JSON array containing the response from Common Data Service. The JSON’s basic structure should look somewhat similar to the example below, representing two Lead records, for the function to parse it correctly. As you can see, this is a LOT of data that Logic Apps returns by default, for a measly 2 Lead records:
[
{
"@odata.id": "https://mycrminstance.crm11.dynamics.com/api/data/v9.0/leads(cd4299fe-a080-ea11-a811-002248012503)",
"@odata.etag": "",
"ItemInternalId": "cd4299fe-a080-ea11-a811-002248012503",
"prioritycode": 1,
"_prioritycode_label": "Default Value",
"address2_addresstypecode": 1,
"_address2_addresstypecode_label": "Default Value",
"merged": false,
"emailaddress1": "[email protected]",
"confirminterest": false,
"numberofemployees": 200,
"decisionmaker": false,
"msdyn_ordertype": 192350000,
"_msdyn_ordertype_label": "Item based",
"modifiedon": "2020-04-17T11:46:57Z",
"importsequencenumber": 1,
"address1_composite": "789 Jones Blvd\r\nLa Vergne, TN 57332\r\nU.S.",
"lastname": "McKay (sample)",
"donotpostalmail": false,
"revenue_base": 100000.0,
"preferredcontactmethodcode": 1,
"_preferredcontactmethodcode_label": "Any",
"_ownerid_value": "9ec35ef0-9ec9-4e2e-b4af-e16bf5ba3b9b",
"_ownerid_type": "systemusers",
"_campaignid_value": "e14099fe-a080-ea11-a811-002248012503",
"_campaignid_type": "campaigns",
"firstname": "Yvonne",
"evaluatefit": false,
"yomifullname": "Yvonne McKay (sample)",
"donotemail": false,
"fullname": "Yvonne McKay (sample)",
"msdyn_gdproptout": false,
"statuscode": 1,
"_statuscode_label": "New",
"createdon": "2020-04-17T11:46:57Z",
"address1_stateorprovince": "TN",
"companyname": "Fourth Coffee (sample)",
"donotfax": false,
"leadsourcecode": 1,
"_leadsourcecode_label": "Advertisement",
"jobtitle": "Purchasing Manager",
"address1_country": "U.S.",
"versionnumber": 11127594,
"address1_line1": "789 Jones Blvd",
"telephone1": "555-0146",
"donotsendmm": false,
"leadqualitycode": 2,
"_leadqualitycode_label": "Warm",
"donotphone": false,
"_transactioncurrencyid_value": "d85b13bb-081f-ea11-a812-00224801bc51",
"_transactioncurrencyid_type": "transactioncurrencies",
"subject": "New store opened this year - follow up (sample)",
"address1_addresstypecode": 1,
"_address1_addresstypecode_label": "Default Value",
"donotbulkemail": false,
"exchangerate": 1.0,
"_modifiedby_value": "9ec35ef0-9ec9-4e2e-b4af-e16bf5ba3b9b",
"_modifiedby_type": "systemusers",
"followemail": true,
"leadid": "cd4299fe-a080-ea11-a811-002248012503",
"_createdby_value": "9ec35ef0-9ec9-4e2e-b4af-e16bf5ba3b9b",
"_createdby_type": "systemusers",
"websiteurl": "http://www.fourthcoffee.com/",
"address1_city": "La Vergne",
"salesstagecode": 1,
"_salesstagecode_label": "Default Value",
"revenue": 100000.0,
"participatesinworkflow": false,
"statecode": 0,
"_statecode_label": "Open",
"_owningbusinessunit_value": "9941c9c6-f71e-ea11-a812-00224801bc51",
"_owningbusinessunit_type": "businessunits",
"address1_postalcode": "57332",
"budgetamount_base": null,
"salutation": null,
"address1_latitude": null,
"address1_fax": null,
"sic": null,
"yomilastname": null,
"address1_longitude": null,
"telephone2": null,
"timespentbymeonemailandmeetings": null,
"address1_upszone": null,
"schedulefollowup_qualify": null,
"_slainvokedid_value": null,
"schedulefollowup_prospect": null,
"purchasetimeframe": null,
"_purchasetimeframe_label": "",
"_owningteam_value": null,
"industrycode": null,
"_industrycode_label": "",
"budgetstatus": null,
"_budgetstatus_label": "",
"stageid": null,
"_accountid_value": null,
"lastonholdtime": null,
"address2_country": null,
"address1_utcoffset": null,
"onholdtime": null,
"address1_line2": null,
"_createdonbehalfby_value": null,
"telephone3": null,
"fax": null,
"emailaddress2": null,
"_parentcontactid_value": null,
"businesscard": null,
"estimatedamount": null,
"address2_telephone1": null,
"description": null,
"overriddencreatedon": null,
"address2_county": null,
"address2_stateorprovince": null,
"_masterid_value": null,
"_qualifyingopportunityid_value": null,
"address2_city": null,
"lastusedincampaign": null,
"address2_composite": null,
"_originatingcaseid_value": null,
"utcconversiontimezonecode": null,
"purchaseprocess": null,
"_purchaseprocess_label": "",
"address2_line3": null,
"emailaddress3": null,
"salesstage": null,
"_salesstage_label": "",
"_relatedobjectid_value": null,
"processid": null,
"pager": null,
"address2_name": null,
"address2_upszone": null,
"_modifiedonbehalfby_value": null,
"mobilephone": null,
"initialcommunication": null,
"_initialcommunication_label": "",
"address2_latitude": null,
"qualificationcomments": null,
"address1_name": null,
"middlename": null,
"address1_telephone2": null,
"address2_utcoffset": null,
"entityimage": null,
"_parentaccountid_value": null,
"address1_telephone1": null,
"address1_county": null,
"address1_line3": null,
"yomicompanyname": null,
"need": null,
"_need_label": "",
"yomimiddlename": null,
"entityimage_url": null,
"entityimageid": null,
"address2_telephone3": null,
"_slaid_value": null,
"address2_line1": null,
"address2_postofficebox": null,
"address2_longitude": null,
"address2_telephone2": null,
"address2_fax": null,
"traversedpath": null,
"address2_line2": null,
"businesscardattributes": null,
"address1_postofficebox": null,
"entityimage_timestamp": null,
"_customerid_value": null,
"teamsfollowed": null,
"yomifirstname": null,
"timezoneruleversionnumber": null,
"_contactid_value": null,
"address1_telephone3": null,
"estimatedvalue": null,
"estimatedclosedate": null,
"address2_postalcode": null,
"budgetamount": null,
"estimatedamount_base": null
},
{
"@odata.id": "https://mycrminstance.crm11.dynamics.com/api/data/v9.0/leads(cf4299fe-a080-ea11-a811-002248012503)",
"@odata.etag": "",
"ItemInternalId": "cf4299fe-a080-ea11-a811-002248012503",
"prioritycode": 1,
"_prioritycode_label": "Default Value",
"address2_addresstypecode": 1,
"_address2_addresstypecode_label": "Default Value",
"merged": false,
"emailaddress1": "[email protected]",
"confirminterest": false,
"numberofemployees": 2000,
"decisionmaker": false,
"msdyn_ordertype": 192350000,
"_msdyn_ordertype_label": "Item based",
"modifiedon": "2020-04-17T11:46:58Z",
"importsequencenumber": 1,
"address1_composite": "797 Roosevelt Ave NE\r\nSaint Louis, MO 83385\r\nU.S.",
"lastname": "Stubberod (sample)",
"donotpostalmail": false,
"revenue_base": 150000.0,
"preferredcontactmethodcode": 1,
"_preferredcontactmethodcode_label": "Any",
"_ownerid_value": "9ec35ef0-9ec9-4e2e-b4af-e16bf5ba3b9b",
"_ownerid_type": "systemusers",
"_campaignid_value": "e14099fe-a080-ea11-a811-002248012503",
"_campaignid_type": "campaigns",
"firstname": "Susanna",
"evaluatefit": false,
"yomifullname": "Susanna Stubberod (sample)",
"donotemail": false,
"fullname": "Susanna Stubberod (sample)",
"msdyn_gdproptout": false,
"statuscode": 1,
"_statuscode_label": "New",
"createdon": "2020-04-17T11:46:58Z",
"address1_stateorprovince": "MO",
"companyname": "Litware, Inc. (sample)",
"donotfax": false,
"leadsourcecode": 7,
"_leadsourcecode_label": "Trade Show",
"jobtitle": "Purchasing Manager",
"address1_country": "U.S.",
"versionnumber": 11127601,
"address1_line1": "797 Roosevelt Ave NE",
"telephone1": "555-0127",
"donotsendmm": false,
"leadqualitycode": 1,
"_leadqualitycode_label": "Hot",
"donotphone": false,
"_transactioncurrencyid_value": "d85b13bb-081f-ea11-a812-00224801bc51",
"_transactioncurrencyid_type": "transactioncurrencies",
"subject": "Mailed an interest card back (sample)",
"address1_addresstypecode": 1,
"_address1_addresstypecode_label": "Default Value",
"donotbulkemail": false,
"exchangerate": 1.0,
"_modifiedby_value": "9ec35ef0-9ec9-4e2e-b4af-e16bf5ba3b9b",
"_modifiedby_type": "systemusers",
"followemail": true,
"leadid": "cf4299fe-a080-ea11-a811-002248012503",
"_createdby_value": "9ec35ef0-9ec9-4e2e-b4af-e16bf5ba3b9b",
"_createdby_type": "systemusers",
"websiteurl": "http://www.litwareinc.com/",
"address1_city": "Saint Louis",
"salesstagecode": 1,
"_salesstagecode_label": "Default Value",
"revenue": 150000.0,
"participatesinworkflow": false,
"statecode": 0,
"_statecode_label": "Open",
"_owningbusinessunit_value": "9941c9c6-f71e-ea11-a812-00224801bc51",
"_owningbusinessunit_type": "businessunits",
"address1_postalcode": "83385",
"budgetamount_base": 3000.0,
"salutation": null,
"address1_latitude": null,
"address1_fax": null,
"sic": null,
"yomilastname": null,
"address1_longitude": null,
"telephone2": null,
"timespentbymeonemailandmeetings": null,
"address1_upszone": null,
"schedulefollowup_qualify": null,
"_slainvokedid_value": null,
"schedulefollowup_prospect": null,
"purchasetimeframe": 2,
"_purchasetimeframe_label": "Next Quarter",
"_owningteam_value": null,
"industrycode": null,
"_industrycode_label": "",
"budgetstatus": null,
"_budgetstatus_label": "",
"stageid": null,
"_accountid_value": null,
"lastonholdtime": null,
"address2_country": null,
"address1_utcoffset": null,
"onholdtime": null,
"address1_line2": null,
"_createdonbehalfby_value": null,
"telephone3": null,
"fax": null,
"emailaddress2": null,
"_parentcontactid_value": null,
"businesscard": null,
"estimatedamount": null,
"address2_telephone1": null,
"description": null,
"overriddencreatedon": null,
"address2_county": null,
"address2_stateorprovince": null,
"_masterid_value": null,
"_qualifyingopportunityid_value": null,
"address2_city": null,
"lastusedincampaign": null,
"address2_composite": null,
"_originatingcaseid_value": null,
"utcconversiontimezonecode": null,
"purchaseprocess": 1,
"_purchaseprocess_label": "Committee",
"address2_line3": null,
"emailaddress3": null,
"salesstage": null,
"_salesstage_label": "",
"_relatedobjectid_value": null,
"processid": null,
"pager": null,
"address2_name": null,
"address2_upszone": null,
"_modifiedonbehalfby_value": null,
"mobilephone": null,
"initialcommunication": null,
"_initialcommunication_label": "",
"address2_latitude": null,
"qualificationcomments": null,
"address1_name": null,
"middlename": null,
"address1_telephone2": null,
"address2_utcoffset": null,
"entityimage": null,
"_parentaccountid_value": null,
"address1_telephone1": null,
"address1_county": null,
"address1_line3": null,
"yomicompanyname": null,
"need": null,
"_need_label": "",
"yomimiddlename": null,
"entityimage_url": null,
"entityimageid": null,
"address2_telephone3": null,
"_slaid_value": null,
"address2_line1": null,
"address2_postofficebox": null,
"address2_longitude": null,
"address2_telephone2": null,
"address2_fax": null,
"traversedpath": null,
"address2_line2": null,
"businesscardattributes": null,
"address1_postofficebox": null,
"entityimage_timestamp": null,
"_customerid_value": null,
"teamsfollowed": null,
"yomifirstname": null,
"timezoneruleversionnumber": null,
"_contactid_value": null,
"address1_telephone3": null,
"estimatedvalue": null,
"estimatedclosedate": null,
"address2_postalcode": null,
"budgetamount": 3000.0,
"estimatedamount_base": null
}
]
- A query parameter header - calledĀ attributes - containing the list of attributes we wish to return as a comma-separated list e.g. fullname,companyname
You can then feed these two bits of information into an Azure Function endpoint, with the complete code for this illustrated below:
using System.IO;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Azure.WebJobs;
using Microsoft.Azure.WebJobs.Extensions.Http;
using Microsoft.AspNetCore.Http;
using Microsoft.Extensions.Logging;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using System.Linq;
using System.Collections.Generic;
namespace APIHelper.LogicApps
{
public static class SelectJSONAttributes
{
[FunctionName("SelectJSONAttributes")]
public static async Task<IActionResult> Run(
[HttpTrigger(AuthorizationLevel.Anonymous, "post", Route = null)] HttpRequest req,
ILogger log)
{
log.LogInformation("C# HTTP trigger function processed a request.");
string attributes = req.Query["attributes"];
string requestBody = await new StreamReader(req.Body).ReadToEndAsync();
try
{
JArray json = JArray.Parse(requestBody);
attributes = attributes ?? json?.ToString();
if (attributes == null)
{
return new BadRequestObjectResult("The 'attributes' query parameter is missing from the request.");
}
foreach(JObject item in json.Children())
{
IList<string> keys = item.Properties().Select(p => p.Name).ToList();
IList<string> retainKeys = attributes.Split(',');
IList<string> removeKeys = keys.Except(retainKeys).ToList();
foreach(string attribute in removeKeys)
{
item.Property(attribute).Remove();
}
}
return new OkObjectResult(json);
}
catch(JsonReaderException e)
{
return new BadRequestObjectResult(e.Message);
}
catch (JsonSerializationException e)
{
return new BadRequestObjectResult(e.Message);
}
}
}
}
To then add this onto your Logic Apps after deploying out to your function app, there are a few additional steps you’ll need to do:
- Enable the managed identity for your Logic App. I recommend using the system-assigned option, as it makes the setup process a whole lot easier.
- One enabled, this managed identity object needs to be grantedĀ Contributor permission or higher onto the Azure Function App.
- When setting up the Logic App, ensure that the authentication settings mirror the options indicated below:
With all that done, we can test the Logic App and confirm everything works as expected:
It’s working! š And thanks to the capabilities built into the Newtonsoft JSON framework, the amount of actual code written is kept to an absolute minimum.
Now, even though I am blogging about this solution, I must highlight that I am not an overall fan and am frustrated slightly that Logic Apps does not have a way around this. Answers on a postcard if you think I have missed something glaringly obvious but, for now, and until the Common Data Service connector exposes out an appropriate select column option, this is the best solution we have available to us. Ultimately, we should take solace in the fact that Azure Functions are blisteringly easy to get set up and running. Also, for this particular scenario, they provide a streamlined way for us to leverage the low-code capabilities of Logic Apps and dip into more complex, bespoke processing, without necessarily needing to throw Logic Apps out of the window in the first place.