How To: Populate Repeating Section with SQL Data

There are plenty of reasons that we would want to view data on a form but pulling that data from other sources can be difficult. Not only that but formatting that data so that it is readable by the end user is just as important! With Nintex Forms for SharePoint we can easily pull back SQL data, but we can also pull back multiple records and then present them in a way that an end user can easily consume. Let’s look at how we can achieve this.

Form Setup

In your form, you will need a Repeating Section control that will be used to add each record from the SQL query. Within the Repeating Section, you can add as many controls that you need as they correspond to the columns you will be getting from your SQL query. For this example, I will be querying for “property_ID” and “state”, so I will need two controls in my Repeating Section.

For each control in your Repeating Section, you will need to update the Control CSS Class with a unique name so that we can push the SQL data to it. In mine, I set the control for Property ID as “rs_propertyid” and State as “rs_state”.

We will also need to make sure that the Repeating Section itself has the proper CSS classes. Some of these are set by default, but to be sure, make sure your Repeating Section looks like this:

Advertisements
Advertisements

Now that we have where the data is going all setup and configured, we should go and get that data! For this we will use the SQL Request control. Place the SQL Request control anywhere on your form. If you have not setup this control in the past and need some assistance, check out the Nintex Help Files and specifically look for the SQL Request. You will need to select the server and database where you want to pull from and then add your query. For the query, we want to be sure that it returns in a format that we can easily work with in the form. This would be XML, so here we can add FOR XML PATH (‘record’) and return the data in XML. Here is a sample:

SELECT TOP(1) ( 
SELECT 
property_ID, 
state 
FROM TABLE_NAME 
WHERE agent_ID = 'XXXX' 
ORDER BY property_ID 
FOR XML PATH('record')) 
as datatable FROM TABLE_NAME

Final steps for this control are to be sure to set the Value field and Display field within the SQL Request control as “datatable” and enable the data to be stored as a JavaScript Variable. This is important as it allows us to grab the data and then do something with it!

If you perform a “Run Now” you should get a result like this:

So now we have the SQL control getting the data, and the Repeating Section setup to receive the data, but we need to push the data from the SQL control to the Repeating Section. We do this via adding a custom script to the form.

Advertisements
Advertisements

The Script

If you are unfamiliar with JavaScripting, that is ok. I took a simplistic approach to this so that anyone can easily add more data points if needed. That said, I am sure that there are more eloquent ways to accomplish this. For now, we know this works! I will break this down a bit and walk through it but if you want to skip over this and get right to the script, the full script is as the end.

The first function that we need to build out is to process the data. We need to pull the SQL XML data into the something that we can work with. From there, we will match each record using RegEx and create a new array to hold all of our parsed data. I added comments for placeholders if you want to add more data points to query. You may notice there is a line to delete the last row in the Repeating Section, this is to clean up the form a bit and only present the queried data.

function processData() {
	/*Get XML Data*/
    var sqlXML = NWF$("#" + xmlData).val();
    /*Break XML apart for each record using RegEx*/
    sqlXML = sqlXML.match(/<record>(.*?)<\/record>/g);
    /*Create new array for all parsed data*/
    var parsedData = new Array();
    
    /*For Each Record, pull out the Property ID and State*/
    NWF$.each(sqlXML, function(i, val) {
        var xmlRowData = NWF$.parseXML(val);
        var NWF$xmlRowData = NWF$(xmlRowData);
    	var tempArray = new Array();
    	
    	/*Here you can add or remove as many data points you may need*/
        var tempPropertyID = NWF$xmlRowData.find("property_ID").text();
        var tempState = NWF$xmlRowData.find("state").text();
        /*EXAMPLE: var tempYourVariable = NWF$xmlRowData.find("YourDataFromSQL").text();*/

        tempArray.push(tempPropertyID);
        tempArray.push(tempState);
        /*EXAMPLE: tempArray.push(tempYourVariable);*/

        parsedData.push(tempArray);
    });
    processArray(parsedData);
    /*Delete Last Row In Repeating Section*/
    NWF$('.repeatingSection .nf-repeater-row:last').find('.nf-repeater-deleterow-image').click();
}

Now that we have the data in an array, we can process the array and add it to our Repeating Section. We do this by pulling out each data point we added to the array and putting it into the corresponding control within the Repeating Section. We also remove the “Delete Row” image to avoid data being deleted from the form. Lastly, we add another row to the Repeating Section and loop back through the array.

function processArray(array) {
	function addRow() {
		var currentRow = 0;
		while (currentRow < array.length) {
			var i = currentRow;
			/*Add data to Repeating Section*/
			/*Here you can add or remove as many data points you may need. Be sure to increment 'n' in array[i][n]*/
			NWF$(".repeatingSection .nf-repeater-row:last").find(".rs_propertyid").val(array[i][0]);
			NWF$(".repeatingSection .nf-repeater-row:last").find(".rs_state").val(array[i][1]);
			/*EXAMPLE: NWF$(".repeatingSection .nf-repeater-row:last").find(".YourControlCSSClass").val(array[i][2]);*/

			/*Remove image for row deletion if you want to not allow delete*/
			NWF$(".repeatingSection .nf-repeater-row:last").find(".nf-repeater-deleterow-image").css("visibility", "hidden");
			/*Create next row*/
			NWF$(".repeatingSection").find("a").click();
			currentRow += 1;
		}
	}
	addRow();
}
Advertisements
Advertisements

