Skip navigation
All Places > Getting Started > Blog > Authors greenawayr

Getting Started

9 Posts authored by: greenawayr Champion
greenawayr

Workarounds

Posted by greenawayr Champion Sep 29, 2017

So this morning I was trying to write to a date & time field on a button click on the new responsive forms designer. I clicked on the button settings > Advanced > Connected To but in my list of columns, my Date & Time column wasn't there. It appears that only the text based columns can be written to here. This was frustrating, especially as you can write the date and time to a text column using the common references.

 

Ah-ha, I wonder? Let's see.

 

That light bulb moment you just witnessed was me thinking, if I set the button to write to a Single Line of Text (SLOT) using the "Current Time" reference, I get myself a date and time, that's fine, but no good for reporting really, I want my datatype to be Date and time, not just a string containing the date and time. Nevertheless, I published the form and knowing that my button was configured how I wanted it to be, then in my list, head to the List Settings, head to the SLOT column settings for the column I've just connected my button to and in these settings I can switch the data type from SLOT to Date and Time. 

 

Now here comes the test, firstly, is my form still configured? Head back to the form designer and check my button settings, all the configs are still there...great.

 

Now create a new item in my list and hit that button and Bingo! Date and time written to a date and time column, even though the form designer didn't really want me to. Perfect.

 

I'm guessing that Nintex didn't want people to be able to write to date and time columns because they might be prone to putting something other than the date and time in here and values will be lost, but as they give us a reference, it still seems odd to me.

 

Anyway, this relatively simple fix means you can write to a datatype using certain actions that you might not have thought you could. 

 

I wonder whether the same might work with a Choice column (you could allow Fill In choices for flexibility perhaps)?

 

On that note, what other workarounds/fudges/obstruction breakers/loopholes have you discovered in your journeys through Nintex? Not after whizzy javascript solutions here, stuff that people can do from the interface with no custom code. Intrigued to find out what else lingers out there.

A while back I wrote this blog about copying attachments from a list item to a document library.

 

Well times have moved on and technologies change. I'm currently learning my way around O365 and of course Nintex Workflow and Forms for the platform.

 

My requirement this time was similar, but with a twist.

 

I'm creating a Mobile App that will allow frontline users to go out to sites and fill in inspection forms. The customer is keen for these inspections to be visually orientated so therefore they're going to be taking pictures on tablets to provide evidence that a section has met standards. At least 1 photo needs to be taken for each section. A section is going to be a particular area of the store (Front of House, store room etc). We then need to be able to easily identify section the photo was taken via metadata.


To achieve this we created our form with an attachment control for each section. This gives the user a nice easy way to associate the pictures with a certain section, and crucially it allows us to name each of the attachment controls.

Also, I needed to allow for the fact that they may not upload a photo for every section.

 

Kudos go to Marian Hatala for helping with the next bit. He suggested using the FormData XML to identify each of the attachment controls, and each of those attachment controls contained the filename of the picture that had been taken with it.

 

So my first job is to identify each of the sections. I create a 2nd list with an item for each of the sections in the form, I then query that list and store the results in a collection.

 

I then use a For Each action to iterate through each of the items in that collection. This provides me with the element I'm going to be looking for in the FormData XML so it needs to be spelt exactly the same as the Name of the attachment control on the form

 

 

My Query XML action looks a little like this

Here I am querying the NFFormData Item Property and my XPath query simply looks inside the FormVariables element for the name of the attachment control. Because there can be more than 1 picture in each attachment control, the result is stored in a collection, which I will break out using the following Regex actions. I'm no expert in Regex, so happy to be told if there is a better way of doing this.

 

