May 2012 - Posts - Hilary Stoupa
in

InfoPath Dev

Hilary Stoupa

May 2012 - Posts

  • Use InfoPath to Teach Yourself XPath

    I'm working on a blog post that includes a mention of relative XPath, and rather than just continue adding info to that post (thus possibly creating the longest blog entry ever in the history of the world), I thought I'd include this tip on its own.

    When I am unsure of the XPath I need for an expression, I leverage InfoPath, and you can too. Here's how:

    1. Find the field or group you need to get the relative path from:
    2. Add a rule to it - I usually add a conditional formatting rule, but since we are going to delete the rule when we are done, it really doesn't matter what type of rule you use.
    3. Create a condition - for the first drop down, select the field you need the relative path to:
    4. Change the first drop down to "The expression":
    5. Voilà! You have a relative XPath!

    Now you can just delete your rule. For more complex expressions, you can use the third drop down, and select "Use a formula" - and check the Edit Xpath box in the formula editor. Get in the habit at looking at the actual XPath InfoPath is using, and you'll be surprised how much XPath knowledge you pick up. 

  • qRules SubmitToSharePointList - Walkthrough for v4.2

    qRules 4.2 is coming soon, and with it - big changes to the SubmitToSharePointList command! But wait - don't panic - if you have a 4.1 injected form that is doing what you need, the command is backward compatible. You'll be able to inject with 4.2 and use all the awesome new string, math and financial commands without worrying about making any changes to your SubmitToSharePointList command.

    Since the last walkthrough I did on this command was way back when we introduced it, I thought it was about time for a re-do. I've got another blog post that talks in general terms about the changes we've made, and one that explains the mapping file in more detail than you probably really want, so take a look at those if you are interested in any of the nitty gritty details.

    Here's a link to my very favorite SharePoint template, and one that I plan to use for this walkthrough - If you are like me and the Northwind data makes you a little misty, you'll love this SharePoint template - it comes pre-loaded with Northwind data! We will be using the Customers, Orders and Order Details lists - our form will use qRules to submit to all three of them, with just one submit connection, and just one mapping file! Fasten your seatbelts, and here we go ....

    The Form

    First, we'll create a simple form. In case you'd be happier not having to do this yourself, you can find my form here - just a warning, it is pretty bare bones, we'll add to it as we go along. Oh, if you are wondering how I was able to add multiple fields with the same name - I added a reference to the field in the Data Source Task Pane. You can learn a bit more about that here, or towards the end of your qRules user guide.

    Here's my schema:

    The Customer, Employee and Ship Via columns in my SharePoint Orders list are lookups - we'll have to handle those specially. So is the Product column in the Order Details list.

    The Mapping

    First, we'll create our mapping file. If you have not already installed the InfoPath to SharePoint List Tool form that comes with qRules, you'll want to do that first - you can find more info starting on about page 3 of your qRules user guide.

    Open the mapping tool:

    EDIT: In qRules 4.3, we changed the installation process for the full trust forms. If you are using v4.3 or later, you'll see this in your start menu:

    Clicking on qRules Forms in the start menu opens a folder:

    The mapping tool is there, along with an installer for the form and instructions on how to install the form. Once the form has been installed, you can either access it directly from the folder, or by opening InfoPath Filler and opening the form from there:

     

    Under the Define Mapping tab, attach your XSN file and enter your SharePoint URL:

    Click the Get SharePoint Lists button to expand the Mapping Definition section of the form and allow you to pick the lists you wish to map to.

    In qRules 4.2, our lists will be submitted to in the order they are mapped. In our form, the parent list is the Customers list, then the Orders list, and finally, the Order Details list. So, we will map our data in that order. Also, in our form, the Customer is not repeating, but we can have multiple orders and multiple order details.

    First, we'll map our customer information. Select the Customers list from the SharePoint List drop down control:

    Click the ... button next to the FormField:

    This will display the form fields in the task pane:

    Double click the ShPId field. We are going to use this field for the Customers list item id. It doesn't get mapped to a column - on a successful submit, qRules will populate it with the newly created item's id, so we can update our list item from our form. Click the Show button in the Options column, and select Id - your first column mapping will look like this:

    Map the rest of the fields - these are all from the Customer group in my form template schema:

    Click the widget to insert another SharePoint Mapping - we are now going to map the Orders group to the Orders list:

    We want to map our lists in the order we want them to submit, as I mentioned above, since qRules will process the mapping file sequentially (unless we specify the /mappingName parameter in the command). Because we want to submit our new Customer list item id with our Order for the Customer lookup, we want the Customer mapping to submit first, so the field we've indicated as the id is populated prior to submitting the Order.

    Here's my partially completed Order mapping:

    I have only filled out the Form Fields that can be selected using the field picker task pane at this point. This is a repeating group, so I have indicated that, and selected the group in the Repeating Group field.

    I'm going to enter the XPath for the remaining mappings by hand - that's because they need to be relative to the repeating group itself.

    My repeating group is: my:myFields/my:Customer/my:Orders/my:Order

    The other information I want is in my Customer group - I have to go up the tree twice: ../../

    That takes me from my:Order back up to my:Orders (../) and then again back up to my:Customer (../). When in doubt, let InfoPath figure it out - if I need a relative XPath and am not sure what it would be, I leverage InfoPath - here's a short post on that.

    Now, I realize that my form may be working from an unrealistic conceit, but for my scenario, my Customer address information will be my Shipping Address information. My finished mapping, then, once I have carefully hand typed the relative paths to my Customer info, looks like this:

    Notice I'm going to submit the ShpId field for the Customer to the Customer column in my Order list? That is because to set the value for a lookup column, I need to submit the item's id. When my customer is created in the Customers list, that field (/my:myFields/my:Customer/my:ShpId) will be populated with the new customer id. To link my Order to my Customer, I'll want to submit that very same id to the Customer column in my Orders list.

    Finally, we insert yet another SharePoint mapping for the template - this time, I'll be mapping to the Order Details list:

    This time, I'm including a relative path to the OrderNumber field - while my list has an Order Number column, it is not a lookup. Thus, I want to submit the actual OrderNumber value, not the id of the Order list item.

    Finally, click the Save as qRules Mapping button:

    The saved mapping will default the name to mapping.xml - I usually just leave that as is. In case you'd like it, my mapping is available here - keep in mind that even if you are using the same SharePoint template, your URL and list GUIDs will be different from mine.

    The Data Connections

    Now we will add our mapping file, mapping data connection, and submit data connection to our form. I have a rather specific way I like to add my XML resource files - I like to implicitly add the resource file instead of letting InfoPath do it, as InfoPath strips off the XML file extension when it "helps" you - since my mapping data connection schema won't change, if I add the resource file myself it makes it simpler when I decide to change the mapping.

    From InfoPath, select the Data tab on the Ribbon, then From Other Sources --> From XML File:

    Click the Resource Files button:

    Click the Add button in the Resource Files dialog, and select the mapping.xml saved earlier:

    Click OK, then Next and Finish - the default name for your mapping data connection will be mapping - just leave that as is, and leave the checkbox to load data on open selected. In the data connections dialog, the details for your data connection should look like this:

    Next, we need to add a Submit data connection to the UpdateListItems method of the SharePoint Lists web service. On the Data tab of the Ribbon, select To Other Locations --> To Web Service:

    The URL for the Lists web service is: http://YourServer/_vti_bin/lists.asmx - replace "YourServer" with your actual server name. If in doubt, test your URL in a browser, making sure you get a page that looks like this:

    After entering the URL in the Data Connection Wizard, click Next and select the UpdateListItems method:

    Click Next again and leave the parameters blank in the following dialog - just click Next and Yes on the warning:

    qRules will populate the data for the submit parameters. In the final window, I usually deselect the checkbox for using the connection as the submit default, and I generally give my connection a simpler name:

    Make a note of the name you use - including the casing - as you will need it for your command.

    Handling Special Fields

    I mentioned earlier that we would need to handle the lookup fields in a special manner - we need to make sure we are submitting valid list item ids for those fields. So, we need to add data connections in our form to the following lists:

    • Employees
    • Products
    • Shippers

    You can use the From SharePoint List button on the Data tab of the Ribbon to create these connections. Be sure you select the ID field as well as whatever field you'd like to use for display in a drop down - like the Employee Name, Product Name or Company Name. After all your data connections have been created, your form's Data Connections will look like this:

    If you need help creating a SharePoint List data connection, here's an Office Online article that may be of use.

    In the form, we'll use these new data connections as data sources for drop down controls - we need to use drop downs for the Employee, Ship Via and Product fields:

    Here's my form thus far, in case you need it - the important thing to remember is that the value for these drop down controls needs to be the list item id:

    When we test our command, I'll show you what happens if we submit incorrect data to a lookup - I know you won't want to miss that!

    Add the qRules SubmitToSharePointList Command

    Oh, finally. We are at that point where we can add our command. And guess what? This will be the shortest section of the entire blog post. First, you'll need to inject your template with qRules. Save and close your form template, and from the Start menu, navigate to Qdabra / Tools and find your qRules folder - open the injector and inject your form. If you need more detail, qRules installs with a Quick Start guide that you'll find helpful.

    After injecting, open your form in design mode, add a button to your form, and add an action rule to the button:

    The Action should set the Command node in the QdabraRules data source:

    The value we will set the Command node to is:
    SubmitToSharePointList /submit=ShPList

    Remember several thousand words ago, when I suggested you make note of the name of your UpdateListItems method data connection? This is why - the value to the right of the "=" sign needs to be the exact name of your SharePoint Lists web service data connection - including the casing. So, if yours is named MyFancySharePointSubmit your command would be SubmitToSharePointList /submit=MyFancySharePointSubmit.

    I'm also assuming you followed along and left your mapping data connection named mapping - if it is named anything else, you'll need to include the /mapping parameter (SubmitToSharePointList /submit=MyFancySharePointSubmit /mapping=MyMappingThatIRenamedToBeDifficultAboutThings  for example).

    Test Your Form!

    First, from the QdabraRules data source, drag the Error node onto your form:

    You can also drag the Success node onto the form if you like.

    Then, preview the form. Fill out some data:

    And click the Submit button.....

    If the Error node is blank (and Success is checked as true), go take a look at your lists, and do a little happy dance at your desk (it's okay, I won't tell anyone).

    Here's what my lists look like -

    Customers:

    Orders:

    Order Details:

    Hang onto your form - we'll use it again in some future blog posts. We still haven't talked about adding attachments or refreshing our form data, and I've not even mentioned updating.... So much to say, so little space. But, before you race off to other, more exciting blogs, one last thing....

    Troubleshooting

    What if your form didn't submit? You have an error? Let's take a look at an issue I mentioned earlier - that your data won't submit if you have invalid values submitting to a lookup.

    I've added the ShipVia field to my form again, but as a text box:

    I'll fill out the form partially - and deliberately put bad data in that field:

    When I click my button, I get an error:

    That little gem is courtesy of SharePoint - not too helpful, is it. When I get errors like this, I usually do a web search with the error message and the name of the web method (UpdateListItems) - generally I can find enough clues to get me headed down the right path. For the sake of future searches - The operation failed because an unexpected error occurred. (Result Code: 0x80020005) from the UpdateListItems method may mean you are trying to set a lookup column to an invalid value!

    Be sure to search this forum on errors as well, and if you have an issue you can't resolve, create a new thread in the qRules forum, so we can help you get it figured out.

    ***********************

    Hey, I did a video walkthrough on this - you can find that here.

    More posts coming soon on adding attachments to our list items, updating existing list items from InfoPath, refreshing the list items in our form, submitting only specific mappings, and other diverse topics of interest. Hang onto your form from this walkthrough, since I'll be using the same form for these future posts. I hope you are as excited about qRules 4.2 as I am!

    ***********************

    Working with Attachments
    Submitting a Single Mapping / Updating Existing Data
    RefreshSharePointListItems

  • qRules SubmitToSharePointList Command - General Mapping Overview

    Before I go into too much depth on using the SubmitToSharePointList command in qRules 4.2, I thought it might be helpful to provide a brief overview on how the command works.

    At it's most basic, the command requires:

    1. A submit data connection to the SharePoint Lists web service's UpdateListItems method
    2. A mapping file indicating the connection between the InfoPath form and the SharePoint list
    3. A data connection to the mapping file

    The mapping file is used by the command code to discern the following:

    1. The URL for the SharePoint server for the list the data should submit to
    2. The GUID of the SharePoint list to submit the data to
    3. The form field to list column connection for the data

    Here's what the mapping XML looks like for a 4.2 mapping - marked up to help you understand the data:

    In the screenshot below, I've expanded the "Products" mapping so you can see how the mapping changes when we are mapping repeating data:

    So - if there is an XPath in the RepeatingGroup node, all the other FormField values need to be relative to that XPath. That is - starting at the my:Product node (using the example above), how do we get to the my:Name field inside the group? Starting at the my:Product node, how do we get to the my:ShpId located here: my:myFields/my:Category/my:ShpId?

    And you thought this was going to be boring!

    Next up - a tutorial on actually using the 4.2 version of the SubmitToSharePointList command.

  • qRules SubmitToSharePointList Command - Changes for v4.2

    The last time I blogged at any length about SubmitToSharePointList was when we originally released the command. With the upcoming qRules 4.2 release, this command is undergoing a pretty major overhaul, and I think some update posts are in order.

    To start with, however, I want to assure you that we have worked very hard to make sure the command remains backward compatible. So, if you have a form that uses SubmitToSharePointList, and it works just the way you want it to – you can re-inject your form with qRules 4.2, and command will keep working. No changes needed on your part.

    I intend to post a series of walk-throughs for the command, but to begin with, I’d like to simply talk about what is changing.

    The Mapping Tool
    This form has been revamped to allow you to create multiple mappings for a single template within the form. This means that if your form template is meant to map to multiple lists, you can create a single mapping file.

    There are some caveats here – currently, you’ll need to map your form to the lists in the order you wish to submit them – while we have plans to simplify this even further, they are not in place for the 4.2 release. So, if you have, for example, a parent-child relationship in your form between a customer and the contacts for the customer, you’d want to map the parent first, then the child. qRules will submit (and refresh, if you use the RefreshSharePointListItems command) the mapping in order (unless you specify which mapping you wish to submit / refresh, more on that further down in the post).

    In the mapping tool, you can indicate which field you would like to store the SharePoint list item id in – this replaces the /id parameter. You do not have to specify an id field, of course – there are use cases where you wish to only submit items from your form to the list, but not leave the form connected to the list item. If you have a field that contains attachments you wish to include with your list items, you can also specify those.

    Id and attachment fields are not mapped to columns – if you select one of those options, you’ll instead see some help text on how those fields are used:

    This form still functions in a stand-alone mode as well – including with multiple mappings. So, if you have a big bunch of XML you’d like to get submitted to your lists, you can use the form on its own. As a stand-alone form, however, the fields indicated as the id fields do not get updated, nor are attachments added to your list items.

    We’ve also added an “Import Existing qRules Mapping” button – you can import any qRules mapping file with this functionality – whether 4.2 or earlier.

    The Submit Connection
    As in previous versions of the command, you'll need to create a submit data connection in your form to the UpdateListItems method of the SharePoint Lists web service. That web service URL looks like this:
    http://myserver/_vti_bin/lists.asmx
    With "myserver" replaced with your actual SharePoint server URL.

    When you create the data connection, with qRules 4.2 you no longer have to select any values for the parameters - InfoPath will fuss, but you can safely ignore it: 

    Click "Yes" to the warning and finish creating the connection - qRules will set the input parameters for you when you execute the command.

    The Command Parameters
    If you’ve used earlier versions of this command, the syntax will be familiar:

    SubmitToSharePointList /submit={name of your submit connection} /mapping={name of your mapping connection} /id={XPath to your id field – relative for repeating}

    The /id parameter is optional.

    With the new mapping file format, you can indicate the field for your id from within the mapping – that’s because if you are mapping to multiple lists, the /id parameter would be insufficient. That is – if you had mapped to a Customers list and an Orders list in a single mapping file, and included an /id parameter – which SharePoint list would be populating that id? The Customers list? The Orders list?

    By indicating the correct field for the SharePoint list item id from the mapping, the mapping file contains all the information qRules needs to populate your id field.

    Since you can now have a single data connection to a single mapping file no matter how many SharePoint lists you will be submitting data to, the command no longer requires the /mapping parameter - if you name your mapping data connection mapping. If you name it mySuperFancyMappingDataConnection, you'll still need the /mapping parameter to indicate the correct data connection for the mapping file.

    So, your command could be as simple as:

    SubmitToSharePointList /submit={name of your submit connection}

    Just one parameter, but with the ability to submit to multiple lists, and to populate your form with the new list item ids.

    If you also want to submit attachments, you’ll need to create a data connection to the AddAttachments method of the Lists web service and include the /dsname parameter. You can indicate the fields with the attachments in the mapping tool.

    Each individual list mapping will have a name – it defaults to the name of your list:

    If you wish to submit to just a single list, even though your mapping file has multiple list mappings, you can include the name in the command:

    SubmitToSharePointList /submit={name of your submit connection} /mappingName={name of your list mapping – in my screenshot above, it would be Customers}

    If you were for some reason to map the same form to the same list twice – perhaps you had two different groups that needed to submit to the same list – the form will display the mapping name for the second instance of the list so you can adjust the name to something unique:

    *************

    So, that’s the basics – the mapping tool now allows multiple list mappings for a single template. You can indicate the old /id parameter in the mapping as well as the /xpath for attachments. The command itself can be greatly simplified, especially if you name your mapping data connection mapping and want to submit to all your lists at once. You can inject 4.1 forms with 4.2 – say you need to use some of the amazing new math, financial or string commands in 4.2 with a form that has been using the SubmitToSharePointList command – without any issues… the command is fully backward compatible. I should add - if you want to use separate mappings for each list, you certainly can still do that, with multiple mapping data connections in your form. But you can use just one submit connection for all of them - no more creating a submit connection for each list.

    In the next couple weeks as we finish testing and prepare to release qRules 4.2, I’ll get more blog entries up on using the updated mapping tool and command - when qRules 4.2 is ready, you'll be ready too!

  • qRules: Use SubmitToSharePointList with a Calendar

    The qRules SubmitToSharePointList command is a great way to get information from your InfoPath form into a SharePoint List - however, there was recently a thread in the forum where a qRules user was having problems submitting form data to a Calendar. The error was:
    "Errors occurred submitting to list: Invalid date/time value. A date/time field contains invalid data. Please check the value and try again."

    Turns out the date time for the Calendar list required a different format:
    yyyy-MM-ddTHH:mm:ssZ

    The qRules format data command can return a date with a custom format - so, if you need to submit to a calendar, you may want to leave regular date time controls on your form for your users to select with, but have another field that is mapped to your SharePoint list. Set that field to the result of the FormatDate command, so that your Start and End date have the correct format prior to submitting.

Copyright © 2003-2017 Qdabra Software. All rights reserved.
View our Terms of Use.