Putting it All Together

Now that we have the form setup to query the SQL data and capture it in a XML datatable, as well as the script to handle parsing the data and placing it into the Repeating Section, we just need to trigger it! This could be done in a variety of ways such as when another control is filled in or even when the form is loaded. I decided to do something a bit more direct and added a button. When you want to have the script execute, just push a button! To do this, we add a button control to the form and set the action type to JavaScript. Give it a label and then set the client click to our processData() function.

Advertisements

End Result

Form with SQL Query before JS Executed
Form with SQL data populated within the Repeating Section

Final Thoughts

I am not a JavaScript wiz by any means, so I am sure there are plenty of more “eloquent” ways to approach this, but it does work! From here, we can take the Repeating Section XML and perform a variety of actions with it. I wrote some other articles about this over in my Nintex Blogs, so be sure to check them out if you want to take this solution further and build a workflow to automate more!

Entire Script

function processData() {
	/*Get XML Data*/
	var sqlXML = NWF$("#" + xmlData).val();
	/*Break XML apart for each record using RegEx*/
	sqlXML = sqlXML.match(/<record>(.*?)<\/record>/g);
	/*Create new array for all parsed data*/
	var parsedData = new Array();

	/*For Each Record, pull out the Property ID and State*/
	NWF$.each(sqlXML, function(i, val) {
		var xmlRowData = NWF$.parseXML(val);
		var NWF$xmlRowData = NWF$(xmlRowData);
		var tempArray = new Array();

		/*Here you can add or remove as many data points you may need*/
		var tempPropertyID = NWF$xmlRowData.find("property_ID").text();
		var tempState = NWF$xmlRowData.find("state").text();
		/*EXAMPLE: var tempYourVariable = NWF$xmlRowData.find("YourDataFromSQL").text();*/

		tempArray.push(tempPropertyID);
		tempArray.push(tempState);
		/*EXAMPLE: tempArray.push(tempYourVariable);*/

		parsedData.push(tempArray);
	});
	processArray(parsedData);
	/*Delete Last Row In Repeating Section*/
	NWF$('.repeatingSection .nf-repeater-row:last').find('.nf-repeater-deleterow-image').click();
}

function processArray(array) {
	function addRow() {
		var currentRow = 0;
		while (currentRow < array.length) {
			var i = currentRow;
			/*Add data to Repeating Section*/
			/*Here you can add or remove as many data points you may need. Be sure to increment 'n' in array[i][n]*/
			NWF$(".repeatingSection .nf-repeater-row:last").find(".rs_propertyid").val(array[i][0]);
			NWF$(".repeatingSection .nf-repeater-row:last").find(".rs_state").val(array[i][1]);
			/*EXAMPLE: NWF$(".repeatingSection .nf-repeater-row:last").find(".YourControlCSSClass").val(array[i][2]);*/

			/*Remove image for row deletion if you want to not allow delete*/
			NWF$(".repeatingSection .nf-repeater-row:last").find(".nf-repeater-deleterow-image").css("visibility", "hidden");
			/*Create next row*/
			NWF$(".repeatingSection").find("a").click();
			currentRow += 1;
		}
	}
	addRow();
}

Process Automation: Where to Begin

When it comes to the process automation phase of an organization’s transformational journey, it can be a struggle to pinpoint which process to automate first. Would it be better to jump in and automate a process that is high profile and one that everyone in the organization uses on a daily basis, or perhaps on that is only used a few times a year by a select few? Should organizations only look to new processes that need to be automated or use one that is already automated but needs to be updated? Here are some things to consider when starting the automation journey.

Ask Around

If you are looking for processes to automate, why not ask those that are performing the tasks of a given process everyday! This is one area that is not considered until after automation efforts have begun or are about to rollout. Getting people to provide their insights and feedback allows organizations to have a holistic view of the process rather than what is perceived. This not only helps understand the full scope of what the process is, but also gets people involved in the ongoing efforts to improve their processes.

Advertisements
Advertisements

Another reason to get people involved early on is to avoid tension later when changes are rolled out. No one likes it when changes happen to a process and they were never told. Getting people involved early helps adoption of any changes as everyone has the ability to voice their concerns and provide feedback. This is a great time to uncover areas that are not well documented.

What Are The Benefits (Drawbacks)?

If your organization is struggling to find where to begin, you may need to look at the benefits of automating a given process. Are there gains to be made that will lead to revenue, time saved, or even employee happiness? What about the impact on the organization? Is the process one that reaches everyone within the company or just a small team? Is the process one that occurs once a year or every week?

