InfoPath Dev

 Dear All,

I am an absolute beginner with Infopath, and I'm seeking advice on sound strategies and the best practices for working with databases. 

My database has triggers to manage audit fields (e.g. created by, created on), and PK/FK referential integrity in place.  I'm seeking to utilise Infopath to the max, but I don't know when I should code and when there's a way of better doing things in Infopath without programming.  I'm comfortable using system.xml.xpath, but I would like to minimize programming for ease of maintenance.  BTW, I use VB as it's easier for others to maintain.  

I guess I don't really understand the Infopath engine deeply enough.  I especially don't understand its internals and architecture i.e. what bits of the XML schema(s) get automatically populated by Infopath, and when. 

Let me throw in an additional wrinkle.  I'm working on a real development problem, right now, and Version 1 of the solution needs to be a bare-bones Infopath/SQL Server solution, with a full-fat client.  The database is built, and all triggers have been programmed and tested (so things like internal audit fields, date/time constructs all work, so on and so forth.  There are a lot of INSTEAD OF triggers.)  I will then build version 2, with Enterprise MOSS 2007 with Forms Services, and I'll be writing full-featured ASP.NET Web Services for data access.  

One thing I would appreciate your comments on, is what kinds of functionailty are best served at what layer in this stack.

Anyway, here's the scenario.  As I say, it's a real one, but I've tried to boil it down to simple elements without making it simplistic.  Specific questions are interspersed throughout the text:

Employee

• UserName (primary key)
• DisplayName

Journal

• UserName (primary key)
  
• CreatedOn (primary key, part of internal audit fields, auto generated by SQL Server On Insert/Update Trigger)
• JournalComment  (entered by user)
  

Shift

• UserName (primary key, taken from selected Employee))
• ShiftStart (primary key, entered by user)
• ShiftEnd (entered by user)
  

ShiftActivity

• UserName (primary key, taken from Shift)
• ShiftStart (primary key, taken from Shift)
• ShiftActivityStartTime (primary key, entered by user)
  
• ShiftActivityCode (entered by user, needs to be validated against another SQL Server table, ActivityCodes)
• ShiftActivityHours (entered by user)
  

So, those are the bones.  We have a master table, Employee.  There's a maintenance screen.  That's quite straightforward, although here's Question 1.

Q1.  What's the best way of updating an Employee versus providing the functionality to add a new record?  Should I use one form with two views?  Two forms?

We have two sub-structures. 

• Substructure 1 is the Journal, which is simply the ability of supervisors to record comments against an Employee.  It's not related to Shifts at all. 
  
• Substructure 2 is a master/detail structure of Shifts and the various ShiftActivitys recorded on the Employee timesheet.   

There are thus two associated maintenance activities:  entering/updating timesheets, and entering comments/viewing all comments made to date against an Employee.

I could treat Employee as a secondary data source.  I only want to retrieve one specific person's record at one time.  If I did that, then I would need to do things like copy certain fields (like UserName) from the secondary to the main data source.  But of course, the main data source (even for entry of a single Journal record) is a repeating group. 

Q2.  Do I need to use things like NodeIterator and AppendChild for single record entry?  If so, how would the code look?  I can get the data from my secondary source using something like nav.SelectSingleNode("/dfs:myFields/dfs:dataFields/d:Employee/@UserName", NamespaceManager).Value, but I don't think I could use .SetValue with SelectSingleNode.  I think I would need to use Select to be able to set single values for a single record in the repeating group of DataFields.  I'd sure appreciate some insights here, becuase my understanding is real hazy.

 Q3.  The Journal CreatedOn date is an internal database field, populated by the trigger.  But as it is a primary key, it cannot be removed from the data source.  Should I put a dummy date in there simply to get past the Infopath requirement to have it populated with something before form submission?  This depends on Q2.

Q3.  Should I use a second view of the Journal data entry function to display all Journal comments entered to date?  Obviously, given 'design once', I cannot use a master/detail control anywhere in this solution.  Repeating section with controls?  In a vew?  A second form?  What are your recommendations on best practice?

