Copy SharePoint List Data to Main Data Source - Mel Balsamo
in

InfoPath Dev

This Blog

Syndication

Mel Balsamo

Copy SharePoint List Data to Main Data Source

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

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 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 Full Trust and Administrative-Approval publishing type. I have not tested any in browser forms yet
  • The CopyTable command will only work if the nodes match, which also means each node 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.

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).

Figure01 

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).

Figure02 

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).

Figure03 

Fig 3 - Creation of Meeting Workspace in Outlook 2007

  • Edit the default page of your new Meeting Workspace and close the default web parts (unless you prefer to use one or many later).
  • 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.

Figure04 

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).

Figure05 

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).

Figure06 

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.

Figure07 

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 step 2. Simply go to the Data Source pain 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). Be sure that the field names and data types match exactly for the attribute fields (Fig 8 ).

Figure08 

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

  • Drag repeating Tasks group from 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 ndecimal 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."

Figure09 

Fig 9 - Tasks Main data source repeating table with formatting

Install qRules and Inject Form

  • After installing qRules, you will see that it does far more than 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 into the form template (Fig 10)

Figure10 

Fig 10 - Injecting form template with qRules

Apply CopyTable Command Rule

  • Create button for retrieving Active Tasks on demand and for performing the CopyTable qRules (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 is not shown, 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 or not.

Figure11 

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).

Figure12 

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 the form as much 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)

Figure13 

Figure14 

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.

Figure15 

Fig 15 – Final view of completed form

  • Another point of note is that we aren't using the Auto-Generating Filenames for InfoPath Forms concept, because if you use submit in a Meeting Workspace, then it saves the XML form to the root of the form library, which then makes it not visible 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.

Figure16 

Fig 16 – Saved form shows up in proper meeting date

Comments

 

Alyssa said:

So you’re a fan of InfoPath, you use it every day when designing your forms, you even literally dream

July 23, 2013 8:33 AM
Copyright © 2003-2019 Qdabra Software. All rights reserved.
View our Terms of Use.