Not only should you figure out benefits or automating a process but to also consider the repercussions of not automating a process. This could lead to things such as missed deadlines, delays in approvals, or even processes not starting in a timely manner. Think about an employee off-boarding process. If this process is not automated in some way, the organization runs the risk of an employee, that is not longer employed, having access to company assets and information. It is important to consider both the positive gains as well as potential losses when it comes to automating a process.

If It Ain’t Broke, Do Fix It!

Many times organizations jump into automation efforts and pick a process that is already in place and working. While this may be a good starting place because it is a well known, documented processes, it does not get anyone thinking differently about it. Often the process will be automated just as is and never improved or even questioned. Even the ones that are automated in some fashion should be reimagined and thought about from the standpoint of “what more could be done to improve this process”.

Advertisements
Advertisements

How to Measure

This is a difficult one as every organization will (should) measure their success criteria differently. Many times it falls into one of two buckets: cost and speed to market. While each of these aspects are important, your organization needs to look at each automation candidate or effort individually as well as the tool set that will be applied. For example, when looking at cost, it is easy to go with a solution that will save money or a solution that is rolled out very quickly. However, the drawbacks to that line of thinking could lead to missing functionality in the solution or having to go back again and again to fine tune the solution.

Speed to market is another aspect that comes up a lot as well when determining which processes to automate. This is because organizations want to see ROI on any technology or services they may have procured to automate their processes. While this is a great thing to see, it should not be the only area that drives decisions. Approaching automation with only this in mind can force decisions or cut corners to get a solution in place and rolled out.

Ultimately your organization needs to determine what your success criteria is and how to measure it before you dive into automation. If this step is skipped, it is almost impossible to determine if what was done to improve the process was worth it.

Advertisements

Final Thoughts

There is a lot of information out there regarding automation, which tools to use, what to consider when going through digital transformation, and all of the other buzzwords people love to throw around. I think that in the end, it is crucial to get your users involved early in the documenting of their processes and how to improve, as well as determine how to measure success within your organization.

Would love to hear your thoughts or ideas. What has worked or perhaps has not worked?

How To: Save Task Attachments to Current List Item

If you are using SharePoint Online tasks, you have probably run into the need to have users attach additional documents that are related to the task. While these attachments will be stored with the task, many times there is a greater need to attachment them to the original list item. This allows for all the associated documents and attachments to be together on one item rather than on related task items in separate lists. Let’s look at how we can achieve this in a few steps using Nintex.

Getting Started

Before we dive in, be sure that you have access to a Nintex Workflow Cloud (NWC) tenant. If you do not have one today, you can go to the Nintex and grab a free 30 day trial. The reason we are using Nintex Workflow Cloud is because it is a simple four (4) step workflow that is used with any of you existing workflows in Office 365. We are also going to use Nintex Workflow for Office 365 on the list that will generate the task.

SharePoint Online

For the SharePoint Online piece, you will need to have a list where you are using a task. In your list workflow you will need to setup the workflow so that there is a Start workflow in Nintex Workflow Cloud action on the outcome you wish to use. For this example, I have it on the Approve outcome branch, but could very easily copy and paste the action onto the Reject branch.

Simple task workflow

You will also need to create a workflow variable for the TaskID. The TaskID will be used to identify which task attachments to move into the list item in the NWC workflow. Now, let’s setup the Assign a task action in the workflow. Configuring the task action is straightforward, but I want to focus in on the TaskID section towards the bottom of the action. This is where you want to store the ID of the task that is going to be generated.

Assign a task action

Once the rest of the Assign a task action is configured, save it and we can move on. At this point, we have not created the workflow in Nintex Workflow Cloud, so we will need to go do that. Be sure note where your tasks (task list name) will be created, as that will be important later. Save your workflow in O365!

Advertisements
Advertisements

Creating the Nintex Workflow Cloud Workflow

In Nintex Workflow Cloud we will want to create a new workflow. For the start event, we will select Component Workflow, which allows us to call this workflow from O365. Let’s setup our variables to match the data that we will be getting from the workflow in O365. You will need at least the following:

  • ContextItemID Integer
  • TaskID Integer

You may want to pass more data, but to accomplish the moving of the task attachments, this is all we will need. Here is a look at the workflow and the steps needed:

  • SharePoint Online > Get attachment names
  • Logic and flow > Loop for each
  • SharePoint Online > Get attachment by name
  • SharePoint Online > Add attachment to an item
Move SPO Task Attachments

Let’s step through each of these actions. First up, the Get attachment names (1), is where we will target the item with the TaskID in the Workflow Tasks list and get the collection of AttachmentNames. I also store the NumberofAttachments just in case I need it in the future. Next, the Loop for each (2) item in the collection action is to loop through the collection of attachments and store the AttachmentName in a variable one at a time. Now that we have the attachment name, we can Get attachment by name (3) from the Workflow Task list by matching on the TaskID and the AttachmentName. Here we will store the result in a file variable called AttachmentToMove. Lastly, we Add attachment to an item (4) by targeting the list name where the Context Item is matching on the ContextItemID. Everything within the loop will execute each time for every item in the collection, or in other words, for each attachment on the task item.

