Clayton's SharePoint Madness

All About SharePoint, InfoPath, and SharePoint Designer!

InfoPath – Copy SharePoint List Data to Main Data Source

Posted by Clayton Cobb on August 3, 2009


qRules Series #1 – CopyTable


One thing that you can’t easily do in InfoPath is to copy repeating data from a secondary data source – like a SharePoint list – to the main data source.  This is an important necessity, because the data from secondary data connections does not get saved in the resulting XML data file of a submitted/saved InfoPath form.  So, when you look at the form initially, you see the nice pretty data from your SharePoint list enumerated within the repeating table you placed on the canvas, but if you were to open the raw XML file, none of that data would be there.  This is because an InfoPath form only keeps whatever data is saved to its main data source.  A data connection to a SharePoint list is just a secondary data connection, which in essence becomes a window into the data as it currently exists.  Yes, you can re-open your XML file in InfoPath and see the data from your SharePoint list, but it’s the current instance of that data, not the data that existed at the time you previously submitted the form.  Sometimes, this is ok, but what if you needed to get that SharePoint data into your form and keep it there?  To do this, you must copy the data into the main data source.  How can you do that?  Well, you can custom code something, or you can use the great set of special commands known as qRules that is provided to all of us by Qdabra (qRules 1.7 is currently a free download).  qRules allows you to do many things, but the one command we will focus on is named CopyTable.

Caveats:

  • qRules introduces code to your form, so if your forms are browser-enabled, then they will require the Administrative-Approval publishing type. As of qRules 1.8, which will release this week (first week of September 2009), we can now use CopyTable in browser forms. I have tested it and confirmed that it does work. If you need information on how to properly publish a form template in this manner, refer to Janice Thorn’s blog post on the topic.
  • The CopyTable command will only work if the nodes match, which also means each column has to have data in it (no blank fields). The reason your fields can’t be blank is because when there is a blank field, no placeholder for that field is brought down in the XML, so it’s as if that node doesn’t exist. Qdabra is looking at supporting this in a future version.

The example I will use for this blog is a Weekly Status form template (based off the Meeting Agenda sample) within a Meeting Workspace where you pull in Active Tasks from a SharePoint Task List on demand so that a snapshot of the tasks is saved within the form.  The purpose of this is so that at a later time, the Weekly Status for that week’s meeting can be opened, and you will see the status of the team’s tasks when the meeting occurred rather than the current point in time.  In essence, it becomes an historical record of the team’s tasks just like meeting minutes are intended to be.  For the purposes of this blog, I will not go into great detail about meeting workspaces and how they work, but I will briefly explain how InfoPath forms need to be used in order to get the recurring meeting functionality. Here are the steps to accomplish the goal:

  1. Create Tasks List (covered in minimal detail)
  2. Create Meeting Workspace with a Form Library (covered in minimal detail)
  3. Create Form Template
  4. Install qRules and Inject Form
  5. Apply CopyTable Command Rule
  6. Publish the Form and Verify Success

Create Tasks List

  • Create a simple tasks list.
  • Add some tasks with a variety of statuses, priorities, and due dates (Fig 1).

 

Fig 1 – Basic tasks list with several tasks added

  • Take note that the Active Tasks built-in view only shows tasks that are not completed (Fig 2).

 

Fig 2 – Active Tasks view only shows incomplete tasks

  • Set Active Tasks view as Default View.

Create Meeting Workspace

  • Create a Basic Meeting Workspace with Recurrence (I prefer to use Outlook 2007 to create my recurring meeting, and then I use the integrated Meeting Workspace feature button to provision my site in SharePoint – Fig 3).


Fig 3 – Creation of Meeting Workspace in Outlook 2007

  • Edit the default page of your new Meeting Workspace and close the default web parts (optional).
  • Create Form Library and be sure to NOT click Yes for “Change items into series items” (Fig 4).  If we do that, then all forms show on every meeting page (each recurring date).  If we leave this as No, then only the relevant form for that meeting will display each week.

 

Fig 4 – Form library within a Meeting Workspace – not a series item

  • This will put your new Weekly Status form library on the default page for each recurring meeting within this workspace (Fig 5).


Fig 5 – View of default Meeting Workspace page showing the current week Create Form Template

  • Create new template based on built-in sample Meeting Agenda template and modify to your preference (Fig 6).

 

