Making a Read Only PDP with InfoPath Forms

The genesis of this post lay in a conversation I had with a client.  The goal of the discussion was to identify a method to lock down specific project detail pages (PDP) within Project Server.  I figure that there’s probably a number of ways to accomplish this, but since I had been playing with InfoPath lately, this seemed to be the most logical choice.

From a process standpoint, the locked down PDP would enable a PMO to set specific fields at the project initiation, and then either prohibit or strongly discourage project managers to modify these fields.  If you’ve used Project Server for any time, of course, you’ll know that there is no true field level security….once a user can edit the project in Microsoft Project Professional, all of the fields may be edited.  But in some cases, perhaps where the project manager is primarily interacting with the project within the browser, this may be an effective way to control fields.

For other techniques that would probably be used in conjunction with this, I would encourage you to take a look at this post on security trimming PDPs, and some of my early posts of extending PDP pages into SharePoint lists.  At some point, I’ll probably come up with a proposed solution for leveraging SharePoint lists to provide secure extensions of Project Server data.

The Challenge

So the challenge I set before myself was to replicate a PDP page, but to make it read only.  At the end of this experiment I wanted to be able to pull up a PDP page, and have it render Project Server data without requiring any further input from the user.  My conclusion is that it’s not all that hard to accomplish this, albeit it requires a couple of tricks to get the output of the Query Filter Webpart to match the data requirements of the database query.

Preparing the SharePoint Site

The first thing we need to do is to add two libraries to our main PWA site: a data connection library and a forms library.  The data connection library will contain the required UDC file to link our form to the Project Server database.  The forms library will contain the form for our PDP pages.

You’ll find the options to create both libraries after selecting the View All Site Content option under Site Settings, then clicking Create and Libraries.

image

As a useful trick, you may note an InfoPath form maps to a specific content type within the form library.  If you plan on deploying this solution with different InfoPath forms, you’ll probably want to enable content type management within the forms library and then create a couple of extra content types.

Once you’ve created those two libraries, you’ll probably want to confirm that InfoPath is in fact configured within your SharePoint farm.  To do that, you’ll have to navigate to the Central Admin site.  Click on the General Application Settings.

image

Click on Configure InfoPath Forms Services.  Assuming that this does not violate your organizational security policies, go ahead and check the last three check boxes.  Click OK.

image

Close Central Admin.

Creating the Data Connection

To create the data connection, let’s open a blank form in InfoPath Designer.

image

On the Data tab, click on the Data Connections button.

image

Follow the default options to create a new data connection file to receive data from a SQL database.  For this example, I will be using the EPMProject_UserView table from the Reporting database.

image

Select the fields that you will require.  For this post, I’ll just go ahead and select all the fields in the view.

image

Complete the wizard.  Make sure to deselect the last checkbox on the last screen.

image

Once you get to the Data Connections dialog box, select to convert the newly created data connection to a connection file.

image

Post the connection file to the connection library created when we started this process.  Close InfoPath and navigate to the connection library.

image

As versioning is turned on within this library by default, you may need to click on the drop down arrow next to the connection file and set it to approved.

Configuring the InfoPath Form

Our next step is to create the InfoPath form.   With the data connection created, this is actually quite simple – with one minor caveat.  We need to add a mechanism to the form to manipulate the data received from the query string filter and convert it into something recognizable by the form query.

Create a new form with the Data Connection File template.  You could probably use any template, but this is the one that seemed the most appropriate when I was writing this post.

image

Connect it to the data connection created previously.

image

You should end up with a screen that looks like this.  Note the data fields on the right hand side of the screen.

image

InfoPath fields show up in two flavors: query and data.  The query fields are used to query the database….the data fields yield the results.  So our goal is to add the Project UID field to the appropriate query field, trigger the query, and show the results in the data fields at the bottom of the form.

You may wish to play with various permutations of this form, but the simplest way to emulate the PDP functionality is to delete everything but the data field box.

image

Now add a custom field called TempUID to the form data set by right clicking on the myFields item in the right hand dialog box.  This will create a field called my:TempUID.

image

Now click on the option to Manage Rules in the Ribbon.  Select the TempUID field, and click the option to set a New rule.

image