For all your SharePoint Online actions, you will need to make a connection into that tenant. Once the connection is made, the action will ask for the SharePoint site URL. This will be the URL for the site that the list or workflow task list resides on. From there, simply select the list name from the drop-down and configure the required pieces. Once you have the workflow all setup and configured, go ahead and publish it to see the URL with the token needed to call it. It will look something like this:

Published Component Workflow in Nintex Workflow Cloud

Back to SharePoint Online

Last bit is to update the Start workflow in Nintex Workflow Cloud action in O365. Paste your URL from the workflow you just created and click connect. Once connected, you should see the variables that will be passed to the workflow via the web call. These will need to be configured but are straightforward. One is the Item ID of the current item the workflow is running on, and the other is the TaskID that we created and are logging in the Assign a task action. That’s it! You are all set now and any task attachments that are added to the task item, will be moved to the context list item.

Start workflow in Nintex Workflow Cloud

Final Thoughts

Through some simple steps, we can easily move SharePoint task attachments to the list item the task is associated to. This make keeping track of attachments and supporting documentation easy as they will always be on one item. We also built this process with Nintex Workflow Cloud, which opens the door to a lot of other possibilities when it comes to pushing and pulling data into various other platforms. Imagine if we had to push these attachments into Salesforce or do some sort of document generation from these. With Nintex Workflow Cloud, each of these scenarios are achievable with ease.

How To: Create List Items from a Repeating Section with Nintex Workflow

Last month I wrote the first part of this blog and that was focused on the Nintex Form piece of this solution. This month we will focus is on how to take those individual items on the single form and create a list item for each one. There are many scenarios that this could come in to play such as breaking out each item in a request to better understand metrics or even running processes on individual item rather than on the entire submission. Let’s jump in and take a look on how we can tackle this.

Scenario

For this scenario I will continue from the last blog and frame it as an expense report. The report will allow for each submission to have multiple items on the report, but we want to break out each item into a separate list for additional processing. For this, we will need to create a separate list to push the necessary data into. Here columns I created for my list:

  • Date of transaction (Date)
  • Description (Single Line Text)
  • Amount (Number)
  • Vendor (Single Line Text)

Keep in mind that this is the list is where the items will be pushed to in order to process them further. The next step is creating the workflow that will push each item into this list. This workflow will be on the list where your form resides.

The Workflow

The workflow itself is not overly complex as we are gathering each node in the XML structure and then creating a list item for each item in the XML (in the form). I break it down into two section, Query XML and Create Item.

Query XML
Create Item
Advertisements
Advertisements
Querying The XML

Let’s start with querying the XML in a parallel block to help speed up the process a bit since we are going after different nodes each time. For the Query XML action, you just need to pull from “Content” that is already on the item in your list (this is why we connected the Repeating Section Control to the RepeatingSectionXML list column). Once you have the query pointed to the right place, place your XPath query to the desired node you want to pull back. I recommend creating an item (filling out the form and submitting) in your list to look at the XML structure for your specific use case. Here is what mine looks like:

<?xml version="1.0" encoding="utf-8"?>
<RepeaterData>
	<Version />
	<Items>
		<Item>
			<_x0036_110e1da-1f9e-4f14-8766-c9ebb6610df6 type="System.DateTime">08/03/2020 00:00:00</_x0036_110e1da-1f9e-4f14-8766-c9ebb6610df6>
			<_x0032_e7e5e75-9cbc-4d7b-89fc-70983844451d type="System.String">Description Goes Here</_x0032_e7e5e75-9cbc-4d7b-89fc-70983844451d>
			<_x0038_f5c375c-86f6-4218-b493-af99e1dc5e2f type="System.String">100.00</_x0038_f5c375c-86f6-4218-b493-af99e1dc5e2f>
			<_x0030_a6cd3d3-1fdc-4e65-90bf-85c2720412da type="System.String">Vendor ABC</_x0030_a6cd3d3-1fdc-4e65-90bf-85c2720412da>
		</Item>
	</Items>
</RepeaterData>

What you will need out of this is the name of the node (My Date of Transaction nodeis : _x0036_110e1da-1f9e-4f14-8766-c9ebb6610df6). This is what you can paste into the //Items/Item/{Your_Node_Here}. Store the results in a collection variable so that you can loop through all of them. You will need to re-create this action for each node (list column) you wish to grab data from.

Query XML Action
Creating Items

Now that we have all of the data points we need, all that is left is to create a list item. This is rather straightforward, but we need to ensure that we line up each piece of data so we do not have mismatched data in our itemized list. In order to accomplish this we will loop through a single collection and pull the corresponding data point based on the index we are on. I chose to loop through the Vendor collection and store the output in a variable called VendorName and ensuring that I capture the index in a variable (called index).

For Each Action

With the index, we can easily pull the corresponding data from all of the other collections that we gathered in the previous part of the workflow. This ensures that all of the data will line up when we create the list item. Again, here we are using the parallel block action to speed up the processing time a bit, so once you have one Get Item From Collection action configured, you can easily copy and paste for each collection you need to pull from. All that is need when configuring the Get Item From Collection action is the collection you wish to target, the index to pull from, and what variable you want to store the result in.

