cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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

@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.
Check out my PowerApps Videos too!

View solution in original post

10 REPLIES 10
Super User III
Super User III

@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.
Check out my PowerApps Videos too!

@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!

@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.
Check out my PowerApps Videos too!

View solution in original post

@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. 

@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.
Check out my PowerApps Videos too!

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

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"

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",
"")

 

 

 

@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

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (8,005)