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

Getting Started

5 Posts authored by: janvonreith

In this blog post I’d like to show, how to use the Nintex Forms “Web Request” control together with the Google Distance Matrix API to automatically calculate the distance between two cities and the estimated duration for that trip.

At the end the form will look like this (Edit mode):

image

Setup Google Distance Matrix API

Before the implementation of the SharePoint list and the Nintex form, some simple steps need to be performed, to be able to use the Google Distance Matrix API. The documentation of the Distance Matrix API can be found here: Developer's Guide  |  Google Maps Distance Matrix API  |  Google Developers 

The most important point is the generation of necessary API key, the process for this is described in the “Get a key” section of the documentation:

image

After reading the documentation I configured the following request:

https://maps.googleapis.com/maps/api/distancematrix/xml?origins=Hamburg+22763&destinations=Muenchen+80331&mode=driving&language=de-DE&key=************************  

In this request, there can be found the departure value (“Hamburg”) with the corresponding departure zip code value (“22763”) and the destination value (“Muenchen”) with the corresponding destination zip code value (“80331”). Furthermore, the mode is set to “driving” and the language is configured to be German (“de”). At the end of the request there’s the necessary API key.

Please be aware that you need your own API key! The URL provided in this blog post won’t work for you!

The result of this call looks like this:

<DistanceMatrixResponse>
    <status>OK</status>
    <origin_address>22763 Hamburg, Deutschland</origin_address>
    <destination_address>80331 München, Deutschland</destination_address>
    <row>
        <element>
            <status>OK</status>
            <duration>
            <value>26400</value>
            <text>7 Stunden, 20 Minuten</text>
            </duration>
            <distance>
            <value>797062</value>
            <text>797 km</text>
            </distance>
        </element>
    </row>
</DistanceMatrixResponse>

Creation and configuration of the needed list

For this scenario, I set up a simple custom list called “Driver’s logbook” and added the following columns:

image

Configuration and implementation of the Nintex form

Then I started to configure the Nintex form. As always, I first made some basic changes and adjustments like changing the background colour, the grid cell size and the logo. Furthermore, I removed some control that I didn’t need, like the “Distance” and the “Duration” control.

Afterwards my form looked like this:

image

Then I added two “Web Request” controls into the blank areas in my form:

image

Then I started with the configuration of the control that should show the distance, at the end the configuration looked like this. These are all the settings that I changed

Name = Distance
Text connected to = Distance
Display format = Text
Service URL = https://maps.googleapis.com/maps/api/distancematrix/xml?origins=Departure+Departurezipcode&destinations=Destination+Destinationzipcode&mode=driving&language=de-DE&key=************************
XPath for display = //row/element/distance/text
Execute in view mode = Yes

image

Afterwards I configured the control that should show the duration, at the end the configuration looked like this. These are all the settings that I changed:

Name = Duration
Text connected to = Duration
Display format = Text
Service URL = https://maps.googleapis.com/maps/api/distancematrix/xml?origins=Departure+Departurezipcode&destinations=Destination+Destinationzipcode&mode=driving&language=de-DE&key=************************
XPath for display = //row/element/duration/text
Execute in view mode = Yes

image

So, that’s the final configuration of my form:

image

When I now fill out the form and enter a destination the distance and the duration will automatically be calculated. To get a better result I decided to also add the destination zip code and made it mandatory, as in some cases cities also have the same name.

image

For this example, it doesn’t make a big difference (2 minutes and 8 km).

image

If you have any questions, found an error or have a better way to accomplish this, feel free to contact me!

In this blog post I'd like to provide a step-by-step guide to build a dynamic repeating section with Nintex Forms that can be used in order forms for example. Furthermore this guide will include a solution to check the total price of an order against a predefined budget. The focus of the whole solution will be on the Calculated Value control, the Lookup function, Runtime functions and Rules. I've created this guide by using Nintex Forms 2016 (On-Premises), but it should also work for Nintex Forms 2013 (On-Premises).

 

