lookup to a list on a different page / site ?

B
Badge +11

Hello,

I am trying to implement the lookup function to check the selected value from a drop down and
then show me the result from column Manager.

The cost center is a lookup function which looks at a list on a different page. My idea behind is so that when a cost center number is selected from the drop down, then it should show the relevant manager.

The CostCentre list contains Cost Cetre Nr | Manager | Exec etc.

The problem is for me how to make the lookup fetch the data from a list on a different page??
This is the url to the actual list: https://domain.com/X/Lists/CostCentre/AllItems.aspx 

I have tried the following as a calculated function:

lookup("https://domain.com/X/Lists|CostCentre ", "ID",CostCentre,"Manager")

the result states "Loading..." but no results showing.

Any advise in what I am doing wrong .... ???

15 replies

L
Badge +9

Try like below.

lookup('/X|CostCentre','ID',CostCentre,'Manager')

B
Badge +11

Hi, still getting #Value! error message.

I have also tried following:

lookup("/A|CostCentre","ID",CostCentre, "Manager")
lookup("/A/Lists|CostCentre","ID",CostCentre, "Manager")

not sure what the "ID" is used for anyway.
The CostCentre list contains following fields: CostCentre | Department | Manager | Exec.

The dropdown field on the form named Cost Centre is also a lookup looking at the above mention list.
Now the calculation field is used to lookup the dropdown field and show the manager of the selected cost centre.

Not sure if this is the right way ...

G
Userlevel 6
Badge +13

Lakshmi C‌ has given you the correct answer, I think you've just got your syntax wrong in the formula.

In your screenshot you've inserted the reference "ID" as the column to filter on. This is incorrect, as using the reference will insert the value of the current items ID rather than the string ID.

Make sure you just type in "ID" rather than inserting the reference.

Make sure you read the Lookup syntax so you understand the formula.

The four required parameters are:

  • list to lookup - "/A|CostCentre"
  • name of column to filter on in destination list - e.g. Cost Centre ID
  • value to filter on - e.g. the value in your form that will contain a Cost Centre ID 
  • column to return in your lookup - Manager
B
Badge +11

Hi,

I am not disputing Lakshmi's suggestions but it fails to work on my side.
Whatever I am missing ... I do not know.

As per the "ID" it is not a reference as I corrected it after I made the screenshot.

This is what it looks like:

Why is the Lists from the actual link /A/Lists|CostCentre been taken out?
Instead it is used as /A|CostCentre though I tried with both options and nothing has helped.

The ID means it looks in the ID column right?
In this case; the list does not display the ID column but instead CostCentre | Department | Manager

The CostCentre as the reference is meant to be the field to look for which is the dropdown box in the form.


Not sure what it is I am doing wrong ...

B
Badge +11

UPDATE

I think I found the mistake I was doing wrong ...

This is the actual calculated formula which works and shows the 46;#ManagersName

The list in the domain is shown as domain.com/X/Lists/CostCentre but the actual name of the list is Cost Centre
I was simply using the name as shown in the URL instead.

Next question is, how to remove the 46;# from the ManagerName.

is it the pharselookup by any chance?

L
Badge +9

formula should be  lookup('/A|CostCentre','CostCentre',CostCentre,'Manager')

The formula will take 'CostCentre' (control) value from the form and looks in 'Cost Centre' list column in siteA and returns Manager.

G
Userlevel 6
Badge +13

yes, you can use parselookup(lookup("/A|Cost Centre", "ID", CostCentre, "Manager")) to return just the value.

And regarding your resolution, yes remember, because we are encapsulating our values in quotes ("") then we are always trying to refer to the display name of the list and column, not the internal name. 

B
Badge +11

Hey Lakshi

I finally figured out that the /A|CostCentre meant to be /A|Cost Centre

Your above formula does not display any results but with the ID it shows some random number#;Managername

I guess it will be the parselookup to show the exact value.

B
Badge +11

Yes, this works perfect.
The only minor problem I am having it that the cost centre result is saved as the ID number in the field.

For example 999999 is the cost centre and the ID is 26
When I submit the form, the ID 26 saved in the field instead of the actual value.

UPDATE:

Had to change from ID Connected to Text connected to happy.png

G
Userlevel 6
Badge +13

I recently updated Nintex and have experienced this problem with a List lookup. It display the value in the form, but saves the ID to the list, even if you've connected the Text to your column.

I've raised a support call for it. I don't recall it being an issue before.

What version are you running?

B
Badge +11

we are running SP16 with NINTEX 2016 Version: 4.3.0.11

G
Userlevel 6
Badge +13

Same here. Think it's a new bug.

B
Badge +11

So after you updated Nintex to 4.3.011 the value changed to ID ??
What have you done to change the ID back to the value then??!

G
Userlevel 6
Badge +13

Yes, instead of the value being stored in the list column, the ID is.

I haven't yet done a workaround for it, but I imagine using a lookup function will resolve the issue if you're just selecting a single item. Unfortunately, I'm selecting multiple items so the lookup runtime function won't sort this for me. I may have to workflow the solution unfortunately.

A
Badge +1

Hi Lakshminarayana,

 

I am trying to do the same thing but I am facing issue even though I tried your suggested solution. 

Can you please help me with this?

Here is my scenario: 

So, I have one list in the different site and the list contains different fields such as PositionTitle, PositionNumber, Branch. 

I have different list in the different site and I want to auto-populate PositionTitle and Branch when user enter PositionNumber. 

 

For this I am writing the lookup function like this: 

lookup("/sites/Master/Lists|PositionList", "PositionNumber", PositionNumber, "PositionTitle")

 

I tried your version as well.

lookup("/Master|PositionList", "PositionNumber", PositionNumber, "PositionTitle")

lookup("/Master/Lists|PositionList", "PositionNumber", PositionNumber, "PositionTitle")

 

When I preview this it say "Loading..." and just disappeared nothing else displayed. 

I tired using parseLookup(PositionNumber) but didn't worked. 

Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings