Lookup fields are not showing up in my form

Hi Josh,

Could you explain your situation with more detail?  You might need to use the lookup runtime function but I can't quite tell how you want the form to behave.  If you want something like this, then lookup is your friend.

You have a lookup list that has a column of ideas (Idea title) and a column showing the people that submitted that idea (submitter).  You have a new list/form where one of your fields is a lookup back to the idea name and you also want to retrieve who submitted that idea - that's exactly where you'd use the lookup runtime function.  Create a calculated value and type this lookup("Name of the ideas list", "Idea title", nameOfTheIdeaControlOnYourForm, "Submitter").

You might have to further format the submitter if that column is a person/group column but you get the idea.

Cheers,

Chris

Hey Chris. Thanks for the response. I have tried a similar formula. I am running into the problem that my lookup field is not showing up in the named controls. I think you know what I am talking about because I understand your explanation.

More info: I created a list called System List. It has 2 columns, one is User ID and the other is User Name. I have created a lookup column in sharepoint to this list and as people enter the user id, it auto populates the User Name into another field. So going forward, I want all end users that use this form, to be able to select the user id from a drop down on the form, and it auto populates the User Name on the same form. I can't get User Name to show on the form.

can you put some screenshots on as I am struggling to understand the requirement and the problem.

what sort of field types are your lookups?

Hey Josh,

I think I have your answer.  I'm going to give you two answers though because they depend on how you have the user name stored - is the column a text column or is a person/group column?  Here's the System List I made for reference:

Inside your ideas list you'd have made a lookup column and that will reference Staff ID in my example.  In my example, I've called this field Staff ID so the corresponding control on the form will be automatically named StaffID. You'll also have another column that refers to the person's name, let's call it "Submitter's name".  This must be a text field, not a person field. So that's the first thing to check for you.

Then in your form, follow these instructions depending on how you have set up your System List.

Option 1: Your User Name is a Single line of text

• Create a calculated field and connect it to "Submitter's name"
• Write this formula: lookup("System List", "Staff ID", parselookup(StaffID), "Staff Member")

I'm guessing you had missed out the parselookup function in your lookup formula.  Why do we need it? Because the lookup field that you're referencing doesn't just return the staff ID, it returns the unique ID as known by the list and that string looks something like this: 12;#Staff1

You don't want all that rubbish - you just want their ID (Staff1).  The parselookup function will chop all the unnecessary bits away.  It does a bit more than that and if you're interested you can search this forum or check out the help.

Option 2: Your User Name is a Person or Group

• Create a calculated field and connect it to "Submitter's name"
• Write this formula: parselookup(lookup("System List", "Staff ID", parselookup(StaffID), "Staff Name")

)

Only a minor change to the first option in that we're using parselookup twice and we're using it for exactly the same reason.  If you lookup a person/group field, it returns some extra stuff that you don't want and parselookup gets rid of it.  If you want to see what that extra stuff looks like, just keep the same formula as option 1.

Hope that helps!

Cheers,

Chris

Hey Josh,

Did you try it out?  Let me know if it worked or not.

Cheers,

Chris

Hi Chris

I am a little confused still. So in my separate list that my lookup is looking at, I have the "Submitters Name" as a single line of text field. I tried to create a calculated field on my form but the field that the lookup brought back to my main list is not available in the "connect to" dropdown. What am I missing?

Option 1: Your User Name is a Single line of text

• Create a calculated field and connect it to "Submitter's name"
• Write this formula: lookup("System List", "Staff ID", parselookup(StaffID), "Staff Member")

Hey Cassy

This is what I am trying to accomplish. I want to enter a submitter ID in the first dropdown (from my lookup list) and it bring over the Submitters Name (from the same lookup list). The probably isn't getting it to populate in my main SP list, but just to populate on this form so the end user can see who the Submitter Name is.

