rhia

Look-up Value Calculation on Nintex Mobile

Blog Post created by rhia Champion on May 18, 2017

The Mobile forms that Nintex provides are powerful and make it so easy for users to gather data out and about.

 

But every so often, we run into a function that we're so used to using on Desktop, but isn't available in Mobile. A great example of that is the lookup() function. We have List Lookup at our fingertips, but sometimes we need to calculate a value without our users needing to select from a dropdown with only one value in it.

 

 Just because this is how I've gotten around it, doesn't mean it's the right way!

 

So let's think about what a use case scenario might be.

 

 

 

 

THE PROBLEM

 

On our Nintex Mobile form, we want our users to choose their Airplane Type from a dropdown, and then their Airplane Model - and have the form automatically select the correct Fuel Capacity and multiply that by the current fuel cost ($1.20/L).

 

However, at present, we'd need our user to select the Type from a lookup ... and then the Model from a lookup... and then the Fuel Capacity as a single item from the final lookup, in order to be able to take it out and calculate it. We don't want our users to have to select any more than the Type and Model of their plane.

 

Users can get understandably frustrated when they have to redundantly enter / select information.

 

OUR SITE & REQUIREMENTS

 

1) We have a list of airplanes, named "Airplanes" - in this list, we have the following columns:

 

  • Airplane Type
  • Airplane Model
  • Fuel Capacity

 

3) We have a mobile form where we want a user to select the Airplane Type and display a dropdown of Models along with their Fuel Capacity. 

 

4) On that mobile form, we also want to take the Fuel Capacity and multiply it by a fuel cost which will reside on the form.

 

5) We want to display the total cost to fuel the airplane to the user, live, on the iPad / Mobile form.

 

 

 

 

 

THE RESOLUTION

 

1) The very first thing we'll do is create a column in both of our lists that will combine each row of data into one, with a label in the front, automatically. We will only grab those items we require. 

 

  • Create a calculated SharePoint column - name it something like MergedAirFuel or whatever makes sense for your situation.
  • Combine each column by using a formula like:

="AIRPLANE MODEL: "&[AirplaneModel]&" FUEL CAPACITY: "&[FuelCapacity]

This produces a column that looks like:

AIRPLANE MODEL: C FUEL CAPACITY: 1600

 

2) Now that we have our merged column, we can set-up our controls on our form. Here's what we'll need:

 

  • 2 List Look-up controls

  • Calculated Value controls

3) In the first List Look-up, we just want to fetch the list of Airplane Types that we have, so we can do the following:

 

Name: AirplaneType

Source SharePoint Site: (the one your list is in)

Source List: Airplanes

List Column Name: Airplane Type

 

4) In a Calculated Value, we'll make sure the ID of the look-up doesn't show up so we can use it to filter our other look-up's control:

 

Formula: parseLookup(AirplaneType)

Name: AirplaneTypeParsed

 

5) In another List Look-up, we'll find our merged row of data dependent on the Airplane Type that was chosen earlier, like so:

 

Name: AirplaneDataRow

Source SharePoint Site: (the one your list is in)

Source List: Airplanes

List Column Name: MergedAirFuel

 

We'll also open up the "Filtering" section, and choose:

 

Filter available selections: By a control's value

Where field: AirplaneType

Filtered by control: AirplaneTypeParsed

Action when no filter applied: Show none

 

 

INTERMISSION

 

 

 

 

 

What have we achieved so far: We now have a drop down where, when the user selects their Airplane Model, they will also see the details beside it - for example:

AIRPLANE MODEL: C FUEL CAPACITY: 1600

But how do we get that Fuel Capacity out of there to work with!? 

 

RegEx, is how. Here's what we'll do:

 

I'm doing these all separately to show each step, but it could be just one field, probably.

 

6 a) In a Calculated Value field, we'll parse again to ensure we get a nice clean string to work with (without the ID) : 

 

Formula: parseLookup(AirplaneDataRow)

Name: AirplaneDataParsed

 

6 b) In a Calculated Value field, we'll use Reg Ex to strip out the Fuel Capacity first - this Reg Ex formula says "select everything up to & including "FUEL CAPACITY: " and remove it from the variable:

 

Formula: replace(AirplaneDataParsed,".+\FUEL CAPACITY: ","")

Name: FuelCapacityRegEx

6 c) In a Calculated Value field, we'll use subString to get only the first 4 digits returned, in case there is additional data after that we do not need.

 

Formula: subString(FuelCapacityRegEx,0,4)

Name: FuelCapacity

7) In our final Calculated Value, we'll determine the total cost for the selected airplane which is the Fuel Capacity times the cost.

 

Formula: FuelCapacity*1.20

Name: FullFuelCost

 

And there we have it - getting and utilizing a value from a look-up list in a Nintex Mobile form. A bit round about, and I fully expect someone to say "Rhia, you idiot, what about this way", but I welcome it -- this worked for me in a pinch. 

 

Let me know what you think. 

 

Further Recommended Reading:

 

Outcomes