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
Does anyone know what I am doing wrong. How can I get the 86-100 amounts to populate.
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 ...
so what you need is lookup filtered by two keys/columns (D Type, Percentage).
sadly, this is not supported/available (as of now)
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.
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")
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 220.127.116.11, is it a fix in a future release?
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.
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.