Set the rule to set the query field ProjUID whenever the TempUID field is changed. (Not the data field). The format for the data will appear as follows:

concat(“{“, translate(., “abcdefghijklmnopqrstuvwxyz”, “ABCDEFGHIJKLMNOPQRSTUVWXYZ”), “}”)

…where the “.” represents the TempUID field.  This formula does two things:

  • Adds round brackets around the TempUID entry, i.e. {abcdef}
  • Converts the lower case letters to upper case, i.e. {ABCDEF}

If you don’t add this formula, then the query will return an error stating that the ProjUID field is invalid data.  When completed, the calculation should look something like this (noting again that the field name appears as a “.”):

image

Add a second rule to the same field to query for data after the first rule is run.

image

Going back to the form, drag the desired fields to the data field box.

image

Save the form, and publish it to the form library created at the beginning.  When publishing the form, promote the TempUID field so that it may be used in Webpart connections.

image

After publishing the form, close InfoPath.  You are now ready to create the Project Detail Page.

Creating the PDP

Back in Project Server, navigate to the Project Detail Pages under Server Settings.  Add two Webparts to the page, the Query Filter web part and the InfoPath form.  Edit the Query Filter Webpart to pull the ProjUID parameter.

SNAGHTML2154f81

Configure the InfoPath form to pull the Query String Filter into the TempUID field we promoted when we published.

image

…and that’s it.  Add the PDP to your project type, and it will automatically display Project Server data whenever the page is viewed.

SNAGHTML21954ce

Yes, I admit the form is kind of ugly – and that description field got truncated, but dressing up InfoPath forms is probably a decent topic for another post.  For now, we got the data to show up – which I think is pretty good progress.

Credits

To give credit where credit is due….thanks very much to Jimmy McAnally (@jimmymcanally), a former colleague.  I ran into him at a recent Houston SharePoint User Group meeting, and he graciously spent a couple of hours helping me with a different hypothetical InfoPath question.  In the process, he ended up teaching me enough about InfoPath to get me started down this path.  Also thanks to Clayton Cobb (@warrtalon), SharePoint MVP, for graciously attempting to help me out on the online forums – despite my near-incoherence in being able to actually describe the issues I was facing.  I’ll give them credit for everything that actually works in this post and I would encourage you to blame me for any incorrect or misleading information.

Advertisements
Making a Read Only PDP with InfoPath Forms