Step 1 - [" this removes the leading square bracket from the array

Step 2 - ;"] this removes the trailing square bracket from the array

Step 3 - Use the Split function in Regex to split the array by semi-colons

 

The reason this is required, is that this is the output of our array

["IMG_20170614_164431_008.jpg;IMG_20170614_164433_045.jpg;"] 

But this is the output of our collection after our Split function

["IMG_20170614_164431_008.jpg","IMG_20170614_164433_045.jpg"]

These subtle differences make the difference for our For Each loop

 

So I now have another collection that I will perform another ForEach loop on (so I am looping through however many attachments are contained within each attachment control).

The variable that is output from the ForEach loop is the name of my attachment, with a snag, I need to perform another Regex to remove the trailing ";" on the string (if it has one) and then I'm ready to go and upload it.

Above are the screen grabs of the configuration of the "Office 365 Upload File" action.

A couple of things to explain here.

 

File to Upload - We're uploading content that already exists in SharePoint, but if you didn't know, attachments are stored in a hidden folder within the List that the item belongs to, so our URL here is "Lists/LISTNAME/attachments/IDOFITEM/Attachmentname.jpg".

 

Folder path - This is the name of the library the file will be uploaded to PLUS any folders within the library that you want the file to be placed into.

 

Fields - These are the pieces of metadata we will use for our new documents/files.

 

The final piece for our requirement is the Run If. This is to capture any instances where there is no attachment in a control. If this is the case the string that should be the filename, will simply be a [ ] and our Run If makes sure the variable doesn't equal this before trying to upload, otherwise the upload fails and the workflow falls over.

greenawayr

Free Text Search of SQL

Posted by greenawayr Champion Feb 6, 2017

A slight variation on Retrieve cities from a database post by Pamela Denchfield where Pamela uses a dropdown to create a cascading dropdown filter on a SQL Request control.

 

We have a lonnnnnnnngggggggg list of suppliers who our users can select from when they want to request a new product.

We've cleansed this list as best we can but it still numbers thousands and it's not fun for our users to have to scroll through the dropdown to try and find the needle in the haystack. Because there's no set choice of parent values that would allow us to do a cascading dropdown, we want to let our users search for the suppliers.

 

First add a text box to the form and give it a name, in our case I'm it "SupplierSearch".

 

Then we simply add our SQL Request Control to our form

and fill in the db details

The important part for us is the Query.

select name from vw_sp_CleansedSuppliers WHERE name LIKE '%SupplierSearch%'

We're just using the LIKE operator and wildcards either side of the reference to our text box to give our users the best chance of returning results.

 

So now the user can type a value in the text box and then select the dropdown box and get a much more succinct list of values.

The ability to define our SQL query with this control and insert references from our control gives us very valuable and flexible tool for presenting users with SQL data.

 

I'd be interested to hear of any other techniques people are using on this control to make their users life easier.

greenawayr

Want a Rota?

Posted by greenawayr Champion Feb 2, 2017

The client had an extensive form that needed to go online to improve the process. It's a variation to contract form that feeds into many external systems. Currently the form is owned by HR and due to poor process management, the data from this form rarely reaches other departments for updating their information, for example IT require the form for updates to Active Directory.

 

Anyway, a small but fiddly part of this form was to create a Working Pattern control where the user could submit the standard weekly working hours of the employee. The employee might also have fortnightly or even monthly working patterns so they needed to be able to submit more than just a weeks pattern.

 

The only output required for this in terms of data, was the total hours for the week, as this, combined with other data in the form could be used to calculate the employees annual salary.

 

It's a fairly simple control, but because there is no such thing as a "time" field, I took the approach of creating dropdowns to allow the user to select Hours and Minutes (in quarter-hour increments) and Lunch break (in quarter-hour increments).

Then calculated values do the requisite formulas to calculate the daily hours and then the weekly total.

The entire table is encapsulated in a repeating section that is limited to a maximum of 4 rows.

 

It's a simple little control, but I thought I'd make it available to others as it was a little fiddly and time consuming to put together.

This is probably not news to many on here, but for those un-initiated in some of SharePoint quirks, I present the famous "Calculated Column" trick.

 

Do you want a really easy way to render a piece of HTML in a dataview on your SharePoint list or library?

 

Capture.PNG

Then here you go.

Create a Calculated column that looks a little something like this

Capture.PNG

 

Be sure to select anything OTHER THAN "Single line of text"

 

Your formula should look a little something like this

="<a href='www.google.co.uk'>Click Here to Approve</a>"

 

You can in fact use this for an number of other functions including Concatenates and IF's, for more details check out this link.

 

SharePoint will render this HTML as a clickable link for your users.

 

"What's the Nintex angle?" I hear you ask.

 

Well by creating a column called Approval URL or WorkflowMapURL and using the Nintex common properties to write to that column, you can then include that URL as your hyperlink, thus giving your users a link to the approval task straight from the item. Of course, only users who have had the task assigned to them can approve, but this is still a nice little trick. In fact you can use it for all sorts.

 

I created a Document library that contained 3 content types, Procedures, Forms and Deviations. The default view was set to Procedures, which would show all Procedures. Then as users began adding Forms and Deviations, they would be prompted for a piece of metadata called Proc Number, this was an identifier that linked a Procedure to a Form or a Deviation.

When an item was submitted, a workflow ran to identify whether a Procedure existed with that number, if it did it would build a URL of http://server/sites/sitename/libraryname/Forms.aspx?ProcNum=123456789

where the proc number is taken from item property. Write this to a column and then throw in an IF as well so that it only shows the hyperlink if a Form exists with the same Proc Number. It looks a little like this

 

=IF(
  ISBLANK
([FORMURL])
  
,""
  
,"<a href='SiteURL/Documents/Forms.aspx?ProcNum="
  
&[ProcNum]
  
&"'>Form(s)</a>"
  
)

 

I did the same with the Deviations and there it was, a library with Procedure documents and links to Forms and Deviations if they existed.

 

The power of the Calculated column

Capture.PNG

greenawayr

Pick up where you left off

Posted by greenawayr Champion Aug 18, 2016

We've all been there haven't we? You've got a multi-stage workflow, your director has approved it, your manager has approved it, and then **ERROR**

 

Something goes wrong. Maybe your next approver has left the company since the workflow was started, or there's an erroneous value in your variable that your SharePoint column won't accept.

 

Either way, you've got into a position where you've already had approval from a director (or anyone) once, you don't really want to have to ask them again.

 

On long running workflows this can be a real pain. So we introduced a setup that allowed us to "Pick up where we left off" (or were kicked off as the case might have been).

 

The State Machine is a brilliant action for for these staged workflows that transition through a number of different states and the Switch Action allows us leverage these states. Take the following example:

An item is created, to achieve approval it must go through 3 stages on a State Machine.

  1. Manager Approval
  2. Director Approval
  3. Task to be actioned by individual

Each of these stages is a branch in a State Machine, and after each task has been approved a Status column is updated to represent it's stage.

  1. Manager Approval - Status = "Approved by Manager"
  2. Director Approval - Status = "Approved by Director"
  3. Task to be actioned by individual - Status = "Ready for Action"

 

The status is updated immediately after the task completes and then the State Machine changes state to the next branch.

Item with ID = 101 is submitted and the approval workflow starts, the manager approves it immediately and then a month later the Director approves is, however, in that month, the user listed in the Item as the person to complete Stage 3 has left the company. So when Stage 3 goes to assign it's task, the workflow fails because the user does not exist in AD.

 

Enter the Switch!

 

When we designed our workflow, we created an additional branch on our State Machine called "Status Check" The state machine is set to go to this branch first. In this branch we have a Switch which will check the status column of our item and look for one of "Approved by Manager", "Approved by Director", "Ready for Action" or "Other". Each branch of the Switch has a Change State action that will send the workflow off to the required branch in the State Machine.

 

So when our item is updated with a new user to "action" and the workflow is restarted, in the case of Item 101, the status is "Approved by Director". The Switch identifies this status and the Change State action is set to send the workflow straight to the "Task to be Actioned by Individual" state. Thus the workflow "Picks up where it left off".

StateMachineSwitch.PNG

The main downside we have from this is that we are left with 2 workflow instances for an item in order to prove the audit trail of the item, however, the full story is there to see.

Bingham Blalock posted a very useful blog about Some Common Misperceptions - Q & A

 

In this post he touched upon a principle around the design (or architecture, if you want to try and convince your boss that your should earn an extra 5k a year) or workflows when it comes to changing a workflow whilst it's running.

 

This prompted me to write about the idea of "micro-workflows" (I made that up, lets see if it sticks).

 

Micro-workflows (as defined by me ) are workflows that perform specific tasks within a process.

 

An example of this was an app I developed with an approval process which required a number of conditions to be queried before deciding which route the approval would take. There were a number of other tasks involved in the process as well, and the complexity led me to break the single workflow down into a number of smaller workflows. Not only does this mean I can make changes to a workflow whilst the "process" is running against the item, it makes making changes to the workflows easier and tidier. When you open a large complicated workflow map, especially one designed by someone else, it can be pretty daunting to try and understand where a change may need to be made. It also helps avoid any limitations around Nintex or SharePoint workflow, with regards to the number of nested conditions you may have in a workflow, or the size of the workflow.

 

So my workflow was broken into 8 smaller, more manageable workflows. We'll follow this example as if it were an expenses or hospitality claim.

 

1. Set Item Permissions - This workflow would run as soon as an item was created, it may not get submitted straight away, just saved, but the requirement was that only certain people would see and have access to items created in this list, so Item level permissions were required. Once permissions were set, it would pause and wait for a Status update from the form, which happened when the user hit "Submit" rather than "Save". A few other small actions were run before this workflow started the "Master Workflow".

 

2. Master Workflow - This workflow would run the first couple of major conditions to evaluate which direction the workflow would be sent next. Conditions could be something like the value of the request, or the expenses claim, or the type of client involved in the claim. Once the conditions were met it would start one of the 4 following workflows, but remember this could be any number of

 

3. Workflow Option 1 -  Obviously you would probably look to call your workflow something more obvious that describes the route taken to reach this workflow, such as "> than $50 with a sensitive client". These workflow might evaluate further to ascertain the nature of the submission, is it an expense for food or hosting a team building exercise at paintball? Whatever your business process dictates you need to evaluate before finally deciding who the approval may go to. So a small claim for $10 worth of lunch may go straight to your Line Manager, hosting a client for 3 course meal worth $50 may go to your department or business unit lead or taking the entire team to a conference for a week might go to your Directors. Once all the evaluaions are made, the workflow will start the final workflow to send the approval.

 

4. Workflow Option 2 - As with Option 1

 

5. Workflow Option 3 - As with Option 1

 

6. Workflow Option 4 - As with Option 1

 

7. Send Approval - So having decided who the workflow should be sent this final workflow will actually send the approval task. By passing variables from the previous workflows I can create a single task for all possible outcomes of the conditions that were evaluated in the previous workflow, no repetitive work, so if they decide they want to change the text in the email sent to the approver, I don't have to copy and paste it to all the different branches created in the previous workflow.

 

I found this modular approach to building workflows around complicated business processes, far more manageable than one large workflow. Using Action sets and snippets can also help deal with repetitive work across the numerous workflows.

 

I'd be interested to hear how different folks approach their workflow design before they start building.

When prompting your users to fill in a form, it can be handy to sometimes help them out by filling in some of the information for them. You can do this by default values of course, but sometimes it needs to be more contextual.

 

For one solution I was working on, I had an issues list that I needed our users to populate as and when they encountered a bug, the issues would relate to an item that a workflow was running on, but the issue was being recorded on a separate list.

 

I built a link in the email the workflow would send them, prompting them to raise any bugs they found that looked like the following:

 

http://server/sites/sitename/lists/listname/NewForm.aspx?URL=www.google.co.uk&Status=New

 

Then, with Nintex forms, open your control properties and in the Default Value field add the Inline Function "fn-GetQueryString(URL)".

 

This would produce the following results when set for Site URL column and the Status column in the screenshot below. Obviously, you could do an awful lot more with this.

 

issuelist.PNG

In the recent request for community members to name their favourite workflow action, I put forward the "Copy To SharePoint" action, this was down simply to the element of surprise, that it helped me do exactly what I wanted to, easily, with no hacking, or fudging, as we call it.

 

I had a requirement for attachments in an event calendar to be treated like true SharePoint managed documents when uploaded by users.

 

Step 1

We've created an OOTB calendar. We've also created a document library called "Event Resources" and in that library we've added some Site columns, the ones that will contain metadata in the event calendar that we want to associate with the document. The meatdata is important because we will use it later to give the users a nice way of viewing the documents.

 

Step 2

We will create a workflow that will run on Create or Modfied and the first action we will insert is "Copy to SharePoint", this action will basically try to copy the event item to a document library, but it's actually going to copy the attachment into the document library as a document in it's own right, and by ensuring we tick "Copy item meta data" it will add the metadata to the document in the library as well. Perfect.

 

C2S.PNG

 

Step 3

Shout out to Paul Crawford who helped me see the errors of my ways with the convaluted approach I was taking to this next part. So we need to now delete these attachments, after all, they are no longer needed here and we don't want to duplicate the data. So we're going to Call a WebService.

getattachement.PNG

We're going to be using the lists.asmx WebService and we'll invoke the "GetAttachmentColleciton" method. Pass it our list (the calendar) name and the ID of the current item and store it in a Multiple lines of text field as it will be spat out in XML.

 

Step 4

We're going to be querying the XML we just got and storing the URL's in a collection. The XML node is simply called "Attachment" so configure your action as below.

queryxml.PNG

 

Step 5

Use the "For Each" action to iterate your way through the collection storing the attachment url from the collection into a text variable. We're going to use another "Call WebService" action and use the lists.asmx WebService again to delete the attachment. The for each will loop through, so for multiple attachments they will all be deleted.

deleteattachment.PNG

So you can see we're just passing the calendar name, the ID and the url given to the attachment. It's important to do it this way because the WebService encodes the URL differently, using "+" for spaces rather than %20, so getting the URL through a WebService ensures that it's correctly formatted.

 

Step 6

Finally, we're going to have to present this to the user in a nice friendly way when they open up the event item, so we want to edit the list form and add the list view webpart for our "Event Resources" library to the bottom of the form and create a connection from the event item webpart to the list view webpart on a common piece of metadata. In this case I used Title, but it might be whatever suits you to ensure it's unique.

event.PNG

So there we go, 2 working documents linked nicely to our event.

Filter Blog

By date: By tag: