Use Length() Expression in Upsert Scenarios

Many systems require a new record to be unique and return an error if a duplicate record is detected. In other situations, it is good practice to avoid redundant master data. So the question is, how can we detect whether a record exists within a system if there isn’t an explicit action/operation for us?

In this post we are going to use the Length() expression to provide an indicator of whether a record already exists. The expression works by accepting a collection as an input parameter and then returns the length of that collection. This expression can be useful when trying to determine the size of a result coming from SharePoint or Dynamics 365 CE.

Let’s take a deeper look, by building out a scenario. In our scenario, we are storing a contact list in a SharePoint list where we store customer master data, including their related discount. We want to have a clean contact list and do not have any duplicates. So before we add a new record to this list, we want to check for its existence.

The overall design of our logic app includes an HTTP Trigger that will receive an inbound request. We use a String variable, called ResponseBody that we will update and send back to the caller. Next, we have a SharePoint Get Items action that will perform an OData filter query looking to see if our Email Address eq ‘’. This filter will target an existing email address and allows us to filter out irrelevant contacts.

This is the place where our Length() expression really shines. From a technical perspective, the Get Items action will return an array. The array can have 0 records, 1 record or many records. Since we are using an OData filter query, we should, at most, only have 1 record which implies the contact already exists. However, if 0 records are returned, then we know the contact doesn’t exist and we can create the contact.

To check the length of our SharePoint Get Items output, we will use the following expression: length(body('Get_items')?['value']). We will then check if the output is equal to 0. If this is true, we will then create the new contact using the SharePoint Create Item action and then update our ResponseBody variable. In the event our SharePoint Get Items output does not equal to 0, we will then assume the contact already exists and update our** ResponseBody** variable indicating the record already exists. Lastly, we will send our ResponseBody back to our caller.

Get a better understanding of Azure Logic Apps monitoring at multiple levels.

Conclusion

Some systems provide Upsert capabilities that will automatically detect if a record exists and if it doesn’t, the record will automatically be created. However, some systems don’t do this and in this case, we can use the Length() expression to help us determine whether or not an object exists and then react accordingly.