Solved

Lookup a SharePoint list based on another field value.

  • 6 August 2021
  • 3 replies
  • 1155 views

Badge +1

I have 2 SharePoint 2016 lists, one is a lookup list and the other is a list for request form.  Unfortunately we do set the employee division in AD only the Department.  The employee's department and division needs to be added to the request form. So I created a look up list that lists all of our departments and the associated division:  

19103i1BC4CB4625E8A644.png

On the Nintex Form (Classic) I have a calculated field that populates the employee's department:

19104i9D4B8C2B27905FF0.png

To populate the employee's division (based on their department), I added another calculated field:

19105iE463493D71DAA25F.png

The department is populating correctly but the division is blank.  I've also tried using parse lookup, singe quotes, spaces, no spaces.  Nothing seems to work.  Here are some of the different ways I have tried to auto-populate the division field:

19106i757D46463187D893.png

19107iFDE46C5AE96C17F6.png

19108i423CE9CF0FB7200B.png

19109i4070AEA9EB5101D8.png

19110iA32F541D91A3997F.png

I would really appreciate if someone could point me in the right direction!!

 

 

 

icon

Best answer by MegaJerk 10 August 2021, 05:09

View original

3 replies

Userlevel 5
Badge +14

Though it's difficult to say what's going on without any other info of the values from your list, I would say that a little sanity checking could go a long way in your case.


 


Because your original lookup() function syntax is correct, the first thing I would do is check to see what happens if you were to just manually enter the string of said Department into your arguments like: 


lookup("DivDeptLookup", "Department", "KNOWN_DEPT_NAME", "Division")

 


That should get you *something* back. If it does, then you know your problem is somewhere to do with the reference to the value of your {Dept} Control.


 


If you get NOTHING back, and you know that it *should* be valid, then it's time to start digging a bit deeper.


 


The next questions are:


 



  1. Is the Target List ("DivDeptLookup") on the same Site as the Form?

    1. No: Then you need to add the relative url path and a pipe delimiter before the target List Name, like: "/sites/siteCollection/siteA|customList"




  2. What are the *actual* Column Names of the Columns that you're targeting?

    1. Go to the list in question

    2. Go to List Settings

    3. Click on a Column Name

    4. Look at the end of the URL (as detailed here: https://community.nintex.com/t5/Nintex-for-SharePoint-Forum/Need-to-Look-up-items-from-same-list-and-return-back-on-data-on/m-p/205140/highlight/true#M78432)




 


Try going through this list of possible solutions and if none of them work, then we'll dig a little deeper. Hope this helps. 


 


 

Badge +1

Thank you!  That was an excellent explanation!  This worked perfectly when using a lookup field.  But I am using a calculated field that uses the User Profile Lookup.  So I added another calculated field that captures this value then added this field to the fn.  It is working perfectly.  Thanks again!

Userlevel 5
Badge +14

That's what I like to hear! Excellent! 

Reply