There are a few often overlooked 'gotchas' when trying to use the Lookup runtime function with a Lookup Control value.
Lookup Controls do not return just the text value of whatever it is you have selected from the dropdown. The value is actually a combo of the Item's ID (for whichever item you had selected from the dropdown) + the Value of the column the Lookup is targeting.
You can see this easily if you create a Calculated Control, and have it's value populate with the value of your Named Lookup Control.
Here is a small list of items
In a form if I make a Lookup Control and then show that value in a Calculated Control directly next to it, this is what I'm talking about:
We see the ID of the Item, a delimiter of ";#" and then the actual value we're concerned with "PSE-000003"
So how do we get just the last part of the value? We can use the parseLookup runtime function!
Here is another Calculated Control's formula to show how it's setup:
The results (parseLookup calc control is on the far right):
So now that we know how to get the value that we actually wanna use, let's setup a Lookup inside of a different Calculated Control.
And here is the formula:
lookup("Self Lookup List", "PRD_x0020_Tag_x0020_Number", parseLookup(control_TagNumber), "Fluid_x0020_Name")
The arguments described in order are:
- The Target List
- The column you're checking your value against
- The Value
- The column from which you'd like return a value IF a match can be found
You may notice the column names I'm using look kinda strange. That's because SharePoint requires you to use the "Internal Column Name" of a column when you're making a CAML Query as this is doing in the background. To get your Internal Column Name, go to your List Settings:
And then find and click on the column(s) in question:
In the Column Settings menu, look up at the URL of your browser and go to the very end of it. There you will be able to see the Internal Column Name of your column in question!
Once you do that for both of your columns, you should be good to go!
If you get back to your form and test it out but don't see any results, the last thing you can do is to open up the Browser Dev Tools by pressing the F12 button on your keyboard.
Whenever a lookup operation happens on your form, it actually outputs information to the Console of your browser. By making a selection from our dropdown, we can see that the console is being populated with information about the Query being used that was based off of the lookup function
Formatted Query:
<View>
<Query>
<Where>
<Eq>
<FieldRef Name="PRD_x0020_Tag_x0020_Number" />
<Value Type="Text">PSE - 000004</Value>
</Eq>
</Where>
</Query>
<RowLimit>1000</RowLimit>
</View>
If something were to be wrong we might also see error messages there. However if you're unsure whether or not your Query is valid, you can always go into a Query List, Workflow Action (in the Workflow Designer) and use the Run Now option to test and see exactly what's being returned.
I hope this helps you to solve your lookup issues.
Thank you very much for your help. You have an amazing way of explaining complex item in a very easy to follow and understand how the application works not just giving me step by step instructions. I can use this in other projects.
This will return a blank field