Q4.  In general terms, how does Infopath manage automatic propagation of data such as primary keys from master to detail records?  This is crucial for entry of new records.  It's also critical when considering browser-enabled forms, where the only way to restrict detail information is through conditional formatting (and where you really don't want to retrieve more data than is actually going to be displayed on the browser form).  I sure as heck don't want to retrieve everyone's Journal comments just to display the ones relevant to the selected Employee.  This one is key to understanding the whole shooting match, I think.

The timesheets are completed one per employee, one per shift.  They have 1 to n activities recorded on them.

Q5   Do you recommend using a single main data source with all three tables (Employee, Shift, and ShiftActivity), or do you recommend using Employee as a secondary data source purely for data retrieval, and a Main source with Shift and ShiftActivity as the records to be created (and potentially updated)?

Q6.  Do I need code, or can all this be done with rules etc.?  One point I would mention is that I know that I will need code, at some point, as I want to programatically add rows as long as the total hours worked (i.e. entered) is less than ShiftEndTime minus ShiftStartTime.  However, for the purposes of this question, let's say I was not being fancy.  Could I get away without programming?

Q7.  The update function is more than a simple update.  Whilst I would be updating a specfic timesheet, the changes could involve the addition or deletion of ShiftActivity rows in the database.  How best should I approach this?   Use code to manually generate a diffgram?  Create a diffgram in the web service?  Delete the lot and re-insert the rows as new?

Q8.  How should I best approach the validation of ActivityCode?  There are only about 50 or 60 codes, which suggest to me that they should all be loaded into the form as 'automatically retrieve data when form is loaded' in the data connection wizard.  I'm guessing that there's now some XML document structure in-menory? Is that true, and if so, how best should I utilise it.

I am seeking to achieve three things with this post:

• Regarding my immediate needs, I don't want to go down ratholes trying to program my way out of something, when with a better understanding of Infopath I could be adopting a swifter, better approach;
  
• More generally, I'm trying to get a much deeper insight into what works well with Infopath (and equally, what doesn't, so I would appreciate any Dont Go Here warnings), and finally;
• A solid understanding of the development strategies that you, the experts, recommend.

Thanks very much for taking the time to read this very lengthy post. 

I look forward to your responses, and I'd be more than happy if any given response addressed just a single point or question.

Finally, I am sure that this thread will prove extremely useful to all who come after, looking to improve their own skills and knowledgebase.  I tend to reach for an assembler coding pad and a pencil when faced with something like this, and I'm quite sure that's not the best way forward. :-)  

Cheers,

Donna Kelly

Did I post this in the wrong forum?

 Donna

Hi Donna and welcome!

Thanks for the detailed post. Sorry we missed it last weekend. I'll reply more in detail later.

Best practices:

• Use a Web Service between InfoPath and SQL - allows for SQL to be on a different server when you have Forms Services; reduces cost of maintenance when schemas change
• Use Loose Coupling in your Web Service and save the XML - our DBXL Web Service (free trial here) maps the XML to SQL via a data layer, so you can use one Web Service for all your forms and when the form's data source changes, you can update the mapping later and re-map all of your data at your leisure (since you have the XML stored as well) the submissions process is not interrupted. Same for a DB change.
• Do not bind your form to a Web Service. Create your form from scratch with an independent main data source (based on XML Schema Definition (XSD)). This provides maximum flexibility.
• Don't put code in InfoPath form - complicates Forms Services deployment (requires admin deploy) and increases maintenance costs when code changes (you have to recompile all of your forms). Put the code in your Web service or your database
• Don't have mulitple aplications writing to same DB. To prevent data integrity issues (synchronization and locking between the writers), you should limit your writer to your Web Service or the SPROCS/Triggers in SQL behind it.

Q1: use one form and a data view in MOSS to find the employees to edit (conditionally set to look for rowsets already saved via InfoPath, in which case you open the stored XML vs. performing a SQL query on open). For existing employee data that has not been saved in XML, you'll need to populate a new form on open by passing some parameters to the InfoPath form. After the data is saved once (and exists both as XML and SQL), the form should populate from the XML store in the DB (this requires the single writer best practice above).

