Newbie Questions

Re: Retrieving Column Data from Sharepoint List to a Repeating Table in Infopath

Omegacron — Tue, 11 Jun 2013 14:46:51 GMT

I realize this is an old post, but for those finding it as I did... the reason for the invalid token error is because the filter is in the wrong format. The entire filter statement must be enclosed in brackets, not just the field name. Also, I'm not sure if the operator "or" is valid in this context.

Re: Retrieving Column Data from Sharepoint List to a Repeating Table in Infopath

buck_murdock — Fri, 06 Mar 2009 21:36:30 GMT

Hi Hilary,

Thanks for that bit of help, i didnt know you could add a filter in at that point. I put those changes into my code, but now when i preview the form i get an error which i can't seem to decipher.

Error:

System.Xml.XPath.XPathException
'/dfs:myFields/dfs:dataFields/dfs:Agenda_Items[@Status = 'Pending Discussion'' has an invalid token.
   at MS.Internal.Xml.XPath.XPathParser.CheckToken(LexKind t)
   at MS.Internal.Xml.XPath.XPathParser.ParsePredicate(AstNode qyInput)
   at MS.Internal.Xml.XPath.XPathParser.ParseStep(AstNode qyInput)
   at MS.Internal.Xml.XPath.XPathParser.ParseRelativeLocationPath(AstNode qyInput)
   at MS.Internal.Xml.XPath.XPathParser.ParseRelativeLocationPath(AstNode qyInput)
   at MS.Internal.Xml.XPath.XPathParser.ParseRelativeLocationPath(AstNode qyInput)
   at MS.Internal.Xml.XPath.XPathParser.ParseLocationPath(AstNode qyInput)
   at MS.Internal.Xml.XPath.XPathParser.ParsePathExpr(AstNode qyInput)
   at MS.Internal.Xml.XPath.XPathParser.ParseUnionExpr(AstNode qyInput)
   at MS.Internal.Xml.XPath.XPathParser.ParseUnaryExpr(AstNode qyInput)
   at MS.Internal.Xml.XPath.XPathParser.ParseMultiplicativeExpr(AstNode qyInput)
   at MS.Internal.Xml.XPath.XPathParser.ParseAdditiveExpr(AstNode qyInput)
   at MS.Internal.Xml.XPath.XPathParser.ParseRelationalExpr(AstNode qyInput)
   at MS.Internal.Xml.XPath.XPathParser.ParseEqualityExpr(AstNode qyInput)
   at MS.Internal.Xml.XPath.XPathParser.ParseAndExpr(AstNode qyInput)
   at MS.Internal.Xml.XPath.XPathParser.ParseOrExpr(AstNode qyInput)
   at MS.Internal.Xml.XPath.XPathParser.ParseXPathExpresion(String xpathExpresion)
   at MS.Internal.Xml.XPath.QueryBuilder.Build(String query, Boolean allowVar, Boolean allowKey)
   at MS.Internal.Xml.XPath.QueryBuilder.Build(String query, Boolean& needContext)
   at System.Xml.XPath.XPathExpression.Compile(String xpath, IXmlNamespaceResolver nsResolver)
   at System.Xml.XPath.XPathNavigator.Select(String xpath, IXmlNamespaceResolver resolver)
   at MatrixAdmin_MeetingMinutes.FormCode.FormEvents_Loading(Object sender, LoadingEventArgs e)
   at Microsoft.Office.InfoPath.Internal.FormEventsHost.OnLoad(DocReturnEvent pEvent)
   at Microsoft.Office.Interop.InfoPath.SemiTrust._XDocumentEventSink2_SinkHelper.OnLoad(DocReturnEvent pEvent)

When I run a Debug from VSTA I see:

'/dfs:myFields/dfs:dataFields/dfs:Agenda_Items[@Status = 'Pending Discussion'' has an invalid token.

I changed it to:

XPathNodeIterator rows = secDSNav.Select("/dfs:myFields/dfs:dataFields/dfs:Agenda_Items[@Status] = 'Pending Discussion'", NamespaceManager); but when i debug it i get this error:

Expression must evaluate to a node-set.

Code:

public void FormEvents_Loading(object sender, LoadingEventArgs e)