Advertisements
Advertisements

Once you have all of the data pulled from your collections, the last step is to create a list item from the data. We will first pick the list where we want to create the item (the list we created at the beginning of this blog) and pass the variables to the appropriate List Item Property. Be sure to give it a title that makes sense and leverage the references Nintex provides. Here is how it looks when all setup:

Final Thoughts

This process is meant to pull out repeating section items from a form and push them into a different list than the one the form sits on top of. The reason for this could vary, but I see this a lot when having to perform processes on the individual items rather that the submission as a whole. Workflows can be created to process each item individually. In our scenario, a supervisor could approve or reject expenses at a much deeper level than a blanket approve or reject. How else could this be leveraged? Let me know in the comments!

Nintex Forms for Office 365: Save and Continue

This is something that has been requested for quite some time and happy that it has been added into the product. Save and Continue is available in Responsive Nintex Forms for Office 365. This quick blog to meant to cover some of the basics about what it is meant for and what it does. Let’s dive in!

What is it?

The Save and Continue feature that has been added to the Nintex forms product for Office 365 in the July 2020 release. This new feature allows users to partially complete Nintex forms and return to them later to complete. Previously, in order to achieve this, one would have to build all of the necessary logic and functionality into the form (check out this older blog). The feature has been requested for some time and is intended to help with longer forms where there are possible disruptions to users filling out the form. Also, there may be a need for field workers to fill out partial information on-site and complete the rest of the data entry at a later time. I am sure there are other use cases, but you get the point; you do not need to complete the entire form each and every time now!

Advertisements

What happens to the data?

Since we are filling out a form with Nintex for Office 365, the data is being committed back to the SharePoint list that the form is connected to. This means that any data that is filled out on the form, is committed back to the list each time it is saved. When a user needs to return to the form and add more data or complete and submit the form, it is as easy as finding it in the SharePoint list and opening the form again. What about validations? Any data that is required by SharePoint is still required to be filled out even when using Save and Continue. However, if the control is required on the form, but not by the list, Save and Continue will bypass that control validation. So, for those that do want to be hindered by SharePoint column requirements, you can easily build in the required controls and rule validations into your Nintex Forms and take advantage of the Save and Continue functionality.

Another piece of this to consider is the workflows that would be kicked off when a form is submitted. What happens when a form is saved and will be completed and submitted later? Euan Gamble posted a great video highlighting how to approach this (see below) but here is a quick overview.

Form Submit Action Panel

In the Submit/Cancel action panel, connect it to a text column on your list. In the video example it was Form status. You can set the value for when a user clicks on “Submit” to whatever you want, but I would recommend something list “Completed“, “Final“, or “Submitted” so everyone knows this is a filled in form. You can also set the value for when a user clicks on “Save and Continue” to be something like “Draft” to signify that it has not been completely filled out.

With this Form status column set, you can build conditions into your workflows to only kick off if the Form status is equal to “Completed” or whatever you choose. Now each time the form is saved, the workflow will know if it needs to execute all the steps or not kick off at all.

Workflow Start Options

Final Thoughts

All the above mention functionality can be added into your Responsive Nintex Forms through simple configurations. There is nothing that needs to be deployed or added to your solution for you to take advantage of this. Everything is in the Nintex solution already, just waiting for you to leverage it!

How To: Using Nintex Forms to create Itemized Requests

There are a lot of complex forms that users interact with on a regular basis and I wanted to review one of the common use cases that we see with Nintex Form; an itemized form. If you are unfamiliar with an itemized form, think of any form where you are adding multiple entries or items on a single form. A great use case for this is an expense report. You would not create a report for each expense, but rather multiple expenses that you will submit in a single report. Let’s look at a simple example of just how easy it is to setup.

Scenario

For this scenario, we will be building an expense report that our users can fill out each month from an intranet site within our O365 environment. Let’s keep it simple and build our form to gather the following expense details:

  • Date of transaction
  • Description of transaction
  • Amount of transaction
  • Vendor Name

As you can imagine, there are many more things that we can require from our users such as who was in attendance, copy of a receipt, etc. However, adding more data points to our form is straightforward and can very easily be done.

Setup Environment

For this example, we are going to build out a Nintex Form in Office365 using a SharePoint Online site. I have a site already that I will use and created a list called Monthly Expense Form. In this list I created one (1) column:

  • RepeatingSectionXML – Multiple Lines of Text

Again, you could add more columns to your list if you want or need to, but for this example, we are focusing on the itemization within the form. The RepeatingSectionXML column will be used to collect all the items that are going to be added to the form. You may be able to guess that we will be using a repeating section within the form, but more on that in the next section. Once I have my site and list setup, we can dive into the Nintex Form designer.

Advertisements
Advertisements

Nintex Form Designer

I decided to build out my form in classic view because there is a need to add CSS to the form. Don’t worry, we are not diving into CSS in forms today! You could use either Responsive designer to accomplish the same thing, however, there are some differences that I will point out at the end.

