cancel
Showing results for 
Search instead for 
Did you mean: 
Workflow Hero

lookup to a list on a different page / site ?

Jump to solution

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 .... ???

0 Kudos
Reply
14 Replies
Workflow Hero

Re: lookup to a list on a different page / site ?

Jump to solution

Try like below.

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

Reply
Workflow Hero

Re: lookup to a list on a different page / site ?

Jump to solution

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 ...

0 Kudos
Reply
Workflow Hero

Re: lookup to a list on a different page / site ?

Jump to solution

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
0 Kudos
Reply
Workflow Hero

Re: lookup to a list on a different page / site ?

Jump to solution

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 ...

0 Kudos
Reply
Workflow Hero

Re: lookup to a list on a different page / site ?

Jump to solution

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?

0 Kudos
Reply
Workflow Hero

Re: lookup to a list on a different page / site ?

Jump to solution

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.

0 Kudos
Reply
Workflow Hero

Re: lookup to a list on a different page / site ?

Jump to solution

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. 

Reply
Workflow Hero

Re: lookup to a list on a different page / site ?

Jump to solution

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.

0 Kudos
Reply
Workflow Hero

Re: lookup to a list on a different page / site ?

Jump to solution

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

0 Kudos
Reply