Creating a Bulk Editing Dashboard for Office 365

donstephen — Fri, 17 Mar 2017 12:32:00 GMT

A business intelligence dashboard is a data visualization tool that displays the current status of metrics and key performance indicators for a business, department, or specific process at a quick glance. Using InfoPath as a Dashboard gives us more control or customizability over the design, layout, filters, and may even perform complex type of queries.

Since InfoPath forms provides us with such customizability, why not push the limit further by adding a bulk editing feature to your InfoPath Dashboard.

Bulk Edit Dashboard Requirements

This guide assumes we are working on a SharePoint Online environment with Office 365 and requires the following elements:

qRules
InfoPath
FormsViewer

The Bulk Edit Dashboard also works for SharePoint 2016 or lower, where Code-Based Sandbox is enabled – FormsViewer is not required and can be optional.

The Target Library (Optional)

This section is optional if you already have a target library which you intend to query for your Bulk Editing Dashboard tool, but for instructional purposes, a re-usable Form Template Library (STP) file is provided for Office 365.

Deploy the site template (STP) file.

Download the STP file here.
Go to the Site Settings (make sure you are on the top level site settings)
Under Web Designer Galleries, click List templates.
Upload the Invoice_Request_Library STP file.
- Click Upload Document under Files tab
- Browse for downloaded STP file and click OK

Refer to the link below for a more detailed steps on the web on how to upload an STP template

https://support.office.com/en-us/article/Manage-list-templates-c3884ad1-bc49-44b8-b3d6-3bc6a01eb393

Create the Library using the STP file

Proceed to the target site/subsite.
Go to Site Contents
Add an app.
Search for the Invoice_Request_Library STP file.
Click the App to create the library.
Enter a library name such as Invoice_Request_Library

When done, proceed to the library and check out the contents:

We will be performing a bulk edit operation on the items above at the end of this guide.

At the Library tab, create a new column called select.

Leave the rest of the configuration as is and click OK.
Repeat the same procedure for creating two more columns called submit and success.

The 3 newly added columns will be used later on for the Dashboard’s Bulk Edit logic.

When done, you may choose to submit new items by clicking on the New button.

FormsViewer

This guide assumes that you already have FormsViewer installed on your SharePoint O365 Environment. Kindly skip this section if you already have the FormsViewer app installed.

If you plan to create this Bulk Edit Dashboard on SharePoint 2016 or lower without FormsViewer, be sure to have Sandbox enabled in order for qRules to run.

To install FormsViewer, download the App along with the guide from the link provided below:

http://www.infopathdev.com/files/folders/formsviewer_v11/entry104513.aspx

Creating the Dashboard

In order to fully understand the fundamental elements of the Bulk Edit Dashboard, you will be creating the dashboard from scratch.

Before we proceed, here’s a list of data connections needed:

Retrieve library data connection to query target library – for displaying items intended for bulk editing
Retrieve XML document data connection – for retrieving the XML document to be updated
Submit to Library – for submitting retrieved document along with modified content/s

Steps:

Open a Blank InfoPath Designer form.
Under the Data Tab, click Data Connections and Add a data connection which receives data from the target SharePoint library, in this case the Invoice_Reques_Library created earlier.
At the next page, select the following columns from the SharePoint library:

Click Next twice until you reach the last page of the data connection wizard.
At the last page, untick the Automatically retrieve data checkbox and click Finish to exit the wizard.

Disabling the Automatically retrieve checkbox is optional but highly recommended since it enables you to filter out unwanted data on your on load query by specifying a filter at the data connection’s query fields before triggering the query action – As far as performance is concerned, we would not want to return all the data when the form loads.

Add another data connection which receives data from an XML document.

Click Next.
Enter the URL to the library.
After the URL, add a slash and enter an existing File Name (XML) from the library and add the prefix .xml.

https://qdabra.sharepoint.com/Website/Invoice_Request_Library/~~don.lambatin_20170303142035.xml~~

Open up the link in the browser to verify if you have the correct link to your form. Be sure you got the File Name correct otherwise an error will appear if you do not have a correct link and it will not properly open up the form in the browser.

Click Next.
At the next page, select Access to the data from the specified location.
Click Next.
Provide a meaningful data connection name such as GET_Invoice_XML.
Click finish.

