Answered

Check for Duplicates in SharePoint list before Form Submit

(0) Share

Report

Posted on by golfnutt82

1,362

Hello,
I have a form where a user can enter an Out of Office request and the person is not allowed to enter the request for the same role twice unless the first request was completed or in this case "Processed"
This is for a new entry, not edit.
The lookup field being used is "Role Name" that looks up the role from the "Roles" List.
The person enters the to and from dates they will be out of office, enters a "Role Owner" and a "Delegate" to handle approvals while out of office.
What I was hoping I can do, and I looked everywhere online and found various of solutions, but none really related towards what I was looking for.
Is it possible to look at a list before submitting the form and compare it with a DataCard Entry and check the "Approval Status" is equal to "Processed". If the Role Name = the DataCard Role Name and the Approval Status equals "Processed" then submit the form else show a warning and hide the submit button.

Categories:

Building Power Apps

I have the same question (0)

All responses (12)

Answers (1)

Sort by

Anonymous_Hippo 1,247 Super User 2024 Season 1 on at

Like
a
(2)

Report
Copy link

Link copied!
Hello @golfnutt82

Please refer to my latest post here regarding the same 🙂

Pasting answer for any people who stumble upon the same issue

If(!IsEmpty(Filter('Role Owner Out of Office', Title = txtRoleName && 'Approval Status'.Value <> "Processed")), Notify("The Out of Office Request was already entered and in process.",NotificationType.Warning);RequestHide(),SubmitForm('New OoO Form'))

Cheers !

Was this reply helpful? Yes No
golfnutt82 1,362 on at

Like
a
(0)

Report
Copy link

Link copied!

I get the notification whether the title field in the list is blank, and the Approval Status is not equal to Processed.
The logic is under the test button.

So when looking at the data I have two Governor entries for the same Role and both are set to "Processed" so I should be able to add a new Out of Office entry for that role but I get the error. Below is the list.

Any ideas what I could be missing.

Was this reply helpful? Yes No
Anonymous_Hippo 1,247 Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!
Hello @golfnutt82 ,

Can you try this. Here i am replacing Not equal with the actual set of value that i can see from the list i.e "Pending" And "In Progress" if you have more options like Blank add it with and or statement ||.

Please note that <> is a non delegable operator and seems to be the main culprit here. I also remove the "!" since it is not relavent

If(IsEmpty(Filter('Role Owner Out of Office', Title = txtRoleName && ('Approval Status'.Value = "Pending" || 'Approval Status'.Value = "In Progress"))), Notify("The Out of Office Request was already entered and in process.", NotificationType.Warning)); RequestHide(), SubmitForm('New OoO Form'))

Please let me know if this works

Was this reply helpful? Yes No
golfnutt82 1,362 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi,
Thanks again for your assistance.
When I saw this, I thought it made sense and was excited to give it a shot. However, when I put the logic in I received an error.

Frustrating.

Was this reply helpful? Yes No
Anonymous_Hippo 1,247 Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!

Hello @golfnutt82 !

Can you please explain what "txtRoleName" is... is it a variable or a name of dropdown. Is Role name a drop down field or a combo box field ?

Thanks

Was this reply helpful? Yes No
golfnutt82 1,362 on at

Like
a
(0)

Report
Copy link

Link copied!

I have a Power Automate workflow that uses Odata filter query in a Get Items action and it uses txtRoleName.
It is the value of the lookup/combo box that holds the Role Name.
The Role Name is a look up to the "Roles" list as there are many, which is why I have the search function in the Items property.

The Title field WAS a field that held the requestor name and the Item ID but based on the logic that was out there that I had tried to apply it was checking to see if the Title field was equal to the txtRoleName field.
I am open for suggestions to pull this off.

Was this reply helpful? Yes No
Anonymous_Hippo 1,247 Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!
can you try this @golfnutt82 :-

We are basically looking at what the user entered in the combobox is same as the sharepoint column. Hopefully this one works ... fingers crossed 🙂

If(IsEmpty(Filter('Role Owner Out of Office', txtRoleName=ComboBox1.Selected.Value && ('Approval Status'.Value = "Pending" || 'Approval Status'.Value = "In Progress"))), Notify("The Out of Office Request was already entered and in process.", NotificationType.Warning)); RequestHide(), SubmitForm('New OoO Form'))

Was this reply helpful? Yes No
golfnutt82 1,362 on at

Like
a
(0)

Report
Copy link

Link copied!

ComboBox1 is actually the "Role Type Filter" that filters the role by one of four choices.

The lookup or combo Box is designated as dataCardValue1

Because the logic is comparing the wrong field it allowed me to put in multiples, but I like the direction.

If I could compare the value of DataCardValue1 to the txtRoleName then I believe it would work.
I get an error if I try to use DataCardValue1

Was this reply helpful? Yes No
Anonymous_Hippo 1,247 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

I think this looks promising 🙂 @golfnutt82 We will get this to work , we are very close !!!

Can you hover over the equal to sign and see what the error is most probably we are not comparing similar data type maybe one is table and the other is a text.

can you try to do the following add a "." next to txtRoleName and add the suggested popup. That should get to work else please let me know what error you are seeing also can you please mention what kind of field txtRoleName is in the sahrepoint column. Is it a choice or a single text field

Thanks

Was this reply helpful? Yes No
Verified answer

golfnutt82 1,362 on at

Like
a
(1)

Report
Copy link

Link copied!

Got it!!!
I had to use the "!" character and then with your help I was able to come up with this and it works.

I know you said that the "Not Equals" is not delegable, I knew that which is fine because I don't plan on keeping over 2000 records in the list anyway.
Mr. Hippo you are a life saver.
Got me thinking differently.

Was this reply helpful? Yes No