In the repeating section the user will be able to choose from a specified list of products, whereby the price of the product, the overall price based on the desired amount and a picture of the product will be shown. At the end of the form the user will see the overall price of all products and furthermore will get an information whether she or he is still in the defined budget or not.

 

At the end the form will look like this (Display mode):

 

Creation and configuration of the needed lists

First create a list to store the single products, this list should be called "Products" and it should look like this:

 

 

Name = Single line of text

Price =  Currency

Picture = Multiples lines or text (Plain text)

 

Add another list called "Product orders", in this list the orders will be created and saved. The list looks like this:

 

 

Title = Single line of text

Total price of order =  Currency

Order = Multiples lines or text (Plain text)

 

In a real world scenario there would probably be more columns to store additional information, like information regarding the customer/user.

 

General settings and configuration of budget definition

Start to edit the form in the "Product orders" list with Nintex Forms. Remove all controls that are not needed, change the background color to white (#FFFFFF) and change the grid cell height and weight to 20 pixel.

 

Add a "Label control" at the top of the form and insert "How much money do you want to spend?". Furthermore add a "Single Line Textbox" control right next to it. Name it "Budget", change the default value to "0.00" and set the data type to "Currency".

 

 

 

Configuration of the repeating section

This is how the repeating section should look like at the end:

 

 

First add a "Repeating section" control. Name it "Order", connect it to the "Order" column in the list and change the "Text for add row icon" to "Add additional product".

 

 

Add a "Label control" and insert "Position". Furthermore add a "Calculated Value" control below. Name it "Position", in the "Formula" section add the following runtime function currentRowNumber().

 

 

    

Add a "Label control" and insert "Product". Furthermore add a "List Lookup" control below. Name it "Product" and configure it in the way that it displays all items that are stored in the "Products" list.

 

 

 

Add a "Label control" and insert "Amount". Add a "Single Line Textbox" control below. Name it "Amount", change the default value to "0" and set the data type to "Integer".

 

 

 

Add a "Label control" and insert "Product Price". Furthermore add a "Calculated Value" control below. Name it "ProductPrice", in the "Formula" section add the following runtime function lookup("Products","ID",Product,"Price"). This function will display the price (List "Products" --> Column "Price") of the chosen product in the "Product" control. Set the "Save as data type" to "Currency" and the "Value prefix" to "$".

 

If you want some more information regarding the Lookup function take a look at this blog post by Eric RhodesTechRhodes | Blogging about SharePoint, Nintex and life in general 

 

 

 

 

Add a "Label control" and insert "Total Price". Furthermore add a "Calculated Value" control below. Name it "PriceTotal", in the "Formula" section add the following formula ProductPrice*Amount. Set the "Save as data type" to "Currency" and the "Value prefix" to "$".

 

 

 

Add a "Calculated Value" control and name it "Picture". In the "Formula" section add the following runtime function lookup("Products","ID",Product,"Picture"). This function will display the picture (List "Products" --> Column "Picture") of the chosen product in the "Product" control.

 

 

At this point many thanks to Steffen Hennig for the picture solution! You can find it here: Populate a Calculated Value Field with HTML/Picture 

 

Configuration of budget check

Add a "Label control" and insert "Total price of your order". Furthermore add a "Calculated Value" control right next to it. Name it "OverallPrice", in the "Formula" section add the runtime function sum(PriceTotal). The "sum" runtime function returns the result of all the values in a set being added together. Set the "Save as data type" to "Currency" and the "Value prefix" to "$".

 

 

 

Add another "Calculated Value" control right next to the "OverallPrice" control, name it "BudgetCheck", in the "Formula" section add the following runtime function If(OverallPrice>Budget,"You spend too much", "You do not spend too much").

 

 

If the defined overall price of the order in the "OverallPrice" control is greater then the budget in the "Budget" control, the calculated value will be "You spend too much", if this is not the case it will be "You do not spend too much".

 

To highlight the result add two rules on the "OverallPrice" control and configure them like that:

 

 

After a little bit of formating the whole form should look like this at the end:

 

 

Of course there are many more things that could or must be done to get a perfect order form, like adding validation rules (i.e. if the overall price is greater then the budget) but I hope this gives you a good starting point and has provided you some tips on how to implement your requirements regarding such a form. Maybe I'll add some more features in the future ;-)

 

If you have any questions, found an error or have a better way to accomplish this, feel free to contact me! :-)

 