Notice that you now have a new secondary data connection source with fields matching the columns from the target library.

Add a submit data connection that submits data to the target SharePoint Library:

Be sure to specify the correct link to the library.

Tick the Allow overwrite checkbox – IMPORTANT!
For the File Name, click the fx
Click Insert Field or Group.
Select the FormName field from the GET XML data connection:

Note that this field should match the target library’s XSN template’s selected File Name field declared at the Submit to SharePoint Library data connection wizard.

Click Next.
Leave the name as is and click Finish.

Your list of data connections should look similar to this:

Save the form and inject with qRules.
- Go to File tab > Save As > select desired save location.
- Close form
- Launch the Qdabra Rules Injector tool by clicking on Start > Programs > Qdabra > Tools > qRules > qRules Injector.
- Specify the template by browsing for the Dashboard XSN saved.
- Click Inject!

Re-open the injected form (right click on the XSN and select design).
Design your layout – Header and Footer
At the Invoice_Request_Library secondary data connection, layout your repeating table onto the canvas. – Right click on the repeating section, drag it onto the canvas, and select Repeating Table
Select columns you would like to display.
Replace all text boxes with a Calculated Field Box (Right click on the field > Change Control > select Calculated Value).
At the left most section of the table, insert a narrow column for the select check box
Change the select text field control to a checkbox (right click > properties > change control > select check box).
At the header of the select check box column, add a button.
Under the Home tab click on Manage Rules.
Add a rule that sets the value of the select field to the value Name this rule Select All.

At the left most section of the table, insert a column for the success
Click on the success field and change this control to a calculated field.
Still at the success field, Add two formatting rules:
1. Set the name of the first formatting rule to Green if success
2. Add a condition where success is equal to the word “success”
  - For the first and second dropdown, leave the two selections as is
  - For the third dropdown, select Type text and manually enter the word “success” without the double quotes
  - At the Formatting section, change the shading to Green and set the text font to Bold.
  Your formatting rule should look similar to this:
3. Perform the same steps for the second formatting rule where:
  - Rule name: Red if failed
  - Condition: success is equal to “false”
  - Set shading to Red, text font to White and set it to Bold.

This field will later on display the result of the SubmitToSharePoint qRules command

At the Main Data Source, change the section’s name myFields to a more meaningful name such as Dashboard.
Add a new section named FormLogic.
Inside this section, add the fields below and follow the corresponding data type:

Your schema should look similar to this:

Insert a 2 x 1 Custom Table right above the repeating table – this section will hold the buttons and dropdown control for the bulk edit Status selection.
Inside the right section of the custom table, display Status field from the FormLogic section as a dropdown.
Right click on the dropdown control, select properties, and manually enter the following choices:

Beside the dropdown control, add a button and rename the button label to Update.
Add an action rule named Update.
- On this rule, Add an action that sets a field’s value.
- Set the target field to the submit field of the Invoice_Request_Library data connection.
- Set this field to the value “1” without the double quotes.
Add a new rule to set the Status field of the FormLogic section to a blank value – we would like to reset the dropdown field after the bulk edit operation has succeeded.
Add a formatting rule that disable the Update button if the value of the Status field is blank – this will prevent users from clicking the Update button if there is no new Status selected.
At the Left section of the Custom table, add a new button and re-name the button label to Refresh.
At the Refresh button, add an action rule that queries the Invoice_Request_Library data connection.

We are now down to the last major component of this guide which will use the following qRules commands:

ChangeConnectionUrl
- This command will update the XML location specified at the Get_Invoice_XML data connection created earlier, to the corresponding FileName selected from the displayed items at the Invoice_Request_Library repeating table.
InsertPi
- This command is used to manipulate the InfoPath form’s Processing Instructions
- Since we are only getting the XML content from the Get_Invoice_XML data connection, we will insert Processing Instructions that will conform to the target Library’s requirements in order for the forms to open up properly in the browser.
SubmitToSharePoint
- This is a bit self-explanatory – this command will be used for submitting the each row separately as XMLs to the target Invoice_Request_Library.

At the submit node of the Invoice_Request_Library, add the following rules:

