Creating an OData service using Logic Apps – Part 3

On the last post about creating an OData service using Logic Apps, I explained how to receive a property from a URL in a Logic Apps flow and handle optional parameters by using expressions. This time, for one of the properties, the expression will not be enough, so we will use an Azure Function to help us handle that.

Handling a few more properties

So we still need to handle the following 4 properties below:

• Skip Count
• Top Count
• Select Query
• Order By

For Skip Count and Top Count, my expressions are below:

int(coalesce(trigger()['outputs']?['queries']?['skipCount'],0))

int(coalesce(trigger()['outputs']?['queries']?['topCount'],1000000))

Order By property

For the Order By property, to solve the problem of an optional parameter I tried to use the same approach as we did for the other parameters as you can see below:

if(equals(coalesce(trigger()['outputs']?['queries']?['orderBy'],''),''),'Id',trigger()['outputs']?['queries']?['orderBy'])

The problem is that with this logic I need to specify what is going to be the default column. Since each table has its own column names I can’t make a generic definition for all tables as I did for the other parameters, and also I don’t want to handle the logic of defining this default column name inside my logic app flow.

So that’s when I decided to use a Azure Function to handle this logic for me.

To do so, you need to create a new resource in Azure called Function App, specify the name, resource group, storage and consumption plan. After this is complete you can start defining your functions.See image below:

After the resource has been provisioned, let’s create a new function to handle the requests from the Logic App flow.

In this function, we will be expecting a JSON with the following structure:

{
"Resource": "[parameter from URL]",
"OrderBy": "[parameter from URL]"
}

Then we will make validation to see which table was requested the information and in case no information was provided for the OrderBy property, then we will define a default column name value to be provided to Logic Apps.

Once the function is up and running, we can configure the Logic App flow to execute it as you can see in the picture below:

Select Query property

So now, the only parameter missing to be handled is the select query.

Since it’s not possible to specify a default value for this property in the SQL connector I used a condition shape in the logic app flow to make a decision if I was going to specify this property or not.

Below you can see the full flow.

The amount of time I took on this development where I can expose all the tables of my database as an OData REST API was about 4 hours, mainly because I needed to research a few things and write the post at the same time, so it’s a very short time to came up with a solution like this.

If you have a better way of handling these optional parameters, please leave your comment, and I’ll adjust the solution in case your solution is better than mine. 😉

In the next post, I’ll finally talk about  the API Management. Don’t forget to sign up to receive an alert when a new post is posted.

See you!!!

Check it out the other posts of this series:

• Part 1
• Part 2
• Part 3