Tech Thoughts

Binding document content to Document Information Panel in MOSS2007

Today I will talk about the possibility to bind the content of a Word 2007 document to it Document Information Panel in SharePoint 2007.

This is quite a useful feature as the content of documents can be automatically populated via the metadata and viceversa.

The trick is done by Content Controls.

Let’s create a document library named “To Be Approved”. Let’s add some required columns to this document library. I have added a column named “Approver” (single line of text, required) and another column named “Date Approved” (date time, date only, optional).

Let’s now create a new Word 2007 document from our document library.

Let’s insert a header (I chose the “austere” style formatting) and save the file locally as a docx. This is the most important step as Content Controls are only available in Open XML documents.

Continue editing the header of the file: insert a Quick Part -> Document  Properties and choose Approver (our sharepoint column).  Do the same for “Date Approved” field.

Note that if you modify the value of either Approver or Date Approved fields the content of the document will change accordingly, and vice versa modifying the date in the header the metadata will change.

Now let’s go back to our document library: document Library settings, Advanced Settings: and enable content types.

Click on “Document” under Content Types and go to advanced settings. Check Upload a new document template and browse for the file we just finished editing.

Now go back to our library and click on New: a new docx file will open.

Edit metadata (approver, approved Date, Title), you will see the header updates with the metadata values you’ve just entered.

Note. You can bind any part of a docx file to metadata. I chose a header just for the purpose of demonstration.

Designing a browser-enabled InfoPath form that can store data in a database - Part3

Here's the last part of this blog post.

We will finish creating the form and we will test it.

Now create another section named DiscountData, insert a table with title and another table with three columns and four rows as follows:

startDate and endDate are date pickers controls, discount rate is a textbox.

This section will contain the discount data that will be stored to the database via our web service.

Designing a browser-enabled InfoPath form that can store data in a database - Part2

In the last post we have set up the DB that will receive data from our InfoPath forms. Now we will create the InfoPath form.  

Creating the form

Now we’re left with the easiest part: creating our InfoPath form.

Since I am using a SQL Server DB I won’t need a web service to retrieve data as InfoPath knows already how to do this with a SQL Server DB, but if you will be using other Database types, you will have to create another webmethod in the endpoint definition in order to map a web service name to a stored procedure.

Start InfoPath 2007 and choose “Design a Form Template…” from the “Design a form” options.

Choose to design a new form template based on “Blank”. This will just create a form that is empty.

Choose Tools->Forms Options and in the “Compatibility” section select “Design a form template that can be opened in a browser or InfoPath, uncheck “Hide errors…” and enter the URL of your SharePoint server (http://servername:portnumber).

In the “Security and Trust” section, uncheck “Automatically detect security level” and choose “Domain”. If the DB to which you will be connecting isn’t a member server of the domain, then you will have to set the security level to “Full Trust” and you will have to sign the form.

Now on to designing  the content of the form:

Create a section named ShopIDgroup that contains a table with title (Design Tasks -> Layout-> Table with Title – see below)

Then add a table with three columns and one row: make the table containing a text box control named shopID and a button as shown:

Double click on the button and select Rules->Add->“Add Action”: in the data connection section select “query using a data connection” then “add”. The data connection wizard will start: select “create a new connection to:” then “receive data” then click “next”; at this point select “database” if you have a SQL server DB (if you don’t have a SQL Server DB here you will have to select “web service” and then you will be prompted to connect to the server that exposes the web services and select your webservice) and click “next”. Now click on “select database…” and then in “my Data Sources” select “+newSQLServerConnection.odc”, this will start a data connection wizard where you will have to insert the name of the SQL server on which your db resides and select the database and table to which you are willing to connect (in our case the database name is InfoCityHall and the table is “Shops” (see below).

Click next and Ok you will return to the InfoPath wizard. Make sure you have selected all the columns of the Shop table and then click Next, Next and Finish. Then click OK till all the dialog boxes will close.

Now that we have set up the connection we need to store it in a Data Connection Library in SharePoint (this is mandatory for browser enabled form to access external data).

In the Data Source section click on Manage Data Connections, select the "Shops" data source and click on the Convert button, insert the URL of your data connection library (if you haven’t created one so far, create one in the same site collection as the one where you will store the forms) – the URL must include the udcx file name and make the data connection file relative to site collection and click OK.

Now delete the Shops connection and create another one as follows: in the data connections wizard, select Search for connections on SharePoint, Next. Select your SharePoint site and select the data connection (in our case Shops.udcx) click Next, Next (unselect automatically retrieve data when form is opened) and Finish and close the data connections window.

Now back to our form design: add another section named ShopData: this section will expose the info about the Shop that has been retrieved from the ShopID above.

Insert a table with two columns and three rows as follows:

shopName, shopAddr and shopOwner are all textboxes.

For each one of those controls: go to the control Properties and as the value of the control select the relevant column from the Shops table as follows: click the fx button, Insert a Field or Group, Data Source: Shops(secondary), select the relevant column and click on Filter Data then click Add and set the filter to ID is equal to shopID (from the main data source).

Repeat this for each one of these controls.

We will finish designing the form in the next blog post.

Designing a browser-enabled InfoPath form that can store data in a database - Part1

Used technology:

You will need Infopath 2007 to design the form, a Form Services enabled server (either SharePoint Server 2007 or Form Server 2007) and a database server that is able to expose web services (I will use SQL Server 2005).

Scenario:

A city hall needs to receive data from shops regarding discounts, in particular the discount type (30% off, 40% off, etc) and the period during which the discount will be applied.

Let’s assume that the shops are already registered in the city hall database.

The forms will retrieve the shop data based on the shop ID (that the owner will already know) and the owner will post data regarding the period and the discounts that will be applied.

Please take this as an example, the important part is setting up the form to talk to the DB and send data to it.

Set up the DB

The DB (InfoCityHall) will contain two tables: one for the SHOPS(ID, NAME, ADDRESS, OWNER) and one for the DISCOUNTS(SHOPID, STARTDATE, ENDDATE, DISCOUNTRATE).

The data already inserted is the following:

SHOPS

DISCOUNTS is empty. InfoPath will insert data into it.

Set up the Web Service

Now  we will need to create the web service. We will use the web service to store discounts into the DB from InfoPath.

The Web Service in SQL Server is created through an “HTTP Endpoint” and a T-SQL stored procedure.

Here is the stored procedure

And here is the HTTP Endpoint:

The web service will now be discoverable at the location http://yoursqlservername/sql?wsdl

In the next post we will start designing our InfoPath form.