InfoPath Dev

Hi,

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 about a dozen fixed nominal codes and a handful of miscellaneous 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, probably because it turns out all business units are using that code as one of the miscellaneous codes.

I have a SP list of the nominal codes against which data needs to be collected.  I want to use a repeating table that by default generates a row for each item in the list.  The first column in the row will display the code.  Then there will be seven columns for seven days amounts and a totals column.

I can make a repeating table in the form to display the items in the list.  But that just displays static data.  I can't find a way to use the list as just the row titles and add data in other columns.

Please can anybody help me?

Guy

Hi! Do you have an idea of how many rows you'll need in the form? That is, do you know the max number of rows you'll have in your SharePoint list?

Unless this is a list form, by the way, you can use code in browser forms. The object model has some limitations and some differences from client, but code is allowed.

At any rate, under the Data tab in your form in design mode, under defaults, you can insert multiple rows by default. Then you could use a formula to set one field to the values from your list.

Hello Hilary,  Thank you for your reply.

In theory there isn't a maximum number of rows but in practice it is normally around 15 to 25 rows.

As soon as I touch code the form refuses to publish in browser version.  I don't have to enter any code, just suggest to InfoPath I might be thinking about code and it blocks browser publishing.  I thought that was just the way it is designed.  Code would give us all kinds of solutions to all kinds of problems so I would love to know how I get around that?

How do I set a field to a value from a list?  I tried setting the default value but it just gives me the first value off the list on every row of the table.  I tried setting up 15 rows in the default screen and setting up the default on each but I still couldn't find a way to point the at different rows on the list so any guidance would be great.  The best I could do was manually set the defaults to the different nominal codes and get a lookup list to return the correct description.  But if the nominal change next week I will have to go back in and change the defaults which isn't really practical.  IS there anyway to look up a value that doesn't involve a drop down list?  I have got the drop down lists disabled with a formatting rule but that is clumsy.

Thanks.  Guy

So - do you get an error message when you add code to the form and try to publish to the library? I'm guessing the sandbox service may not be running on your form. Browser forms can have code and be published either directly to the library if the sandbox service is running, or can be admin approved if not.

Usually I use a count of preceding-sibling. See attached. Save the form locally, right click and select design, then preview.

 Hilary,

Thank you.

 Code just not an option.  Sandbox??  I am admin but I can't approve anything.

 Preceding-sibling.  Great!  Where do you get this stuff?  I can see it works but I can't for the life of me find anywhere that explains why?  Which makes modifying it to match our actual data structure a whole lot of trial and error.

Nope!  Can't work that out at all???  If I touch food in food[count(current()/../preceding-sibling::group2) + 1]/name the whole thing falls apart.  Is current() supposed to be a function?  It isn't on the function list.  What is this text made up of?  Because it isn't just text because I can't edit it character by character.

current() is an XPath function, yes. It refers to the current node. I selected the checkbox to edit XPath in the formula and manually added the filter of [count(current()/../preceding-sibling::my:group2) + 1]

So, select the field that has the value you want from your secondary data source, then click the checkbox to edit the XPath, and manually add the filter. If the field with the default value is in your repeating group, all you should have to change is "my:group2" to be the name for your repeating group.

 Which bit is the filter?  Where do I add it?

 

Ahhhhh!  Hilary, I love you.  I am a complete idiot but please marry me :-)

Hmmmmm.... the second bit makes me think you figured it out? The filter is the square brackets and the stuff inside... :)

Hi Hilary, sorry but the wedding is off.

Firstly, I am married :-)

Secondly, your solution works up to seven rows but if I add an eighth default row I get an error,

"An error occurred in the form's code. The number of calls to the Changed event for a single update in the data exceeded the maximum limit."

It works fine in preview.  I have added up to 20 default rows with no problems.  Even though the preview is pulling data on the same data connections from the same SharePoint list.

But once published and opened in browser it always works with seven or less default rows and always fails with eight or more default rows.

Any ideas?

Thank you so much :-)

You get the error on form open? Are there any other rules running off the field with the default value? I'll check my sample in our environment later today and see if the same issue reproduces for me.

Hi Hilary, I've actually got the data connection set to query only on update of another field which selects the rows available in the list. So the form opens OK. I only get the error when I run the query. Guy *** Right, I just tried amending the data connection to query automatically. Without a filter set the query goes in to an infinite loop. When I OK on the error message it successfully displays eight rows from the list in the repeating table. I took out the reference to the secondary data source in the repeating table and instead added another repeating table just to display the list. It now opens without error and displays all sixty odd rows of the list in the second repeating table. Of course it doesn't display the values from the list in the original table, just eight blank rows as per the defaults. So something about the query of the table and the repeating table together forces the count way up high and causes errors once I get to eight rows in the repeating table.

 InfoPath limits how many values can be updated without manual intervention to less than 16.

The default value Hilary provided is one value.  Each time it causes a row to be added it updates one value.  So it gets to 15 rows OK and then stops.  It is quite happy to let the user keep adding more rows one at a time, the calculation still works correctly each time.  But because there is a manual intervention to add a row the count of operations sets back to zero.

I was hitting the error before I got to 15 because I had another field on the row that calculated the total of the row.  So adding one row caused two values to update.  So the 16 limit was hit if I added eight rows.

So now I understand the problem,  And I can increase the limit to 15 by stopping the total calculating automatically, instead setting it to calculate only when a value on the row changes.

But 15 is still the maximum number of rows I can add automatically.

Unless anyone knows a way to change that maximum?

So - I added rows to my sample and then changed the data connection to include more items and was able to reproduce something similar. So yes, it appears that InfoPath forms services is pickier about this than the filler - sorry this isn't going to work for you in your browser form!

If you decide to use code (or you can use qRules) here is some info on admin approving a template:https://support.office.com/en-za/article/Introduction-to-browser-compatible-form-templates-7a6e40ff-eb1d-4311-945d-169857d340af?ui=en-US&rs=en-ZA&ad=ZA&fromAR=1

Here is some info on sandboxing: https://msdn.microsoft.com/en-us/library/office/ee526360.aspx

Thank you so much for all your help Hilary. I'll have to work through the code solution another time.

InfoPath freaks me out sometimes.  The filter [count(current()/../preceding-sibling::my:group2) + 1] keeps changing to [count(/../preceding-sibling::my:group2) + 1] when I do something completely unrelated.  Fields that don't appear on the repeating table or even on the same view seem to be able to influence the function.  Totally bizarre!  It took me hours to figure out what was happening.  I know now to just rebuild that function as the last thing I do before I publish the form each time but it is a right pain.