{

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

// Retrieve the rows of the secondary data source

//XPathNodeIterator rows = secDSNav.Select(

//"/dfs:myFields/dfs:dataFields/dfs:Agenda_Items",

//NamespaceManager);

XPathNodeIterator rows = secDSNav.Select("[@Status] = 'Pending Discussion' or [@Status] = 'Follow-Up", NamespaceManager);

// Loop through the rows of the secondary data source and fill the repeating table

//string meetingdate = GetValueOrEmptyString(rows.Current.SelectSingleNode("/dfs:myFields/dfs:dataFields/dfs:Agenda_Items/@Meeting_Date", NamespaceManager));

int counter = 1;while (rows.MoveNext())

{

string title = GetValueOrEmptyString(rows.Current.SelectSingleNode("@Title", NamespaceManager));

string owner = GetValueOrEmptyString(rows.Current.SelectSingleNode( "@Owner", NamespaceManager));

string notes = GetValueOrEmptyString(rows.Current.SelectSingleNode( "@Notes", NamespaceManager));

string status = GetValueOrEmptyString(rows.Current.SelectSingleNode( "@Status", NamespaceManager));

// Increment the counter

counter++;

// Add the item to the repeating table

AddItem(title, owner, notes, status);

}

// Remove the first empty item from the repeating table

DeleteFirstEmptyItem();

Re: Retrieving Column Data from Sharepoint List to a Repeating Table in Infopath

Hilary Stoupa — Fri, 06 Mar 2009 20:32:36 GMT

Hi:

Add a filter to the XPath in this line:

XPathNodeIterator rows = secDSNav.Select("/dfs:myFields/dfs:dataFields/dfs:Agenda_Items", NamespaceManager);

So it only selects the Agenda Items that match your criteria. Since I'm not sure of your data structure, you'll have to modify the XPath, but something like this:

XPathNodeIterator rows = secDSNav.Select("/dfs:myFields/dfs:dataFields/dfs:Agenda_Items[@Status = 'Pending Discussion' or @Status = 'Follow-Up'", NamespaceManager);

Re: Retrieving Column Data from Sharepoint List to a Repeating Table in Infopath

buck_murdock — Fri, 06 Mar 2009 18:16:31 GMT

Well that worked out great, but now there's one more snag. The code works flawlessly except for the fact that it pulls in every List item from the Sharepoint List. I would like to filter it and only pull in the List items where the "Status" field is set to "Pending Discussion" or "Follow-Up". I know it needs some sort of If-Else statement, but i can't seem to get the syntax right. I thought if i just changed my data source to use a particular view of the sharepoint list it would work, but it still pulls all list items.

Here's the code snippet as it exists now, if you could help me figure out how to tell this to only display or retrieve list items with those two statues I would be oh so greatful.

public void FormEvents_Loading(object sender, LoadingEventArgs e)

{

XPathNavigator secDSNav = DataSources["Agenda Items List (Pending View)"].CreateNavigator();

// Retrieve the rows of the secondary data source

XPathNodeIterator rows = secDSNav.Select("/dfs:myFields/dfs:dataFields/dfs:Agenda_Items",

NamespaceManager);

// Loop through the rows of the secondary data source and fill the repeating table

int counter = 1;while (rows.MoveNext())

{

string title = GetValueOrEmptyString(rows.Current.SelectSingleNode("@Title", NamespaceManager));

string owner = GetValueOrEmptyString(rows.Current.SelectSingleNode( "@Owner", NamespaceManager));

string notes = GetValueOrEmptyString(rows.Current.SelectSingleNode( "@Notes", NamespaceManager));

// Increment the counter

counter++;

// Add the item to the repeating table

AddItem(title, owner, notes);

}

// Remove the first empty item from the repeating table

DeleteFirstEmptyItem();

Re: Retrieving Column Data from Sharepoint List to a Repeating Table in Infopath

buck_murdock — Thu, 05 Mar 2009 22:25:16 GMT

Thank You, that worked perfectly. One of these days i'll get my brain wrapped around this whole programming thing.

Thanks Again!!!!

Re: Retrieving Column Data from Sharepoint List to a Repeating Table in Infopath

Jimmy — Wed, 04 Mar 2009 02:40:31 GMT

Just like in the post directly above yours, you have to check that the values are not blank before you try to access them. Please add the following method to your code:

/// <summary>
/// Returns the value of the specified node, or an empty string if the object is null
/// </summary>
/// <param name="node"></param>
/// <returns></returns>
public static string GetValueOrEmptyString(XPathNavigator node)
{
return node == null ? string.Empty : node.Value;
}

And then change these lines:

string title = rows.Current.SelectSingleNode( "@Title", NamespaceManager).Value;
string owner = rows.Current.SelectSingleNode( "@Owner", NamespaceManager).Value;
string notes = rows.Current.SelectSingleNode( "@Notes", NamespaceManager).Value;

to this:

string title = GetValueOrEmptyString(rows.Current.SelectSingleNode( "@Title", NamespaceManager));
string owner = GetValueOrEmptyStringrows.Current.SelectSingleNode( "@Owner", NamespaceManager));
string notes = GetValueOrEmptyString(rows.Current.SelectSingleNode( "@Notes", NamespaceManager));

Re: Retrieving Column Data from Sharepoint List to a Repeating Table in Infopath

buck_murdock — Wed, 04 Mar 2009 00:22:46 GMT

I'm trying to follow the example from http://www.bizsupportonline.net/infopath2007/copy-rows-from-sharepoint-list-to-main-data-source.htm as well. I'm using C# for my code.

I'm running in to the same issue in regards to blank fields. Some of the fields in my Sharepoint list are blank and it stops when it hits the first blank field. My sharepoint list has 4 columns (Title, Owner, Notes, Meeting Date). I want my IP form to pull those columns into a repeating table, if one of those fields is blank i want it to continue onto the next item without an error.

How can i initialize these fields as empty or have it ignoe empty fields? I ran into a similar issue before and someone helped me with it, but i applied the bit of code that helped me in that situation and it did not help this time.

The error i get is:

System.NullReferenceException
Object reference not set to an instance of an object.
   at MatrixAdmin_MeetingMinutes.FormCode.FormEvents_Loading(Object sender, LoadingEventArgs e)
   at Microsoft.Office.InfoPath.Internal.FormEventsHost.OnLoad(DocReturnEvent pEvent)
   at Microsoft.Office.Interop.InfoPath.SemiTrust._XDocumentEventSink2_SinkHelper.OnLoad(DocReturnEvent pEvent)

My code snippet is as follows:

using Microsoft.Office.InfoPath;

using System;

using System.Xml;

using System.Xml.XPath;

namespace MatrixAdmin_MeetingMinutes

{

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()

{

EventManager.FormEvents.Loading += new LoadingEventHandler(FormEvents_Loading);

}

public void FormEvents_Loading(object sender, LoadingEventArgs e)

{

XPathNavigator secDSNav = DataSources["Agenda Items List"].CreateNavigator();

// Retrieve the rows of the secondary data source

XPathNodeIterator rows = secDSNav.Select("/dfs:myFields/dfs:dataFields/dfs:Agenda_Items",

NamespaceManager);

// Loop through the rows of the secondary data source and fill the repeating table

int counter = 1;while (rows.MoveNext())

{

string title = rows.Current.SelectSingleNode(

"@Title", NamespaceManager).Value;

string owner = rows.Current.SelectSingleNode(

"@Owner", NamespaceManager).Value;

string notes = rows.Current.SelectSingleNode(

"@Notes", NamespaceManager).Value;

// Increment the counter

counter++;

// Add the item to the repeating table

AddItem(title, owner, notes);

}

// Remove the first empty item from the repeating table

DeleteFirstEmptyItem();

}

private void AddItem(string title, string owner, string notes)

{

XmlDocument doc = new XmlDocument();

XmlNode group = doc.CreateElement("group2",

NamespaceManager.LookupNamespace("my"));

XmlNode field = doc.CreateElement("Title",

NamespaceManager.LookupNamespace("my"));XmlNode node = group.AppendChild(field);

node.InnerText = title;

field = doc.CreateElement("Owner",

NamespaceManager.LookupNamespace("my"));

node = group.AppendChild(field);

node.InnerText = owner;

field = doc.CreateElement("Notes",NamespaceManager.LookupNamespace("my"));

node = group.AppendChild(field);

node.InnerText = notes;

doc.AppendChild(group);

MainDataSource.CreateNavigator().SelectSingleNode(

"/my:myFields/my:group1",

NamespaceManager).AppendChild(doc.DocumentElement.CreateNavigator());

}

private void DeleteFirstEmptyItem()

{

XPathNavigator domNav = MainDataSource.CreateNavigator();

XPathNavigator itemNav = domNav.SelectSingleNode(

"/my:myFields/my:group1/my:group2[1]",

NamespaceManager);

if (itemNav != null)

itemNav.DeleteSelf();

}

Re: Retrieving Column Data from Sharepoint List to a Repeating Table in Infopath

kelogs1347 — Fri, 28 Nov 2008 04:14:47 GMT

Jimmy:
kelogs1347:

swathip:

Hi,

Before getting the value of a node, verify that node is existing or not. Check rows.Current.SelectSingleNode( "@Person_Responsible", NamespaceManager) is null or not. If it is not null then go for rows.Current.SelectSingleNode( _
"@Person_Responsible", NamespaceManager).Value.

Do the same thing for other fields also.

How do you check if it's null or not? Sorry I'm really new to this. Thanks.

It would be something like this:

Dim tempNode As XPathNavigator = rows.Current.SelectSingleNode("@Person_Responsible", NamespaceManager)
Dim owner As String
If tempNode Is Nothing Then owner = "" Else owner = tempNode.Value

Of course, if @Person_Responsible is always supposed to have a value it would be best to figure out what's going wrong.

Thanks for all the help. It finally works. I used your way of checking. And it worked. Apparently it doesn't want the HasAttributes way of checking. Thanks for all the help. I totally appreciate it. :D

Re: Retrieving Column Data from Sharepoint List to a Repeating Table in Infopath

kelogs1347 — Fri, 28 Nov 2008 04:00:18 GMT

I was able to figure out how to check if it was null and guess what it is. I don't really know why...

Ok rant over..Sorry for that. :)

Re: Retrieving Column Data from Sharepoint List to a Repeating Table in Infopath

Jimmy — Fri, 28 Nov 2008 03:16:55 GMT

kelogs1347:

swathip:

Hi,

Before getting the value of a node, verify that node is existing or not. Check rows.Current.SelectSingleNode( "@Person_Responsible", NamespaceManager) is null or not. If it is not null then go for rows.Current.SelectSingleNode( _
"@Person_Responsible", NamespaceManager).Value.

Do the same thing for other fields also.

How do you check if it's null or not? Sorry I'm really new to this. Thanks.

It would be something like this:

Dim tempNode As XPathNavigator = rows.Current.SelectSingleNode("@Person_Responsible", NamespaceManager)
Dim owner As String
If tempNode Is Nothing Then owner = "" Else owner = tempNode.Value

Of course, if @Person_Responsible is always supposed to have a value it would be best to figure out what's going wrong.

Re: Retrieving Column Data from Sharepoint List to a Repeating Table in Infopath

kelogs1347 — Fri, 28 Nov 2008 02:39:45 GMT

Jimmy:
What you can do is put a breakpoint on that line, then do a quick watch on rows.Current, and look at its OuterXml. That should give you some idea of what it looks like and what may be missing.

You might have to iterate a few times to find the offending row.

I will try to do that. Thanks.

Re: Retrieving Column Data from Sharepoint List to a Repeating Table in Infopath

kelogs1347 — Fri, 28 Nov 2008 02:34:44 GMT

swathip:
Hi,

Before getting the value of a node, verify that node is existing or not. Check rows.Current.SelectSingleNode( "@Person_Responsible", NamespaceManager) is null or not. If it is not null then go for rows.Current.SelectSingleNode( _
"@Person_Responsible", NamespaceManager).Value.

Do the same thing for other fields also.

How do you check if it's null or not? Sorry I'm really new to this. Thanks.

Re: Retrieving Column Data from Sharepoint List to a Repeating Table in Infopath

Jimmy — Fri, 28 Nov 2008 02:26:20 GMT

What you can do is put a breakpoint on that line, then do a quick watch on rows.Current, and look at its OuterXml. That should give you some idea of what it looks like and what may be missing.

You might have to iterate a few times to find the offending row.

Re: Retrieving Column Data from Sharepoint List to a Repeating Table in Infopath

kelogs1347 — Fri, 28 Nov 2008 02:21:00 GMT

That's what baffles me because I made sure from my list that the Person Responsible column has values. Oh well I guess I'll just have to find another way. :)

Re: Retrieving Column Data from Sharepoint List to a Repeating Table in Infopath

Jimmy — Fri, 28 Nov 2008 02:15:53 GMT

Is it possible that a certain row might not have a Person_Responsible assigned? If so, you'll probably want to make sure that

rows.Current.SelectSingleNode("@Person_Responsible", NamespaceManager)

Is not null (or Nothing, my VB is a bit shaky), and assign an empty string to owner if it is.