A little more background of my actual requirement for this. I have 12 defect fields listed on my list. I want to be able to tell the auditor who the initial analyst was that submitted the incorrect information for each defect. So I will have 12 fields on my form for Submitter ID and Submitter Name (this is the initial analyst that did the entry incorrectly).

Hi Josh,

Can you post screenshots of your System List and your Main list (defects list?) so we can see what the columns and types are?  If you can't see the submitter ID when you go to look for a list of control names means that control might not have a name.  Did you create the submitter ID lookup field in your list before you created the form or did you drag the list lookup control onto your pallette and then link it to your System List?  If you used the second method then it won't have a name and you need to enter one.

Cheers,

Chris

Hi Josh, did that work?

Hey Chris

Still no luck. Here are some screen shots.

This one is how my lookup columns appear. MSP ID for Defect 1 would be the entry field that the end user puts the "ID" into and it populates the MSP ID for Defect 1: Assigned Associate. This Assigned Associate field is the field I want to show on the form as end users enter the MSP ID for Defect 1. Also, I created this list prior to building the form so I did not drag a lookup field in. Although I did try that too. Thanks for your attention and follow up! ‌ I am tagging you as well since you also asked for screen shots.

This is the MSP List referenced above

MSP List

On your form add a Calculated Value control with this:

this is the formula

lookup("MSPList", "Title", parselookup(MSPID), "Assigned Associate")

in my example:

• my list name is "MSPList"
• the MSP ID is using the "Title" field
• the red MSPID is my named control on my form which is the lookup - requires parselookup as per Chris' earlier explanation
• Assigned Associate is the field I want to bring back.

Works perfect on my form (PS we got married haha)

Hahahahaha. Well it's about time you said yes! I like the sound of that lol.

So I am still missing something.My

Congratulations on your wedding you two.  You will have beautiful children!   Between the three of us we'll definitely get this little problem sorted before Christmas!

This is good that you can now see MSPIDforDefect1 in your named controls list.  In an earlier post you said you couldn't see it so this is progress.  In the same list I also note that you have MSP ID for Defect 1:ASSIGNED ASSOCIATE as a lookup field.  This shouldn't be a lookup field because you are setting it programatically.  It should be a text field so it matches the ASSIGNED ASSOCIATE in your MSP List.  However that alone shouldn't be creating the #value error so please explain again.

My understanding is:

1. You have a list called MSP List.  It has a column "MSP ID" (must be unique, must be mandatory).  It also has another column called "Assigned Associate" which is either a text field or a person field (doesn't matter).  Also must be mandatory.
2. You have another list called Defects.  It is a lookup column called "MSP ID for Defect 1" this column looks up your MSP ID column in the MSP list.  All of this seems to be working fine so far.
3. Also in that defects list you have another column called "MSP ID for Defect 1:ASSIGNED ASSOCIATED".  It should be text (not a person field).
4. Nintex forms creates controls based on field names and removes spaces so therefore your formula should be lookup("MSP List", "MSP ID", parseLookup(MSPIDforDefect1), "Assigned Associate"). i.e. exactly what Cassy wrote except I've used MSP ID instead of Title.  Remember if Assigned Associate is a person field you'lll need that extra parseLookup that I specified earlier in this thread.

Cheers,

Chris

I don't think Josh has created additional lookup columns for ASSIGNED ASSOCIATE, he has just checked the property on the original lookup column like this right Josh?

Did you rename the title field on MSP list to be MSP ID?  Or is it a new column?

if you are not sure go into MSP List > Site Settings > Choose column and you will see in the url the name of it.

Woohoo! I got it to work! I think what I was missing was the fields were not required and I added the space in MSP List. Now just to add 11 more fields just like it lol. And yes Cassy, I did rename the Title field to MSP ID. This was something new I learned so thank you!

Thank you to both of you for your patience and help!

Haha. I agree. I keep telling her to come to the states!

Thanks for your diligence in helping me with this. This is going to be huge for me now that I know how to do it.

Happy Holidays!

Glad we could assist!

Thanks Chris, that ParseLookup hint solved it for me.