Q2: don't put code in your form. Use our free qRules accelerator that lets you copy data from 2DS (secondary data source) without writing code. Use a Web Service to filter queries to SQL so that you aren't pulling a boat load of data down on open. Filter based on fields in your form. Search the forum and our blogs for information on our Query Builder tool that comes with our Web Service for how we do parameterized filtering (which is yet another reason to use a Web service since InfoPath SQL support doesn't let you filter).

Q3a: Your IP form should be based on XML not a DB, so you won't be required to fill this in. For existing data, when you save back via InfoPath for the first time, you'll want to add a DocID column that is filled in so that next time you open, you can detect it's previously saved. For new employees, if you are using a Web Service like ours, you can ignore the mapping to the PK column since SQL will fill this in.

Q3b: I would use an optional section with a repeating table either at the bottom of your form or in a separate view.

Q4: InfoPath forms that are created from SQL use ADO.NET data sets and diff grams to manage propagation to child tables. There is a philosophical debate between using this approach and a loosely coupled approach. Our Web service takes the latter approach. Read more here: http://www.infopathdev.com/blogs/patrick/archive/2008/07/09/using-dbxl-with-existing-databases.aspx. The implication to you is that ideally you should create a separate database that you use for your InfoPath forms and replicate to your line of business applications. Since you already have a DB, you can either a) hybridize it to work with a loosely coupled Web service, or b) just batch populate the XML you will need to use the new model.

Q5: Employee should be secondary. The timesheet entries will map to separate tables for work block and refer back to the employee data via an FK. We can show you how our Timesheet works in a live meeting demo if you are interested.

Q6: In general, if you use our "gap-fillers" (DBXL and qRules) you won't need any code. Otherwise, you will need to write a bunch yourself (Web service, form logic, etc.). qRules supports programmatically adding rows and the total hours calc can be done in a rule/function.

Q7: I've already touched on this update issue in my responses above. Our DBXL web service handles complex arbitrarily deep DB updates today. It does this by updating primary table in place but by deleting and reinserting for child tables. If you have an existing DB that is constrained and you need to preserve the current ids for those child tables, this won't work (see my blog post above) without hybrizing it, or creating a new DB that is not constrained. In general, I advocate going with a loose coupling because InfoPath will validate your XML structure and data types and you don't want the Web service to fail a submit due to some SQL data type limitation. Rather, you want to fix those errors up asynchronously by changing SQL column size or mapping and resubmitting. In general, errors won't be seen that often and you have integrity since you are storing it in XML so you can always just re-map after the fact. This is a different way of thinking for most people who have been taught to constrain their DB and Web service interfaces.

Q8: Just create a data connection using an XML file that you create, or you can use a SharePoint list that you query on open and use IP's caching to store contents in the form when offline. This is pretty simple to do.

You are smart to think through this stuff at the beginning. There is definitely a big rat hole with regards to InfoPath and SQL.

Thanks for thinking outside the box and posting on a topic that we should have posted on previously. I'd be happy to do a Webinar to a bunch of people explaining what we have learned developing InfoPath solutions over the last 10 years. We have the biggest InfoPath and SQL installations out there and they are all using Web services. We have had to migrate many customers from the default InfoPath-SQL connection and it has been painful because of the structure of the data source that is created when you start from SQL or a Web Service. You should start from XML and submit it to a Web Service as a string. The loose coupling ideology is the most important best practice to take away at the beginning. Because of what InfoPath can do (XML structural and data type validation), you don't need to lock the interface and DB down like in the past. You get data integrity just by the virtue of using InfoPath and storing as XML. You should make sure your data source is well-defined, but with loose coupling you can modify it later if needed and either a) create a separate instance of the XSN that maps to the same DB table, or b) upgrade your current version in place. Depends on whether you want people to see the old form when they open old data or auto-upgrade them to the new form.

More response later,

Patrick,

thank's ever so for this.  I'll post a more detailed response later.

Cheers,

Donna