none
Validation Rule - ignoring current item on Edit

    Question

  • Hi there,

    I have asked a similar question before but I didn't seem to get a satisfactory answer...  So, I have a "booking" form (a custom list) for users to book a date and time slot.  Let say there are only three time slots.  So my columns are:-

    • Date (date column)
    • Time Slot (choice column)
    • Date&TimeSlot (text field with default value in InfoPath of concat(substring-before(Date, "T"), Time Slot))

    I need it so that if a user selects a time slot that has already been used, they get a validation error.  I have set a Validation rule in InfoPath with the below

    So that works perfectly fine.... Until I need to go in an edit this item.  Because of the rule above, it wont' allow me to save as it validates that there is already this Date & Time set in the list.

    My thoughts are that I just need to add to the rule to ignore the current item but I simply can't work out how to do this.  My initial thought is the use the ID column but I can't work out a condition to do this?

    Any ideas?? 

    Tuesday, December 05, 2017 4:42 AM

Answers

  • Hi kelsnz,

    If you want to check if the time slot in current item is booked in another item, there are two methods.

    1. You could select “Enforce unique values” for the column “Date&TimeSlot”.

    Go to list settings->Click column “Date&TimeSlot”->select “Enforce unique values” and click OK.

    After that, when you save the item, it will check the value in the column “Date&TimeSlot” is booked in other items, if it is booked in other items, the item cannot be saved.  

    2. In InfoPath, we will add secondary data source to check if the time slot in current item is duplicated. For more detailed information, refer to the article below.

    Prevention of duplicate entries based on two or more columns in a SharePoint list.

    https://salnikan.wordpress.com/2011/07/26/prevention-of-duplicate-entries-based-on-two-or-more-columns-in-a-sharepoint-list/

    Best regards,

    Sara Fan


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    • Marked as answer by kelsnz Sunday, December 10, 2017 11:38 PM
    Friday, December 08, 2017 2:48 AM
    Moderator

All replies

  • Hi kelsnz,

    If you have set the validation rule, you cannot save the item to the list when meeting the rule. It is by designer.

    There is a workaround.

    You could add a field named “Error Message” and when meeting the “Date&TimeSlot= Date&TimeSlot”, then set the error message with “the time slot on this date has already been taken”. Then hide the “Error Message” column in the view.

    Best regards,

    Sara Fan


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    • Proposed as answer by croute1 Tuesday, December 05, 2017 4:43 PM
    Tuesday, December 05, 2017 10:17 AM
    Moderator
  • Hi Sara,

    I should have clarified a bit further...  So, I still want the rule to be applied upon edit, but I want the rule to somehow ignore the current item's time slot.  So, if I edited the item and changed the time - I still want the rule to check if the time slot is changed and has been booked on another item, but to ignore if the time slot is on the current item. 

    I'm not sure I'm explaining it sufficiently...

    Tuesday, December 05, 2017 8:53 PM
  • Hi kelsnz,

    If you want to check if the time slot in current item is booked in another item, there are two methods.

    1. You could select “Enforce unique values” for the column “Date&TimeSlot”.

    Go to list settings->Click column “Date&TimeSlot”->select “Enforce unique values” and click OK.

    After that, when you save the item, it will check the value in the column “Date&TimeSlot” is booked in other items, if it is booked in other items, the item cannot be saved.  

    2. In InfoPath, we will add secondary data source to check if the time slot in current item is duplicated. For more detailed information, refer to the article below.

    Prevention of duplicate entries based on two or more columns in a SharePoint list.

    https://salnikan.wordpress.com/2011/07/26/prevention-of-duplicate-entries-based-on-two-or-more-columns-in-a-sharepoint-list/

    Best regards,

    Sara Fan


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    • Marked as answer by kelsnz Sunday, December 10, 2017 11:38 PM
    Friday, December 08, 2017 2:48 AM
    Moderator
  • Thanks Sara! 

    Of course!  Didn't even think of Option 1. This is an easy way to go but I'm going to try Option 2 also.

    Thanks for your help.

    **EDIT**

    Option 2 works really well also - the only thing to note with this is that if you have the form open, change the time slot to a new time slot then change it back, you will get the validation error.  But, in all likelihood, that particular issue would not happen often so I'm happy with this.

    • Edited by kelsnz Monday, December 11, 2017 3:18 AM
    Sunday, December 10, 2017 11:38 PM