3 Ways to avoid duplicate records in Power Apps

When you create items or records in your data sources using Power Apps, you will often want to avoid duplicate records. In this post 3 ways to avoid duplicates.

The setup of a list

So I’ve created a SharePoint list and an app that creates items in this list. As items are created I want to avoid duplicate records in my list. For my example I will just look at checking for unique titles but the same pattern can of course be used for different fields.

For my submit buttons I’m going to enable or disable the buttons depending on the existing records. I’ve included 3 different expressions that almost do the same thing:

If(TextInput1.Text in MyList.Title , DisplayMode.Edit,Disabled)

If(LookUp(MyList, Title = TextInput1.Text, ID) = Blank() , DisplayMode.Edit,Disabled)

If(CountRows(Filter(MyList, Title = TextInput1.Text)) = 0 , DisplayMode.Edit,Disabled)

Use “in” to avoid duplicate records

Using the following code, the button will be enabled depending on if the text entered is in the list’s title field or not:

If(TextInput1.Text in MyList.Title , DisplayMode.Edit,Disabled)

Use “Lookup” to avoid duplicate records

In a similar way we can do a lookup and if the lookup returns nothing then enable the submit button.

If(LookUp(MyList, Title = TextInput1.Text, ID) = Blank() , DisplayMode.Edit,Disabled)

But how about if you want to do something like !Blank() (you might thing that this means not equals to blank, where it actually means equals to not blank) ? Well whatever you find is never going to be equal to not blank. So you can either switch your two branches inside the if around or you could use the following code

If(!(LookUp(MyList, Title = TextInput1.Text, ID) = Blank()) , DisplayMode.Edit,Disabled)

or the slightly less efficient:

If(IsBlank((LookUp(MyList, Title = TextInput1.Text, ID)) , DisplayMode.Edit,Disabled)

Use CountRows and Filter to avoid duplicate records

The 3rd option is, the worst option, but I have seen it being used too many times, not to include it in this post.

If(CountRows(Filter(MyList, Title = TextInput1.Text)) = 0 , DisplayMode.Edit,Disabled)

As shown in the recording at the top of this post the button takes a lot longer to disable itself. This shows how the Filter function is a lot slower. Giving your users the experience that you don’t want to give them.