cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
golfnutt82
Super User
Super User

Check for Duplicates in SharePoint list before Form Submit

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.

1 ACCEPTED SOLUTION

Accepted Solutions

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

golfnutt82_0-1652808635802.png
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.

 

View solution in original post

12 REPLIES 12
Anonymous_Hippo
Super User
Super User

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 !

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.

golfnutt82_1-1652730117733.png

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.

golfnutt82_0-1652732838387.png

 

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 

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.

golfnutt82_1-1652789838787.png

Frustrating. 

 

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

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.

golfnutt82_0-1652796897130.png

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.

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'))

 

 

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

golfnutt82_0-1652804857185.png

golfnutt82_2-1652805171097.png

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

golfnutt82_3-1652805353414.png
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

golfnutt82_5-1652805660314.png

 

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

Helpful resources

Announcements
Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Super User 2 - 2022 Congratulations

Welcome Super Users

The Super User program for 2022 - Season 2 has kicked off!

September Events 2022

Check out all of these events

Attend in person or online, there are incredible conferences and events happening all throughout the month of September.

Users online (3,047)