nintex lookup control lookup function calculated value repeating section runtime functions order rules forms

In my scenario, I have two lists. The first list is called „Car marks and Product locations“, where I store car marks and the locations where they are produced.

image

The second list is called „Car marks“, there I can create entries and choose between different car marks via a lookup column on my first list. The corresponding locations for the chosen car marks should automatically be stored in an additional column called „Product locations“.

image

The workflow that identifies the corresponding values looks like this:

First I use a „Set variable“ action to get the ID’s of the values that have been chosen in the lookup column „Car marks“ and store them in a variable called „varCarMarksLookupIDs“.

image

At this point it’s important to mention that I choose „Lookup IDs, Comma Delimited“ as the format for the values I store in the variable.

image

Then I use a „Regular expression“ action to split the single IDs and put them into a collection variable called „vCarMarksLookupIDsCollection“.

image

Now I’m using a „For each“ action to iterate through all ID’s that have been stored in the collection. The current ID always gets stored in a variable called „vCarMarkSingleLookupID“.

image

In the first step of the „For each“ action I get the product locations for the current car mark ID and store them in a variable called „vCarMarkProductLocations“ with the help of a „Set variable“ action.

image

In the second step of the „For each“ action I build a continuous string of all product locations with the help of the „Build string“ action.

The variable „vCarMarksProductLocationsAll“ contains all product locations. For every loop, I add the corresponding product locations for the current ID to that string.

clip_image016

At the end of the „For each“ action I have all corresponding product locations for every car mark in my string variable and can now update the item with it.

image

This is how the whole workflow looks at the end:

clip_image019

And this is the result after the run of the workflow:

image

You can find the workflow in the attachments ;-)

The "Document Generation" action offers the opportunity to create documents based on a definable template with all the information that is gathered while the workflow is running.

After the release I’ve asked myself whether it is the possible to get the information of a repeating section (created with Nintex Forms for Office 365) into a document via the “Document generation” action. The answer is yes and in this blog post I’m going to show you how this can be achieved.

The focus of this blog post will be the workflow including the „Document Generation“ action, but for a better understanding I will also describe the other aspects of my scenario. There’s a custom SharePoint list with a Nintex form which offers the opportunity to order products in the desired quantity. Afterwards a workflow will automatically create a PDF document with all relevant information concerning the specific order. The PDF document is created based on a predefined Word template.

SharePoint List and Nintex Form

First, I create a custom SharePoint list called „Orders“ with a choice column „Product“ that includes different products and a multiple text column called „OrderedProductsandQuantity“. I modify the form of this list via Nintex Forms and add a repeating section that includes the „Product“ column as well as a new single line textbox control called „Quantity“. The information of the repeating section (the XML) will be stored in the column „OrderedProductsandQuantity“.

image

This is the XML that is stored in the column „OrderedProductsandQuantity” for the example shown in the previous screenshot:

<?xml version=“1.0″ encoding=“utf-8″?><RepeaterData><Version /><Items><Item><Product type=“System.String“>SharePoint 2013</Product><_x0037_9a74352-f26f-4f5e-a1d7-32c39f64ddfa type=“System.Int32″>2</_x0037_9a74352-f26f-4f5e-a1d7-32c39f64ddfa></Item><Item><Product type=“System.String“>Nintex Workflow 2013</Product><_x0037_9a74352-f26f-4f5e-a1d7-32c39f64ddfa type=“System.Int32″>1</_x0037_9a74352-f26f-4f5e-a1d7-32c39f64ddfa></Item><Item><Product type=“System.String“>Nintex Forms 2013</Product><_x0037_9a74352-f26f-4f5e-a1d7-32c39f64ddfa type=“System.Int32″>1</_x0037_9a74352-f26f-4f5e-a1d7-32c39f64ddfa></Item></Items></RepeaterData>