Starting with the out of the box Nintex Form, we can remove the RepeatingSectionXML label and control and replace it with a Repeating Section Control.

Within the repeating section control, we want to add in all the data points that need to be collected for each expense (see above). To do this, grab a date control and drag it into the repeating section control. We also need a few single line text controls for the other data points. Once you have all your controls added in, it should look something like this:

Now that we have all the controls in the repeating section, each time a user fills out the form, they can easily click on the “+ Add new row” to add another item to the same form. This makes submitting expenses super easy! We could also apply this to requesting new materials on an invoice or even requesting a replenishment of office supplies. The use cases for this are many!

We are collecting the data, that’s great, however, there is nothing detailing out what each control is on the form. That is a problem as the users will not know which control is for the amount, vendor, or description. To rectify this, we simply add some labels outside of the repeating section control. Why outside? We do not want to repeat the labels each time there is a new item. Drag the label control onto the canvas and align it so it is above the proper control on your form:

I would say the form is good to go now. Wait, what about the data the is being collected? Since we are not committing it back to a SharePoint column, where is the data going?

The Form Data

The form data is still being collected in the Nintex Form even if the data is not being committed back to a SharePoint list column. This means that each time I open the form, it will render all the data that was collected in the correct controls. What about when I want to get to the data from the repeating section and use it elsewhere, like within a workflow or another list? There are a few different ways to go about this but one of the most straightforward ways to is to simply connect the repeating section control to a SharePoint list column (I did it here in this blog). In our scenario here, we are connecting it to the RepeatingSectionXML column we created earlier.

Final Thoughts

We see this scenario come up a lot and there are a lot of ways to approach it. This is how I have built out solutions in the past and approach similar ones each time. From here, we can take the XML data within the repeating section and do just about anything with it within our workflow. We could even send each item in the repeating section to another “Itemized” list with a link back to the original form (we will cover that on the next blog). This keeps the original form intact but gives a clean list view for each item in the repeating section for users to perform further actions upon, like reviews and approvals.

Default SharePoint UserProfile Properties

I constantly have to search for the out of the box UserProfile properties for SharePoint and figure I should write them down someplace for myself but also for others to easily find them as well.

I will add more details to each of them over time but here are the Basic Information and Contact Information properties that are most commonly used.

Display NameName
Section: Basic Information 
About meAboutMe
Account nameAccountName
Active Directory IdADGuid
Ask Me AboutSPS-Responsibility
Claim Provider IdentifierSPS-ClaimProviderID
Claim Provider TypeSPS-ClaimProviderType
Claim User IdentifierSPS-ClaimID
Data sourceSPS-DataSource
DepartmentDepartment
Display OrderSPS-DisplayOrder
Distinguished NameSPS-DistinguishedName
Don’t Suggest ListSPS-DontSuggestList
Dotted-line ManagerSPS-Dotted-line
First nameFirstName
Hire dateSPS-HireDate
IdUserProfile_GUID
Job TitleSPS-JobTitle
Last Colleague AddedSPS-LastColleagueAdded
Last Keyword AddedSPS-LastKeywordAdded
Last nameLastName
ManagerManager
Master Account NameSPS-MasterAccountName
MemberOfSPS-MemberOf
My Site UpgradeSPS-MySiteUpgrade
NamePreferredName
Object ExistsSPS-ObjectExists
Outlook Web Access URLSPS-OWAUrl
PeersSPS-Peers
Personal sitePersonalSpace
Phonetic Display NameSPS-PhoneticDisplayName
Phonetic First NameSPS-PhoneticFirstName
Phonetic Last NameSPS-PhoneticLastName
PicturePictureURL
Proxy addressesSPS-ProxyAddresses
Public site redirectPublicSiteRedirect
Quick linksQuickLinks
Resource Forest Account NameSPS-ResourceAccountName
Resource Forest SIDSPS-ResourceSID
Saved Account NameSPS-SavedAccountName
Saved SIDSPS-SavedSID
SIDSID
SIP AddressSPS-SipAddress
Source Object Distinguished NameSPS-SourceObjectDN
TitleTitle
User nameUserName
Web siteWebSite
Work phoneWorkPhone
Section: Contact Information 
AssistantAssistant
FaxFax
Home phoneHomePhone
Mobile phoneCellPhone
OfficeOffice
Office LocationSPS-Location
Time ZoneSPS-TimeZone
Work e-mailWorkEmail

Building Automation Around Existing Processes Using the Nintex Gateway

I worked through a different use case recently and wanted to share out my thoughts and approach to it for others to leverage in the future. This one is unique because I did not focus on improvement but rather on putting intelligent automation around process. Like all other things, there are multiple ways to approach this and probably more elegant ways, but this sheds some light on how we can solve the issue of taking existing data from a cloud source and pushing it to an on premise machine to be processed. So let’s take a look at what I did and how I approached it.

Scenario

I need a way to submit my monthly project risks and have them validated in order to be uploaded into a master system. I have a predefined Excel document to be uploaded and the current process requires me to email it to our admin for validation. Now, as an admin, this is manually done countless times at the end of each month and want to put some automation around it.

