Vaneza
Forms Fledgling

Need to Look up items from same list and return back on data on form

Jump to solution

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.

PRD TAG NO2021-06-14_21-52-05.jpg

 

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.

 

1_2021-06-14_22-04-39.jpg

  • This will return an error: #Value!

 

  • 2_2021-06-14_22-05-38.jpgThis will return a blank field

 

Is a lookup field to query for information on the same list not the correct control to use?

Your help would be greatly appreciated.

 

 

 

 

 

0 Kudos
Reply
2 Replies
MegaJerk
Automation Master
Automation Master

Re: Need to Look up items from same list and return back on data on form

Jump 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

MegaJerk_0-1623940053548.png

 

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: 

MegaJerk_1-1623940099129.png

 

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:

MegaJerk_2-1623940272000.png

 

The results (parseLookup calc control is on the far right): 

MegaJerk_3-1623940358133.png

 

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.

 

MegaJerk_4-1623940438971.png

 

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:

  1. The Target List
  2. The column you're checking your value against
  3. The Value
  4. 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: 

MegaJerk_5-1623940874943.png

 

And then find and click on the column(s) in question: 

MegaJerk_6-1623940946229.png

 

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!

MegaJerk_9-1623941295538.png

 

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. 

MegaJerk_10-1623941509437.png

 

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

MegaJerk_11-1623941692217.png

 

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. 

 

 

 

View solution in original post

Reply
Vaneza
Forms Fledgling

Re: Need to Look up items from same list and return back on data on form

Jump to solution
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.
0 Kudos
Reply