Answered

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

(0) Share

Report

Posted on by DS2

297

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)

Categories:

Building Power Apps

I have the same question (0)

All responses (14)

Answers (1)

RandyHayes 76,299 Super User 2024 Season 1 on at

Like (1)

Report
Copy link

Link copied!
@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.

Was this reply helpful? Yes No
DS2 297 on at

Like (0)

Report
Copy link

Link copied!

@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?)

The errors say there are an invalid number of arguments:
I really appreciate your help!

Was this reply helpful? Yes No
Verified answer

RandyHayes 76,299 Super User 2024 Season 1 on at

Like (4)

Report
Copy link

Link copied!
@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.

Was this reply helpful? Yes No
DS2 297 on at

Like (1)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
RandyHayes 76,299 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

@DS2

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

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

Was this reply helpful? Yes No
tlevine 230 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
shrijabajpai 63 on at

Like (0)

Report
Copy link

Link copied!

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"

Was this reply helpful? Yes No
Sajarac 3,276 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Sajarac 3,276 on at

Like (0)

Report
Copy link

Link copied!

@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

Was this reply helpful? Yes No
RandyHayes 76,299 Super User 2024 Season 1 on at

Like (1)

Report
Copy link

Link copied!
@sajarac

Try changing your formula to this:

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

Note the use of the Value Function.

Was this reply helpful? Yes No