Skip to main content
Nintex Community Menu Bar

lookup a string field in a list using two numeric fields concatenated

  • February 21, 2020
  • 3 replies
  • 15 views

Forum|alt.badge.img+2

Hello,

 

I have a list (Risk Appetite Matrix) which as a [Single line of text] index in the form of a number e.g. 12 or 34.

I have a nintex form with two fields which are Number dropdowns, each is 1-4

I want to lookup in the first list the concatenation of the two values so that for instance:

Risk Impact: 2      Risk Likelihood: 4     allows me to find the list record "24" (to retrieve a color)

 

For this I use:

lookup("Risk Appetite Matrix", "Risk Index", parselookup(RiskImpact) + "" + parselookup(RiskLikelihood), "Risk Color")

 

This works perfect upon edit mode right after selecting the two dropdown values, confirmed in the Console:

[Risk Appetite Matrix-8deff7b9-6d54-404e-9bc9-39acb8579b1f] - Querying SharePoint for list item data: Query [<View><Query><Where><Eq><FieldRef Name='Risk_x0020_Index' /><Value Type='Text' >24</Value></Eq></Where></Query><RowLimit>1000</RowLimit></View>]

 

But in Display mode, or upon entering Edit mode with existing values, it doesn't work. The Console reports a silly number of zeroes polluting the string even though I have set the Decimal to zero on the fields:

[Risk Appetite Matrix-a0cd2ca2-3f41-44b2-bba9-374b89dcbc93] - Querying SharePoint for list item data: Query [<View><Query><Where><Eq><FieldRef Name='Risk_x0020_Index' /><Value Type='Text' >2.000000000000004.00000000000000</Value></Eq></Where></Query><RowLimit>1000</RowLimit></View>]

 

Any advice ? I could not find a formula like Int() or something similar.

Thanks!

 

3 replies

Simon Muntz
Nintex Partner
Forum|alt.badge.img+23
  • Nintex Partner
  • February 24, 2020
HI,

I tested this using a Number column in SharePoint 2016 configured with 0 decimal and it worked as expected in New Mode, Edit Mode and View Mode.

Forum|alt.badge.img+2
  • Author
  • Rookie
  • February 24, 2020

Hi,

Thanks for the answer. Well, I'm on Sharepoint 2013 on-premise would that make a difference ?

Whatever I try, if I have a lookup on a Number field, Nintex Forms displays it like this:

6553i483F2A06A55DCDCA.png

So that's the real problem. Obviously when I try to concatenate these two fields I get 4.000000000000004.00000000000000.

Any idea ?

 


Forum|alt.badge.img+2
  • Author
  • Rookie
  • February 25, 2020

Okay I have solved this issue by using subString to strip "4.0000000000" to "4"

subString(parseLookup(RiskImpact),0,1) + subString(parseLookup(RiskLikelihood),0,1)

but that doesn't explain where this infinite number of zeroes come from. However it's a different topic so I will open a new question for that.