Forum Discussion

JamesPhImp's avatar
JamesPhImp
Copper Contributor
Nov 15, 2023

Sharepoint List Column Lookup based on other columns

Hi 

I have 3 Sharepoint Lists

1- System Names

2 - System Access Rights

3 -  System Access

 

List 1 has just one Column called Title and has a list of system names that we use as a business. these Values are all unique. For example System 1, System 2, System 3 etc

 

List 2 has 2 columns. System Name - which is a lookup of the above list and Access right name which is a test field. This List lists out all the access types relevant for each System, eg
System 1; Payment Inputter

System 1; Payment Releaser

System 1; System Administrator

System 2; Read Only

 

List 3 has 3 columns. Person Name - Person field, System Name - lookup from List 1,  Access Rights - which is where I have my question

 

I would like the Access Rights column to be a lookup but only show filtered items relevant to the system selected in the System name column. For Example:

John Smith; System 1; This I want to just display Access rights relevant to System 1, using example above Payment Inputter; Payment Releaser; System Administrator and allow user to select relevant right or rights relevant to that person from the lookup list.

 

Hope this makes sense, is this possible?

Lists
  • John-CK's avatar
    John-CK
    Copper Contributor
    Apr 08, 2024
    Makes sense to me -
    I have a similar blocker ;
    BusinessFunctions list
    HAS
    Dependencies list
    HAS
    Vendors list

    Everything is fine until I try to nest below 2 levels - it seems SP Lists can't cope with 3 levels of nesting / relationships - which sucks

    Every question I've seen in the community about this (or similar) has NO replies.....

    In my example
    List 1 (Functions) looks up List 2 (Dependencies). List 2 looks up List 3 (Vendors) - In isolation, this all works, but, I cannot find a way to surface the vendor name at Function level.

    This has lead me round in circles for about a week now and I can't find any solution.
    Did you ever solve yours?
    Thanks
    • ganeshsanap's avatar
      ganeshsanap
      MVP
      Apr 08, 2024

      John-CK Yes, you are correct. SharePoint lookup columns does not support 3 levels of nesting / relationships.

       

      If you are using SharePoint online, you can customize the list forms (for list 3) to show drop down options (from list 1).

       

      Check:

      1. Customize a Microsoft Lists or SharePoint form by using Power Apps 
      2. Use drop-down lists with choices from lookup columns in a list created using Microsoft Lists 
      3. Adding Power Apps dropdown list from a different SharePoint list 

      Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

      • John-CK's avatar
        John-CK
        Copper Contributor
        Apr 08, 2024
        Hi ganeshsanap

        As I do not have the resources at present to support a power apps development, I have elected for a dirty workaround

        Using automation, I will populate a hidden field with the text value of the selected lookup option in List 2. By doing this, I will carry a text version of the value that will allow me to group, sort, link within lists etc. In addition, I am also storing the VendorCodeID (also hidden) so that when I need to produce analyses outside of Lists, I can then nest to 3 levels and beyond.
        Not very elegant, but it gets the job done

        I have to say - I am surprised at discovering this significant limitation and irritated that its not more clearly detailed / called out (at least no anywhere I've seen so far)

        Thanks

Resources