For those thinking about using Nintex Forms or any other front-end, this would be a viable solution, however, there are times where we do not need to. Think about existing processes where you may be leveraging Excel files that have 50+ columns that have their own drop-downs and rules within. We do not want to rebuild that interface, at least right now. That is a separate effort entirely. While we would agree that there is a better way to (should) approach this, for now, we simply need a way to off load the burden of checking and validating the data within the file to something more hands-off.

Advertisements

The Process

Ultimately we want to have the Excel file checked for any issues. We need to setup a botflow that we can train to look for various business logic exceptions and then format the document to send back to the user. But what about getting the file to the RPA bot? Typically users would email the Excel file to the admin, but if we want to eliminate, or reduce, the interaction needed from the admin, we need a way to get the Excel file from the user to the RPA bot. Also, we will need to consider how do we manage letting the user know that there is re-work required. This is where Nintex Workflow Cloud comes in to focus.

We can easily build out a simple form to collect the Excel document and route that document to a cloud based file storage location. We will use OneDrive for this scenario. Once the the form has been submitted with the uploaded Excel document and stored into OneDrive, the RPA bot can easily get to the file. But how do we kick off the RPA bot? How does it know that it has something to go and do? Why we tell it to do something! Through the Nintex Gateway we can kick off a RPA job and process whatever we define within the botflow, which, the first step would be to grab the Excel file!

We pass data through the Nintex Gateway in order to provide necessary details for the botflow to execute properly. These details are:

  • Excel File Location
  • Submission Date
  • Submitter’s Email Address

From here, the botflow has all that it needs to execute the process and validate the file. In my example, I am looking for special characters; specifically semicolons (“;”) and slashes (“/”) as well as validating that the data provided matches what is expected. I am looking at the “Inherent Risk Rating” for each project and have a list of expected choices. If the provided data does not match, I need to mark it along with the special characters.

Looking for Special Characters

I made a sample file with only 6 columns and 5 records for simplicity, but you could imagine just how time consuming this would be to manually check with hundreds or records and 50+ columns. Here is how it looks in real time:

Final Thoughts

I wanted to share this experience and showcase how easy it is to go from Nintex Workflow Cloud to Nintex RPA using the Nintex Gateway, but also to highlight something we often get stuck in; process improvement. I admit, the first time working on this solution, I wanted to build out a polished form in Nintex Workflow Cloud, but that would have taken a long time. I quickly realized that I can take what was already there in front of me and simply wrap automation around that. Improving upon this further will make us take a closer look at the Excel document and see if we can make a form out of it by removing data points or perhaps moving them to other steps in the process.

Working From Home: The Impact

Now that we most of us have been working from home for a few weeks, we are starting to see the impact it has to our daily routine. Not only to ours, but to those around us, like your children that are now trying to do all their schoolwork from home or your spouse trying to find normality in all of this. Even those that are close to you like your extended family or close friends are impacted by this change. Why is that? For some, it has been a large change, but for others, like me, working from home is a normal workday. So, I wanted to share my thoughts and experiences so far and have an open discussion on how to handle these disturbances.

This is fine…

For those that do not know much about me, I have been working from home full time (other than when I have work events) for over a year now. In the time that I have been working from home I have been able to remodel a house, buy a new house, move, and sell a house (the one I remodeled). Oh, and I have 5 kids. Yeah….5. Their ages are 9, 6, 4, and twin 2-year-olds, so needless to say, each day is an adventure in my house. My amazing wife is a full-time mom but also taking classes at Penn State University for Cyber Security!

So now that you know a bit more about me and how crazy my life is normally, let’s talk about all the changes that have affected everyone over the last few months. In case you missed it, I am talking about the shelter in place order that we were all given in order to slow down COVID-19. I won’t go into the politics of it nor the other impacts this epidemic is having on the rest of the world, let’s just focus on our work and home life. I am fortunate enough to work for an amazing company and poised for success while working through this difficult time. I wrote a blog previously about how to be successful while working from home so go check that out. However, it takes more than a dedicated space and some leveraging technology to be successful. You need to understand that the situation affects everyone around you and how you can adjust to that is just as important.

Advertisements

The Changes

For many, just working from home on a regular basis has been a big change. Not going to an office or traveling to clients is something we all must figure out on how to adapt to our new work life. This means thinking about how to manage your time better or creating a routine to stay on top of work and not slip into hours of surfing YouTube videos! But what about being comfortable not having that social interaction? What about not having those 5-minute breaks to go and chat with a co-worker? These are important aspects of our routine that is difficult to replace when we are being told to not leave our house!

Another large change has been having kids home. All. The. Time! Don’t get me wrong, I love my kids, and think the world of each one of them, but there is a reason I am not a 4th grade teacher! My wife and I do our best to help both of our kids that are in school with their work and making sure they are doing it. This alone is not difficult and not very time consuming but coupled with the typical day-to-day that is required with work, it becomes a bit stressful. You must juggle their work, keeping them on track and engaged, as well as your own work.

