Accidental DBA

It's been nearly three months since my last post and that was not intentional. I got away from InfoPath a bit and my projects in June included a SQL migration to SQL 2008 and a SharePoint Migration to SharePoint 2010. Luckily we had a couple great consultants who did the heavy lifting to speed it up, but there were a lot of tweaks along the way and other projects tha have taken away from my time to post. On the plus side, I have all new posts in my head on new subjects.

My falling into a DBA position was not intentional. The conversation went like this:

CIO: "You are the only person here with SQL development experience. I know your DBA experience was in PROGRESS for 10 years and not SQL, but with the migrations, I need you to become the SQL DBA."

I've done a lot with SQL since 2005 - stored procedures, views, creating tables, etc., however, it was all as a developer, but the leap to being the DBA made sense as I was a DBA for Progress for over 10 years.

The first real DBA task I had to solve seems so basic, but it really took some research as I was not sure if it was a DBA task or a Network Engineering task. SQL has a design that goes out and sucks up all the memory on the server.

With a UNIX box, this was an enviromental setting and a DB setting where I would allocate a certain amount of memory for the PROGRESS DB, but I learned in SQL, this is simply a property setting.

And while it seems simple, I really questioned the wisdom of Microsoft with the out of the box setting.

In the left hand panel, select "Memory."

The parameter you want to change is "The Maximum Amount of Memory (in MB.)

The SQL default was two billion.  You are reading that correctly.  Two Billion.  Seriously.

I set ours to 8 GB (8192 MB) as that was what was recommended for a Sharepoint installation of our size.

TWO BILLION.

InfoPath – Saving the Form as a PDF

It is not uncommon that you may need to save the results of a form as a PDF file.  In a published form, you can select from the menu:

File/ Export To/ PDF or XPS

Seems simple enough, but here’s a huge caveat:

If you have controls such as check boxes or controls for ink entry (we use tablets to capture electronic signatures,) it can drop data input near the page breaks!!!

This was a very frustrating and aggravating problem, made more so by Microsoft when they said they would not be fixing the problem until InfoPath 2010. 

To try this, set up a page of controls and put either check boxes or an ink entry control at the bottom near the page break.  Publish the form and then fill in the values.  Now print the page.  The controls that are close to the page break will have no values when they print out.

If you want to do this programmatically and place a button on the form, the following C# code will actually do the same action as the menu command:

string fileNamePDF = @"C:\test.pdf";

this.CurrentView.Export(fileNamePDF, ExportFormat.Pdf);

But this means you will have the same issue with controls too close to the page break.

A second caveat when exporting is that printer drivers may affect the printing. 

We ran into an issue where a particular set of print drivers on our clinician tablets, when set as the default printer destination, would actually change the layout of the form.  

When exporting to PDF, it enlarged the font on the printed form from what it was in the published form.  Our form jumped from 2 pages to 3 pages as a result.  

The cascading effect as it turned out was that it pushed controls down next to the page break, dropping the values.  These are legal documents signed by patients, so we cannot allow for these values to be dropped.

What a mess.

What we have done is use a third party tool called PDFCreator.  It attaches as a printer and when the users select the print functionality, they select the PDFCreator as the destination printer.  What’s nice is it does not drop the values in the controls that are near the page break when the published form is printed.  Plus we can set properties in the PDFCreator environment so our PDF can automatically go to the printer.

I have no vested interest in PDFCreator as an owner.  I am simply a very happy end user.

***

Another caveat when printing is that that InfoPath uses the Internet Explorer print engine.  Some of your text may be altered depending on your IE Zoom setting and Font size. 

We have large screens at our scanning stations and someone had changed the font size on IE.  When the forms printed out, the font size was smaller than the other scanning station for only one field in our form.  It turned out it was the settings in IE.

1. Always have your IE Zoom setting at 100% of screen size. 

2. Always have the IE font size setting set to medium

Mutually Exclusive CheckBoxes in InfoPath

This is a demonstration of how to create mutually exclusive check boxes using InfoPath 2007.

Below is a screen shot from an InfoPath design form.  It contains 3 fields that I want to be mutually exclusive: Yes, No, N/A.  I have named these fields fldYes, fldNo and fldNA.

Double click the “Yes” check box and the "Check Box Properties" Dialog Box appears.

Select the rules button and the “Rules” Dialog Box below appears.

Click the “Add” and the “Rule” Dialog Box appears.

Change the name to something user friendly for your purposes.  This action is called “When True.”

Select the “Set Condition” button. 

The “Condition” Dialog Box appears.

Select the value TRUE and click OK.

That pop up disappears and you are back to the Rule Dialog Box.