Document template

Before I start with the creation of the workflow, I create a word document named “Order template” in a document library called “Documents”. This document will serve as my template for the “Document Generation” action later on.  

image

Nintex Workflow

As I’ve already mentioned the workflow creates a PDF document with all relevant information concerning the specific order based on the Word template I just described. Every information that is supposed to appear in the document needs to be saved in a variable, so the workflow consists of more than one action.

imageimage

First of all I use two “Query XML” actions to get the information about the particular order positions out of the XML in the column „OrderedProductsandQuantity”. The products are stored in a collection variable called “ProductsCollection” and the quantities are stored in a collection variable called “ProductsQuantityCollection”. This is how the configuration of the action looks like for the gathering of the quantities.

image

Then I create three variables: OrderNumber (Current Item:ID), Requester (Current Item:Created By) and OrderDate (Current Item:Created). Afterwards I use a “For Each” action to iterate through all elements in the collections variables.

 

image

With the help of the “For each” action I get all the products of the order. As I also need the corresponding quantity for the particular product I use the “Get Item from Collection” action.

image

So for each loop I get two values, the product and the corresponding quantity. These two values will temporarily be stored in a dictionary variable called “ProductQuantityDictionary” that I create via the “Create Dictionary” action. The dictionary variable has two keys, “Product” and “Quantity” both text by type.

image

The values in the dictionary are added to a new collection variable called “AllProductsQuantityCollection”. Therefore I use the “Add Item To Collection” action.

image

After the last loop I have a collection variable with complex values that includes all products with the corresponding quantity. The content of the collection variable looks like this:

image

After all the desired information has been stored into variables, I start to configure the “Document Generation” action, which in the end looks like this:

image

In the “Template document library” column I define the document library where the template for the generated documents is saved, in my case “Documents”.

In the “Template” column I select the already existing template called “Order template”. You also have the choice to create a new template if you like to, whereby you can choose between an Excel, a PowerPoint and a Word template.

Then I edit the template by clicking on “Edit in Word”. When the template is opened for the initial creation or for changes, the so called “Nintex Document Generation Tagger” panel can be found on the right side.

image

This panel is used to insert the desired variables into the document template. Therefore the cursor has to be placed where the value of the variable should appear, then the desired variable must be selected and added by clicking in “Insert Tag”.

image

For my variable “OrderNumber” the result looks like this:

image

To display the information of a repeating section in the document I have to create a table. In my case I create a table with two columns and two rows. I use the first row as a header and the second row for the necessary tags.

When I then use the “Nintex Document Generation Tagger” again and chose a collection variable, there’s one additional column, a column named “Tag type” with three different parameters (“Start collection table”, “Key” and “Simple”).

In the first step I place the collection variable with the tag type “Start collection table” in the first column of the table. In the same column I place the collection variable with the tag type “Key” and enter “Product” as the key. In the second column I place the collection variable with the tag type “Key” and enter “Quantity” as the key.

imageimage

The result looks like this:

image

The tag type “Start collection table” specifies the start of a collection variable table, a column will be created for each entry in the collection. This is how my final template with all desired variables looks like:

image

As I like to have the order as a PDF document I select “PDF” in the “Output file type” column. Furthermore I have to define, where the generated documents will be stored. In my case they’ll be stored in the document library “Documents”, which I define  in the column “Output document library”.

In the last step I define the “Output file name”, which in my case consists of “Order –“ as the prefix and the ID of the list element as the suffix.

I configure the workflow to start when a new element has been created and this is how the PDF looks like after the workflow has finished successfully.

image

More information about the “Document Generation” capabilities can be found in the official Nintex Help!

If you have any questions please feel free to ask! office365 repeating sections documentgeneration

In one of our projects, we needed to implement a solution to track the visits of sales representatives in form of a visit report. Most of the times there are multiple visit reports for one visit, because multiple representatives attend a visit.