Stop if not 1 and not selected
- Rule name: Stop if not 1
- Condition: submit is not equal to “1”
  - Click the condition link and leave the first dropdown as is
  - Change the second dropdown to not equals to
  - Select Type text for the third dropdown and manually enter the value 1
  - Click the And button beside the third dropdown to add a new condition below, and replace this with an OR
  - Set the first dropdown to the select node.
  - Change the second dropdown to not equals to
  - Select Type text for the third dropdown and manually enter the value yes
- Action target field: stop attribute field of the FormLogic section located at the main data source.
- Value: none (blank)
- Tick the checkbox to stop all other actions if the condition is met.

Your rule should look similar to this:

The objective of this rule to prevent other rules if the submit action is not triggered and if the current row is not selected.

Reset self

Rule name: Reset self
Condition: none
Action target field: submit (self)
Value: none (blank)

ChangeConnectionUrl and query XML
- Rule name: ChangeConnectionUrl and query XML
- Condition: none (blank)
- Action target field: Command node of qRules located at the QdabraRules data source.
- Value: click the fx button to insert the formula:
concat("ChangeConnectionUrl /dsname=GET_Invoice_XML /url=https://qdabra.sharepoint.com/Website/Invoice_Request_Library/", Title)
- Highlight Title and select the Title field from the Invoice_Request_Library

In this formula we are calling the qRules command ChangeConnectionUrl which will update the XML location specified for the GET XML query.

The parameter dsname identifies which data connection will be updated. For this guide we have used the GET XML data connection named GET_Invoice_XML.

The parameter url should point to the location of the XML in SharePoint. Since the target library is the Invoice_Request_Library, we will use the link to that library and concatenate that url to the Title field which contains the File Name (or FormName) that was specified in step 19.

Concatenating the syntax to the Title field allows us to filter out the query data connection dynamically – each row will have a different URL.

The result of this formula should be similar to the URL specified in step 9.

Add a data connection to query the Get_Invoice_XML data connection

Your rule should look similar to this:

Update current Status
- Rule name: Update current Status
- Condition: none
- Action target field: Status field of the Get_Invoice_XML data connection
- Value: Status field of the FormLogic section from the Main data source

Your rule should look similar to this:

Before proceeding to the next rule, we must first understand what the parts of the Processing Instructions are.

Download a sample XML from the Invoice_Request_Library and open the file using a text editor such as notepad. Here’s how the first Processing instruction should look like:

<?mso-infoPathSolution solutionVersion="1.0.0.33" productVersion="16.0.0" PIVersion="1.0.0.0" href="https://qdabra.sharepoint.com/Website/Invoice_Request_Library/Forms/template.xsn" name="urn:schemas-microsoft-com:office:infopath:Invoice-Request-Library:-myXSD-2017-02-15T12-49-08"?>

In the sample above, we have the Processing Instruction name located at the beginning of the string: mso-infoPathSolution. The succeeding data are the contents for this particular Processing Instruction.

Let’s build the InsertPi command.

InsertPi /name=mso-infoPathSolution /data=solutionVersion="1.0.0.33" productVersion="16.0.0" PIVersion="1.0.0.0" href="https://qdabra.sharepoint.com/Website/Invoice_Request_Library/Forms/template.xsn" name="urn:schemas-microsoft-com:office:infopath:Invoice-Request-Library:-myXSD-2017-02-15T12-49-08 /dsname=GET_Invoice_XML /update=true

Here we have declared the name of the Processing Instruction along with the data content. We have also declared the parameter dsname so that qRules identifies where we would like to add the processing instructions to, and that we are updating the data that is already existing.

For the first action for this command, we will

Insert PI
- Rule name: Insert PI
- Condition: none
- First Action:
  - Target field: Command node of qRules
  - Value:

InsertPi /name=mso-infoPathSolution /data=solutionVersion="1.0.0.33" productVersion="16.0.0" PIVersion="1.0.0.0" href="https://qdabra.sharepoint.com/Website/Invoice_Request_Library/Forms/template.xsn" name="urn:schemas-microsoft-com:office:infopath:Invoice-Request-Library:-myXSD-2017-02-15T12-49-08 /dsname=GET_Invoice_XML /update=true

Note that this is a hardcoded value and not a formula

Second Action:
- Target field: Command node of qRules
- Value:

