Alyssa

InfoPath Forms and Task List

alyssastgermain — Tue, 23 Jul 2013 15:35:00 GMT

Summit the Peak!

Tasks help you monitor progress towards goals. SharePoint task lists provide a central location for “To Do” lists across your team site. From your task list, it’s easy to add tasks, assign resources, update progress and view status towards milestones. SharePoint workflows tie tasks from all lists and libraries on your site back to your central task list repository. Your task list is your central hub for To Dos on your team site. Simple and effective!

So, what’s the catch? Only SharePoint’s built-in workflows can automatically create the tasks which means your users need to navigate away from InfoPath to view status. For in form viewing of workflow status you need to pull tasks into the view and when a user clicks approve, submit them back to the task list. You’ll want tasks that are associated with your individual forms so that you can display them when the form opens.

Out-of-box Solutions

SharePoint comes with several options for power users:

Map your task items to the Task list using CAML
- Pros: no code
- Cons: very tricky configuration, doesn’t work for parent-child, brittle when things change
- How-to links:
  - http://www.youtube.com/watch?v=4oQ7QdpYdx8
  - http://www.ilikesharepoint.de/2012/08/sharepoint-2010-u2u-caml-query-builder/
Write custom code to submit your data to Task list
- Pros: set up complex mappings
- Cons: developer needed and likely a brittle solution not flexible when things change
- How-to links:

More Flexible

To reduce cost, complexity and enable flex when your form’s data changes, you’ll want to look beyond hardcoded solutions that are not dynamic:

Tasks XTP Plugin
- Pros: no code, customizable, reusable template across all forms; lets you view the tasks in the form
- Cons: Need to configure rules when XTP is injected to the InfoPath form template
Use Qdabra’s qRules to map task items to the Task list
- Pros: no code, complex mapping support
- Cons: need to buy a license, but it’s inexpensive
- How-to link:
  - http://www.infopathdev.com/blogs/hilary/archive/2012/05/30/qrules-submittosharepointlist-walkthrough-for-v4-2.aspx

Learn More

Qdabra offers short video tutorials and in depth how-to hands-on-labs. Check out the following links:

YouTube Video highlighting the above techniques:
- More Flexible – Tasks XTP Plugin
  - http://www.youtube.com/watch?v=4oQ7QdpYdx8
Qdabra Toolkit includes detailed how-to instructions and sample templates:
- http://www.qdabra.com/webstore/p/61/qdabra-webinar-kit-task-xtp-for-infopath-integration-with-sharepoint-tasks

How Do I Map InfoPath Form Data to a SharePoint List?

alyssastgermain — Tue, 23 Jul 2013 15:34:00 GMT

Have SharePoint Tickets, but Can’t Get on the Train?

InfoPath makes data collection easy – build a form, publish to SharePoint, and BINGO – your users fill them out. When users save or submit, the form promotes values to SharePoint and it’s easy to create custom views on that data or export it to Excel for further analysis.

But, there’s a problem. You can only promote fields that are not repeating, and most forms have repeating data – in other words, the data world isn’t flat either. Promoted properties for repeating data limit you to aggregating or picking one item. How are you going to get multiple items data fields out of your form? Reporting today is all about “pivoting” data tables. How do you export more than one table from InfoPath?

Out-of-box Solutions

SharePoint comes with two out-of-box solutions, but both are brittle:

Map your data to one or more SharePoint lists using CAML
- Pros:
  - No code
- Cons:
  - Tricky configuration,
  - Doesn’t work for parent-child,
  - Maintenance cost when things change
- How-to links:
  - http://www.youtube.com/watch?v=4oQ7QdpYdx8
  - http://sharepointknowledgebase.blogspot.com/2011/10/updating-sharepoint-list-by-using.html
Write to a database and use ShP dataview web part
- Pros:
  - No code
- Cons:
  - Can’t convert existing InfoPath forms
  - Doesn’t support browser forms,
  - Tightly coupled to database means limited data structure,
  - Can’t migrate document data when database changes
- How-to links:
  - http://office.microsoft.com/en-us/sharepoint-designer-help/web-parts-for-views-and-forms-in-sharepoint-designer-2010-HA101805424.aspx#_Toc245608913
  - https://www.nothingbutsharepoint.com/sites/eusp/Pages/how-the-data-view-web-part-can-enhance-the-quality-of-your-life.aspx