Fig 6 – Weekly Status form template

  • Create a Receive data connection to the Team Tasks SharePoint list, but set it NOT to “Automatically retrieve data when form is opened” (Fig 7).  Be sure to choose only the fields you want to ultimately see in the form, because this will play a big part in our CopyTable command.

 

Fig 7 – Tasks data connection with automatic retrieval check box de-selected

  • Create data structure in form template that exactly matches the data structure of the SharePoint data connection.  Your SharePoint Tasks list data connection will include whatever fields you selected during the creation of the data connection from the previous step.  Simply go to the Data Source pane in InfoPath and choose your “Tasks (Secondary)” data source.  Drill down until you get to the nodes.  You will notice that the fields within the Tasks repeating group are attributes and not elements (this is important).  *Note: You can also see the raw data structure by clicking Save As Source Files and opening your Tasks.xsd file in notepad. Now, go back to your main data source and add a new non-repeating group (aka Table) that includes the Tasks repeating group (aka Row) and all the attribute fields (Columns).  By default new fields will be added as elements, so be sure to add them as attributes so that they exactly match the attribute fields from the SharePoint list (Fig 8 ). The field names don’t have to exactly match, but you may find it helpful.

 

Fig 8 – Tasks node structure in main data source matching SharePoint secondary data source

  • Drag the newly added repeating Tasks group from your form’s Main data source onto the canvas as a repeating table and configure the columns/fields (Fig 9).
    • Stretch and shrink the columns so that the data will display properly
    • Change the Complete column header to %
    • Change the % field control format to show Percentage with no decimal places
    • Change the Due Date field control to a Date Picker (optional)
    • You’ll also probably want all of these to be read-only fields. *Note: To make a Date Picker control Read-Only, you set conditional formatting on it that says, “If Due_Date is present, then make control Read-Only.”

 

Fig 9 – Tasks Main data source repeating table with formatting Install qRules and Inject Form

  • Download and install qRules 1.7 from InfoPathDev.com (Qdabra’s community site).  You can see here that qRules 1.7 does far more than just CopyTable, but those other commands will be the subject of future blogs.  You can also get previously-written explanations and discussions related to qRules here.
  • After a successful install, Inject qRules 1.7 into the form template (Fig 10)

 

Fig 10 – Injecting form template with qRules 1.7 Apply CopyTable Command Rule

  • Create button for retrieving Active Tasks on demand and for performing the CopyTable qRule (Fig 11)
    • Simply drag a Button to the canvas and change the label to something like Show Active Tasks
    • Click the Rules button and Add a rule named something like Query Tasks and Copy
    • Add an Action that Queries using the Tasks data connection
    • Add an Action that Set’s a Field’s Value, choose the Command node from the QdabraRules (Secondary) data source, and set the value to this command string: CopyTable /dsnamesrc=Tasks /tablesrc=/dfs:myFields/dfs:dataFields /rowsrc=dfs:Tasks /tabledest=my:meetingAgenda/my:Task /rowdest=my:Tasks /empty=yes
      • KEY NOTE!! Do not try to paste your command string into the function builder (fx button). Just paste it directly into the Value field.
      • dsnamesrc: This is the name of your source data connection, which is named Tasks in our example.  Remember that this could be different if you apply this elsewhere, so be sure to use the proper name for this attribute
      • tablesrc: This is the table within your data source that provides the data.  You need to properly type in the hierarchy from the Tasks secondary data connection using the information you found in Figure 8.  Notice that the namespace for a SharePoint list is dfs and not my.
      • rowsrc: This is the repeating group that includes the nodes from your SharePoint data connection.  Mine is Team_Tasks, but if you use the default Tasks list from a Team Site, this would just be dfs:Tasks.
      • dsnamedest: This parameter is not used in the above command because it defaults to the main data source, but if you needed to copy your data to another secondary data source, you would provide the name of that data connection here.
      • tabledest: Like tablesrc, this is the table that will receive the data.  This uses the structure we created in our main data source in Figure 8.  Notice the default namespace for an InfoPath form is my, and then my data source root is meetingAgenda due to using the Meeting Agenda sample template.
      • rowdest: This is the repeating group where we want to send the data
      • empty: This is a field that accepts a yes/no flag based on whether you want to first erase all existing data first from the destination table.

 

