InfoPath Dev

I am using InfoPath Designer 2010 to work on an InfoPath 2007 compatible form that currently works off an existing SharePoint form library.  The form is a request for equipment.  Each request is identified by an incrementing number constructed from the Fiscal Year column/field and the next request number column/field.  When the fiscal year starts on Oct. 1 the request number increment needs to restart at 1.  From with the form I successfully use the max() function to find the highest fiscal year (SPSurFYNum), and then set a separate control/field (SPNewFY)equal to that number.  Then I try to use the max() function to find the highest request number for that fiscal year using a filter, as follows:

max(@ReqNum[@SPCurFYNum = SPNewFY])

This apparently fails because it returns nothing.  Any suggestions?

I just tried this type of formula in a test form and it worked as anticipated. Offhand, I'd guess that @SPCurFYNum is not equal to SPNewFY - possibly because the data returned by the secondary data connection has decimal points or some other different. Can you drag your secondary data source data fields onto the form and take a look at the values being returned?

 Hillary,

 Hello.  I followed your suggestion and dragged the FY and Request number columns data in from the secondary data connection SharePoint 2007 site library into the form template.  I ran a preview and the data in these columns comes in with ".000000000" after the actual number data, so the Fiscal Year comes through as 2015.000000000, and the request number comes in as 32.000000000. 

I'm not sure how to proceed at this point.  Unless I was using the max() function incorrectly at the start, I can't run it against a text data field but only against a numeric data field.  If this is correct then I have to figure out a way to deal with the appended decimal and zeros.

Well - you don't have to do anything for the Request Number, just for getting that Fiscal Year filter to match. So you could use substring-before on the "." in the FY from SharePoint.

 Would that be "substring-before(max(@fieldname, ".")" -OR- max(substring-before(@fieldname, ".")

max(@ReqNum[substring-before(@SPCurFYNum, '.') = SPNewFY])

If you set up the filter using the UI - select @SPCurFYNum and "is equal to" and SPNewFY, then change the first drop down to "The expression" you can manually add the substring-before bit.

 Hilary, let me say thank you very much!!  Your filter syntax worked perfectly.  It frustrated me for a few hours until I realized that I'd been typing "." instead of '.' when setting the substring-before() break point.  Duh!?!  Once I used '.' it worked like a charm.

I was able to build the "max(@field1[substring-before(@field2, '.') = field3])" argument syntax in the normal function formula window.  After verifying the formula I selected the entire argument inside the max() function parentheses, hit the "Insert Field" button which opened the normal field selection window and then hit the "Filter" button, I saw that InfoPath had built the entire expression for me.  Cool!

 This was very much a learning experience for me and I appreciate your taking the time to help! 

I'm glad you got it working! One thing that really helped me a lot with InfoPath was putting secondary data on the form for debugging purposes. Then I could see what the data really was, and it helped me figure out what was going wrong. I think I got so attached to my forms I didn't want to make a mess by putting stuff I didn't need on the form - but that was silly, when you can either use a different view for these things, or just easily delete the controls once you know what's going on. :)

 If I correctly understand the secondary data you mention, I also employ process check/confirmation fields when I design a template, but I don't delete them or set up a different view.  My practice is to insert a section/group at the bottom of the form called "Hidden Fields" and I control the appearance of it with a simple Boolean yes/no field.  After all design activity is complete I hide the entire section, but it is always available for troubleshooting.

The various fields I put in for setting date, calculating the FY breakpoint, the current fiscal year, the request number and for creating the file name for submission to the SharePoint library are all placed in the Hidden Fields section.  I also use this section to add comments about the fields, the rules and the processes followed so that it is self-documenting for the next person to work on it when I'm gone.

 I hope you have a good day and great weekend.