1

I need to create a survey on SharePoint where the users will need to rank 13 unique items in order of importance. As such, I need to somehow enforce all response entries from a single person are unique.

Example:

Say there are three items: Item 1, Item 2, and Item 3. If the user ranks both Item 1 and Item 2 as #1, then the survey should throw an error until the user assigns different ranks to all three items.

Unfortunately, due to access restrictions, we cannot use webparts to write a JS code or anything fancy to achieve this. We are trying to achieve this using column validation, but we ran into a very silly issue.

Essentially, I tried to solve it by just literally comparing every column to one another and ensuring that there are no duplicates. I created 3 separate "Number type" questions, each labeled as "item 1", "item 2", and "item 3".

=AND([item 1]<>[item 2], [item 1]<>[item 3], [item 2]<>[item 3])

which does exactly what we need it to do. However, we have a total of 13 items that need to be ranked, and each item has a long name - unfortunately, SharePoint limits the length of its column validation formulas to 1024 characters.

Given these limitations, are there any work arounds we can use to create this survey? In short, I'm looking for one of these:

  • A validation formula to check for duplicate responses without going over the 1024 character limit
  • A built-in question type that can achieve this out of the box (please note that we cannot use any webparts, so plug-ins are out of question)

I am NOT looking for the same functionality as "Enforce unique values" option available for the "Number type" questions. What that does is it ensures all values within one column are unique; I need to make sure that responses given by a single person must be unique across all columns in the survey.

Thank you in advance.

0

How about using a math formula - sum of squares of provided RANKS. Say, you have 5 items, each can be ranked 1-5. The sum of squares of 1, 2, 3, 4, and 5 is 55.

The Form validation formula would be:

=(Item1^2+Item2^2+Item3^2+Item4^2+Item5^2)=55

I believe, if any number (1-5) repeats, the sum of squares will not be 55. See the screenshot below for few examples. If it's not the case, we can go one degree up, that is use "cube" like item1^3 + item2^3..

Survey Form Validation: Invalid and Valid Entries

enter image description here

enter image description here

Sum of squares of numbers 1-5 with and without repetitions

enter image description here

0

I don’t think this requirement is possible with list validation (or other OOB solutions).

The thing is, you have way too many items/columns that any formula validation needs to loop through. And this is a survey list which does not allow form customization, so InfoPath form rule is also out of the question.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.