Select the “Add Action” button.

The Action Dialog Box pops up. 

Select “Set a field’s Value” from the drop down box.

Upon selection, it changes the interface to the Action Dialog.

In my example, I have named my fields fldYes, fldNo and fldNA.

Use the selection box to the right of the Field text box to select “fldNo” as the field to set.

Type in “0” without the quotes into the Value text box.  I have found 0 to be much more user-friendly than using FALSE when setting these values.  YMMV.

Click OK.

We have set up the rule that whenever the “Yes” box is checked, the “No” box is automatically unchecked (set to FALSE.)  We need to do the same steps no for the “N/A” check box.  Click “Add Action” and add it for the other check box now, this time selecting the fldNA as the field you want to set the value for.

When you are done, the “Rule” Dialog Box will look like this.

Click “OK” to close the “Rule” Dialog Box.

Now Click “OK” to close the “Rules” Dialog Box.

Now Click “OK” to close the “Check Box Properties” Dialog Box.

Now that we did this for fldYes, we need to do this for fldNo and fldNA, setting the other check boxes to "0" whenever the check box is checked.

It’s a lot of clicking to create these check box behaviors, but it’s a Vanilla solution without relying on writing C# on the back of the form.

InfoPath Best Practice - Form Versioning

Because ours is an environment that needed to operate where network connectivity in the field may not be available as our clinicians care for a patient, one of the best practices I found was to actually place a text line in the beginning and end of any form to identify the form.

Every form I publish has something similar to this:

Form Revision Date: 07/07/2011                         Form Revision: 1.5a

I know, it seems so simple!  How do we not realize these things until after a lesson learned? 

If I ever get a call from a clinician who is trying to submit data to our database and they are having problems, I ask them what version they are running. 

This has saved me a few hours of frustration, I am sure.  It's the first question I ask when a clinician calls as the interfaces seldom change at this point, but items behind the scenes may change.

It is possible if you want to go away from Vanilla here and use a record in a DB to check the version before they submit.

My practice is I have a parameter table for the database.

I have a record for each form in the database.  My records would look similar to something like this (Rec#, Form Name, Version) except I use a much friendlier name for the form in my database.

1             Form1                 2.1

2             Form2                 1.1

3             Form3                 1.2

Within the FormCode class of the Form (select Tools/Programming/Microsoft Visual Stduio Tools For Application) create a constant for the form.

const string vFormVersion = "2.0";

As you prepare to publish forms down the road, you need to update this as you go along for each release and have it match the record for your specific form in the database.

Within the InternalStartup() method, you can execute a method you write yourself to query your DB for this value and compare it to the constant within your published form.  If they do not match, you can let the user know via a MessageBox that they need to go get the latest form.

Sending this message when they start up the form is only useful though if they have connectivity. 

If they do not have connectivity in the field, it will allow them to still enter and save the data as an XML document until they try to submit and I stop them again with the error message.

The key that they have the latest form is communication.  It’s not that difficult to put an email out to the group that uses these forms.

And, yes, I know a website or a web page would be the ideal solution regarding not having to tell people to get the latest form, but you have to remember the lack of connectivity is a limitation. 

They need to access the interface offline for submission later as there are large areas in Southern Indiana and Central Kentucky that lack connectivity, thus why InfoPath has worked well for this process.

Working with InfoPath

The first question that someone might ask is, “Why use InfoPath when the .NET platform allows us to create real projects with web interfaces?”

It’s a valid question, so I have to give a little history here. 

I work for a hospice that covers 33 counties in Indiana and Kentucky.  Our mission is to provide end of life care for terminally ill patients.

We have clinicians (nurses, doctors, chaplains and social workers) that cover areas that do not often include MiFi service in Indiana and Kentucky that allow them to connect to our network, so I had to develop something that was easily distributed, allowed them to save their input easily and later submit their information once network connectivity had been established.

InfoPath also offered easy integration with SharePoint and when I arrived here, we already had a functioning interface for one of our key projects between InfoPath and SharePoint.  It is also important to consider that the CIO likes a very Vanilla solution to solve our problems.  The more Vanilla a solution is, the easier it is to upgrade a product down the road. 

Developing a solution in the .NET platform would always be available if the benefit was great enough to move away from the Vanilla solution to something a little more like Butter Pecan, but InfoPath, allowing the ease to build the forms and the connectivity to SQL, was his flavor of Vanilla.

I had a couple of small obstacles I encountered along the way and in some cases, I searched far and wide for answers and that search took a couple days. 

This blog is simply to document some of the lessons learned and some of the best practices I learned along the way for InfoPath and other technologies.