Qdabra qRules

Re: SubmitToSharePoint List and Lookup Columns

Padraic — Fri, 11 Nov 2011 17:42:56 GMT

Hilary,

I think I have found a solution thanks to your expert guidance. When I unchecked the "Include data for the active form only" checkbox in the data connection for retrieving the ID from the form Library, I found that it just returns the first ID in the library. If this checkbox is checked, it doesn't return any value. Obviously it is of no use to me if the ID returned all the time is the first item in the list, so as a workaround, when I'm setting the value for referral on the form I used the function max(ID) and this returns the highest/latest/current value of the submitted item. I cannot think of an instance whereby this logic might fail, but please let me know if I'm missing something obvious.

In addition, I also discovered that I only need to submit the form one time to the library.

Many thanks for your patience and help with this issue. It is very much appreciated.

Padraic

Re: SubmitToSharePoint List and Lookup Columns

Hilary Stoupa — Fri, 11 Nov 2011 17:42:48 GMT

If the referreral field is not getting set in the form, of course, there is no data in the field to submit, then.

Perhaps deactivate most of your rules and just try your submit and query rule to see if you are getting data back in your query? Drag the query datafields onto the form to verify you have the data you'd expect?

Re: SubmitToSharePoint List and Lookup Columns

Padraic — Fri, 11 Nov 2011 17:41:03 GMT

Hilary,

In addition, I also discovered that I only need to submit the form one time to the library.

Many thanks for your patience and help with this issue. It is very much appreciated.

Padraic

Re: SubmitToSharePoint List and Lookup Columns

Padraic — Fri, 11 Nov 2011 17:07:05 GMT

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

Re: SubmitToSharePoint List and Lookup Columns

Hilary Stoupa — Fri, 11 Nov 2011 16:53:15 GMT

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?

Re: SubmitToSharePoint List and Lookup Columns

Padraic — Fri, 11 Nov 2011 15:57:07 GMT

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

Re: SubmitToSharePoint List and Lookup Columns

Padraic — Fri, 11 Nov 2011 10:55:40 GMT

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

Re: SubmitToSharePoint List and Lookup Columns

Hilary Stoupa — Thu, 10 Nov 2011 23:43:42 GMT

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:

Submit to your TestRL library
Query using your new data connection, to get the TestRL library ID of the XML you just submitted
Set some main data source field to the ID you've returned in step 2
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
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?

Re: SubmitToSharePoint List and Lookup Columns

Padraic — Thu, 10 Nov 2011 23:13:55 GMT

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

Re: SubmitToSharePoint List and Lookup Columns

Hilary Stoupa — Thu, 10 Nov 2011 22:40:11 GMT

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...

Re: SubmitToSharePoint List and Lookup Columns

Padraic — Thu, 10 Nov 2011 21:50:02 GMT

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

Re: SubmitToSharePoint List and Lookup Columns

Padraic — Tue, 25 Oct 2011 18:52:45 GMT

Hilary,

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

Thanks for your patience on this one. Much appreciated.

Padraic

Re: SubmitToSharePoint List and Lookup Columns

Hilary Stoupa — Mon, 24 Oct 2011 23:53:31 GMT

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.

Re: SubmitToSharePoint List and Lookup Columns

Padraic — Mon, 24 Oct 2011 22:08:01 GMT

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

Re: SubmitToSharePoint List and Lookup Columns

Hilary Stoupa — Mon, 24 Oct 2011 21:07:32 GMT

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?