InfoPath Dev

Hi,

I am trying to submit to a SharePoint list that has a lookup column.

I am currently getting the error specified below:

"Errors occurred submitting to list: The operation failed because an unexpected error occurred. (Result Code: 0x80020005)"

I am guessing that this relates to submiitting to a list with a lookup column.

Is it possible to sumit to a SharePoint List that has a lookup column. If so, how can I achieve this?

 Thanks,

Padraic

Hi -

The issue with submitting to a lookup is that the lookup expects a particular syntax - ID;#Display

For example:

1;#Denver

There are different ways you could do this - easiest may be to add an additional field to your form that has a default value to set it to the correct ID & Display combo.

I belive if you replace the ID with negative one, SharePoint will attempt to look up the correct ID based on the display.

Hilary,

Thanks for responding to my issue.

I'm not sure I follow your suggestion though.

For example, if I have a repeating table that has the following fields: id and keyword, are you suggesting that I insert -1 as a default value into the properties of the id field.

Thanks,

Padraic

The first thing you'll need to know is what is providing the lookup values to your target SharePoint list.

Is it another list on the site? Or have they been hard coded in?

You need to submit the ID of the lookup item (for example, if it is from another list, you need to use the ID of the item in that list), then a semi-colon, the pound sign, and the display name (although you may be able to submit just the ID if you have it.... I'm not 100% sure on that....). So, most likely, instead of submitting the field you currently are from your form to your list, you'll want another field, not on the form, that has a formula to create the value SharePoint needs to use the UpdateListItems method to update a lookup. Does that make more sense?

Hilary,

This is what I am trying to achieve. I have a form that I submit to a Referrals Library.

There is a repeating table in that form that allows the user to record certain keywords relating to the case. When the user hits the submit button, a couple of actions are kicked off. Firstly, the repeating table data is deployed to a sharepoint list and there are a few of these tables. Secondly, the form is posted to the Referrals library.  The parent list is the Referrals Library and the child list is the Keywords list that I populate using qRules SharePointToList command. The Referrals Library has a Referral No field that is promoted from the form. The Keywords List has a ReferralID field that it uses to lookup the value in the Referrals Library.

I have created a mapping file to use for the SharePointToList command and it works as exepcted if I use a normal text column for the ReferralID field. However, when the ReferralID field is cast as a lookup, then the field does not get populated with data.

Thanks,

Padraic 

So, the issue then is with the Keywords list. It has a lookup that uses the Referrals Library.

Your form - what is submitting to the Keywords list? Valid ReferralIDs? That is, does your form give the user a dropdown to select the ReferralID from?

I think I'm doing a poor job of explaining what I mean, but I will try again.

Currently, you are probably submitting 1, 2, etc. to the Keywords list ReferralID field from your form field.

When the ReferralD column in the Keywords list is just a text field, your submit works without issue. SharePoint is expecting some text, and that is what you are providing.

Lookup columns require the data be in a different format. The format that the lookup column wants is the ID of the lookup item, followed by a semi-colon, a pound sign, and the display value.

If your Referral Library looked like this:

ID     Name
1      First Item
2      Second Item

You'd want to submit:

1;#FirstItem

To successfully populate the lookup from your form.

This is why I'm suggesting you add another field to your form data source and use a formula to set it to the value SharePoint expects (whether a default value or a rule) and submit this new field to the ReferrallID column in your Keywords list.

Hilary,

Now I've got it.......

Thanks for your patience on this one. Much appreciated.

Padraic 

Hilary,

I'm only getting a chance to return to this now due to other priorities in work. I understand what you are saying above but I don't know how to get the ID of the lookup item in the form library to update the Keywords list

How can I acheive this?

Thanks,

Padraic

So, I just re-read your 10-24 post descibing what you need to do. I think you are saying that you need to submit the referral library id for the form to the keywords list at the same time as the form itself is submitted? Is that the case? If so, you may need to submit your XML to the library, get the ID for the XML from the library (what version of SharePoint do you have? I'm hoping you are going to say "2010"), submit the keywords to the list, and if your form is using the /id parameter in SubmitToSharePointList, you'll want to re-submit the XML to the library again in order to have the id fields in the repeating table populated...

Hilary,

You are correct in your assumption that I submit the form to the library and the keywords to the list at the same time. I am using SharePoint 2010. When you say submit your XML to the library do you mean submit the form to the library. I have uploaded a scaled down version of what I'm trying to achieve and I'd be grateful if you could have a look and let me know where I'm going wrong.

Thanks,

 Padraic

When I say submit the XML to the library, yes, I'm referring to the XML generated by the filled out form (IP files are XML).

You have a data connection that submits the form to the TestRL library. I think you need to add a receive connection to that same library. If you are using SharePoint 2010, you'll have the option of only returning data for the current item. You should select that. You'll want to be sure to return the ID column from the library in this data connection.

Your rules would then:

1. Submit to your TestRL library
2. Query using your new data connection, to get the TestRL library ID of the XML you just submitted
3. Set some main data source field to the ID you've returned in step 2
4. Submit your list data to your list, being sure that you've mapped the field populated in step 3 to your column that has a lookup defined on the TestRL library
5. Since your SubmitToSharePointList command is populating the id node in your repeating group, you need to submit to your TestRL library again - that is, repeat step 1

I just tested a CAML UpdateListItems batch earlier today using only an ID for my lookup - submitting the ID alone should be sufficient, you don't have to muck about with ";#DisplayName".

Does this make a little more sense?

Hilary,

This makes sense. However, I won't get a chance to try it out until someitme this weekend.

I will let you know how I get on.

Padraic 

Hilary,

I got a chance to try this out and followed your steps but I'm still not able to set the value in the lookup column in the Keywords List.

Can you have a look at the rules on the Submit button in the attached form to determine if I am doing this correctly?

 Many thanks,

Padraic

Does the command fail with the same error as your initial post? If you change the lookup column to text (temporarily) does a value get submitted there?

The only thing that looks off to me is the mapping for your referral field:

/my:myFields/my:General/my:referral

I would use XPath relative to the repeating group:

../../my:referral

Your rules look right to me - you are getting an ID set in your referral field, correct?

Hilary,

The referral field does not obtain a value from the ID of the library. This field remains blank throughout the process.

I've used the full Xpath for the referral in the main form when updating the ReferralID as a test column and it submits o.k. However, I can change it to reflect your advice.

I don't get any errors displayed when I submit this form. The only field that doesn't get mapped is the lookup column in the Keywords list. The keyword gets posted to the SharePoint list but not the lookup column.

Thanks,

Padraic