28 thoughts on “Making a Read Only PDP with InfoPath Forms

  1. Paul says:

    Hi Andrew,

    Thank you for documenting and posting how to get Project data into a PDP, this is very useful. I do have one issue, I have followed the steps above but I can’t seem to get the data to filter. On my InfoPath form it returns information for all projects. I have added the TempUID field on to the form and can see that the correct Proj_UID is being passed to the form from the URL but this Proj_UID/TempUID is not filtering the data being returned. I am using a custom view in the reporting database rather than the EPMProject_Userview that is used in your example but everything else is identical, the view does include the Proj_UID.

    Do you know what could be missing?

    Many thanks

    Paul

    1. So it sounds like you’re getting the data, and you’re running the query. My first guess would be that you somehow are missing the step where the ProjUID field is copied from the TempUID field to the query field…?

  2. Paul says:

    Hi Andrew,

    Thanks for your reply, I have also added the Proj_UID field from the queryFields on to the form and can see that this also contains the correct Proj UID with upper case characters and the brackets. Do you have to add a filter to any of the rules? I know the example above you set the ProjUID queryField value from the TempUID then run the Query for Data rule. My form, sets the correct value for the ProjUID query field and queries the data but isn’t filtering the data on the page.

    Many thanks

    Paul

      1. Maybe you have the rules running in reverse order? Copy the data, then run the query? Consider removing the rules and replacing them with buttons so you can manually control the sequence.

  3. Paul says:

    Hi Andrew,

    I checked that the rules where running in the correct order, they were. I think I know what the issue is but don’t know how to get around it. In my custom SQL view I have task level information as I wanted to see certain tasks on the PDP (project health check – resources on summary tasks etc). I ran through your steps above again but used the EPMTask_UserView and replicated my issue with the data not being filtered by the ProjectUID. Maybe this isn’t possible?

    Thanks

    Paul

    1. OK – so the issue occurs when you’re trying to get Task level data, but it works fine when you use the EPMProject_UserView? I never tested it against task level data, but I am sure there’s a solution.

  4. Paul says:

    Hi Andrew,

    Yes, it appears to be an issue when the SQL view contains task level data, all of the rows in the view are returned in the form and not filtered to only the data for the project. I’d be interested to hear if you do come a across a solution in the future 🙂

    Many thanks.

    Paul

  5. Juan says:

    Hi Andrew,

    This is a useful post and it would resolve one of my requirements, but I got a question. Is it posible to publish that PDP information in an Infopath form in a project site?

    Thanks a lot.

    1. Good question. I am sure it is possible, and I am sure you’re not the only one to ask that question. I won’t have time in the near future to try to suss that out, but will post back if I do.

  6. Peter says:

    I did exactly what you instructed but for some reason I am missing the “queryFields” group under myFields (it only displays dataFields). This means I can’t query against the data, I tried using the rules you mentioned above to set the dataFields ProjUID instead but that won’t filter the result set and the details are displayed for ALL the projects.

    Any idea’s as to why the queryFields option is missing and how I can add it back in?

      1. Peter says:

        Thanks for the quick reply but there is no ‘Advanced View’ checkbox, there is only a ‘Show Details’ check box which only displays the field types of the dataFields. There is also a Manage Data Connections.. link which isn’t very useful.

        I have ran through your process many times but for some reason I can not find the queryFields folder in the Fields pane. Perhaps there are some steps which haven’t been clearly identified above?

      2. I see it in the Show Advanced View link. When clicking on that I get the query fields. Maybe the template you chose? I’d recommend posting this on the InfoPath forum to see what they say. Just don’t mention it’s for a Project Server implementation or they will kick it over to the Project Server forum.

      3. Peter says:

        Sorry I am still not sure where the ‘Show Advanced View’ link is located? I have tried several options and as far as I can comprehend, the queryFields group will show up only when I add a data source from a Sharepoint List, but it just simply isn’t there for a SQL DB Data Source, and I have no idea how you got it to show up on your SQL data source?

  7. Mayur says:

    Hi Andrew..
    Thanks for the post, it helps me..
    but i have one query, while we create new project in pwa, their is no field to give Project Manager Name, where in Project server Database their is field available with name “ProjectManagerName”, then how can i get this field in New Project Creation page using infopath or lookup table..??
    If any other solution is also available then plz, let me know..!!

    Thank you…:)

    Mayur

    1. The Project Manager field appears in the desktop client under the File > Info > Project Information dialog box. It appears as “Manager.” I think you can throw this in an editable form on the PDP – but don’t have Project Server open in front of me to validate.

  8. Got some more feedback on this post. Per that feedback, I need to emphasize that you copy the TempUID field into the Project UID Query Field. Anything else, and it won’t work. If you don’t see query and data fields from the field picker, make sure you have selected the option to Show Details.

  9. Sudhir says:

    Hi Andrew,

    I was able to create a PDP with infopath form webpart displaying project custom fields. Many Thanks!!!

    I would like to know How I would be able to submit the infopath form data (project custom fields data) back to project server reporting DB.

    Sudhir

    1. Sudhir says:

      I would like to know How I would be able to submit the infopath form data (project custom fields data) back to project server
      Sudhir

      1. Take a look at the CodePlex Solution Starters for Project Server 2010. I think they included an option to do that – specifically for the Risk Survey feature. It does involve custom code though.

  10. Sudhir says:

    Thanks Andrew.
    I actually deployed the solution starter for InfopathFormViewerWebpart and customized as desired and achieved the desired results 🙂

  11. andresfmosquera@gmail.com says:

    How can I create an Infopath to replace PDP, but that captures the information and deposit it in the Database? or just for logging information. It is essential to use enterprise custom fields, to control workflows, but with Infopath

    Thanks

    1. If you look at the risk management solution for Project Server 2010 in Codeplex, it shows you a rough version of how to do that, i.e. to use InfoPath to submit data into the database. In all honesty, unless you have access to a developer, I’m not sure I would do that. It may be easier to have InfoPath push to a custom database, then have the PSI pick up the fields and import them – which can be done with workflow for example.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s