InsertPi /name=mso-application /data=progid="InfoPath.Document" versionProgid="InfoPath.Document.4" /dsname=GET_Invoice_XML /update=true

Submit
- Rule name: Submit
- Condition: none
- Action target field: Command node of qRules
- Value:

SubmitToSharePoint /dssubmit=SharePoint Library Submit /dsname=GET_Invoice_XML

In this command, we are using the SharePoint Library Submit data connection for the submit operation, and we declared that the GET_Invoice_XML is the data source that which will be submitted.

Set Success
- Rule name: Set Success
- Condition:
  - Click the condition link and set the first dropdown to the Result node of of qRules
  - Leave the second dropdown to equals to
  - Select TRUE for the third dropdown
- First Action
  - Target field: success node of the current data connection
  - Value: Type in the word “success” without the double quotes
- Second Action
  - Target field: Status node of the current data connection
  - Value: Status field of the FormLogic section from the Main data source

Your action should look similar to this:

Set Failed
- Rule name: Set Failed
- Condition:
  - Click the condition link and set the first dropdown to the Result node of of qRules
  - Leave the second dropdown to equals to
  - Select FALSE for the third dropdown
- Action target field: success node of the current data connection
- Value: Type in the word “failed” without the double quotes

When done, upload your form to FormsViewer.

At the FormsViewer site, click on Upload a New Template.

Provide a meaningful name, drag the XSN template, and click on the Upload button.

When done, click Open Form to view and test your Dashboard:

Report Builder - Filtering data from a repeating table in DBXL

donstephen — Mon, 03 Aug 2015 04:06:00 GMT

Have you ever encountered querying data from a repeating table on your form uploaded in DBXL, and adding a filter to your query returns all the data in that document including the ones you don’t need?

This happens because the Report builder’s filters are per document filters - if a document that matches the filter has multiples rows, the service will return all the data queried from that document.

Filters can manually be added to the base path if more complex XPath filtering is desired - for example, if you are returning high level form data, but only want forms that meet a particular criteria based on data in a repeating node, you can add the filter manually to the base path:

/my:myFields[my:group1/my:childField = 'Important']

Here’s an example of a form's schema where the data about to be filtered is repeating data:

Using the schema sample above, the Report Builder’s Fields to Retrieve section should look like this:

In most cases, filtering the query will simply need a filter similar to this:

However, this will return all the data from all documents in DBXL that will match the filter above when you click on the Perform Query button – including rows from the table being filtered that does not match the filter description:

To remedy this, you can remove the filter and do the filtering in the Base Path:

This should now get you the data you need:

In the sample used, sorting the phone number doesn't really make any sense. In case you would need to sort the data you are querying, you should add the filter at the repeating group of the node you intend to sort (in the image below my:Product is the repeating group):

Updating the DBXL Administration Tool after installing Office 2013

donstephen — Thu, 24 Apr 2014 03:37:00 GMT

After updating your Microsoft Office to an MS Office 2013 version, the DBXL Administration Tool is left outdated and becomes unusable. To cut the explanation short, previous versions of DAT are not compatible with Office 2013. And here is a quick guide on how to update the DAT Template:

1. Log-on to the server where DBXL is installed.

2. Proceed to the folder location where the DAT template is stored and replace the file with this version (in a non-SharePoint default installation, the template should be located here: C:\Inetpub\wwwroot\QdabraWebService\install).

3. After updating the file, open the following URL in IE: http://<servername>/QdabraWebService/DbxlAdmin.asmx (If on Windows, Vista or Windows Server 2008, run Internet Explorer as Administrator.)

4. Click on the PublishDATFormTemplate method.

5. Enter the path of the DAT-Template form, which is saved to the local drive (i.e. C:\Inetpub\wwwroot\QdabraWebService\install\DAT-template.xsn).

6. Call PublishDATFormTemplate method by clicking on the Invoke button.

The DBXL Administration Tool should now have an updated DAT template for MS Office 2013.

Don Stephen Lambatin

Creating a Bulk Editing Dashboard for Office 365

The Target Library (Optional)

Deploy the site template (STP) file.

Create the Library using the STP file

FormsViewer

Creating the Dashboard

Report Builder - Filtering data from a repeating table in DBXL

Updating the DBXL Administration Tool after installing Office 2013