Custom Code Solutions

Of course, you could just delegate to your developer and have them:

Write custom code to submit your data to multiple lists
- Pros:
  - Enables complex mappings with many lists
- Cons:
  - Creation cost – requires developer resource
  - Maintenance cost – brittle solution will break when things change
- How-to links:

More Flexible Approach

Yes, there’s a better way – actually a couple! First off, let’s look at a more flexible approach.

Use Qdabra’s qRules plugin to enable form mapping to SharePoint lists
- Pros:
  - No code needed,
  - Supports complex mapping – multiple lists, parent child,
  - Easy to update when things change,
  - Batch tool means you can map existing documents
- Cons:
  - New tool to learn,
  - Small purchase required (but there’s a free trial),
  - Won’t work in Office 365 environments,
- How-to link:
  - http://www.infopathdev.com/blogs/hilary/archive/2012/05/30/qrules-submittosharepointlist-walkthrough-for-v4-2.aspx

Most Scalable

But, what happens when you have thousands of forms with tens of thousands of repeating data items? SharePoint gets indigestion. So, here’s a second solution to overcome those limits and still use SharePoint as your dashboard:

Use Qdabra’s DBXL Web Service to map form data to SQL database / Azure
- Pros:
  - No code,
  - Flexible when things change,
  - Supports cloud scenarios (Office 365),
- Cons:
  - Requires server install of product or connection to generic Azure service,
- How-to link:
  - http://www.infopathdev.com/files/folders/sql_integration/entry74107.aspx

Learn More

Qdabra offers short video tutorials and in depth how-to hands-on-labs. Check out the following links:

YouTube Video highlighting the above techniques:
- More Flexible – qRules Plugin
  - http://www.youtube.com/watch?v=WftCp_EbcCU
- Most Scalable – DBXL Web Service:
  - http://www.youtube.com/watch?v=nPhyS2CtScc

Top 10 InfoPath Workarounds

alyssastgermain — Tue, 23 Jul 2013 15:30:00 GMT

So you’re a fan of InfoPath, you use it every day when designing your forms, you even literally dream of it sometimes – you just love InfoPath… but there are times you’re having “InfoPath nightmares” when you can’t make things work because of limitations, or – bugs! Well, the world isn’t perfect, there’s got to be at least one hole or a number of cracks somewhere, right?

In this blog post, we’ll try to patch some of those “cracks” to at least lessen your InfoPath nightmares.

Here’s a list of the top 10 workarounds I have in mind:

1. Copying repeating data from a secondary data source into the main data source?

One thing that you can’t easily do in InfoPath is to copy repeating data from a secondary data source – like a SharePoint list – to the main data source. This is an important necessity, because the data from secondary data connections does not get saved in the resulting XML data file of a submitted/saved InfoPath form. Read more…

Dealing with more complex repeating data structure? Here’s the solution…

2. Concatenating lists of values?

Use the double-eval trick. Let’s say you want to build a list of email addresses in your form, maybe for workflow purposes; or simply for sending the form to a dynamic list of people via email. You want something like this:

For the example shown above, we’ll have this for our schema:

The EmailAddresses field will contain the formula that will build the list of email addresses. The formula should look like so:

Here’s the complete XPath to that formula:

substring(xdMath:Eval(xdMath:Eval(../my:UserList/my:User, 'concat(@my:email, substring(";", 1, string-length(@my:email)), " ")'), ".."), 1, string-length(xdMath:Eval(xdMath:Eval(../my:UserList/my:User, 'concat(@my:email, substring(";", 1, string-length(@my:email)), " ")'), "..")) - 2)

3. Dynamically adding rows to repeating data?

Here again, qRules makes this easier for you – you can write your own code, yes; or even hire a developer, but qRules just makes it a lot simpler (and cheaper!) for you. You can use the command called Insert which allows you to add a row, or any number of rows, at any position in your repeating table. The qRules User Guide which comes with the installation files should help you get started with using this command.

4. Keep form state values out of the main data source

