Stored Procedures to generate InfoPath rules

08 Jan

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


Leave a Reply

Your email address will not be published. Required fields are marked *