Item validation based on 2 columns

[philipp777]

Hello everyone,

I want to avoid that users create the item twice in my SharePoint list "reporting". I want to validate that with 2 columns: 

• "C-Code" (like US10) which is a lookup column
• "Period" (like 2022-Q1-Q2") which is a choice column

So, my goal is that there is only one item with US10 and 2022-Q1-Q2. 

Is there a way to do that with a validation formula in SharePoint or do I have to create a workflow with Power Automate?

Thanks in advance!

[#2]

@philipp777 You cannot use the lookup columns in SharePoint validation formula. Also, you cannot check the column values of another list items in formula.

Best solution for you would be to customize the list forms using Power apps & add these validation in forms using power apps formulas.

---

Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

[#3]

@philipp777 

Using Column validation it's not possible. 

Power Automate will not help to avoid entry into list as Power Automate can run once Item is added already. Of course using Power Automate you can validate whether entry is duplicated based on two SharePoint columns and you can take necessary action after i.e. like removing entry and informing author via email about duplicate.

Here the suggested approach would be to customize your list form using Power Apps and there you can handle this validation.

Reference Link for Power Apps:  (This link shows solution for only one column you need to adapt it according to your requirement).

https://powerusers.microsoft.com/t5/Building-Power-Apps/How-to-check-that-a-field-value-is-unique-be...

Hope it will helpful to you.

[#4]

Thank you both!

@kalpeshvaghela: Where do I have to put the formula? Behind both fields?

[#5]

@philipp777 You have two options to check the same data is already available in list or not:

1. Just before submitting the list form - if you have any button for submit, use "OnSelect" property of button to add formula & logic.
2. "OnChange" property of individual field control in form.

---

Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

[#6]

@ganeshsanap @kalpeshvaghela 

Thanks. I would go with option 2, because I want to include the PowerApp in the SharePoint form and use the default "save" button. Can I deny the user saving his data, when the condition is true (e.g. greying out the save button)?

This is my formula, but not working yet:
If(
And(CountRows(Filter(mylist, C_x002d_Code = Trim(DataCardValue1.Text))),CountRows(Filter(mylist, Period = Trim(DataCardValue1.Text))) > 0),
"Duplicate C-Code and Period not allowed",""
)

[#7]

Could someone help me out with the formula? Thanks!