Hilary Stoupa Sign in | Join | Help in Hilary Stoupa Tutorials (Entire Site) InfoPath Dev InfoPath Dev is dedicated to bringing you the information and tools you need to be successful in your Microsoft Office InfoPath development projects. Home Blogs Forums Photos Downloads This Blog Home Syndication RSS Atom Comments RSS Recent Posts DBXL Migration Tool - Handling errors with the GetListItems Method on Reshred All Power Automate (Microsoft Flow) Demo Videos Windows 10 May Update & List Form Issue Content Type Update Issue in SharePoint 2016 FormsViewer & SharePoint List Data Connections Tags 2007 Object Model Attachments C# Charts Code Data Mapping DBXL v2.2 Email Email Submit Excel FormsViewer GetListItems InfoPath Input Parameters JScript MigrationTool OData qRules RefreshSharePointListItems Repeating Tables Secondary Data Connections Secondary Data Sources SubmitToSharePointList Unique Id XPath Archives September 2022 (1) October 2019 (1) June 2019 (1) October 2018 (1) January 2018 (1) April 2017 (2) June 2016 (1) October 2015 (1) August 2015 (1) July 2015 (1) February 2015 (1) April 2013 (1) March 2013 (1) January 2013 (2) July 2012 (1) June 2012 (3) May 2012 (5) April 2012 (1) March 2012 (1) February 2012 (3) January 2012 (3) December 2011 (3) July 2011 (2) January 2011 (1) November 2010 (2) August 2010 (1) May 2010 (1) February 2010 (1) January 2010 (1) November 2009 (3) October 2009 (1) September 2009 (1) June 2009 (1) May 2009 (2) March 2009 (2) January 2009 (1) November 2008 (1) October 2008 (1) August 2008 (2) July 2008 (4) Hilary Stoupa Sort Repeating Items On-the-fly - with qRules! We recently had a bit of a challenge with one of our internal forms - we wanted to easily be able to re-order some repeating rows, and the approaches we'd tried thus far (using the out of the box InfoPath Move Up and Move Down menu options, little arrow buttons that used qRules to change the value of a field for order and re-sorting, etc.) were not exactly making our collective hearts sing. A bit of a re-think was in order! I like the way SharePoint lets me sort columns in a view: I can indicate the position I want, and the other columns automatically get their new order number based off the position I've selected: Now, I confess - I'm not crazy about a dropdown for this, so I didn't bother with that in this implementation - however, I will address some basic error handling around invalid data entry. The form samples attached to this blog post use a trial of qRules that expires 3/15/2013 - if you are trying this after that date, you'll need to re-inject with a new trial - here's a link to the trial download. Also, the form samples are InfoPath 2007 and are not browser forms - you can try this in IPFS if you'd like, but I am using attributes to hold some data to help with re-ordering, and if you decide to try to implement this in the browser, I'd recommend using elements instead of attributes, since I have seen some event bubbling in IPFS with attributes in the past (i.e. - a changed attribute causing logic on its parent element to execute). I'm starting with this form. I've added default data so that I don't have to bother filling it out as I test it: The initial data source is relatively simple: I like to use an XML secondary data source for logic fields when possible (that is, fields needed for rules, but not really relevant to the form's data) - so here's the XML: Add a secondary data connection to your form that uses this XML: Here's the form with that added, in case you need it. Okay - I just said above that I like to use secondary data sources for fields that are just for logic and here I go breaking my own little rule - but sometimes you have to make exceptions for repeating data because you have logic that needs to be executed on this instance of the group. In this case, I am adding 3 attributes to my Order element to help me in my endeavors: Here's the form again with those attributes added. Since I'm using default data, I've set the initialOrder for each of my existing rows manually. However, in order to handle for newly added rows, we need a rule to set the Order and the initialOrder when a new row is added. I've added a rule to the repeating Book node for this: All the pieces are in place, so let's take a look at the rules and test it out. Our goal is to be able to change the order number for a row and: Have the other rows Order numbers update as needed Resort the rows to put them in the desired order The first rule I'm going to add is on the Order element - I've named it Trigger Increment, and I'm adding a condition on it - I only want it to run when the EditingOrderPosition field in my secondary FormLogic data source is blank: I want this rule to execute only when I change the Order field myself - I'm using the EditingOrderPosition condition to prevent creating a loop, because I know that my rules are going to cause other instances of the Order field to change. So, the first action I'll add to my rule is to set that field's value: I am setting it to the count of preceding-sibling Book nodes - I'll use this in another condition later. If your repeating data has a unique identifier, you could also leverage that here - set EditingOrderPosition to the field with the unique identifier and then, in the condition check on the editing attribute, ensure the row does not have the same unique identifier. Next, I set the increment attribute to the Order field minus the initialOrder attribute - the initialOrder attribute contains the original value of the Order field, and after the user changes the Order field, the difference between the two will let us know how we need to increment our other fields. When the form is running, this is the point where the rule on the increment attribute will fire, so let's go take a look at those and we'll come back to the rules on Order after we walk through the rest of the logic. There are two rules on the increment field - one if increment is less than zero and one if it is greater than zero. I've also included a 2ds field named Sorting in my condition - I use that when I sort the data to prevent rules from firing during the sort (however, I've done a little additional testing, and it appears it is not necessary at this level - only at the Book level if I wanted to prevent that logic from firing). The Negative Increment ruleset (runs if the value of increment is negative) looks like this: First, I'm setting a helper field in my 2ds to the correct amount to increment the field. Then, since our Order field is less than its initialOrder (i.e., the increment attribute is negative), I need to add one to all Order fields that are greater than or equal to the current value of the Order field I just modified and less than the initialOrder attribute of the Order field I just modified. So, if my Order had the value of 4 (with the initialOrder also being 4) and I change Order to 1, any field with a value greater than or equal to 1 and less than 4 needs to have 1 added to it. Whew. In the rule above, I am setting the editing attribute for all fields that meet that condition to true, to execute another rule that increments the row order: This adds the current OrderIncrement to the Order - but note the condition to that will prevent the rule from executing if the field is the same that initiated the sequence (mentioned above - if you used a unique identifier instead of a count of preceding-sibling, you'd use that for the condition instead of the count): A second rule re-sets the editing field back to false if it is true: Back on the increment field, the rules for Positive Increment are similar to the ones for Negative Increment: The condition in this case is that the increment attribute is greater than 0 (along with Sorting being blank), and I set the OrderIncrement helper field to -1. In this case, since our Order field is greater than its initialOrder (i.e., the increment attribute is positive), I need to subtract one from all Order fields that are less than or equal to the current value of the Order field I just modified and greater that the initialOrder attribute of the Order field I just modified. So, if my Order had the value of 1 (with the initialOrder also being 1) and I change Order to 4, any field with a value less than or equal to 4 and greater than 1 needs to have 1 subtracted from it. Guess what? We are finally back to the rules on the Order field! The remaining actions in the ruleset are pretty straight-forward: I set my helper node, EditingOrderPosition back to blank. I set my Sorting node to true (to prevent rules firing during sorting that I don't want to have fire), and then execute a qRules command to sort the rows based on Order: Finally, prior to my Trigger Increment ruleset, I have a rule to handle for invalid data. The condition ensures that the user has entered a positive number: If they have not, a message is displayed and the Order is set back to the initialOrder: Note the use of the "Don't run remaining rules if the condition of this rule is met" checkbox - we don't want to execute the Trigger Increment rule unnecessarily. Here's the final form - I encourage you to download it to walk through the rules, since this blog post is not so much a step-by-step how-to (that would have been mind-numbingly long) and more of a guideline. A few more points of interest on this form - so far, I am also able to use the InfoPath Move Up and Move Down right-click menu widgets with this (due to the re-numbering logic on the Book node) and the right-click Insert options also work well - adding a new row in-between existing rows allows all Order nodes to update their values. However, I don't have anything to handle for delete / cut. The logic is somewhat self-correcting - if you delete a row the numbers after it will be off only until a new row is added or the rows are re-sorted, but it is a weakness I wanted to highlight. If you can think of a good way to handle for this, leave a comment and let me know! When we use qRules SortTable, the nodes that are being sorted are actually deleted and re-added. I could have used my Sorting helper node to prevent the rules on the Book node from firing - but then I realized I could leverage this behavior to reset my initialOrder attribute as well as handle for user entries that are greater than the number of items in the table - that is, if a user enters a too-high number, the rules on Book that fire during the sorting will correct the number. Also - I think this could be implemented without qRules (although I haven't tried) using this method to set the correct editing attributes and manually modifying the view to include xsl:sort and a preserve code block (this won't be browser compatible). Posted Jan 09 2013, 09:47 AM by Hilary Stoupa with no comments qRules RefreshSharePointListItems - Walkthrough for v4.2 In version 4.2 of qRules, we made a number of changes to simplify the SubmitToSharePointList command. Those changes also impact the RefreshSharePointListItems command - in a good way, I'm glad to say. Like SubmitToSharePointList, you can now run the refresh command for multiple lists with a single rule action, and fewer parameters are required. In case you've not seen them, here are my blog posts on these changes thus far: qRules SubmitToSharePointList Command - Changes for v4.2 qRules SubmitToSharePointList Command - General Mapping Overview qRules SubmitToSharePointList - Walkthrough for v4.2 qRules SubmitToSharePointList - Walkthrough for v4.2 - Working With Attachments qRules SubmitToSharePointList - Walkthrough for v4.2 - Working With Existing Data & Submitting a Single Mapping In this post, I'll be starting with the form we left off with on the Working With Existing Data post - here's a copy you can use. Keep in mind that my form & mapping is pointed to sites in my environment, so you'll need to modify that, and it is using the current 4.2 trial version of qRules - which expires 9/15/2012. So, you'll want to re-inject with a new trial if you are following this at a later date. The Basics First off, in case you aren't familiar with the refresh command - let's talk about what it does and how it works. This command is always used with forms that are set up to use the SubmitToSharePointList command where a form field is set to maintain the SharePoint list item ID. The command should generally be run with a rule on QdabraRules data source finshedLoading attribute - a condition should be used so that the rule only runs when that attribute is equal to true. When you submit your list items, SharePoint returns the newly created list item ID to the field you have specified via your mapping: If you look at your mapping.xml file in a text editor, you'll see the IsId attribute is set to "true": On a successful submit, qRules is going to update the indicated field with the value of the associated list item ID - all items in a SharePoint list have a unique integer value in the ID column. By saving that value in the XML for your form, the next time the SubmitToSharePointList command runs, qRules can perform an update on the existing item rather than creating a duplicate. The RefreshSharePointListItems also leverages this field - it adds to your form the ability to update all your SharePoint list connected items with the latest data from the list, so your users can update either the XML or the list - they'll stay in sync when your XML is next opened. It also adds the ability to propagate deletes - both from the XML to the list and vice versa. This command has two modes - Report and Update. Because the command has the potential to delete or change data, the default mode is Report. That means you have to explicitly state that you want the list data to overwrite your form data. A new attribute has to be added to your fields where you are keeping your list item IDs - qRulesLastModified. This attribute stores the date and time the form data was last saved to the list. When the refresh command runs, qRules compares the XML date & time values with the Last Modified column for the list item. If the list item was updated more recently, qRules overwrites the form values, using the mapping.xml to know which column writes to which field. If there is an item in the XML that has a list item ID, but can't be found in the list (and the command is run in Update mode), qRules deletes the data from the XML. When the RefreshSharePointListItems command first runs, it also creates a list of the items with SharePoint list item IDs currently in the form. When SubmitToSharePointList is next run, if any of those items have been deleted from the XML, they will also be deleted from the SharePoint list. Back To Work Hopefully that explanation didn't bore you to death or baffle you. But we'll walk through it, and the background above might make what we are going to do seem a little less obscure, okay? Since we are picking up where we left off last time, our form already has fields for the list item IDs, and we have our submit commands set up. So the first thing we'll do is add the qRulesLastModified attribute to those. When I created my schema, I referenced the ShPId node so that I could have same name in a number of locations: Referencing a node is simple - add the node in one location you want it. Then right click the node and select Reference: In the next dialog, select the group you'd like to add the field to - simple as that. Now the cool part - I just have to add the qRulesLastModified attribute to one instance of my ShPId field, and they all get it: Form Changes We first need to add an additional data source connection. qRules uses an owssvr.dll data connection to return each item for refreshing in preview or in Filler. If deployed as a browser form, it uses the SharePoint object model - but since we are going to want to be able to preview and test our work, we are going to add an owssvr.dll data connection. The data connection is to an XML file, and the URL looks like this:http://yourserver/yourweb/_vti_bin/owssvr.dll?Cmd=Display&List={guid}&XMLDATA=TRUE&noredirect=true Your URL needs to be valid so that you can create the data connection, but qRules will use the server URL from your mapping and the list GUID from your mapping (since, after all, that will be the server and list you submitted to). So, in my case, the URL I'll use looks like this:http://qshp2010/nw/_vti_bin/owssvr.dll?Cmd=Display&List={6E986BE0-F112-4F61-8931-C1891E239E74}&XMLDATA=TRUE&noredirect=true The GUID is to my Customers list - again, during the refresh process, qRules will ignore the GUID in the URL and use the one from the mapping file instead, so all that matters is that the URL is valid. You can test your URL in the browser - depending on your browser, it will either return XML in the browser or give you an option to save the returned XML. We add a new XML data connection to the form: Use your owssvr.dll URL for the location: Be sure to select the "Access the data from the specified location" radio button: And de-select the checkbox that queries the data on load: You'll notice I've named my data connection "Refresh" - name yours whatever you like, but as usual, we need the exact name, including the correct casing, for our command parameters. Adding the Command Now that we've added our qRulesLastModified attribute and our data connection, we can add the command. We are going to put the rule for the command on the QdabraRules finishedLoading attribute, with the condition that the attribute is equal to true (see *Note* at end for some additional information on this): We set the Command node of the QdabraRules data source to the RefreshListItems command:RefreshSharePointListItems /dsname=Refresh Did you name your data source something else? If so, you'd need to use whatever your data connection's name is for the /dsname parameter. If your mapping data connection is named anything other than "mapping", you'll need to include the mapping parameter as well:RefreshSharePointListItems /dsname=Refresh /mapping=MyVeryFancyMappingFile Here's a copy of my work in progress, in case you need it. As I mentioned earlier, the default mode for this command is Report - this means any changed items will be reported to the QdabraRules Results node. Put that node on your form so you can see the outcome. Testing in Report Mode Guess what? We are already ready to test! Once you have the form set up to submit list items, adding refresh functionality isn't much work. To test, we'll preview and submit some items to our lists. We'll save our XML that we created in preview, change some of our list items, and then preview again, using our existing XML so we can see what the report result is. So, preview your form, create or select a customer, and add some orders and order details: Submit your list items, using the submit button, and then use File --> Save As to save the completed XML. Close your form. You can open the XML in a text editor, and you'll see that your list items have a SharePoint list item ID along with a value for the qRulesLastModified attribute: We need to change some of our new list items from SharePoint - here's my original details: And their modifications: Set your form template up to preview with existing data, using the XML you saved in your earlier preview (how-to can be found here): Remember, we have not included the /mode parameter at this point, so by default our refresh command will only report on changes. Preview your form again, and take a look at the Results field - you'll find text like this: Testing in Update Mode Keeping your template set up to preview with your existing data, add the /mode parameter to your command: Preview again - this time, you'll have the same report, but your items that you changed in SharePoint should be refreshed to match: Euw. Big ugly validation errors - what happened there? Well, my form has a data type of whole number for the Quantity field, but SharePoint is returning the data with decimals - that's okay, I can take care of that with a rule on the Quantity field: Preview again, and: Deleting Leaving the preview open, where the refresh command has already run, try deleting existing items and adding some new ones. Remember, the deletes will occur when the SubmitToSharePointList command is executed, so be sure to click your submit button to run the command. Be sure you remove the path to the XML you've been previewing with before you close your template - otherwise, you will do what I always do - delete the XML and be completely confused when your form refuses to preview. *********************** That's it! Here's my final form if you want it. The best part of the qRules 4.2 changes, I think, is that if I need to submit data from my form to another list (I'm sure people in your organization never change requirements on you... ) I can export the mapping from my form, import it into the mapping tool, and add the list mapping. I just need to replace the mapping in my form with the new one, and with no other changes, my current submit and refresh commands will now include the new list. That makes changes easier and faster for the form developer, so hooray for that. *********************** *NOTE*: As you may already know, in InfoPath, rules run before code. qRules is code, of course, but code that is accessed via rules. We want to make sure the qRules code has fully loaded prior to executing any commands. So, if you have a command you would like to run on a form load rule, you should instead run it on the finishedLoading attribute. The qRules code sets that attribute to true when it is fully loaded. Posted Jul 05 2012, 02:49 PM by Hilary Stoupa with no comments Filed under: qRules, SubmitToSharePointList, RefreshSharePointListItems qRules SubmitToSharePointList - Walkthrough for v4.2 - Working With Existing Data & Submitting a Single Mapping With qRules 4.2, we've introduced a major improvement to the SubmitToSharePointList command - especially if you have been submitting to multiple lists. The first walkthrough can be found here, and then in this post, we talked about how to save attachments with our list items. Picking up where the second post left off, we are now going to modify our form to submit to one of the individual lists we've mapped. Thus far, we've only submitted to all the lists. Also, because I am frightfully lazy, we are going to start pulling some existing data into our form so we can edit it and update our existing customers. We'll start with the same template we have been using for the previous two walkthroughs - mine is available here, if you'd like it. Keep in mind that my form & mapping is pointed to sites in my environment, so you'll need to modify that, and it is using the current 4.2 trial version of qRules - which expires 9/15/2012. So, you'll want to re-inject with a new trial if you are following this at a later date. Data Connections First, we need to add 2 data connections to our form - one that we'll use for a customer drop down, and the other that we'll use to return all the data for a given customer. The first one, which I'll call the CustomerNumber data connection, is to the Customer list, and returns these columns: I sorted by Company Name to make the drop down I'll use this data for less annoying. Return data on load for this connection only: Now, create another data connection to the Customers list: Select at least all of the columns that the form is mapped to submit data to. This time, deselect the checkbox to query data on load: Form Changes In my form, I've decided to let the user choose an existing customer or just fill in the fields if they want to create a new customer. I've added a drop down that is bound to the /my:myFields/my:Customer/my:ShpId field - you may recall from the earlier walkthough that when mapping, we can indicate a form field that will hold the SharePoint List item ID after a successful submit- we are going to leverage this field to allow our users to open a new form and edit existing customers. Set the drop down to use the CustomerNumbers data source, with the ID for the value and the Company Name for the display: Here's my form up to this point, in case I've lost anyone. After the user selects the Customer, we'll need to query the Customers data source for that customer's data and then populate the main data source nodes with those values - this can be done with ordinary InfoPath rules. While this could be done with rules set on the ShPId field the drop down is bound to, that would make things slightly more complex for us - we'd need to make sure these rules don't run when qRules is populating that field. So, for the purposes of this walkthrough, I'm going to add a button and put these rules on the button. However, you have other options - like: Bind the drop down to another field, and execute the rules from that field - be sure you also set the form field for the ID if you take this approach Have a helper field in a 2ds that set when the form is about to execute the SubmitToSharePointList command, and base conditions off the value of that field to prevent execution of rules when qRules sets the ID field for a new item For my form - I'm going with a button. My first rule action sets the Customers query ID field: To the value of the /my:myFields/my:Customer/my:ShpId field: Then, I execute the Customers query: Now, since the user selected the ID of the customer she wants, I could have returned all the customer data and used filtered XPath to get the values I want - and if you are using SharePoint & InfoPath 2007, this would be the approach you would probably take (or use the qRules FilterOwssvr command to get back just the single record you need). Since I only will have one record in my Customers data source, I don't have to use filtered XPath to populate my other form values. That makes me happy. In the rules above, I am setting main data source Customer group nodes to values from my Customers list. Preview Time to check our work - preview the form, and select a customer from the drop down - does all the customer's information get populated correctly? If not, check and make sure you are returning data to your Customers data source - and here's my form in progress, in case you need to take a look at it. Submit a Single Mapping Now that we have added the logic to pull an existing customer into the form, we can add a button to save just the user's Customer changes. The big trick here is making sure that in the mapping we have identified a field to use for the ID: And making sure we have in our form set that field to the SharePoint List item ID that we wish to update - in my form, I've done this by binding a drop down to the field ( /my:myFields/my:Customer/my:ShpId) and then using a data connection to my target list for the values - so that the user will be picking a valid item ID from the drop down. Our original submit command was:SubmitToSharePointList /submit=ShPList We then added to it for adding attachments in the last walkthrough. I'm not going to be adding attachments here, just editing my existing customer - so I only need to add the mappingName parameter to indicate which mapping I want to submit:SubmitToSharePointList /submit=ShPList /mappingName=Customers I'm going to add a region to this customer, since it was missing when I pulled his information into my form: After clicking my button with the qRules command on it, I can see the region has been added to the customer: If you would like to verify that only the Customer is submitted, add an Order and use the command that submits only the Customer data - you'll find the Customer is added / updated and the order is not. If you need my final form, here it is. *********************** There you have it - you can make this more interesting by allowing the user to copy in Orders or Order Details - using CopyTable or Insert - so those could be updated. Of course, you'd probably be submitting the XML to a form library in a real form - so you would open the form to update the order. But what if someone changed the list item? What if you want your users to be able to edit from the form or the list, and you want to keep them in sync? Stay tuned - next up is a walkthrough on the RefreshSharePointListItems command! *********************** SubmitToSharePointList WalkthroughWorking with AttachmentsRefreshSharePointListItems Posted Jun 27 2012, 10:33 AM by Hilary Stoupa with 7 comment(s) Filed under: qRules, SubmitToSharePointList qRules SubmitToSharePointList - Walkthrough for v4.2 - Working With Attachments In the previous walkthrough for the SubmitToSharePointList changes, we submitted data to three lists, all with a single mapping, a single submit connection, and the new, much simplified command syntax. If you didn't hang onto your form template from last time - here's one that you can start with - mind you, the form is pointed to my development SharePoint sites, so just about everything will need to be repointed to your environment, but at least you won't have to start all over. Also, this form is injected with a qRules trial - so if you download it after 9/15/2012, you'll want to re-inject it to enable qRules again. Form Changes First, we will need to add a field to our form data source for attachments. I think I'm going to add that to my Customer - it seems like I may want to attach a filled out credit application, or some other file related to the customer: I've set the field to be repeating, since I want the user to be able to add multiple files. Now, I have a decision to make. There are two options when adding attachments to my list item - I can keep the file in my form XML and add it to the list item in SharePoint or I can simply upload the file to my list item and remove it from the XML. Since I don't want to keep the file in two places, I'm going to take the second option - I want qRules to remove the file from my form after saving it to my list item. To do that, all I have to do is add two attributes to my attachment node - qRulesLink and qRulesFilename: The presence of these attributes on the attachment node indicates to qRules that you want to have the file removed after it has been uploaded (like the functionality of the SaveToSharePoint command, only for a list instead of a library). Mapping Changes Next, we need to modify our list mapping file to indicate that we have an attachment field. Now, if you have worked with the SubmitToSharePointList command before, you may have had to re-do your mapping each time you wanted to make a change. We've improved that experience in qRules 4.2 - we can now import our mapping into the mapping form. So, first save your changes to your form template. Then, from the Data tab of the Ribbon, select Resource Files: From the Resource Files dialog, click on your mapping.xml file: And click the Export button to save the file. Open the mapping tool from the Start menu: Select the Define Mapping tab. There is a button toward the bottom - Import Existing qRules Mapping - click that: You'll get a warning about the form deleting existing mappings, which you can click OK on, and then select the mapping file you just exported from your form. Your existing mapping will be imported - all that remains is to attach your template to the Source XSN field - much better than remapping every time, don't you think? In the mapping to the Customers list, select Add mapping: Select the new Files node that was added earlier for the Form Field and click the Show button in the Options column: Select the Attachment checkbox for the Files node mapping: And click the Save as qRules Mapping button at the bottom of the form to save the mapping.xml. If you maintain the same name as the original file you exported, you can simply re-add the file as a resource file - no need to walk through the data connection wizard again. From the Data tab of the Ribbon, select Resource Files again to open the Resource Files dialog. Click Add and navigate to the mapping file you just saved. InfoPath will prompt you: You want to replace the existing file, so leave the default selection and click OK. You shouldn't have to modify your mapping data connection - the schema of the XML file will not have changed at all, simply the data. As an aside, you can always verify what file is used by your data connection in the Data Connection dialog: Add a Data Connection We need to add a Receive data connection to the AddAttachment method of the SharePoint Lists web service. On the Data tab of the Ribbon, select From Web Service --> From SOAP 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 AddAttachment method: Click Next - no need to set any of the query parameters, so click Next again, then one more time - in the final screen of the dialog, deselect the "Automatically retrieve data when form is opened" checkbox and leave the default name for the data connection - you can name it something else if you like, but keep track of the name, because we will need it when we modify our command. Add Controls to the Form Add controls to your form to allow the selection of files. I like to add a file picker and a hyperlink, with the file picker inside a section, and set to hide if the qRulesLink attribute is not blank. Here's a copy of my work in progress, if you'd like to check it out. For the hyperlink, I am using the qRulesLink attribute for the link, and the qRulesFilename attribute for the display: Modify the Command In the first walkthrough, we added a submit button to our form with the qRules command:SubmitToSharePointList /submit=ShPList Now, we need to modify this command to include our attachment files when we submit to the Customer list:SubmitToSharePointList /submit=ShPList /dsname=AddAttachment If you named your AddAttachment data connection something different, you need to modify the command above to use the name of your data connection:SubmitToSharePointList /submit=ShPList /dsname=MySuperSpecialAttachmentDataConnection Remember, case cOunTs - you need to use the exact same spelling and casing. Test the Form As always, be sure you have the QdabraRules Error node somewhere you can see it prior to testing. Preview your form, add a new customer, and add some files for the customer. Click the Submit button: If everything is set up correctly, you'll now have links in your form instead of files - and in your list item: You will have attachments. Extra - Add Delete Logic! So - this is all well and good. Provided your users never ever make mistakes. But every now and again, someone may attach and save the wrong file. It would certainly be nice to allow the user to delete the file from the form, wouldn't it? No qRules required for this - just add another data connection to the DeleteAttachment method of the SharePoint Lists web service - exactly the same way as you added the AddAttachment method above: Don't bother filling out any of the query parameters, and deselect the option to automatically execute the data connection on load (just like we did for the AddAttachment data connection). Add a button to your form next to your hyperlink control: Add a conditional formatting rule to the button to hide it if the qRulesLink attribute is blank: Add an action rule to the button. There are three fields we need to set in the DeleteAttachment data source queryFields: I like to get the listName from the mapping file. MSDN states that the listName parameter can be the list GUID or name. We have the GUID in our mapping file - it is in the Customers mapping: So, I'll set that field to the ListCollection node in my mapping file where the mappingName is equal to Customers: Next, I set the listItemID parameter to the field I am storing my SharePoint item ID in - in this case, that would be /my:myFields/my:Customer/my:ShpId: Then I set the url parameter to the qRulesLink attribute: Don't forget, we need to actually execute the query (I always forget that rule action, leaving myself perplexed when my what-ever-it-is doesn't work): In a perfect world, our web service method would return some useful information to us - sadly, it doesn't (or at least not anything that InfoPath is going to tell us about). If the query fails, any rule actions after it won't run, so I generally work on the assumption that if my query succeeds, my attachment will have been deleted, and add two more rule actions to clear out the qRulesLink and qRulesFilename attributes. However, you do have some options for error handling. Use the qRules QueryData command to execute the DeleteAttachment query - it will handle any errors and return them to the QdabraRules QueryDataError node - you could then add some conditions around clearing the qRulesLink and qRulesFilename attributes Or, after executing the data connection, you could query the GetAttachmentCollection method of the Lists web service - check the value of the /dfs:myFields/dfs:dataFields/tns:GetAttachmentCollectionResponse/tns:GetAttachmentCollectionResult and see if it contains the value of the qRulesLink attribute My final ruleset looks like this: I've simply chosen to let InfoPath return a query error in this case - and I added a condition to only run if the SharePoint ID field is populated. *********************** So, now our form submits to three related SharePoint lists. It can include attachments. The user can delete attachments. Here's the final form, in case you want to take a look at any of the logic. Next up - submitting just specific mappings. Hang onto your form and your test site! *********************** SubmitToSharePointList WalkthroughSubmitting a Single Mapping / Updating Existing DataRefreshSharePointListItems Posted Jun 25 2012, 11:25 AM by Hilary Stoupa with 2 comment(s) Filed under: qRules, SubmitToSharePointList, Attachments qRules 4.2 and useBrowserApi node We are getting ever closer to releasing qRules 4.2 and I wanted to post briefly on a new node we've added to the QdabraRules data source - it is called useBrowserApi and has a default value of "false". Here's what it does and why it is there: With qRules 4.1, we modified a lot of the SharePoint related commands to use the SharePoint object model instead of web services if the form was opened in the browser. We added a node called useWebServices, just in case this change affected any forms upgraded from earlier versions of qRules. But the other day, we suddenly saw an issue where sometimes qRules was not correctly switching between the SharePoint object model code and the web service code, and this caused some issues in our Office 365 forms (which can't fall back on using web services). That code has been addressed in 4.2 and we are now unable to reproduce the issue in our environments, however, it made sense to give form designers a way to switch this off and indicate that they would like to always use the SharePoint DLLs Now, if you ever have issues with a form failing in the browser because it is not using the SharePoint object model, you can set useBrowserApi to true in order to make the form always use the SharePoint object model based code. The logic, then, as of qRules 4.2, is as follows: If the useBrowserApi node in qRules data source = true, we use the SharePoint DLLs. If not 1, and if Application.Environment.IsBrowser = true or Application.Environment.IsMobile = true, we check to see if the useWebServices node in the qRules data source is true: If useWebServices = true, we do NOT use SharePoint DLLs If useWebServices = false, we DO use SharePoint DLLs If not 1, and if not 2, we do NOT use SharePoint DLLs If useBrowserApi is set to true, the SharePoint commands like SubmitToSharePoint will fail in Filler and in Preview - unless the form is opened on a machine with SharePoint installed, of course. Posted Jun 21 2012, 10:38 AM by Hilary Stoupa with no comments 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: Find the field or group you need to get the relative path from: 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. Create a condition - for the first drop down, select the field you need the relative path to: Change the first drop down to "The expression": 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. Posted May 31 2012, 09:14 AM by Hilary Stoupa with 1 comment(s) Filed under: XPath, InfoPath 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 AttachmentsSubmitting a Single Mapping / Updating Existing DataRefreshSharePointListItems Posted May 30 2012, 05:37 PM by Hilary Stoupa with 8 comment(s) Filed under: qRules, SubmitToSharePointList 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: A submit data connection to the SharePoint Lists web service's UpdateListItems method A mapping file indicating the connection between the InfoPath form and the SharePoint list A data connection to the mapping file The mapping file is used by the command code to discern the following: The URL for the SharePoint server for the list the data should submit to The GUID of the SharePoint list to submit the data to 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. Posted May 30 2012, 02:20 PM by Hilary Stoupa with 1 comment(s) Filed under: qRules, SubmitToSharePointList 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 ToolThis 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 ConnectionAs 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.asmxWith "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 ParametersIf 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! Posted May 30 2012, 02:00 PM by Hilary Stoupa with 2 comment(s) Filed under: qRules, SubmitToSharePointList 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. Posted May 24 2012, 11:50 AM by Hilary Stoupa with no comments More IPFS Hijinks! This one was a treat. Worked flawlessly in preview, of course. The admin approved browser form had some buttons that were switching views. The IPFS error was: An editing action referred to a field or control that cannot be found in the current view. The log files helpfully supplemented this with: The StateManager is disposing and calling ReleaseLockedStates() (Count=0) I added new buttons and was able to switch views without error - the original buttons had similar actions and had been copied / pasted from one view to the next - they'd maintained the same control ID. So, I changed the names on the buttons - just added "a", "b", "c" etc. And the error went away. I don't know whether to be happy that I found the issue, or sad that it was so hard to find and the error messages were so meaningless. Posted Apr 19 2012, 11:02 AM by Hilary Stoupa with no comments Sandboxed vs. Admin Approved Just wanted to quickly post this link, which describes the differences between publishing sandboxed vs. admin approved solutions. I hadn't realized that sandboxed forms can't use email submit connections! From the MSDN page: Publishing Form Templates as Sandboxed Solutions Publishing a form with code as a sandboxed solution is no different from publishing any other form to a document library. Just use the publishing wizard as usual and your form will be uploaded to the server and will operate in the sandbox. Note that there are certain restrictions to deploying your form as a sandboxed solution: Must be an InfoPath 2010 form. Must use C# or Visual Basic as the programming language. Cannot submit to e-mail data connections. Cannot have properties promoted for part-to-part connections. Must not have any managed meta-data controls or data connections. Posted Mar 21 2012, 03:57 PM by Hilary Stoupa with 2 comment(s) And yet another qRules video! There is a form available here for download that demos the techniques in the video - the video outlines using qRules to copy complex repeating data between two data sources. CopyTable works well in simple scenarios, but if your data structures don't match, or have nested groups, you may find the video and sample form helpful. Posted Feb 15 2012, 05:24 PM by Hilary Stoupa with no comments More qRules Video Goodness So, I've got 3 recent qRules videos up on YouTube: Introduction to Qdabra qRules - basics on installing and using qRules - SaveToSharePoint and Sandbox - how to use the SaveToSharePoint command in an IPFS form without full trust qRules - Submit To Multiple InfoPath Lists - use a single InfoPath form to add or edit existing list items in multiple lists Hope they help with your qRules forms! PS - the sample form used in the Submit To Multiple InfoPath Lists is available here for download. It points to my dev SharePoint, and has a trial version of qRules installed, but you'll be able to get a good idea of the logic used from the sample. Posted Feb 03 2012, 04:45 PM by Hilary Stoupa with no comments IPFS & qRules - the saga continues I wanted to note a few things I've run into using qRules in Browser forms, along with some troubleshooting ideas. Postback - if your form works in client (preview) but not when opened in the browser, check postback on your controls that are setting commands first. Set postback to "Always" on controls that execute qRules commands. Errors - put the error node from the QdabraRules data source onto your form, and admin approve (or sandbox) the form again. See if an error is being returned. Data Connections - Permissions can be different. If your data connection works in client, it may not in IPFS. Try converting to UDCX (and be sure to approve the UDCX files). If it is a data connection that qRules is using for something, try executing the data connection outside of the qRules command, see if your form shows and IPFS error with a correlation ID you can use to track down the issue in the logs. Bubbling - I've had instances where rules on nodes in secondary data sources will fire when an attribute of the node changes, or where rules on a group fire when a new instance of the group is inserted - these can be hard to track down. Try disabling rules on attributes / child nodes and see if your outcome changes. Then you can start planning a work-around. Spaces - I saw an instance where a SubmitToSharePointList command failed in the browser because of an extra space at the end of the name of the mapping data connection. In client, the space was trimmed. In IPFS, it was considered to be part of the data connection name, and we had an error about the data connection not existing. It can be hard to track some of these things down in IPFS - you can't just set a rule on the Command field to pop up a dialog. What I do is add another field, temporarily, and each time the Command (or Error, or whatever field I want to track) changes, set the trouble-shooting field to its current value concatenated with the new value. This can give me a list of commands that have run (or errors that have occurred) and allows me to track back through the logic to find inconsistencies between browser and client. Posted Feb 03 2012, 04:35 PM by Hilary Stoupa with no comments More Posts « Previous page - Next page » Copyright © 2003-2019 Qdabra Software. All rights reserved.View our Terms of Use.
We recently had a bit of a challenge with one of our internal forms - we wanted to easily be able to re-order some repeating rows, and the approaches we'd tried thus far (using the out of the box InfoPath Move Up and Move Down menu options, little arrow buttons that used qRules to change the value of a field for order and re-sorting, etc.) were not exactly making our collective hearts sing. A bit of a re-think was in order!
I like the way SharePoint lets me sort columns in a view:
I can indicate the position I want, and the other columns automatically get their new order number based off the position I've selected:
Now, I confess - I'm not crazy about a dropdown for this, so I didn't bother with that in this implementation - however, I will address some basic error handling around invalid data entry. The form samples attached to this blog post use a trial of qRules that expires 3/15/2013 - if you are trying this after that date, you'll need to re-inject with a new trial - here's a link to the trial download. Also, the form samples are InfoPath 2007 and are not browser forms - you can try this in IPFS if you'd like, but I am using attributes to hold some data to help with re-ordering, and if you decide to try to implement this in the browser, I'd recommend using elements instead of attributes, since I have seen some event bubbling in IPFS with attributes in the past (i.e. - a changed attribute causing logic on its parent element to execute).
I'm starting with this form. I've added default data so that I don't have to bother filling it out as I test it:
The initial data source is relatively simple:
I like to use an XML secondary data source for logic fields when possible (that is, fields needed for rules, but not really relevant to the form's data) - so here's the XML:
Add a secondary data connection to your form that uses this XML:
Here's the form with that added, in case you need it.
Okay - I just said above that I like to use secondary data sources for fields that are just for logic and here I go breaking my own little rule - but sometimes you have to make exceptions for repeating data because you have logic that needs to be executed on this instance of the group. In this case, I am adding 3 attributes to my Order element to help me in my endeavors:
Here's the form again with those attributes added.
Since I'm using default data, I've set the initialOrder for each of my existing rows manually. However, in order to handle for newly added rows, we need a rule to set the Order and the initialOrder when a new row is added. I've added a rule to the repeating Book node for this:
All the pieces are in place, so let's take a look at the rules and test it out. Our goal is to be able to change the order number for a row and:
The first rule I'm going to add is on the Order element - I've named it Trigger Increment, and I'm adding a condition on it - I only want it to run when the EditingOrderPosition field in my secondary FormLogic data source is blank:
I want this rule to execute only when I change the Order field myself - I'm using the EditingOrderPosition condition to prevent creating a loop, because I know that my rules are going to cause other instances of the Order field to change. So, the first action I'll add to my rule is to set that field's value:
I am setting it to the count of preceding-sibling Book nodes - I'll use this in another condition later. If your repeating data has a unique identifier, you could also leverage that here - set EditingOrderPosition to the field with the unique identifier and then, in the condition check on the editing attribute, ensure the row does not have the same unique identifier.
Next, I set the increment attribute to the Order field minus the initialOrder attribute - the initialOrder attribute contains the original value of the Order field, and after the user changes the Order field, the difference between the two will let us know how we need to increment our other fields.
When the form is running, this is the point where the rule on the increment attribute will fire, so let's go take a look at those and we'll come back to the rules on Order after we walk through the rest of the logic.
There are two rules on the increment field - one if increment is less than zero and one if it is greater than zero. I've also included a 2ds field named Sorting in my condition - I use that when I sort the data to prevent rules from firing during the sort (however, I've done a little additional testing, and it appears it is not necessary at this level - only at the Book level if I wanted to prevent that logic from firing).
The Negative Increment ruleset (runs if the value of increment is negative) looks like this:
First, I'm setting a helper field in my 2ds to the correct amount to increment the field. Then, since our Order field is less than its initialOrder (i.e., the increment attribute is negative), I need to add one to all Order fields that are greater than or equal to the current value of the Order field I just modified and less than the initialOrder attribute of the Order field I just modified. So, if my Order had the value of 4 (with the initialOrder also being 4) and I change Order to 1, any field with a value greater than or equal to 1 and less than 4 needs to have 1 added to it. Whew.
In the rule above, I am setting the editing attribute for all fields that meet that condition to true, to execute another rule that increments the row order:
This adds the current OrderIncrement to the Order - but note the condition to that will prevent the rule from executing if the field is the same that initiated the sequence (mentioned above - if you used a unique identifier instead of a count of preceding-sibling, you'd use that for the condition instead of the count):
A second rule re-sets the editing field back to false if it is true:
Back on the increment field, the rules for Positive Increment are similar to the ones for Negative Increment:
The condition in this case is that the increment attribute is greater than 0 (along with Sorting being blank), and I set the OrderIncrement helper field to -1. In this case, since our Order field is greater than its initialOrder (i.e., the increment attribute is positive), I need to subtract one from all Order fields that are less than or equal to the current value of the Order field I just modified and greater that the initialOrder attribute of the Order field I just modified. So, if my Order had the value of 1 (with the initialOrder also being 1) and I change Order to 4, any field with a value less than or equal to 4 and greater than 1 needs to have 1 subtracted from it.
Guess what? We are finally back to the rules on the Order field! The remaining actions in the ruleset are pretty straight-forward:
I set my helper node, EditingOrderPosition back to blank. I set my Sorting node to true (to prevent rules firing during sorting that I don't want to have fire), and then execute a qRules command to sort the rows based on Order:
Finally, prior to my Trigger Increment ruleset, I have a rule to handle for invalid data. The condition ensures that the user has entered a positive number:
If they have not, a message is displayed and the Order is set back to the initialOrder:
Note the use of the "Don't run remaining rules if the condition of this rule is met" checkbox - we don't want to execute the Trigger Increment rule unnecessarily. Here's the final form - I encourage you to download it to walk through the rules, since this blog post is not so much a step-by-step how-to (that would have been mind-numbingly long) and more of a guideline.
A few more points of interest on this form - so far, I am also able to use the InfoPath Move Up and Move Down right-click menu widgets with this (due to the re-numbering logic on the Book node) and the right-click Insert options also work well - adding a new row in-between existing rows allows all Order nodes to update their values. However, I don't have anything to handle for delete / cut. The logic is somewhat self-correcting - if you delete a row the numbers after it will be off only until a new row is added or the rows are re-sorted, but it is a weakness I wanted to highlight. If you can think of a good way to handle for this, leave a comment and let me know!
When we use qRules SortTable, the nodes that are being sorted are actually deleted and re-added. I could have used my Sorting helper node to prevent the rules on the Book node from firing - but then I realized I could leverage this behavior to reset my initialOrder attribute as well as handle for user entries that are greater than the number of items in the table - that is, if a user enters a too-high number, the rules on Book that fire during the sorting will correct the number.
Also - I think this could be implemented without qRules (although I haven't tried) using this method to set the correct editing attributes and manually modifying the view to include xsl:sort and a preserve code block (this won't be browser compatible).
In version 4.2 of qRules, we made a number of changes to simplify the SubmitToSharePointList command. Those changes also impact the RefreshSharePointListItems command - in a good way, I'm glad to say. Like SubmitToSharePointList, you can now run the refresh command for multiple lists with a single rule action, and fewer parameters are required.
In case you've not seen them, here are my blog posts on these changes thus far:
In this post, I'll be starting with the form we left off with on the Working With Existing Data post - here's a copy you can use. Keep in mind that my form & mapping is pointed to sites in my environment, so you'll need to modify that, and it is using the current 4.2 trial version of qRules - which expires 9/15/2012. So, you'll want to re-inject with a new trial if you are following this at a later date.
The Basics
First off, in case you aren't familiar with the refresh command - let's talk about what it does and how it works. This command is always used with forms that are set up to use the SubmitToSharePointList command where a form field is set to maintain the SharePoint list item ID. The command should generally be run with a rule on QdabraRules data source finshedLoading attribute - a condition should be used so that the rule only runs when that attribute is equal to true.
When you submit your list items, SharePoint returns the newly created list item ID to the field you have specified via your mapping:
If you look at your mapping.xml file in a text editor, you'll see the IsId attribute is set to "true":
On a successful submit, qRules is going to update the indicated field with the value of the associated list item ID - all items in a SharePoint list have a unique integer value in the ID column. By saving that value in the XML for your form, the next time the SubmitToSharePointList command runs, qRules can perform an update on the existing item rather than creating a duplicate.
The RefreshSharePointListItems also leverages this field - it adds to your form the ability to update all your SharePoint list connected items with the latest data from the list, so your users can update either the XML or the list - they'll stay in sync when your XML is next opened. It also adds the ability to propagate deletes - both from the XML to the list and vice versa.
This command has two modes - Report and Update. Because the command has the potential to delete or change data, the default mode is Report. That means you have to explicitly state that you want the list data to overwrite your form data.
A new attribute has to be added to your fields where you are keeping your list item IDs - qRulesLastModified. This attribute stores the date and time the form data was last saved to the list. When the refresh command runs, qRules compares the XML date & time values with the Last Modified column for the list item. If the list item was updated more recently, qRules overwrites the form values, using the mapping.xml to know which column writes to which field.
If there is an item in the XML that has a list item ID, but can't be found in the list (and the command is run in Update mode), qRules deletes the data from the XML.
When the RefreshSharePointListItems command first runs, it also creates a list of the items with SharePoint list item IDs currently in the form. When SubmitToSharePointList is next run, if any of those items have been deleted from the XML, they will also be deleted from the SharePoint list.
Back To Work
Hopefully that explanation didn't bore you to death or baffle you. But we'll walk through it, and the background above might make what we are going to do seem a little less obscure, okay?
Since we are picking up where we left off last time, our form already has fields for the list item IDs, and we have our submit commands set up. So the first thing we'll do is add the qRulesLastModified attribute to those.
When I created my schema, I referenced the ShPId node so that I could have same name in a number of locations:
Referencing a node is simple - add the node in one location you want it. Then right click the node and select Reference:
In the next dialog, select the group you'd like to add the field to - simple as that.
Now the cool part - I just have to add the qRulesLastModified attribute to one instance of my ShPId field, and they all get it:
Form Changes
We first need to add an additional data source connection. qRules uses an owssvr.dll data connection to return each item for refreshing in preview or in Filler. If deployed as a browser form, it uses the SharePoint object model - but since we are going to want to be able to preview and test our work, we are going to add an owssvr.dll data connection.
The data connection is to an XML file, and the URL looks like this:http://yourserver/yourweb/_vti_bin/owssvr.dll?Cmd=Display&List={guid}&XMLDATA=TRUE&noredirect=true
Your URL needs to be valid so that you can create the data connection, but qRules will use the server URL from your mapping and the list GUID from your mapping (since, after all, that will be the server and list you submitted to).
So, in my case, the URL I'll use looks like this:http://qshp2010/nw/_vti_bin/owssvr.dll?Cmd=Display&List={6E986BE0-F112-4F61-8931-C1891E239E74}&XMLDATA=TRUE&noredirect=true
The GUID is to my Customers list - again, during the refresh process, qRules will ignore the GUID in the URL and use the one from the mapping file instead, so all that matters is that the URL is valid. You can test your URL in the browser - depending on your browser, it will either return XML in the browser or give you an option to save the returned XML.
We add a new XML data connection to the form:
Use your owssvr.dll URL for the location:
Be sure to select the "Access the data from the specified location" radio button:
And de-select the checkbox that queries the data on load:
You'll notice I've named my data connection "Refresh" - name yours whatever you like, but as usual, we need the exact name, including the correct casing, for our command parameters.
Adding the Command
Now that we've added our qRulesLastModified attribute and our data connection, we can add the command. We are going to put the rule for the command on the QdabraRules finishedLoading attribute, with the condition that the attribute is equal to true (see *Note* at end for some additional information on this):
We set the Command node of the QdabraRules data source to the RefreshListItems command:RefreshSharePointListItems /dsname=Refresh
Did you name your data source something else? If so, you'd need to use whatever your data connection's name is for the /dsname parameter. If your mapping data connection is named anything other than "mapping", you'll need to include the mapping parameter as well:RefreshSharePointListItems /dsname=Refresh /mapping=MyVeryFancyMappingFile
Here's a copy of my work in progress, in case you need it.
As I mentioned earlier, the default mode for this command is Report - this means any changed items will be reported to the QdabraRules Results node. Put that node on your form so you can see the outcome.
Testing in Report Mode
Guess what? We are already ready to test! Once you have the form set up to submit list items, adding refresh functionality isn't much work. To test, we'll preview and submit some items to our lists. We'll save our XML that we created in preview, change some of our list items, and then preview again, using our existing XML so we can see what the report result is.
So, preview your form, create or select a customer, and add some orders and order details:
Submit your list items, using the submit button, and then use File --> Save As to save the completed XML. Close your form.
You can open the XML in a text editor, and you'll see that your list items have a SharePoint list item ID along with a value for the qRulesLastModified attribute:
We need to change some of our new list items from SharePoint - here's my original details:
And their modifications:
Set your form template up to preview with existing data, using the XML you saved in your earlier preview (how-to can be found here):
Remember, we have not included the /mode parameter at this point, so by default our refresh command will only report on changes. Preview your form again, and take a look at the Results field - you'll find text like this:
Testing in Update Mode
Keeping your template set up to preview with your existing data, add the /mode parameter to your command:
Preview again - this time, you'll have the same report, but your items that you changed in SharePoint should be refreshed to match:
Euw. Big ugly validation errors - what happened there? Well, my form has a data type of whole number for the Quantity field, but SharePoint is returning the data with decimals - that's okay, I can take care of that with a rule on the Quantity field:
Preview again, and:
Deleting
Leaving the preview open, where the refresh command has already run, try deleting existing items and adding some new ones. Remember, the deletes will occur when the SubmitToSharePointList command is executed, so be sure to click your submit button to run the command. Be sure you remove the path to the XML you've been previewing with before you close your template - otherwise, you will do what I always do - delete the XML and be completely confused when your form refuses to preview.
***********************
That's it! Here's my final form if you want it. The best part of the qRules 4.2 changes, I think, is that if I need to submit data from my form to another list (I'm sure people in your organization never change requirements on you... ) I can export the mapping from my form, import it into the mapping tool, and add the list mapping. I just need to replace the mapping in my form with the new one, and with no other changes, my current submit and refresh commands will now include the new list. That makes changes easier and faster for the form developer, so hooray for that.
*NOTE*: As you may already know, in InfoPath, rules run before code. qRules is code, of course, but code that is accessed via rules. We want to make sure the qRules code has fully loaded prior to executing any commands. So, if you have a command you would like to run on a form load rule, you should instead run it on the finishedLoading attribute. The qRules code sets that attribute to true when it is fully loaded.
With qRules 4.2, we've introduced a major improvement to the SubmitToSharePointList command - especially if you have been submitting to multiple lists. The first walkthrough can be found here, and then in this post, we talked about how to save attachments with our list items. Picking up where the second post left off, we are now going to modify our form to submit to one of the individual lists we've mapped. Thus far, we've only submitted to all the lists. Also, because I am frightfully lazy, we are going to start pulling some existing data into our form so we can edit it and update our existing customers.
We'll start with the same template we have been using for the previous two walkthroughs - mine is available here, if you'd like it. Keep in mind that my form & mapping is pointed to sites in my environment, so you'll need to modify that, and it is using the current 4.2 trial version of qRules - which expires 9/15/2012. So, you'll want to re-inject with a new trial if you are following this at a later date.
Data Connections
First, we need to add 2 data connections to our form - one that we'll use for a customer drop down, and the other that we'll use to return all the data for a given customer. The first one, which I'll call the CustomerNumber data connection, is to the Customer list, and returns these columns:
I sorted by Company Name to make the drop down I'll use this data for less annoying.
Return data on load for this connection only:
Now, create another data connection to the Customers list:
Select at least all of the columns that the form is mapped to submit data to. This time, deselect the checkbox to query data on load:
In my form, I've decided to let the user choose an existing customer or just fill in the fields if they want to create a new customer. I've added a drop down that is bound to the /my:myFields/my:Customer/my:ShpId field - you may recall from the earlier walkthough that when mapping, we can indicate a form field that will hold the SharePoint List item ID after a successful submit- we are going to leverage this field to allow our users to open a new form and edit existing customers.
Set the drop down to use the CustomerNumbers data source, with the ID for the value and the Company Name for the display:
Here's my form up to this point, in case I've lost anyone.
After the user selects the Customer, we'll need to query the Customers data source for that customer's data and then populate the main data source nodes with those values - this can be done with ordinary InfoPath rules. While this could be done with rules set on the ShPId field the drop down is bound to, that would make things slightly more complex for us - we'd need to make sure these rules don't run when qRules is populating that field. So, for the purposes of this walkthrough, I'm going to add a button and put these rules on the button. However, you have other options - like:
For my form - I'm going with a button.
My first rule action sets the Customers query ID field:
To the value of the /my:myFields/my:Customer/my:ShpId field:
Then, I execute the Customers query:
Now, since the user selected the ID of the customer she wants, I could have returned all the customer data and used filtered XPath to get the values I want - and if you are using SharePoint & InfoPath 2007, this would be the approach you would probably take (or use the qRules FilterOwssvr command to get back just the single record you need). Since I only will have one record in my Customers data source, I don't have to use filtered XPath to populate my other form values. That makes me happy.
In the rules above, I am setting main data source Customer group nodes to values from my Customers list.
Preview
Time to check our work - preview the form, and select a customer from the drop down - does all the customer's information get populated correctly?
If not, check and make sure you are returning data to your Customers data source - and here's my form in progress, in case you need to take a look at it.
Submit a Single Mapping
Now that we have added the logic to pull an existing customer into the form, we can add a button to save just the user's Customer changes. The big trick here is making sure that in the mapping we have identified a field to use for the ID:
And making sure we have in our form set that field to the SharePoint List item ID that we wish to update - in my form, I've done this by binding a drop down to the field ( /my:myFields/my:Customer/my:ShpId) and then using a data connection to my target list for the values - so that the user will be picking a valid item ID from the drop down.
Our original submit command was:SubmitToSharePointList /submit=ShPList
We then added to it for adding attachments in the last walkthrough. I'm not going to be adding attachments here, just editing my existing customer - so I only need to add the mappingName parameter to indicate which mapping I want to submit:SubmitToSharePointList /submit=ShPList /mappingName=Customers
I'm going to add a region to this customer, since it was missing when I pulled his information into my form:
After clicking my button with the qRules command on it, I can see the region has been added to the customer:
If you would like to verify that only the Customer is submitted, add an Order and use the command that submits only the Customer data - you'll find the Customer is added / updated and the order is not. If you need my final form, here it is.
There you have it - you can make this more interesting by allowing the user to copy in Orders or Order Details - using CopyTable or Insert - so those could be updated. Of course, you'd probably be submitting the XML to a form library in a real form - so you would open the form to update the order.
But what if someone changed the list item? What if you want your users to be able to edit from the form or the list, and you want to keep them in sync? Stay tuned - next up is a walkthrough on the RefreshSharePointListItems command!
SubmitToSharePointList WalkthroughWorking with AttachmentsRefreshSharePointListItems
In the previous walkthrough for the SubmitToSharePointList changes, we submitted data to three lists, all with a single mapping, a single submit connection, and the new, much simplified command syntax. If you didn't hang onto your form template from last time - here's one that you can start with - mind you, the form is pointed to my development SharePoint sites, so just about everything will need to be repointed to your environment, but at least you won't have to start all over. Also, this form is injected with a qRules trial - so if you download it after 9/15/2012, you'll want to re-inject it to enable qRules again.
First, we will need to add a field to our form data source for attachments. I think I'm going to add that to my Customer - it seems like I may want to attach a filled out credit application, or some other file related to the customer:
I've set the field to be repeating, since I want the user to be able to add multiple files.
Now, I have a decision to make. There are two options when adding attachments to my list item - I can keep the file in my form XML and add it to the list item in SharePoint or I can simply upload the file to my list item and remove it from the XML. Since I don't want to keep the file in two places, I'm going to take the second option - I want qRules to remove the file from my form after saving it to my list item.
To do that, all I have to do is add two attributes to my attachment node - qRulesLink and qRulesFilename:
The presence of these attributes on the attachment node indicates to qRules that you want to have the file removed after it has been uploaded (like the functionality of the SaveToSharePoint command, only for a list instead of a library).
Mapping Changes
Next, we need to modify our list mapping file to indicate that we have an attachment field. Now, if you have worked with the SubmitToSharePointList command before, you may have had to re-do your mapping each time you wanted to make a change. We've improved that experience in qRules 4.2 - we can now import our mapping into the mapping form.
So, first save your changes to your form template. Then, from the Data tab of the Ribbon, select Resource Files:
From the Resource Files dialog, click on your mapping.xml file:
And click the Export button to save the file.
Open the mapping tool from the Start menu:
Select the Define Mapping tab. There is a button toward the bottom - Import Existing qRules Mapping - click that:
You'll get a warning about the form deleting existing mappings, which you can click OK on, and then select the mapping file you just exported from your form. Your existing mapping will be imported - all that remains is to attach your template to the Source XSN field - much better than remapping every time, don't you think?
In the mapping to the Customers list, select Add mapping:
Select the new Files node that was added earlier for the Form Field and click the Show button in the Options column:
Select the Attachment checkbox for the Files node mapping:
And click the Save as qRules Mapping button at the bottom of the form to save the mapping.xml. If you maintain the same name as the original file you exported, you can simply re-add the file as a resource file - no need to walk through the data connection wizard again. From the Data tab of the Ribbon, select Resource Files again to open the Resource Files dialog. Click Add and navigate to the mapping file you just saved. InfoPath will prompt you:
You want to replace the existing file, so leave the default selection and click OK. You shouldn't have to modify your mapping data connection - the schema of the XML file will not have changed at all, simply the data. As an aside, you can always verify what file is used by your data connection in the Data Connection dialog:
Add a Data Connection
We need to add a Receive data connection to the AddAttachment method of the SharePoint Lists web service. On the Data tab of the Ribbon, select From Web Service --> From SOAP 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 AddAttachment method:
Click Next - no need to set any of the query parameters, so click Next again, then one more time - in the final screen of the dialog, deselect the "Automatically retrieve data when form is opened" checkbox and leave the default name for the data connection - you can name it something else if you like, but keep track of the name, because we will need it when we modify our command.
Add Controls to the Form
Add controls to your form to allow the selection of files. I like to add a file picker and a hyperlink, with the file picker inside a section, and set to hide if the qRulesLink attribute is not blank. Here's a copy of my work in progress, if you'd like to check it out. For the hyperlink, I am using the qRulesLink attribute for the link, and the qRulesFilename attribute for the display:
Modify the Command
In the first walkthrough, we added a submit button to our form with the qRules command:SubmitToSharePointList /submit=ShPList
Now, we need to modify this command to include our attachment files when we submit to the Customer list:SubmitToSharePointList /submit=ShPList /dsname=AddAttachment
If you named your AddAttachment data connection something different, you need to modify the command above to use the name of your data connection:SubmitToSharePointList /submit=ShPList /dsname=MySuperSpecialAttachmentDataConnection
Remember, case cOunTs - you need to use the exact same spelling and casing.
Test the Form
As always, be sure you have the QdabraRules Error node somewhere you can see it prior to testing. Preview your form, add a new customer, and add some files for the customer. Click the Submit button:
If everything is set up correctly, you'll now have links in your form instead of files - and in your list item:
You will have attachments.
Extra - Add Delete Logic!
So - this is all well and good. Provided your users never ever make mistakes.
But every now and again, someone may attach and save the wrong file. It would certainly be nice to allow the user to delete the file from the form, wouldn't it?
No qRules required for this - just add another data connection to the DeleteAttachment method of the SharePoint Lists web service - exactly the same way as you added the AddAttachment method above:
Don't bother filling out any of the query parameters, and deselect the option to automatically execute the data connection on load (just like we did for the AddAttachment data connection).
Add a button to your form next to your hyperlink control:
Add a conditional formatting rule to the button to hide it if the qRulesLink attribute is blank:
Add an action rule to the button. There are three fields we need to set in the DeleteAttachment data source queryFields:
I like to get the listName from the mapping file. MSDN states that the listName parameter can be the list GUID or name. We have the GUID in our mapping file - it is in the Customers mapping:
So, I'll set that field to the ListCollection node in my mapping file where the mappingName is equal to Customers:
Next, I set the listItemID parameter to the field I am storing my SharePoint item ID in - in this case, that would be /my:myFields/my:Customer/my:ShpId:
Then I set the url parameter to the qRulesLink attribute:
Don't forget, we need to actually execute the query (I always forget that rule action, leaving myself perplexed when my what-ever-it-is doesn't work):
In a perfect world, our web service method would return some useful information to us - sadly, it doesn't (or at least not anything that InfoPath is going to tell us about). If the query fails, any rule actions after it won't run, so I generally work on the assumption that if my query succeeds, my attachment will have been deleted, and add two more rule actions to clear out the qRulesLink and qRulesFilename attributes.
However, you do have some options for error handling.
My final ruleset looks like this:
I've simply chosen to let InfoPath return a query error in this case - and I added a condition to only run if the SharePoint ID field is populated.
So, now our form submits to three related SharePoint lists. It can include attachments. The user can delete attachments. Here's the final form, in case you want to take a look at any of the logic. Next up - submitting just specific mappings. Hang onto your form and your test site!
SubmitToSharePointList WalkthroughSubmitting a Single Mapping / Updating Existing DataRefreshSharePointListItems
We are getting ever closer to releasing qRules 4.2 and I wanted to post briefly on a new node we've added to the QdabraRules data source - it is called useBrowserApi and has a default value of "false".
Here's what it does and why it is there:
With qRules 4.1, we modified a lot of the SharePoint related commands to use the SharePoint object model instead of web services if the form was opened in the browser. We added a node called useWebServices, just in case this change affected any forms upgraded from earlier versions of qRules. But the other day, we suddenly saw an issue where sometimes qRules was not correctly switching between the SharePoint object model code and the web service code, and this caused some issues in our Office 365 forms (which can't fall back on using web services).
That code has been addressed in 4.2 and we are now unable to reproduce the issue in our environments, however, it made sense to give form designers a way to switch this off and indicate that they would like to always use the SharePoint DLLs
Now, if you ever have issues with a form failing in the browser because it is not using the SharePoint object model, you can set useBrowserApi to true in order to make the form always use the SharePoint object model based code.
The logic, then, as of qRules 4.2, is as follows:
If useBrowserApi is set to true, the SharePoint commands like SubmitToSharePoint will fail in Filler and in Preview - unless the form is opened on a machine with SharePoint installed, of course.
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:
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 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:
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:
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 AttachmentsSubmitting a Single Mapping / Updating Existing DataRefreshSharePointListItems
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:
The mapping file is used by the command code to discern the following:
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.
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 ToolThis 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 ConnectionAs 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.asmxWith "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 ParametersIf 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!
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.
This one was a treat. Worked flawlessly in preview, of course.
The admin approved browser form had some buttons that were switching views. The IPFS error was:
An editing action referred to a field or control that cannot be found in the current view.
The log files helpfully supplemented this with:
The StateManager is disposing and calling ReleaseLockedStates() (Count=0)
I added new buttons and was able to switch views without error - the original buttons had similar actions and had been copied / pasted from one view to the next - they'd maintained the same control ID. So, I changed the names on the buttons - just added "a", "b", "c" etc.
And the error went away. I don't know whether to be happy that I found the issue, or sad that it was so hard to find and the error messages were so meaningless.
Just wanted to quickly post this link, which describes the differences between publishing sandboxed vs. admin approved solutions.
I hadn't realized that sandboxed forms can't use email submit connections!
From the MSDN page:
Publishing a form with code as a sandboxed solution is no different from publishing any other form to a document library. Just use the publishing wizard as usual and your form will be uploaded to the server and will operate in the sandbox.
Note that there are certain restrictions to deploying your form as a sandboxed solution:
Must be an InfoPath 2010 form.
Must use C# or Visual Basic as the programming language.
Cannot submit to e-mail data connections.
Cannot have properties promoted for part-to-part connections.
Must not have any managed meta-data controls or data connections.
There is a form available here for download that demos the techniques in the video - the video outlines using qRules to copy complex repeating data between two data sources. CopyTable works well in simple scenarios, but if your data structures don't match, or have nested groups, you may find the video and sample form helpful.
So, I've got 3 recent qRules videos up on YouTube:
Hope they help with your qRules forms!
PS - the sample form used in the Submit To Multiple InfoPath Lists is available here for download. It points to my dev SharePoint, and has a trial version of qRules installed, but you'll be able to get a good idea of the logic used from the sample.
I wanted to note a few things I've run into using qRules in Browser forms, along with some troubleshooting ideas.
It can be hard to track some of these things down in IPFS - you can't just set a rule on the Command field to pop up a dialog. What I do is add another field, temporarily, and each time the Command (or Error, or whatever field I want to track) changes, set the trouble-shooting field to its current value concatenated with the new value. This can give me a list of commands that have run (or errors that have occurred) and allows me to track back through the logic to find inconsistencies between browser and client.