Limit choices in (lookup or choice) field based on user

Question

I have a list with a column 'Customer Code'. This code consists of a 5 digit number. I have another list of active customer codes and authorized users. Each customer code is also set up as it's own permission group. These are currently all entered into one list, but from separate list views. I know this could be accomplished by breaking each customer code into its own list, but that would create a maintenance nightmare that I would really like to avoid :)

Is there a way to restrict what a user can enter for customer code, based on what customer code(s) they are authorized for? (One person can be authorized for more than one customer code, and multiple people can also be authorized for one customer code)

Here is the basic layout of my lists:

List 1 (Primary):

Customer Code | Item Number | Requested Date | Requested Quantity

55555(This is what I want to restrict)        | 8438758743 | 12/01/2016       | 10

List 2:

Region | Contact Person | Contact Person ID | Customer Code

Argentina | John Doe        | JDoe          | 55555

Argentina | Jane Smith      | Jsmith        | 55555

Mexico    | Jane Smith      | JSmith        | 51141

So for this example, I would want to have John Doe only be able to enter customer code 55555 into List1, but Jane Smith would be able to enter 55555 or 51141 into List 1.

Any help would be appreciated!!!!

Answer 1

Since a SharePoint Lookup field can only get information from one list (in this case List 2) you actually cannot accomplished your requirement by "breaking each customer code into its own list" if you want to keep only one "Customer Code" lookup field in List 1.

In addition, entering the customer codes into the lists from different views is irrelevant in this case because list views do not apply or enforce permissions.

You can get closer to your requirement:

• restrict what a user can enter for customer code, based on what customer code(s) they are authorized for

if you simplify List 2 by having only one "Customer Code" per customer (rather than one per contact or region) and then using item-level permission on these list items in List 2.

So, if instead you had List 2:

Region | Customer Code

Argentina | 55555 (set permissions for JDoe & JSmith or SharePoint Group )

Mexico    | 51141 (set permissions for JSmith or SharePoint Group)

Then the lookup choices available in List 1 would be what you expect when John uses the Customer Code Lookup versus when Jane uses the same Lookup field.