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

List Item lookup Help!

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.

Labels: (3)
Tags (1)
0 Kudos
Reply
10 Replies
Workflow Hero

Re: List Item lookup Help!

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

0 Kudos
Accept as Solution Reply
Workflow Hero

Re: List Item lookup Help!

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

0 Kudos
Accept as Solution Reply
Workflow Hero

Re: List Item lookup Help!

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.

Accept as Solution Reply
Workflow Hero

Re: List Item lookup Help!

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")

0 Kudos
Accept as Solution Reply
Workflow Hero

Re: List Item lookup Help!

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.

0 Kudos
Accept as Solution Reply
Workflow Hero

Re: List Item lookup Help!

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?

0 Kudos
Accept as Solution Reply
Workflow Hero

Re: List Item lookup Help!

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.

0 Kudos
Accept as Solution Reply
Workflow Hero

Re: List Item lookup Help!

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?

0 Kudos
Accept as Solution Reply
Workflow Hero

Re: List Item lookup Help!

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.

0 Kudos
Accept as Solution Reply