One of the best practices when designing InfoPath forms is making sure that there are no fields in your main data source that don’t really store data. Anything that’s being used to control the logic in your form but doesn’t necessarily store values should go into a secondary data source such as an XML file. For example, you want to check whether the user opening the form has edit permissions. You could then have a node in your secondary XML file called “CurrentUser”. The logic in your form will populate that CurrentUser node either via an Active Directory web service or SharePoint’s UserProfileServices method, or simply via the username() function; and then can have a rule check whether the value is the same as that of the form submitter, or the approver, or what-have-you. Since the value of the current user will always change, it can go into your secondary XML data source.

5. Submitting repeating data to a SharePoint list?

Ok, you can use SharePoint list forms – but list forms actually have a flat data structure, i.e. non-repeating. If you want to submit multiple products in one order, the list item for that order will either keep a merged list of products, or just the first/last product which isn’t really helpful especially if you want to report on your data. The SubmitToSharePointList qRules command features the ability to submit data from an InfoPath form to the SharePoint list it has been mapped to. It also allows updating of an item in the SharePoint list from the form. The latest version even allows you to submit to different lists – so in our example, we can now keep a separate list of orders, and a separate list of products with reference the corresponding order.

Watch this YouTube video to learn more and see its powerful features in action
Here’s a blog post that has detailed steps to walk you through setting up your form to submit repeating data to list forms using the qRules command

6. Conditional hiding disabled?

You want to hide your Date picker control or your radio buttons, check boxes, or a hyperlink at certain conditions – surprised to find out that the “Hide this control” option is disabled? Easy fix: place the control in a section and add your conditional formatting to that.

7. Always give your forms unique names

You can do this using fields that you know will always be unique, but what if there are none? Yes you can concatenate the company name and the customer name, but you’re still not 100% assured that it will forever be unique. A company can have two John Smiths after all, right? So aside from concatenating field values, you can also add the current date and time by using the now() function which includes seconds in the timestamp. If you require more than that, you can use qRules’ GetFullDateTime command which returns milliseconds – now that will be sure to be unique!

8. Unable to use Digital signatures?

Use Electronic Signatures

9. White space?

You thought you’ve set everything on the canvas to lay out nicely in the browser and/or client, but the white spaces just won’t go away! Aside from making sure that you’ve set row heights, paddings and margins to zero, you also need to remove section borders. Don’t be tricked – you’ll think that all the new section controls that you add on your form (whether they be repeating or non-repeating sections) don’t have borders? They do! Turns out that by default, sections have ‘invisible’ outline borders – so update them and select ‘None’ from the border presets.

10. Need your dropdown list box to display concatenated values?

You’d like your dropdown list box to display the customer’s Full name but you only have the First and the Last name values separated in your data source. Of course, if you have full control of the data source, you can easily make this happen by using another field that concatenates the strings you’d like to display. Otherwise, the other option would be to manually update the .xsl file. If you export your form’s source files and inspect the XSLT for your drop-down list box in the view’s .xsl file, you’ll see something like this:

You can modify the last select value (which is actually the displayName) to be the concatenated formula, like so:

Learn More

Qdabra offers short video tutorials and in depth how-to hands-on-labs. Check out the following links:

YouTube Video highlighting the above techniques:
- More Flexible – qRules Plugin
  - http://www.youtube.com/watch?v=WftCp_EbcCU
- Most Scalable – DBXL Web Service:
  - http://www.youtube.com/watch?v=nPhyS2CtScc

Reporting Options for SharePoint Lists

alyssastgermain — Tue, 23 Jul 2013 15:29:00 GMT

Navigate the Islands!

Your SharePoint lists are all set up and they are full of interesting data – but until that data is presented in an aggregated fashion, it will be pretty difficult to make it useful. Your users need to be able to compare, contrast, create ad hoc reports and assess the information. Your high level folks need dashboards, scorecards, KPIs and gauges.

You need reporting options!

Out-of-box Solutions

SharePoint has some reporting options built in:

Create your own views from the settings page
- Pros: can be for all users or just for you, allows filtering (including the Me filter, which displays just your items)
- Cons: can’t join two lists, have to have correct permissions
- How-to links:
  - http://office.microsoft.com/en-us/sharepoint-server-help/create-modify-or-delete-a-view-HA010377693.aspx
  - http://blogs.msdn.com/b/dinaayoub/archive/2010/04/22/sharepoint-2010-how-to-create-a-view.aspx