Also, I have 3 kids that are not in school yet, so we must make sure they are being entertained and engaged with as well. While my youngest ones would love nothing more than to watch Daniel Tiger all day, they need a bit more. This means coming up with activities for them to do while the other kids are doing their schoolwork. Before all of this, spending time with them was easy and happened naturally, but now we must actively make time for all of them whenever possible.

As I mentioned at the beginning, for me, I did not see this shelter in place order as much of a change for me as I already stay home. The one area I overlooked was how it affected those around me. My wife, for example, has her own routine while I do my work each day. Typical day is cleaning, meal prep, diaper changes, more cleaning, laundry…you get the point, it is a lot of stuff, but she had a routine. When the order was given in our state and the kids were no longer going to school, my wife had to add on to her already packed schedule, 4th grade and kindergarten home-schooling.

This caused a lot of stress for my wife and ultimately myself as we had to figure out how to manage all of it. Again, any one single part of this is easily manageable, but when you combine everything, shelter in place, kids now needing home schooled, it becomes the perfect storm.

Dealing With it All

It was not until the stress got so bad between my wife and I that I had to make a change and made me realize that we had a unique opportunity in front of us. The kids were home all the time and I was not traveling for work. This meant that I could spend much more time with my kids and help them with their schoolwork, play trains or superheroes with my younger kids, or just sit down and read a book. For my wife, this meant that I was available for more projects! However, I overlooked at for the first week or so because for me, it was business as usual, just working from home.

Since then, we put together a plan and it has been working out well so far. I now have a standing meeting with my oldest son every morning at 9am. This allows for him to show me everything that needs to be completed (see image) that day and ask for clarification if he is confused on any of it. I then transition into work calls and emails but force myself to take more breaks with my kids throughout the day. This helps my wife so she can get a break from dealing with kids all day but also myself to keep in mind that there are 6 other people in the house! I dedicate time in my day to do lunches with everyone so that I can get that human (even if it is with a 2-year-old) interaction. My wife does all the order pick-ups so that we are all not exposed to anything and I am OK with that. Being a gamer and working from home means I could stay indoors probably longer than most 🙂

Final Thoughts

I know this blog was not my usual content, but I wanted to share out my experiences thus far as it is important to keep things in perspective during these trying times. We are all a bit stressed out with all the changes and it helps to know that we are not the only ones going through it. For me, it is with my wife and kids, but for others it may be obnoxious roommates or even pets that are not handling this well. Whatever your situation is, just try to keep in mind what is important and take care of that. The rest of it is unknown at this stage.

Working From Home, Everyday

There has been a surge in companies that have recently recommended to their employees that they work from home given the current health conditions. This presents a unique situation for many people as they have never had the opportunity to work from home. Your entire workday is different when you work in an office compared to that of someone that is remote. There is no commute. There is no “water cooler” conversation. Just work. So why do people, like me, do it every day? I believe there are more benefits from working from home than in an office, but there are some drawbacks.

Dedicated place to work

It goes without saying but you should have a dedicated place to work. Whether it be your bedroom or an office, you need a place to work that is quiet and free from distractions. I have an office now, but I have worked from a bedroom before! The key is to find a place that is separate from your normal day to day living area so that you can focus in on the work. If all else fails, there is always Starbucks or even your local library.

My office

Use Technology

We live in an age where we are always connected. Be sure you use it to your advantage! Talk to coworkers on your instant messaging service so that you are in contact with them. Since you’re not in an office setting, there is little time for chit-chat! Also, use your camera when on conference calls. This goes a long way if you are meeting with clients or even coworkers. Yes, this means you need to look presentable, but you don’t need to worry about where you are given most video conference services now provide virtual or blurred backgrounds.

Use background to avoid disruptions

Keeping a schedule

It is important to keep a schedule so that you stay on task. We all have our routines; check email, coffee break, project work, lunch, etc. but it is key to set some sort of schedule while working from home. This keeps you on task with what needs accomplished but also allows for you to plan much easier as new tasks arise each day. This will also help when you want to take some time for yourself and run to Starbucks!

For me, I take some time in the morning to sit with my kids and wife. This helps me set expectations with them on what I have planned for the day (meetings, heads down work, etc.) but also take a moment to enjoy them. I don’t have a commute to my office…it is right down the hall!

Advertisements

Taking breaks

Just as important as making and keeping a schedule, consider taking breaks. When working from home you are always on. Ask anyone that does this on a regular basis, you’ll do more work from home than in the office. This is because we live in an age where we are always connected. We always have our phones on us which means we have access to Teams, Slack, email, or anything else you may use!

This does not mean that you must do work all the time. Taking breaks or dedicated time to disconnect is a big part of doing this over a long period of time. I take time each day to eat with my kids, sit down at nap time, and sometimes get outside and play for a bit. The key is to know when you can fit that in. Setting expectations with yourself and loved ones is important for you to achieve your daily goals.

Taking 5 minutes to read to my “co-workers”

I am sure there are plenty of other areas to consider, but these are the ones I focus on every day. What do you think or what do you do that brings you success when working from home? Leave it in the comments and talk soon!