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.
