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

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

7 REPLIES 7
Super User
Super User

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.
DS2 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!

Super User
Super User

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

DS2 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
Super User

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

shrijabajpai
Frequent Visitor

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"

Helpful resources

Announcements
secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

firstImage

New Ranks and Rank Icons released on April 21!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power Apps Community!

Top Solution Authors
Top Kudoed Authors
Users online (7,673)