Create views using SharePoint Designer
- Pros: variety of layout options, ability to join two lists
- Cons: SharePoint Designer learning curve, easy to break things
- How-to links:
  - https://www.nothingbutsharepoint.com/sites/eusp/Pages/How-to-Link-Two-Lists-and-Create-a-Combined-View-in-SharePoint-2010.aspx
  - http://office.microsoft.com/en-us/sharepoint-designer-help/create-a-custom-list-view-using-sharepoint-designer-HA010379079.aspx

More Flexible

Sometimes you need to be able to visualize the data in ways that aren’t possible in a list view. More flexible options include

Excel workbook linked to list(s)
- Pros: familiar interface, powerful calculation and analysis engines
- Pros: new features like PowerPivot and PowerView allow for data modelling and aesthetically pleasing reporting
- Pros: PowerPivot Gallery in SharePoint (on-prem only at this time) allow you to publish and share easily
- How-to links:
Access database linked to list(s)
- Pros: Access can be used to report on complex data spanning two or more lists
- Pros: easy to read & write, familiar interface for some users
- How-to links:
  - http://www.youtube.com/watch?v=KBA-Sz72Spw
  - http://office.microsoft.com/en-us/windows-sharepoint-services-help/export-or-link-a-datasheet-view-to-access-HA001160860.aspx

Most Scalable

Enterprise reporting solutions can help you keep up on your data even as it grows.

SQL Server Reporting Services
- Pros: powerful reporting tool, can use lookup functions (2008 R2 or later) or sub-reports to join list data
- Pros: Report Builder integrated with SharePoint
- Pros: can be scheduled or subscribed to
- Cons: requires server install of product
- How-to links:
PerformancePoint
- Pros: reusable KPIs, integration with other Microsoft products (including SSRS reports!)
- Pros: can consume data from an Excel PowerPivot model
- Cons: requires SharePoint Enterprise or E3/E4 for Office 365
- Cons: some features only available for data cubes
- How-to links:

Learn More

Qdabra offers short video tutorials and in depth how-to hands-on-labs. Check out the following links:

YouTube Video highlighting the above technique:
- ttp://www.youtube.com/watch?v=EC2b61W60IU&list=PL581899A129522F53
Qdabra Toolkit includes detailed how-to instructions and sample templates:
- http://www.qdabra.com/webstore/p/75/qdabra-webinar-kit-reporting-with-performancepoint

Overlays in InfoPath

alyssastgermain — Tue, 23 Jul 2013 15:25:00 GMT

You Can’t Do That In InfoPath!

InfoPath makes it easy to design complex, high-quality user interfaces, but unfortunately one if its limitations is that it does not support overlay windows. Anyone who has used a web browser is probably familiar with overlays, small windows usually programmed with jQuery that look like pop-ups but are embedded directly into the main web page. The background shading effect used with overlays could add a nice touch to a user interface, creating a sleek and modern-looking InfoPath form…that is if you were allowed to create overlays in InfoPath!

Figure 1: jQuery Overlay Example

Never fear: in today’s blog, we’re going to look at one way to create a browser form that uses multiple views to mimic the look and feel of an overlay.

Creating the Main View

This technique works best on forms with a fixed tabular or grid-based layout. Create the main view using your desired content and color scheme, making sure to put all of the content inside a single unbound section. Here is a screenshot of the Main View in a sample form I created as a demo (scroll down to the end of the post for the link to download the template file):

Figure 2: Main View of Sample Form

In my sample form there are two sections:

Personal Information – a non-repeating collection of fields
Job History – a repeating group with multiple fields

Here is the schema for these sections:

Figure 3: Schema fields for Main View

Creating the Overlay View

For my sample form, I’ve decided that the overlay should appear when I want to add new entry into the Job History repeating group. I’m going to show the overlay when the user clicks the bright blue plus sign picture button at the top right of the Job History section.

Now when I say “show the overlay” I really mean add a rule to the plus sign button that will switch to a new view I have created called the Overlay View.

Figure 4: Adding a rule to switch to the Overlay View