Fig 11 – Custom button that queries the SharePoint list and performs the CopyTable command

  • Preview the form and click the button to verify that there are no errors and that your repeating table populates with the Active Tasks only (Fig 12).


Fig 12 – Repeating table in main data source showing Active Tasks Publish the Form and Verify Success

  • Publish the form, go to your Meeting Workspace, and click New in the form library
  • Fill out as much of the form as you want
  • Click the Show Active Tasks button, ensure it populates the table, and then click Save to save the form back to the library
  • When you see the XML form in the library, click on it and verify that you still see the data
  • Close the form, then go change an active task to mark it as completed so that it doesn’t show up in our Active Tasks view (Fig 13/14)



Fig 13/14 – CopyTable task complete and no longer active

  • Re-open your existing form (Fig 15) and notice that you still see the previous data (3 tasks, not 2).  This is the desired behavior, because we want to know the status of our Active Tasks at the time of the meeting, not later after the meeting when we re-open the form.  If we were only showing the secondary data source, then we would always see the current Active Tasks, which is not desired.  If you were to click the button again, then it would update with the new tasks, but that is not the intent here (feel free to apply conditional formatting to hide the button after it is saved).  You would only hit the button on new forms when conducting future meetings. In the upcoming qRules 2.0, Qdabra will provide SharePoint list diffing capabilities, so you will be able to see what has changed when you re-open the form, because qRules would automatically compute the diff.


Fig 15 – Final view of completed form

  • Another point of note is that we aren’t using my Auto-Generating Filenames for InfoPath Forms concept, because if you use submit in a Meeting Workspace, the XML form saves to the root of the form library, making it invisible to any of the recurring meeting dates.  Using save allows it to save to its respective meeting (Fig 16) date and thus only see one form per meeting and ensuring that you only see the snapshot of active tasks at that given date.


Fig 16 – Saved form shows up in proper meeting date  

Fig 17 – Final Product

As an overview of what we’ve accomplished, here is a final set of pictures showing that our form is published and saved to one site while the Task list resides on a different site, but both are interacting within the same form. What you can’t necessarily “see” is that the data is not being presented from the SharePoint list as a Secondary data source, but rather it is being added into and shown from the form’s Main data source for later use and reference.

Upcoming Blogs in the qRules series

  • Save images to SharePoint and convert to links in your form
  • Calculate difference between two dates
  • Get Error Count
  • Generate GUID
About these ads

