Forum Discussion

philipp777's avatar
philipp777
Copper Contributor
Aug 02, 2022

Item validation based on 2 columns

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!

Lists
SharePoint Online

6 Replies

  • kalpeshvaghela's avatar
    kalpeshvaghela
    Iron Contributor
    Aug 02, 2022

    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-before-saving-back-to/m-p/306434#M88137



    Hope it will helpful to you.

      • ganeshsanap's avatar
        ganeshsanap
        MVP
        Aug 02, 2022

        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.

  • ganeshsanap's avatar
    ganeshsanap
    MVP
    Aug 02, 2022

    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.

Resources