Skip to main content

Notifications

Dynamics 365 Community / Blogs / Learn with Subs / Transform data in Azure log...

Transform data in Azure logic apps, using simple expressions (No Az-functions)

Subhad365 Profile Picture Subhad365 7 User Group Leader
 




Amigos, here goes a very cool hack to handle an incoming payload in your Azure Logic Apps, that can create any type of complex transformation, using just expression and variables -- nothing else.  I have covered how to transform using Data-mappers on another previous blog (https://community.dynamics.com/blogs/post/?postid=d7eda435-952b-ef11-840a-6045bddad7eb), but this one is even easier.
Check it out:

Background:  

We have an incoming purchase order payload that cumbersomely looks like this:
{
  "OrderNum": "PO000111",
  "VendorCode": "v0001",
  "NoOfLines": 3,
  "Currency": "euro",
  "PaymentTerms": "14d",
  "OrderStatus": "Received",
  "Invoiced": "No",
  "Lines" :
  [
    {
      "ItemId": "I0001",
      "Qty": 12,
      "Price": 1000,
      "LineNum": 1
    },
    {
      "ItemId": "I0002",
      "Qty": 20,
      "Price": 2000,
      "LineNum": 2
    },
    {
      "ItemId": "I0003",
      "Qty": 22,
      "Price": 3000,
      "LineNum": 3
    }
  ]
}
So we have a header section where we have Purchase Order Number, vendor code, Payment terms Id, Order status, Invoice status. And then we have Lines, where we have an array containing elements bearing infoirmation like Item id, Qty, Price and other details.
We have to transform it to make it look this:
{
    "PurchId": "PO000111",
    "VendCode": "v0001",
    "TotalQty": 54,
    "Details": {
        "PaymentTerms": 1,
        "OrderStatus": 1,
        "Invoiced": 0
    }
}
In the output, we are summing up the Qtys and also creating another JSON within the main JSON, where are capturing additional details, where each of the Enums like Payment terms, Order status, Invoice Status are getting replaced by integer values, instead of actual literal values. Which means the destination system doesn't undeerstand Payment Terms as 14d, 28d or 60d, but treats it as 0, 1, 2, 3, etc. Similarly Order Status as Invoiced, Packing slip, Invoiced, etc. are also to be transformed as 0, 1, 2, etc. Baiscally there is a map between the values, and the expectation is the Logic app should be able to convert between map values.

Step 1: 

Let us start with the basic Azure Logic of accepting HTTP request logic app trigger:
 
Paste the source payload and the Logic app will generate the schema. 

Step 2: 

Additionally I have initialized a variable called totalQty:
Which I am summing up in a loop later:

Step 3: 

Now comes the mapping logic. I am creating an another variable called TransformData, where I am going to define the map values:
The mentioned JSON is carrying the map values between Payment terms, or Invoice status or Order status. You can stack up as many mapped values you wish in here.


Step 4:

Create a Compose action by creating target payload like this:

Important part is the logic by which the selection is happening between the supplied value to the target value. For example, see how the Payment term value of 28d is tarnslated as 1:
Where by it's referring to JSON object deffined in the TransfformData variable, and selecting the JSON node from the supplied trigger body of Payment terms. 
Repeat it for all other Enums like OrderStatus or InvoiceStaus:

Step 5:

Rest part is prety simple: we can split up result as a condtional response like this:

For Success response: we can say --

And it's execution like this:


Which says if the above action of Compose is a success, then only come to this branch.
And for bad request, evidently we can set the conditon as:


We can test the logic app, by first giving the following payload:

{
  "OrderNum": "PO000111",
  "VendorCode": "v0001",
  "NoOfLines": 3,
  "Currency": "euro",
  "PaymentTerms": "28d",
  "OrderStatus": "Picked",
  "Invoiced": "No",
  "Lines" :
  [
    {
      "ItemId": "I0001",
      "Qty": 12,
      "Price": 1000,
      "LineNum": 1
    },
    {
      "ItemId": "I0002",
      "Qty": 20,
      "Price": 2000,
      "LineNum": 2
    },
    {
      "ItemId": "I0003",
      "Qty": 22,
      "Price": 3000,
      "LineNum": 3
    }
  ]
}

I am using Postman for the quick testing. The response comes as follows:

Let us now send a value which does not exist in the map (Example: Order status = Partial Packing Slip)
Which is evidently a bad request, as expected.

Cool....let me wind it up for today. Will come back soon with another useful hack on Azure AI/data engineering, Power Apps or D365F&O. Till then, take care and Namaste 💓💓💓

Comments

*This post is locked for comments