How do I add rows from list to repeating table?

Question

I am using InfoPath/SharePoint 2010 in browser, i.e. no code solutions only

I have a form to collect income data, daily amounts and weekly total. Data has to be collected against a fixed list of nominal codes.

The form all works OK but the nominal and number of rows available are all fixed. From time to time the fixed list will change. New codes added or redundant codes removed.

I have a SP list of the nominal codes against which data needs to be collected. I want to use a repeating table with a row for each item in the list. The first two columns in the row display the code and its description from the list. Then there will be seven columns for seven daily amounts and a weekly total column.

I can make a repeating table in the form to display the items in the list. But that just displays static data.

How do I use the list to generate the necessary rows in the table and allow user to add the other values?

Answer 1

I solved this exact problem recently, but you're not going to like the answer. You have to use the Code Editor in the Developer ribbon, which also means your form has to be published as an Administrator-approved form template. It's impossible to get the exact functionality you desire without doing so, although you might be able to fake it if you're willing to limit the maximum number of nominal codes and never update nominal codes on existing forms.

Here's how you'll do your initial population of the section via code. Create a repeating section in your form to store the nominal code, nominal description, each day's value, and the total. (You must be using a Form Library; can't do this with a list.) Use the button on the Developer ribbon to create a Loading Event. Update the below code with your external data source's name (NominalCodes), the field names of the code and description (d:Title and d:Description), the XPath of the section containing the repeating section (repeatingParentXPath), the name of your repeating section (/my:repeatingSection), and the repeatingSectionXMLString to perfectly match the XML format of the existing sections. If you get "Object reference not set to an instance of an object" or other equally useless errors that means somewhere your XML isn't perfect.

public void FormEvents_Loading(object sender, XmlEventArgs e)
{
    XPathNavigator mainNavigator = this.MainDataSource.CreateNavigator();
    string nominalXPath = "/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW";
    string repeatingParentXPath = "/my:myFields/my:repeatingParent";

    try
    {
        XPathNavigator nominalNavigator = this.DataSources["NominalCodes"].CreateNavigator();
        XPathNavigator repeatingParentNav = mainNavigator.SelectSingleNode(repeatingParentXPath, this.NamespaceManager);
        XPathNodeIterator repeatingSectionIter = mainNavigator.Select(repeatingParentXPath + "/my:repeatingSection", this.NamespaceManager);      

        XPathNodeIterator nominalIterator = nominalNavigator.Select(nominalXPath, this.NamespaceManager);
        if (nominalIterator.Count > 0)
        {
            foreach (XPathNavigator eachNominalNav in nominalIterator)
            {
            XPathNavigator nominalTitleNav = eachNominalNav.SelectSingleNode("d:Title", this.NamespaceManager);
            XPathNavigator nominalDescriptionNav = eachNominalNav.SelectSingleNode("d:Description", this.NamespaceManager);

            string repeatingSectionXMLString = "<my:repeatingSection><my:NominalTitle>" + SecurityElement.Escape(nominalTitleNav.Value) + "</my:NominalTitle><my:NominalDescription>" + SecurityElement.Escape(nominalDescriptionNav.Value) + "</my:NominalDescription><my:MondayValue></my:MondayValue> <my:TuesdayValue></my:TuesdayValue> <my:and so on.../> </my:repeatingSection>";

            repeatingParentNav.AppendChild(repeatingSectionXMLString);
            }
        }
    }
    catch (Exception ex)
    {
        System.Diagnostics.Debug.WriteLine(ex.Message);
    }
}

If you happen to need to delete all sections, here's the code (took me a while to figure this one out):

// Empty the repeating table
int repeatingSectionCount = repeatingSectionIter.Count;
if (repeatingSectionCount > 0)
{
    XPathNavigator firstRepeatingNodeNav = mainNavigator.SelectSingleNode(repeatingParentXPath + "/my:repeatingSection[1]", this.NamespaceManager);
    XPathNavigator lastRepeatingNodeNav = mainNavigator.SelectSingleNode(repeatingParentXPath + "/my:repeatingSection[" + repeatingSectionCount.ToString() + "]", this.NamespaceManager);
    firstRepeatingNodeNav.DeleteRange(lastRepeatingNodeNav);
}

If you want to update existing items, you have some work ahead of you to find and change and add sections correctly. Let me know if you get this far and I can help you work through how to do that.

But if you want the no-code solution, be prepared to do some ugly, shameful things. Don't automatically retrieve your external data connection to NominalCodes when the form is loaded. Make a boolean for NeverLoaded defaulted to false. On form load, have a rule that checks if it's false and if it is load the external data and set the boolean to true. Make a two column, one row table. On the left side, put a repeating table with the code and description from your external data source. On the right, create n sections, where n is the maximum number of nominal codes you wish to support. Do not use a repeating table or section. In each section, have fields for days and your total and whatever else you want. Set the first section to be invisible when count(xdXDocument:GetDOM("NominalCodes")/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW) < 1, and the second section to be invisible when that's less than 2, etc. Play with formatting so they line up and appear to be from a single table.

Answer 2

Received brilliant answer from Hilary Stoupa on another forum. I could just set one column of the row to be a value from secondary source. Problem was I got same value on every row. Hilary gave me filter statement [count(current()/../preceding-sibling::group2) + 1] It works :-) I now get a different value on every row. Set up multiple default rows and add a formatting rule to hide any rows where the value is empty and we are almost there. Only problem I apparently get an infinite loop if I add more than 7 default rows??