InfoPath Dev

I've been wracking my brain trying to do this without code, but have failed.  So I'm here, asking for help.  (If it can only be done through code, VB would be preferable. But beggars can't be choosers.)

I am attempting to create a repeating table with a specific number of rows.  In the first column of the repeating table must be certain text.  The information to generate the rows is contained elsewhere on the form.

Specifically, the form contains two date fields: StartDate and EndDate.  StartDate can be no earlier than today, and no later than 9/30/2099.  EndDate can be no earlier than StartDate and no later than 9/30/2099.

Those dates must be converted to a fiscal year format.  The organization uses an October 1 - September 30 fiscal year, so I can create a fiscal year through a whole crapload of nested functions, but I'm not sure if this gets me anywhere. Any date between 10/1/20xx and 9/30/20yy (with yy = xx+1) is in FY20xx/yy.  (You can calculate the yy by adding 92 days to the day in the field, then grabbing just the last two digits of the year.  Then xx is just one less than yy.)

I would like the first data row of the table to have in the first column the fiscal year of the StartDate.  Then, each row following would have the fiscal year incremented by one until the last row has the fiscal year of the EndDate.  If StartDate and EndDate are in the same fiscal year, then there would only be one row.

I have wracked my brain trying to figure out how to do this in InfoPath, and have failed.  Any help would be appreciated.

On a (simpler) related note, is there an easy way to reset a repeating table, dropping all existing data?  This code would be called prior to running the above code IF the StartDate or EndDate are changed, prior to regenerating the table.

Not that it matters, but the other columns are for entering requested budget amounts, by year.

How many rows does the table in the form need to be? Simply as many years as are between start and end date?

You could just use default data for this and some conditional formatting. Makes for slightly messy and bigger XML files, but the most rows you'd have is 86 or 87, so that isn't too terrible.

Under the Data tab in the ribbon, look for default values. Set your repeating group to contain 87 rows by default. This will take a lot of clicking.

You can set the date value for each row in the default data.

Then hide the rows if the Start or End are blank. And hide the rows if they are out of the range for the Start and End. Not elegant, but no code needed.

And if Start or End date change, just set the columns that aren't your prefilled date to blank so that the form doesn't have junk hidden data floating around where the user can't see it.

I like it, except it's hardly elegent.  (I was brought up in the bad days, when code had to be elegent.  Developed bad habits.)

Is there a way to zero out the data columns in the hidden rows?  I'm considering the possibiity that data is entered in a row, and then one of the days changes, hiding the row.

Hey, I've got no problem with elegant code, but you'll have to write it and maintain it. If you are a programmer, look into XPathNodeIterator and XPathNavigator classes on MSDN to get started.

If one of the days changes, you can set the fields in the repeating group to blank (assuming the start and end date are outside of the repeating group) but it will set ALL of them. Not just the ones affected.

Here's a blog post on filtering the target of a rule by manually modifying the manifest, if you choose to go a non-code route. You might also want to look at qRules, which would give you flexibility of code w/out you having to write your own, and could handle this logic easily, so you wouldn't have to take the default data path.

I should haven noticed when I first read your answer, but it looks like an InfoPath 2010 answer, not a InfoPath 2007 answer.  There is no ribbon for me to use.

When I go to Default Values, I don't have any options that give me a certain number of rows.  I'm going to go into more detail as to what I want.

I want a table with X number of rows (say 50) of data.  The table should also have a header and a footer row.  I would like the table to have 8 visible columns.  The first column will be the Fiscal Year, which will start with FY 2012/13 in the first data row, and then increment by one (FY 2013/14, FY 2014/15, etc.) until I reach the end of the table.  The second through seventh column will be input columns for currency formatted integers.  The eighth column will be a calculated currency column which is the sum of the six data columns.  The footer will say Total in the first column.  The rest of the columns will be the total of all the rows above it.

I suspect that I'll also need two hidden columns, with the beginning and ending dates for each Fiscal Year.  I want to hide the rows that are outside the Project Start and End dates (determined earlier), which means that I'll be setting up a conditional for each row based upon the beginning and ending dates.

The worst possible way that I can think of to do this is to use just a normal format table, and have each row (other than the header and footer) defined as an optional section.  This would require that I define (for a 50 year grid) 350 separate data fields, plus an additional seven total fields.  (Given the limitations of functions in InfoPath 2007, each of the footer total fields will need to have something like Construction01+Construction02+Construction03+...+Contruction50 entered into their formula box.  This is doable, but seems like a lot of extra work.

I'd like to use a repeating table, because I only need to define seven columns once (as opposed to seperately for each row), and InfoPath functions should allow for me to total the entire column simply.  Theoretically, if I had hidden columns with the start and end dates for each fiscal year, I could also set up a simple conditional formatting to hide/display the applicable rows.

To make it even easier (if it's doable), I've also created separately a SharePoint list with every fiscal year I could possibly want, and the Start and End Date of each year.  If I could, non-programmatically and through a data connection, read the list and create the table, life would be great.  But I can't figure out how to do that, either.  When I attempt to read the list, I only get the first entry rather than a separate row for each item on the list.

I'm stuck, and don't want to hand define 357 fields, and type in 50 simple formulas and 7 real nasty ones.  (Not to mention setting up 50 separate sections with conditional formatting.)