Next, choose the area that you want the overlay to appear in. Choose wisely because it will always show in the exact same area relative to the position of the other sections, so if you scroll too far it will move off the screen. Pick a location that will be on-screen and not too far away from the button that the user will click to display the overlay.

Once you have chosen your location, copy & paste the entire contents of the Main View into the new Overlay View. Below I have circled in red the area that I want the overlay to appear in:

Figure 5: Main View with targeted overlay area circled in red

Ok, now we’ve set up the rule to switch views, so let’s work on building the overlay inside the Overlay View.

The first step is to change the color scheme for everything OUTSIDE of the targeted overlay area to be a darker, greyer shade. You’ll need to play around with a few colors before you settle on the ones you feel are best for your particular form. Change the background color by right-clicking outside of the form sections, selecting “View Properties”, checking the “Use a background color” checkbox, and then selecting a color from the adjacent dropdown color menu.

Figure 6: Changing the background color for the Overlay View

Next, highlight the table cells OUTSIDE of the targeted overlay area and change their color by right-clicking, selecting “Table Properties”, clicking the down arrow next to the fill icon and selecting the desired color from the color menu.

Figure 7: Changing the colors for the rest of the sections in the Overlay View

Change any light colored fonts to a darker grey shade and any dark colored fonts to a slightly lighter grey shade. The goal here is to mute things so that the overlay will pop out at the user.

You may have noticed that the sections in my sample form have rounded corners. These are achieved by inserting image files into the table cells that mark the corners of the sections. If you use similar image files in your own forms, you will need to edit their color outside of InfoPath in your desired image editing software. Likewise, edit any banners or header/footer images outside of InfoPath as well.

Finally, change all controls outside the targeted overlay area to read-only text boxes so that the data is still visible but the user can’t edit them.

Here’s the Overlay View in my sample form after making all the changes described above. You can’t tell from the screenshot, but the plus sign button in the Job History section has been changed to a simple image, because we don’t want it to be an active in this view. (Alternatively you could disable the rules on the picture button, but the user would still be able to click the button and might wonder why it wasn’t doing anything.)

Figure 8: Overlay View with colors outside the targeted area changed & all fields changed to read-only text boxes.

Creating the Overlay Section

Now it’s time to create the overlay itself. Recall that the function of the overlay in my sample form is to add the details for another job to the Job History repeating group. To do this, I’m going to need some new fields that will be displayed only on the overlay. Here’s is the schema for these new fields:

Figure 9: Schema fields for the Overlay section

Rearrange the table rows and columns in the targeted overlay area to hold the controls for these new fields, as well as a title, cancel button and an Add button (to add the user’s entries to the Job History section, of course).

Feel free to be a bit creative here with the layout and the color scheme. I’ve chosen to keep things simple, re-using the light blue color from the Main View and sticking to a simple grid.

Figure 10: Completed layout for the Overlay View

Let’s take a closer look at the buttons inside the overlay.

The cancel “X” button in the upper right corner does two things: (1) clear anything the user may have typed into the overlay fields, and (2) switch back to the Main View. Together these two rules create the effect of “cancelling” and “closing” the overlay.

Figure 11: Rules for the overlay cancel button

The Add button is a bit more complicated and requires use of the qRules “Insert” and “Set Value” rule commands. The “Insert” command is used to add a new blank entry to the Job History repeating group, and the “Set Value” command is used to set the values of the fields in that new entry to be equal to the values that the user has typed into the overlay fields.

For more information on the qRules “Insert” and “Set Value” commands, visit this blog post.

Figure 12: Rules for “Insert” and “Set Value” qRules commands (Note: “Set Value” command shown only for the Company field)

Lastly, add a rule to the “Add” button to switch back to the Main View.

We are now finished with both the Main View and the Overlay View, and the form is ready to be published.

Here are a few screenshots of the sample form in action:

Figure 13: Screenshots from the finished sample form

Learn More

Try out this overlay technique yourself with the sample form template located here.

Want to learn more about the qRules commands described above? Qdabra offers short video tutorials and in depth how-to hands-on-labs. Check out the following links:

YouTube Video highlighting the above techniques:
- qRules Plugin Overview
  - http://www.youtube.com/watch?v=WftCp_EbcCU