I am trying to use a lookup function in a calculated field to display the fields of one item from a different list in my form. The context is a dashboard that allows the users to make forecasts of the workers needed for a particular project throughout the year. Below is my lookup formula
lookup("Investments", "Title", "Linked_Investment:Title","Number_of_Interfaces")
"Investments" is the list that I want to query. "Title" is the column holding the names of all of the projects. "Linked_Investment:Title" is the name of the column on the form for which the form I am making exists. It references the Investments list and stores the Title of the item I want to retrieve. "Number_of_Interfaces" is the project detail I want to retrieve from the Investments list. When I test the form, it starts to load then goes blank. I know I could get this information in a list view but I have too many columns to use one view and making multiple views would clutter the sharepoint page in a way that would confuse the user. Is there something I am missing on this lookup function?
since you enclosed "Linked_Investment:Title" into aphostrophes, it's taken literally as that string.
I guess you rather wanted to put there reference to the Linked_Investment:Title, so that column's value is taken at runtime.
I had mistyped that function and added the apostrophes. The corrected version is below, but even when I replace the Linked_Investment:Title with the name of one of the Linked Investments, the page does not return the number of interfaces. This page also has a lookup control that references this page. Also, can a list be too large for a lookup function to work on it. My list has almost 100 columns in it, and I wonder if that may be the problem.
lookup("Investments", "Title", Linked_Investment:Title,"Number_of_Interfaces")
Agreed with Marian, anything in quotes is taken as a string not a variable. What I would like to know Marian Hatala maybe you have seen or maybe you have done this. Can you pass 2 parameters to the lookup function?
In my situation I got it working passing one parameter but the data is 2 dimensional so I need to pass a 2nd parameter inorder to get correct data that is unique.
Thank you in advance.
I just tried to return other columns in the same list and cannot return any. I think the problem is the list that I am referencing given its size but do not know if this is possible. As stated before, the list has almost 100 columns.
I have kept looking at this problem and found that lookup functions within calculated fields do not work when retrieving data from lists with lookup columns, but when retrieving data from lists without lookup columns, calculated fields can use lookup functions to find those values. Lookup columns give the structure and relationship between the lists, so I can't get rid of them. Also, The lookup column is the only way I can filter the lookup function. Am I mistaken about this problem, is this a limitation of nintex, and is there a work around for this problem? List item and list view controls are some options, but the formats of the controls make it unusable for my purposes.
could you post an example what exactly you mean?
I'm not sure I understand correctly what's exactly your problem.
for me, lookup function used in calculated value control, that uses result of another calculated control to get value searched for and referencing another lookup column in target list works without any problem.
let's have a single line text control named 'num'
let's create calc. value with formula: num + 1
and let's create calc. value with lookup function, that uses former calc. value and ask for data from 'L1_Title' column, which is lookup column to another list.
in runtime it gives me correct result
The problem was that the lists had a lookup column attached to it which prevented a lookup function from being used. I was able to use a work around and additional column to my lookup column and put it in the calculated field. Thanks for your help
RE: lookup("Investments", "Title", Linked_Investment:Title,"Number_of_Interfaces")
Try replacing: Linked_Investment:Title with: trim(Title)
lookup("Investments", "Title", trim(Title) ,"Number_of_Interfaces")
This worked for me.