SharePoint Integration

Re: SharePoint list to repeating table.

sanjay619 — Mon, 16 Aug 2010 06:03:25 GMT

I am Having Infopath form where i am having LeadId....while selecting LeadId from dropdown List....I am getting value in the repeating table the problem is that it only show first value .....i have to manually select second value from insert option and so in repeting table.it is possible that i should get all the value in repeating table. although i have issued CAML query for that....so here are my code .....thank's in advance if one can help.....

using Microsoft.Office.InfoPath;

using System;

using System.Windows.Forms;

using System.Xml;

using System.Xml.XPath;

using mshtml;

using Microsoft.SharePoint;

namespace Quotation

{

public partial class FormCode

{

// Member variables are not supported in browser-enabled forms.

// Instead, write and read these values from the FormState

// dictionary using code such as the following:

// private object _memberVariable

// {

// get

// {

// return FormState["_memberVariable"];

// }

// set

// {

// FormState["_memberVariable"] = value;

// }

// NOTE: The following procedure is required by Microsoft Office InfoPath.

// It can be modified using Microsoft Office InfoPath.

public void InternalStartup()

{

((ButtonEvent)EventManager.ControlEvents["CTRL101_5"]).Clicked += new ClickedEventHandler(CTRL101_5_Clicked);EventManager.XmlEvents["/my:myFields/my:group5"].Changed += new XmlChangedEventHandler(ddlLeadId_Changed);

}

public void CTRL101_5_Clicked(object sender, ClickedEventArgs e)

{

if (this.Errors.Count > 0)

{

this.Errors.DeleteAll();

}

XPathNavigator secDSNav = DataSources["CustomListCAML2"].CreateNavigator();

XPathNodeIterator iter = secDSNav.Select("/Batch/Method");

int methodNodeCount = iter.Count;

XPathNavigator firstMethodNav = secDSNav.SelectSingleNode("/Batch/Method[1]", NamespaceManager);XPathNavigator lastMethodNav = secDSNav.SelectSingleNode("/Batch/Method[" + methodNodeCount.ToString() + "]", NamespaceManager);

firstMethodNav.DeleteRange(lastMethodNav);

XPathNavigator root = MainDataSource.CreateNavigator();

XPathNodeIterator rows = root.Select("/my:myFields/my:group3/my:group4", NamespaceManager);

XPathNavigator QDF = this.MainDataSource.CreateNavigator();

string QuotNo = QDF.SelectSingleNode("/my:myFields/my:txtQuotationNo", NamespaceManager).Value;

string QuotDate = QDF.SelectSingleNode("/my:myFields/my:ddlDate", NamespaceManager).Value;

string LeadId = QDF.SelectSingleNode("/my:myFields/my:ddlLeadId", NamespaceManager).Value;

string CustomerName = QDF.SelectSingleNode("/my:myFields/my:txtCustomerName", NamespaceManager).Value;

string Address = QDF.SelectSingleNode("/my:myFields/my:txtAddress", NamespaceManager).Value;

string ContactPerson = QDF.SelectSingleNode("/my:myFields/my:txtContactPerson", NamespaceManager).Value;

string SubTotal = QDF.SelectSingleNode("/my:myFields/my:txtSubTotal", NamespaceManager).Value;

string VAT = QDF.SelectSingleNode("/my:myFields/my:txtVATAmount", NamespaceManager).Value;

string CST = QDF.SelectSingleNode("/my:myFields/my:txtCSTAmount", NamespaceManager).Value;

string ForwardCharges = QDF.SelectSingleNode("/my:myFields/my:txtFORWARDINGCHARGESAmount", NamespaceManager).Value;

string TotalAmount = QDF.SelectSingleNode("/my:myFields/my:txtTotalAmount", NamespaceManager).Value;

int counter = 1;

while (rows.MoveNext())

{

string ProductName = rows.Current.SelectSingleNode("my:txtItemName", NamespaceManager).Value;

string Desc = rows.Current.SelectSingleNode("my:txtItemDesc", NamespaceManager).Value;

string Qty = rows.Current.SelectSingleNode("my:txtQty", NamespaceManager).Value;

string Rate = rows.Current.SelectSingleNode("my:txtRate", NamespaceManager).Value;

string Amount = rows.Current.SelectSingleNode("my:txtAmount", NamespaceManager).Value;

AddMethodNode(counter, QuotNo, QuotDate, LeadId, CustomerName, Address, ContactPerson, SubTotal, VAT, CST, ForwardCharges, TotalAmount, ProductName, Desc, Qty, Rate, Amount);

counter++;

}

DataConnections["QuotSubmit"].Execute();

DataConnections["MainSubmit"].Execute();

XPathNavigator XPN = this.MainDataSource.CreateNavigator();System.Windows.Forms.MessageBox.Show("Record Saved Successfully........");

}

private void AddMethodNode(int id, string QuotNo, string QuotDate, string LeadId, string CustomerName, string Address, string ContactPerson, string SubTotal, string VAT, string CST, string ForwardCharges, string TotalAmount, string ProductName, string Desc, string Qty, string Rate, string Amount)

{

System.Text.StringBuilder sb = new System.Text.StringBuilder();

sb.AppendFormat("<Method ID=\"{0}\" Cmd=\"New\">", id.ToString());

sb.AppendFormat("<Field Name=\"Title\">{0}</Field>", QuotNo);

sb.AppendFormat("<Field Name=\"Date\">{0}</Field>", QuotDate);

sb.AppendFormat("<Field Name=\"LeadID\">{0}</Field>", LeadId);

sb.AppendFormat("<Field Name=\"CustomerName\">{0}</Field>", CustomerName);

sb.AppendFormat("<Field Name=\"ContactPerson\">{0}</Field>", ContactPerson);

sb.AppendFormat("<Field Name=\"Address\">{0}</Field>", Address);

sb.AppendFormat("<Field Name=\"ProductName\">{0}</Field>", ProductName);

sb.AppendFormat("<Field Name=\"Description\">{0}</Field>", Desc);

sb.AppendFormat("<Field Name=\"Qty\">{0}</Field>", Qty);

sb.AppendFormat("<Field Name=\"Rate\">{0}</Field>", Rate);

sb.AppendFormat("<Field Name=\"Amount\">{0}</Field>", Amount);

sb.AppendFormat("<Field Name=\"SubTotal\">{0}</Field>", SubTotal);

sb.AppendFormat("<Field Name=\"VAT\">{0}</Field>", VAT);

sb.AppendFormat("<Field Name=\"CST\">{0}</Field>", CST);

sb.AppendFormat("<Field Name=\"ForwardingCharges\">{0}</Field>", ForwardCharges);

sb.AppendFormat("<Field Name=\"TotalAmount\">{0}</Field>", TotalAmount);

sb.AppendFormat("</Method>");

XmlDocument methodXML = new XmlDocument();

methodXML.LoadXml(sb.ToString());

XPathNavigator secDSNav = DataSources["CustomListCAML2"].CreateNavigator();XPathNavigator batchNav = secDSNav.SelectSingleNode("/Batch", NamespaceManager);

batchNav.AppendChild(methodXML.DocumentElement.CreateNavigator());

}

public void ddlLeadId_Changed(object sender, XmlEventArgs e)

{

XPathNavigator Demo = this.MainDataSource.CreateNavigator();using (SPSite siteCollection = new SPSite("http://RnDserver:10101/default.aspx"))

{

using (SPWeb web = siteCollection.OpenWeb())

{

string leadid = Demo.SelectSingleNode("/my:myFields/my:ddlLeadId", NamespaceManager).Value;

SPList list = web.Lists["Lead for Existing Product"];SPQuery query = new SPQuery();

query.Query =

"<Where>" +

"<Eq>" +

"<FieldRef Name='Lead_x0020_Id' />" +

"<Value Type='Text'>" + leadid +

"</Value>" +

"</Eq>" +

"</Where>";

SPListItemCollection collection = list.GetItems(query);foreach (SPListItem item in collection)

{

string myNamespace = NamespaceManager.LookupNamespace("my");

using (XmlWriter writer = MainDataSource.CreateNavigator().SelectSingleNode("/my:myFields/my:group3", NamespaceManager).AppendChild())

{

writer.WriteStartElement("group4", myNamespace);

writer.WriteElementString("txtItemName", myNamespace, item.Title);

writer.WriteElementString("txtItemDesc", myNamespace, item.Title);writer.WriteElementString("txtQty", myNamespace, item.Title);

writer.WriteEndElement();

writer.Close();

}

Re: SharePoint list to repeating table.

Crazy Vinnie — Sat, 05 Sep 2009 18:07:52 GMT

Thanks for the reply Hilary, I looked at the article by S.Y.M. Wong-A-Ton and it seems to be exactly what I am trying to accomplish, however there appears to be multiple errors in his code, or I am not following his instructions closely enough. I am using IP 2003, and followed the instructions using Visual Basic to no avail. Does this code not work, or is it just me?

Re: SharePoint list to repeating table.

Hilary Stoupa — Thu, 03 Sep 2009 01:28:11 GMT

Are you using InfoPath 2003 or 2007? If 2007, I'd suggest you use VSTA and C# code -- I find it MUCH easier than JScript. Here is a fantastic article by S.Y.M. Wong-a-Ton that might help you get started....

Re: SharePoint list to repeating table.

Crazy Vinnie — Wed, 02 Sep 2009 23:27:33 GMT

Your help has been invaluable in the past Hilary, thank you. Copying the repeating data from my secondary data source into my main data source is exactly what I am trying to do. So far I have had no luck. I am not a programmer so writing code is way beyond me, and I cannot install qRules on my computer as I have insufficient privileges. MY IT department doesn’t trust anything without the word “Microsoft” stamped on it. I had hoped there might be a simple way to do what I need, or adapt someone else’s JScript to my form. I will keep tinkering with it for a while, but I’m just going around in circles.

Re: SharePoint list to repeating table.

Hilary Stoupa — Wed, 02 Sep 2009 15:40:44 GMT

Hi:

Copying repeating data from a secondary data source into a main data source is possible 2 different ways:

Write custom code. When the user selects either Mon or Tues from the first dropdown, you could use the changed event and get the data from the correct field in your secondary data source (Mon or Tues, as the case may be) and then create and populate repeating fields in your main data source
Try qRules. It is still FREE, and you may be able to use the CopyTable command and do this without code. You'd have conditions on your rules for the command, so if the value of the field was Mon, you'd copy from one field in your secondary data source into your main data source, if it is Tues, another, etc.

Re: SharePoint list to repeating table.

warrtalon — Sun, 30 Aug 2009 22:32:23 GMT

I don't think your explanation makes sense, and I think you're confusing rows with columns. You twice said that you want the user to select something in a column and then show all the rest of the items in that column. That doesn't make sense to me. Columns are nothing but individual pieces of metadata that describe a record. Each selection in your column corresponds with a record in that list. When you select ONE item from that column, you can then show all the rest of the columns associated with that record, but there is no association between that one record and all the other records that correspond to the other items in that column. When you attach a dropdown to a specific node (column), then what you are doing is showing each RECORD that exists but only the data in that one column. When you select ONE of those records, you then only can reference and display the rest of the columns for that one record.

It doesn't make sense to me that you would want to show all the items in a COLUMN, because columns aren't records but rather metadata to describe records. Maybe you meant to have everything in your column as a row instead? Anyway, the way you've described it doesn't make sense to me, so please describe EXACTLY what you want to accomplish...not what you want the form to do, but rather your business problem that you're trying to resolve.

So here's the deal. Do this so that you see what I mean. In the Data Source pane, select your secondary data connection that connects to the SharePoint List. Drill down to the REPEATING GROUP that contains your nodes (columns). Drag that repeating group onto the canvas and preview the form. You will all the columns and all the rows from the list. If you only want to show one column, then you simply remove the other columns from this repeating table, or you only drag the one node to the canvas. This is how you show all the items within a column, but that still doesn't make sense. Why would you be trying to choose a column to display? You typically choose RECORDS within your list (aka table, aka database), because the record is the real data, and the column is just one descriptor of the record.

PS. There is no MOSS 2003.

Re: SharePoint list to repeating table.

Crazy Vinnie — Sun, 30 Aug 2009 14:47:05 GMT

Let me try to clarify what I am trying to accomplish.I have created a SharePoint list with two columns labeled MON and TUE. In each of these columns is a list of names. My form has a drop-down list box which the user would select either MON or TUE. Upon making their selection I would like all the names in the column they selected to populate text boxes in a repeating table.Problem: Upon making a selection in the drop-down list box, only the first name is inserted into the text box in the repeating table. I cannot figure out how to make the repeating table insert another line item and put the next name in the list into that text box and so on.The end result I am looking for is this. I need the user of my form to be able to select the column they want from my SharePoint list and have all the entries in that column populate text boxes in my form. I may be going about this all wrong; maybe there is a better way to accomplish what I am trying to do. I hope this is clear as I cannot post a link to my secure SharePoint site and my form has personal information on it as well. Any and all help is appreciated.

My boss thinks I’m smart, I would be lost without this forum.

Re: SharePoint list to repeating table.

Qazi Anis — Sun, 30 Aug 2009 06:51:20 GMT

hi Vinnie,

i think u r simply comparing the value of the selected field in the repeating table,

Can you attach the xsn, so that we can see what we can do.

SharePoint list to repeating table.

Crazy Vinnie — Sat, 29 Aug 2009 16:50:36 GMT

Beginner, using IP 2003 and MOSS 2003 I’m in over my head, but still required to produce results. I am creating an IP form that needs to get data from a SharePoint list that has several columns. In my form the user would select which column he wants with a simple drop-down list box. The data in that column would then populate text boxes in a repeating table.

I have tried to accomplish this myself, but upon execution all I get is the first entry in the selected column and nothing more.

Thanks in advance for any help. My boss thinks I’m smart, I would be lost without this forum.