I have a from that I created and the data is stored in the SharePoint list. I would like to create a look up that when a new form is created I can retrieve data from a previous submission in the same list.
I created a Look Up to retrieve the unique Tag No from the same list so it can retrieve the data for that submission.
I then created a calculated field to look up the field that I need bring in to the new form based on the PRD Tag No that is selected from the drop down (lookup field). I have tried two different ways.
Is a lookup field to query for information on the same list not the correct control to use?
Your help would be greatly appreciated.
Solved! Go to Solution.
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:
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
<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.