21 Responses to “InfoPath – Copy SharePoint List Data to Main Data Source”

  1. Nick said

    Thanks for the article.
    Do you know of another way to achieve the same results without using qRules? I’m working in Browser Enabled forms and I don’t think qRules 1.7 can make this work for me.

    • Clayton Cobb said

      Nick, I am all about doing things in a codeless manner, so believe me when I say that if it could be done, and I knew how to do it, this blog post would be written thusly. However, this is a big limitation that has been known for a while, which is why Qdabra went through the trouble of writing the code and packaging it nicely for the masses. You _could_ write your own code, but that would take longer and pretty much end up with the same result. You can use qRules in browser forms, but they just have to be administratively approved. I also think they’ve added more support for browser forms either in this release or an upcoming release. To get those exact specifics, I recommend following my link to the qRules forum above and ask the question directly.

  2. Nick said

    Clayton, thanks for your reply.
    As it turns out, we did have some difficulty deploying a qRules injected form in a browser compatible form but, in case it should prove useful to yourself or your readers, we reverted to using a code solution based on this article: http://www.bizsupportonline.net/infopath2007/copy-rows-from-sharepoint-list-to-main-data-source.htm
    and so far it seems to have worked.
    Thanks again for your time and the article.
    Nick.

    • Clayton Cobb said

      Thanks for the feedback. Soncia (SYM) is a great resource, and I’m glad her code fix worked for you. Since both solutions use code on browser forms, you still have to do full trust and administratively approved the form. Due to this, you would think that qRules would be the better solution, since you don’t have to mess with any code yourself and since you get a boatload of other great features. However, if it’s not deploying for some reason, then that’s of course no good. Do you have time to let me know at which point it was failing? During the publishing process, activation, or when opening the form after proper deployment?

      Thanks!

      • Clayton Cobb said

        Now that qRules 1.8 is being released, we can now put this feature into our browser-enabled forms.

  3. Jaime said

    This solution brought me a bit closer to what I need. But we need a way to enter tasks in a form and have it populate a task list in SharePoint. Now the catch would be that every form will have different tasks but they all need to populate into one task list. I’m a bit lost on how I would go about it and if I can partially use this solution.

    Thanks!

    • Clayton Cobb said

      Jaime, this is actually a scenario that I’ve been trying to work towards. With the next release of qRules (2.0), we will have the ability to write back to lists directly, and I plan to incorporate that concept with this exact form. I don’t just want to read the tasks and copy them to my main data source, but rather I’d like to have 2-way interaction with the tasks so that I can create new and update existing tasks. I’d also expect to be able to interact with different tasks in different forms that all come from the same task list (or even multiple task lists). I will be writing a new blog on that as soon as Qdabra gets me the beta code for 2.0.

      In the meantime, I don’t know of a way to do this, because you can’t do anything like that with repeating data in IP forms unless you go full-blown with Qdabra’s DBXL tool, which is a great tool in case you want to check it out.

  4. Lee said

    Great article, thanks.

    Although the walk-through example would work fine for a series list, is there any way I could grab the data for only the instance of the recurring workspace im creating in the form in – i.e. only get the Attendees for that particular meeting? At the moment, it gets every attendee from the entire series.

    • Clayton Cobb said

      Lee, my form is built to work in a recurring workspace, so I am not sure what you mean. Maybe you can clarify? On my form, the attendees that I assign within the form are specific to that week only. Each form is relevant only to that recurrence of the meeting. That’s the whole point of the tasks snapshot. It’s built so that you only have information relevant to that point in time.

    • Lee said

      I see, thank you. I will continue testing.

      Is there any way of filtering the information retrieved i.e. only get items from ListA where ColumnA = ValueA?

      Thanks again for the article, its been a massive help.

      • Clayton Cobb said

        You can put conditional formatting on the repeating table that hides records where a field’s value matches or doesn’t match another value. That’s a client-side filter. To filter on the server-side requires the Qdabra qRules 2.0 tool.

  5. [...] This post was originally written by Clay Cobb and is republished here with permission. To see the original article, please click here. [...]

  6. Ryan said

    Thanks for the walkthrough, I’m just trying to get to grips with qRules to see if it’s going to be useful for us. I was wondering if this walkthrough would work in the same way for copying a fields from the first instance of a repeating group to the second instance. I.e. Customer 2 has the same address as customer 1, so click on a button and the specified address fields will copy over.

    Thanks for any help you can give me on this.

    • Clayton Cobb said

      Yes, it can be used to copy from one table to the next whether it’s in the main or secondary data source – it doesn’t matter.

  7. Joseph Boland said

    Clayton, I’m interested in a solution of this type in a SharePoint 2010 environment. The scenario I envision is that the tasks list would actually be maintained in a SQL Server database so that it could be shared across many projects/teams, with display filtered by specific usage (specific project, team, individual, etc.). Have you implemented this in 2010? Any thoughts on using a centralized task repository?

    • Clayton Cobb said

      Joseph, I haven’t done that, but I don’t think I’d do it anyway. If the data is in a SQL table, then it’s not really a task list – it’s just a table of data without all the SharePoint task-based functionality. If I were to use SQL, I would then use BCS to expose the data via external lists on each site, I suppose. However, I would rather use regular task lists on each project site and then roll up all the data into one dashboard/tracking site for everyone to see/filter/group/sort, but not to DO the work from there.

      • Joseph Boland said

        Thanks. I’m not a SharePoint developer and probably misspoke. What I actually had in mind was to use a database table as a task list data source for multiple task lists — so preserving the use of the lists as UI elements. Taken as a whole, the set of task lists might span +1 site collections. Roll-up would be an option within an collection about not across all. This might be implemented using InfoPath forms for entry/edit of tasks. Given all this, it remains appealing to have a form for meeting minutes capable of presenting both data about the meeting as such (name, attendees, date, etc.) and an area showing currently open tasks (determined by the site (project) for which the meeting is being held. That’s what attracted me to your solution.

  8. wee teck said

    Hi Clayton,
    Could I get this qRules DLL file in free version as i need it for infopath form development used? thanks.

    Rgrds,
    Wee teck

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 170 other followers

%d bloggers like this: