Category Archives: InfoPath

Stored Procedures to generate InfoPath rules

In an InfoPath project you need to make a decision about where you’re going to store your data, the most common options being either in the InfoPath form itself as XML fields or externally using SmartBox SmartObjects (see my earlier post for the pros and cons of these options).

Assuming you’ve gone the route of using SmartObjects you’ll soon notice that when you have InfoPath controls bound to the Load SmartObject methods and you want to save them, you need to set up a rule for each SmartObject property to copy that field from the Load datasource to the Save datasource. This can mean a lot of clicking!

If you’re happy with splitting your InfoPath up into source files and editing rules, you can save a lot of clicking. Add this stored procedure to your SmartBox database and run it against your SmartBox SmartObject. The result of the query will be a bunch of InfoPath rules which you can copy and paste into your InfoPath source files, repackage the form and then all your rules are done.

CREATE procedure LoadToSave
@tableName      nvarchar(max)
SELECT '<xsf:assignmentAction  targetField="xdXDocument:GetDOM(&quot;' + TABLE_NAME +  '_Save&quot;)/dfs:myFields/dfs:queryFields/tns:ExecuteSmartObjectMethod/tns:ExecuteSmartObjectMethodInput/SmartObjectInput/InputProperties/'  + COLUMN_NAME + '" expression="xdXDocument:GetDOM(&quot;' + TABLE_NAME +  '_Load&quot;)/dfs:myFields/dfs:dataFields/tns:ExecuteSmartObjectMethodResponse/tns:ExecuteSmartObjectMethodResult/SmartObjectReturn/ReturnProperties/'  + COLUMN_NAME + '"></xsf:assignmentAction>'
FROM  INFORMATION_SCHEMA.Columns where TABLE_NAME = @tablename
CREATE procedure ClearSaveInputParameters
@tableName      nvarchar(max)
SELECT '<xsf:assignmentAction  targetField="xdXDocument:GetDOM(&quot;' + TABLE_NAME +  '_Save&quot;)/dfs:myFields/dfs:queryFields/tns:ExecuteSmartObjectMethod/tns:ExecuteSmartObjectMethodInput/SmartObjectInput/InputProperties/'  + COLUMN_NAME + '"  expression="&quot;&quot;"></xsf:assignmentAction>'
FROM  INFORMATION_SCHEMA.Columns where TABLE_NAME = @tablename

People have told me that this post doesn’t make a lot of sense, and yeah… they are right. So here’s what I mean:

K2 lets you integrate SmartObject methods into InfoPath forms (another topic, another post) but the upshot is that each SmartObject method becomes a data source in InfoPath. If you’re using smart box SmartObjects to store your data (like we had to do) then you have to do some magic to make it all work nicely on your form. The main issue you have to get your head around is that the data sources are blissfully unaware of each other – just because your Load and Save datasources are methods from the SmartObjects doesn’t mean they know anything about each other.

The most common task you’ll need to do is display the data in the smart box, allow the user to edit it, and then save the data. However, it’s not as simple as that… in reality you need to do the following:
1. Put some text boxes (or other controls) on your infoPath form
2. Bind the text boxes to the fields of the LOAD data source

Now when you execute the LOAD data source the values in the smart box table will be shown on the form. However, to allow the user to edit the values you’re going to need to add a ‘Save’ button, and on this save button you need to add some rules:
1. Copy EVERY SINGLE FIELD value from the LOAD data source to the input of the SAVE data source
2. Execute the SAVE data source

Now imagine if you have a SmartObject with 50 fields in it (NOT good practice by the way, but we had to). You know how many times you need to click to add 50 rules to copy values across? You don’t? No, neither do I, because I refused to do it. I wrote a SQL script to generate the rules for me.

Basically the SQL above will generate InfoPath rules for you which will copy fields from the load data source to the save data source. It’s not idiot proof, but it works in almost all cases. Once you’ve got your rules, you only need to break the Infopath form up into it’s source files, manually edit the manifest file, insert the rules in the right place, repackage it up and REALLY hope you haven’t messed the whole thing up.

Yes, it’s complicated, and yes, it’s risky, but I’ll give $100 to anyone who creates all 50 rules manually and doesn’t break down crying when the structure of the SmartObject changes and they don’t need to do it all again…

Leave a comment

Posted by on January 8, 2011 in InfoPath, SmartObjects


SmartObject Gotcha – Service Methods not refreshing

We recently spent some time tracking down a bug which turned out to be out of date SmartObject methods in our InfoPath form. We had edited our Smartbox methods to add extra service broker calls, for example to save the SmartObject we call the ‘GetCurrentUser’ and ‘GetCurrentDate’ service methods to store the ModifiedBy and ModifiedDate values, then we call the ‘Save’ service method.

Now… if the SmartObject method is integrated with InfoPath and you change the structure of your SmartObject, the service methods are no longer being updated. This means that if you add new fields they won’t appear in the Save method until you edit the SmartObject, delete the call to the Save service method, re-add it, redeploy the SmartObject and then refresh the form.

Just something to keep in mind.


Error during deployment: Server was unable to process request. —> Object reference not set to an instance of an object

If you’re deploying a workflow with InfoPath integration and you get the error “Server was unable to process request. —> Object reference not set to an instance of an object” it’s almost always because something in your form is incompatible with forms services. Run the InfoPath design checker and you’re sorted.

Leave a comment

Posted by on May 20, 2010 in InfoPath, SmartObjects


InfoPath data store – XML vs SmartObjects

K2’s recommended approach for using InfoPath integration is to use SmartObjects as the data store as opposed to the standard XML store. Speaking from experience I don’t necessarily agree with this approach for all instances.

Using the SmartBox does give you certain advantages, the main one being that your data is in a database, which simplifies reporting and makes it available to systems without needing to parse XML. It also makes parallel activities simpler by minimising data concurrency issues. However, there are disadvantages. In our case here we had no choice but to use SmartObjects since there was a requirement to be able to view and edit the data outside of the process. If you have to decide between SmartObjects and XML, here are some reasons to consider not using SmartObjects (This is just off the top of my head, I’ll add more as I think of them):

  1. Performance. If you’re using SmartObjects you’re making back end database calls and you will have a less responsive UI (particularly if you’re using forms services).
  2. Validation Rules cannot be added to controls bound to a SmartObject property.
  3. You cannot have a drop down with manually entered options to choose from if the control is bound to a SmartObject data source. For example if I have a property ‘DayOfTheWeek’ and I want the user to choose from Monday, Wednesday or Friday then I need to create an XML field, drag that on to the form as a drop down list box, enter in the values, then remap the binding to the SmartObject control. This is because if it’s bound to a SmartObject property then you can’t set a default value in InfoPath which means you can’t click ‘Ok’.
  4. Editing SmartObjects is very painful because every field needs to be manually copied from the Load data source to the Save data source.
  5. You can’t have nested lists. For example if you have an XML structure of repeating properties (a list of departments each with a list of members) you cannot display them all with SmartObject data sources. This is because to get a list of members you need to enter in the department id and then look up the members, and then when you get to the next department and look up the members for that department the results of the previous lookup are overwritten. The workaround for this is to create a custom List SmartObejct method which flattens the department and members list, but it’s ugly and clunky.
  6. Deployment is necessarily more complicated since you’re deploying SmartObjects.
  7. Data is more difficult to get to from K2 processes – you need to load SmartObjects as opposed to using the XML data fields.


1 Comment

Posted by on May 3, 2010 in InfoPath, SmartObjects