Entry Validation in SharePoint List

Question

I would really appreciate it if someone can help me with a validation that I want to do for the data in a SharePoint list column. The values are input into the column by external requestors and I want to validate if their entry exists in a list of acceptable values that I have. The list is really big 60k+ entries so limiting the choices with a drop down is not really an option. My idea is to just cross check the requestor entry against the values in my list and if no match exists to give an error stopping the requestor from saving their entry. For some of the other fields I managed to apply cascading dropdown menus but for those the number of entries is significantly smaller and this solution is feasible.

Your help will be greatly appreciated!

Answer 1

If you have that many valid values you need to check against, I would do this with a workflow.

1. Put your 60K+ valid values in a separate SP List (let's call it Values in this example).
2. Create a workflow on the list your requesters use.
3. Create a workflow variable (let's call it Lookup in this example).

What you will want to do, is check the requester's value against the values in the Values List you created in Step 1. To do this, use the Set Workflow Variable Action. For the variable, we will use Lookup (from my example above).

Now, for the value it gets only slightly tricky. Set it up like this:

Field Data to Retrieve

• Data Source: Values
• Field from source: (the column where your valid values are)
• Return field as: (appropriate data type... probably String)

Find the List Item

• Field: (again, the column where your valid values are)

• Value: (the field that you are validating in this list)

The result of this will be that if the value the Requester is providing is valid, there will be a value in the workflow variable. If the value the Requester is providing is NOT valid, the variable will not contain any data. Once you have that, you can check if the variable holds data or not. If it does not, then you can remove the value from the item (using the Action "Set field in current item"), and notify the Requester that they provided a non-valid value, and that it was removed.

The only real barrier I can see to this is if you absolutely need to prevent the Requester from saving the item, as workflows only run when an item is saved (or they can also be set to be manually started). You can set it to run when the item is first created (on first save), and when an item is changed (second save and so on).

Please let me know if I can offer any additional info.