cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate II
Advocate II

How to check that a field value is unique before saving back to a SharePoint list?

I have an Input Screen with a Form where there is a text input field for an invoice number. I have a Save Icon that upon OnSelect Submits the Form and the data is saved to a SharePoint List. We need all invoice numbers to be unique to avoid duplicate entries. Although I can make the Invoice number column in SharePoint unique, there is no error message in PowerApps (or a useful one anyway) although a record with a duplicate invoice number cannot be saved. 

 

How can I check for a duplicate entry upon Submit? I already have some code in OnSelect for the Save icon:
SubmitForm(Form1);
Set(
selectedInvoice,
Blank()
);
ResetForm(Form1);
NewForm(Form1)SubmitForm.jpg

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

Re: How to check that a field value is unique before saving back to a SharePoint list?

@DS2 

Sorry...typo in the formula - doing it all by hand, it's easy to get a syntax error 😉

 

If(
   IsBlank(Trim(DataCardValue1.Text)), "An invoice number is required",
   CountRows(Filter(yourDataSource, Invoice = Trim(DataCardValue1.Text) ) ) > 0,  "Duplicate invoice not allowed",
   "")

Needed another parenthesis.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.

View solution in original post

10 REPLIES 10
Highlighted
Super User III
Super User III

Re: How to check that a field value is unique before saving back to a SharePoint list?

@DS2 

Instead of trying to validate and throw errors and that sort of thing when a user submits the form, consider not even letting them get to that point.

The process I might take would be as follows:

1) Alter your Invoice DataCard - I'm going to refer to it as Invoice_DataCard1, but substitute whatever yours is.  I am also going to refer to the TextInput box in your datacard container as DataCardValue1.

   a) Unlock the card

   b) Find the ErrorMessage control in your datacard container - I'm going to refer to it as ErrorMessage1

   c) Change the Text property of ErrorMessage1 to :

If(
   IsBlank(Trim(DataCardValue1.Text)), "An invoice number is required",
   CountRows(Filter(yourDataSource, Invoice = Trim(DataCardValue1.Text) ) > 0,  "Duplicate invoice not allowed",
   "")

   d) Change the DelayOutput property of DataCardValue1 to true (to limit the lags when typing)

   e) Change the Visible property of ErrorMessage1 to : !IsBlank(ErrorMessage1.Text)

 

2) Change the DisplayMode property of your Submit Icon to the following:

If(ErrorMessage1.Visible, Disabled, Edit)

Now your users will see when and where there is an issue with the invoice number and will not be allowed to submit the form until it is corrected.

 

I hope this is clear and helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Highlighted
Advocate II
Advocate II

Re: How to check that a field value is unique before saving back to a SharePoint list?

@RandyHayes - this is so clear and helpful with the steps, thank you! I am getting an error in step c, the text property of the ErrorMessage and also am not sure what field the "Invoice" is pulling from (is it the SP column name?)

 

errorCDL.jpg

The errors say there are an invalid number of arguments:
Invalid number arguments.jpg

I really appreciate your help!

Highlighted
Super User III
Super User III

Re: How to check that a field value is unique before saving back to a SharePoint list?

@DS2 

Sorry...typo in the formula - doing it all by hand, it's easy to get a syntax error 😉

 

If(
   IsBlank(Trim(DataCardValue1.Text)), "An invoice number is required",
   CountRows(Filter(yourDataSource, Invoice = Trim(DataCardValue1.Text) ) ) > 0,  "Duplicate invoice not allowed",
   "")

Needed another parenthesis.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.

View solution in original post

Highlighted
Advocate II
Advocate II

Re: How to check that a field value is unique before saving back to a SharePoint list?

@RandyHayes 

 

Thank you so much for that!! It is working great (attached) I so appreciate this and have learned something for future apps as well as a citizen PA developer. 

Very grateful. 

Highlighted
Super User III
Super User III

Re: How to check that a field value is unique before saving back to a SharePoint list?

@DS2 

Perfect!  Glad it helped and glad you learned something from it.

Feel free to post another message if you run into other problems.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Highlighted
Kudo Collector
Kudo Collector

Re: How to check that a field value is unique before saving back to a SharePoint list?

@RandyHayes Works perfectly for me, a great solution! Thanks

Highlighted
Helper I
Helper I

Re: How to check that a field value is unique before saving back to a SharePoint list?

I am getting below error while using this formula. My datasource is SharePoint and totals records are less than 500.

 

"Delagation Warning: CountRows Operation not supported by your connector"

Highlighted
Kudo Kingpin
Kudo Kingpin

Re: How to check that a field value is unique before saving back to a SharePoint list?

Hi @RandyHayes ,

 

Would you mind to answer this?

 

If(
IsBlank(Trim(DataCardValue1.Text)), "An invoice number is required",
CountRows(Filter(yourDataSource, Invoice = Trim(DataCardValue1.Text) ) > 0, "Duplicate invoice not allowed",
"")

 

In my case DatacardValue is a number value and I am getting Incompatible. We can't evaluate your formula.

 

Here is mine:

 

If(
IsBlank(Trim(DataCardValue5.Text)), "A Country Code is required",
CountRows(Filter(Country, CoO_Code = Trim(DataCardValue5.Text) ) > 0, "Duplicate Code not allowed",
"")

 

 

 

Highlighted
Kudo Kingpin
Kudo Kingpin

Re: How to check that a field value is unique before saving back to a SharePoint list?

@RandyHayes ,

 

Never mind I just saw this :

 

Sorry...typo in the formula - doing it all by hand, it's easy to get a syntax error 

 

 

Is working fine now. thanks for this easy tutorial

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Watch Now

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (5,312)