List Item lookup Help!


Badge +7

All,

I have a list that that I am trying to create a calculated column for in my form.

I have 3 different director levels (A lookup field) - D 1, D2 and D3.

I have another lookup percentage field ranging from 85-100 for the 3 different director levels

I have a commission amount for the various director levels and percentage.

This is the query I am using for the calculated column, which is only bringing the first amounts for the three different director levels only

lookup("Plans", "Percentage:Title", parseLookup(AtoG), "Sales Commission")&&lookup("Plans", "D Type:Title", parseLookup(D Type), "Sales Commission")

Does anyone know what I am doing wrong. How can I get the 86-100 amounts to populate.


10 replies

Userlevel 5
Badge +14

could you post an example content of your single list and what you want to accomplish?

I'm not able to identify what are relations/hierarchy among different list, what is looked up from where, etc.

as well, how does variables/references in your formula relate to mentioned lists and/or fields?

logical condition on lookup results sounds to me quite weird if you expect returned some string or number ...

Badge +7

My lookup calculated value in the form is only picking up the first 100 and not the other two even if I change the RD type from 1 to 3

Userlevel 5
Badge +14

so what you need is lookup  filtered by two keys/columns (D Type, Percentage).

sadly, this is not supported/available (as of now)

some ideas how to work around the restriction you can find eg. here  

if you could redesign your sales commission list into several ones, you could make them hierarchically dependent (cascaded) which would help you then to filter just by single key and with few nested lookup you could get what you need.

Badge +7

Thank you. That was useful. I am trying to do a calculated value look upin another subsite but it is not returning what I need.

My calculated value:

lookup("site/resourcecenter|IP Plans", "RD Name", parseLookup(EmployeeName), "Other Key Measures")

Userlevel 5
Badge +14

list URL has to be 'server relative URL' and has to start with a slash.

looks like you passed in site relative URL and without slash.

Badge +7

Hi Marian, I've tried with the slash but it doesn't work. It is a subsite within the site.

lookup("/site/resourcecenter|IP Plans", "RD Name", parseLookup(EmployeeName), "Other Key Measures"). In on version 2.9.3.1, is it a fix in a future release?

Userlevel 5
Badge +14

despite it's subsite of your current site, it has to be supplied relative to server.

ie. path to the 'IP Plans" list by your example is evaluated like: http://sp.domain.com/site/resourcecenter/Lists/IP%20Plan

so check whether this is really your URL.

second problem could be how does parseLookup(EmployeeName) evaluates, have you check it evaluates to a name that exists in referenced list?

last hint, check developer consloe, lookup function logs there CAML it uses to query data. it might give you a clue what exactly is being queried and what might be a problem.

Badge +7

employee name exists in the current list not the lookup list. How do you create variable in another list? Do I need to create a form variable?

Userlevel 5
Badge +14

I have meant it, if there is at least one item in 'IP PLans" list that in field 'RD Name" have a name that's in variable EmployeeName.

furthermore there might be a datatype problem - if 'RD Name" is people&group type filed and you query by I EmployeeName string that can not be recognized/converted to people&group entity, lookup will obvioulsy fail.

Badge +11

The topic may be old but I am having a difficulty with a lookup value.

I am trying to use lookup to look at a different subsite by using the URL address.

parseLookup(lookup(https://sp.domain.com/A/Lists/CostCentre/AllItems.aspx,"ID",CostCentre, "Manager"))

the aim is to pull the Manager's name based on the dropdown field : CostCentre but the result shows #Value!

There is a list named to Cost Centre as a custom list is and when you go to the list it shows the following URL

https://sp.domain.com/A/Lists/CostCentre/AllItems.aspx

I also tried as following: https://sp.domain.com/A/Lists/Cost%20Centre but nothing helps.

Any advise?

Reply