In those visit reports the sales representatives should say if they were the driver at the visit or only a passenger. It’s of course possible that not all representatives drive with the same car, for example because of different starting points or follow-up visits. But if they have the same starting point, the same destination and overall there’s no reason why they should not drive together, it probably doesn’t make sense, that they do not drive all together in one car.

In these cases, there’s a chance for optimization and we needed a workflow that can discover these optimization possibilities. This workflow needs to be able to identify specific duplicates (visit reports) in a list.

In this blog post I’d like to show you how I set up this workflow (I simplified the implementation to focus only on the relevant parts).

For the collection of the visit reports there’s a list called “Visit reports”. The following columns can be found in the list:

Visit-ID (Number)*
Starting Point (Single line of text)*
Destination (Single line of text)*
Date (Date and Time)*
Follow-up visit (Yes/No)*
Driver (Yes/No)*

The unique ID for the visit gets stored in the column Visit-ID (ID for the whole visit, not a single visit report, multiple visit reports can exist for a one visit).

In the “Follow-up visit” column the representatives can define if they had another visit after the current visit and in the “Driver” column they can define whether they have been the driver or not. If there are two visit reports of two representatives that are completely identical except of the point that one was the driver and the other one was not, this totally makes sense and of course is the desired scenario.

image

This is how the list looks like with four visit reports for two visits:

image

The goal of the workflow is the identification of the visit with the Visit-ID 1, because the single visit reports of this visit are completely identical and in both visit reports the “Driver” column is set to “Yes”. Identical visit reports with the “Driver” column set to “No” aren’t relevant, because that’s the way it should be.

I worked out the following idea for the workflow. The workflow should process every single visit (Visit-ID) and for every single visit all single visit reports (only if “Driver” column is set to “Yes”) should be stored in a complex collection.

Then the workflow should count the entries (visit reports) in the complex collection and afterwards remove duplicates out of the complex collection. At the end, the workflow should compare the count of the entries (visit reports) before and after the removal of the duplicates. If the count is the same, everything is fine, if the count is not the same, there’s a visit (like the visit with Visit-ID 1) with multiple drivers, so there’s an optimization possibility.

So far so good, let’s start to build the workflow!

As the workflow is not related to a single element (visit report) and should run every day at a defined time, I decided to set up a site workflow.

In the first step, the workflow collects all “Visit-IDs” with a “Query list” action and stores them in a collection variable called “Collection_VisitIDs”. Afterwards the workflow removes the duplicates by using a “Collection operation” action.

image

In the next step the workflow iterates through all Visit-IDs to check the single visit reports. The Visit-ID gets stored in a variable called “Text_SingleVisit-ID”. In the first step of this action the workflow collects all necessary elements (visit reports) and corresponding properties of the single visit reports and stores them in different collections.

image

In the “Query list” action there’s a filter on the actual Visit-ID and on the “Driver” column, as only visit reports with “Driver” column set to “Yes” are relevant.

image

Now the workflow iterates through the  collected elements (visit reports) and the corresponding properties to get all the properties of the single visit reports that need to be added to the complex collection. Therefore, I added another “For each” action and three “Collection operation” actions afterwards, to get all needed properties.

image

Then the workflow uses the “Build string” action to create the single entries (visit report with needed properties) for the complex collection and afterwards uses the “Collection operation” to add the entry to the complex collection “Collection_ComplexCollection”.

image

The “Build string” action looks like this:

image

That’s it for this “For Each” action. At the end of the other “For Each” action the workflow now counts the entries (visit reports) of the complex collection. Afterwards it removes the duplicates and then counts the entries (visit reports) again.

If the number of entries isn’t the same no more it’s a visit where we have two drivers, which is probably one too much, so there’s an optimization possibility. For this blog post the workflow just logs the result into the history list.

image

This is how the workflow history looks like, after a successful run:

image

As you can see, the workflow successfully detects the visit with the Visit-ID 1!

Filter Blog

By date: By tag: