Unanswered

How do I check if item exists in multiple columns (SharePoint List) before SubmitForm?

(0) Share

Report

Posted on by WhatisPowerApps

How do I make sure I have all unique values in 5 columns before submitting the new or editing the existing form? I have 5 columns that contain serial numbers. When checking on SP list, I see that I can only enforce unique values for each column. What would be the best option for me?

Thanks.

Categories:

Building Power Apps

I have the same question (0)

All responses (9)

Answers (0)

Sort by

mdevaney 29,991 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
@WhatisPowerApps

Stack all 5 columns in a single column

ClearCollect( myCollection, RenameColumns(your_datasource_name.Column1,"Column1","ColumnA"), RenameColumns(your_datasource_name.Column2,"Column2","ColumnA"), RenameColumns(your_datasource_name.Column3,"Column3","ColumnA"), RenameColumns(your_datasource_name.Column4,"Column4","ColumnA"), RenameColumns(your_datasource_name.Column5,"Column5","ColumnA") );

Then you can check if the unique values equals the count of totals rows

CountRows(Filter(your_datasource_name, IsBlank(ColumnA)=false)) = CountRows(Distinct(Filter(your_datasource_name, IsBlank(ColumnA)=false), ColumnA))

I took part of this answer from the Collections Cookbook. You might want to take a look at it for some inspiration

🙂

Link To Collections CookBook App:

https://powerusers.microsoft.com/t5/Community-App-Samples/Collections-Cookbook-50-Visual-Examples-amp-Code/td-p/437594

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Was this reply helpful? Yes No
WhatisPowerApps 96 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks for the quick reply.

I'm getting a delegating warning for CountRows. It says "CountRows operation is not supported by this connector." Anything I can do to get it fixed?

Was this reply helpful? Yes No
mdevaney 29,991 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

@WhatisPowerApps

I know CountRows cannot be delegated but that error message is super wierd. Please take a screenshot and post it. Make sure to capture the formula and the error message.

Also, please ensure your delegation row limit is increased to 2,000 in advanced settings.

Was this reply helpful? Yes No
WhatisPowerApps 96 on at

Like
a
(0)

Report
Copy link

Link copied!

delegation error
Also, it's not catching the duplicate values. What am I doing wrong? I don't even have more than 10 rows at the moment. It's already set to 2000 limit.

Was this reply helpful? Yes No
WhatisPowerApps 96 on at

Like
a
(0)

Report
Copy link

Link copied!

Was this reply helpful? Yes No
mdevaney 29,991 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
@WhatisPowerApps

The blank rows are going to impact the solution. I did not anything to be blank.

See my original post for my revised solution. Here's what I would use to compare the row count instead.

CountRows(Filter(your_datasource_name, IsBlank(ColumnA)=false)) = CountRows(Distinct(Filter(your_datasource_name, IsBlank(ColumnA)=false), ColumnA))

You can ignore the delegation warning. It will still come up even if there are less rows than the limit.

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Was this reply helpful? Yes No
WhatisPowerApps 96 on at

Like
a
(0)

Report
Copy link

Link copied!

@mdevaney
Could you check your function? I think something wrong with it.

Was this reply helpful? Yes No
mdevaney 29,991 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

@WhatisPowerApps
Can you explain what is going wrong?

Was this reply helpful? Yes No
WhatisPowerApps 96 on at

Like
a
(0)

Report
Copy link

Link copied!
@mdevaney
I don't get the second part of your code. I have 5 unique DataCardValue from the Form. How do I compare them with the collection? This following code of yours is comparing datasource vs datasource. Am I missing something? Sorry, my brain is not working from trying to solve this one task for hours. lol

CountRows(Filter(your_datasource_name, IsBlank(ColumnA)=false)) = CountRows(Distinct(Filter(your_datasource_name, IsBlank(ColumnA)=false), ColumnA))

Was this reply helpful? Yes No