Validate a field in Nintex with a custom list on SP2013


Badge +11

Hello Ladies and Gents

how would I achieve the following: to validate a field with the data from a customList on SharePoint 2013??

There is a field called CostCenter and but ideally, would love the form to show an error when a wrong NR is entered that is not present within the customList - this is some type of a validation before the workflow runs.

Assuming all the data entered is correct - the WF generates a RefID and sends the ApprovalRequest to the Manager depending on the costerCenter number. Yes, there is a QueryList at the beginning of the WF that looksUp for the data.

What if the Cost Centre is entered wrong?! Well, then the WF will check if the entered number is not found on the list - then it will reject the request. In this case, the user will have to make a new request!!

To avoid this; I was wondering if there is a way of validating the C.C field before the WF runs ?!

I am thinking of something like a vlookup but not really able to get it to work.

lookup(List Title, Column to filter on, Value to filter on, Output column)

This is what I am trying:

lookup("domain.com/services/PageName/Cost%20Centre","Cost%20Centre",CostCentre,"LinkTitle")

  • URL link is pointing to the Cost Centre file
  • Cost%20Centre is the field title of the customList when you look at the normal view
  • CostCentre is the column/field name within the Nintex form
  • LinkTitle is the actual name in html view that points at Cost Centres field

Just add, the CosterCentre field on the form is a normal text-field.
It is NOT a calculated field ... maybe it must be a calc.-field????

Please advise


11 replies

Userlevel 5
Badge +14

first, wouldn't be an option for you to make CostCentre field in your list of lookup type directly?

that way users will only be allowed to select one of provided options and you need not bother with validation.

reg. usage of lookup function - isthe list of cost centres on the same site or on different one?

if it is on the same site, provide in place of 'List title' just list name without any domain and path references.

if it is from another site check docu (or serach forum) what's the proper syntax http://help.nintex.com/en-US/nintex2013/help/#Forms/RootCategory/Designer/Nintex.Forms.LookupFunction.htm%3FTocPath%3DFo… 

Badge +11

Hi Marian

If I turn the CostCentre field into a choice field where I pre-define the numbers: yes this would work but imagine having multiple of these forms and I would require to maintain all forms manually to update the data ... therefore, I thought to have a single-point of a customList where all these date resides - and to fetch from.

Another idea would be somehow make the vlookup script to show ALL CostCentres from that customList and give the user to simply click on the CostCentre field and choose his/her relevant number. Question is, how to do that happy.png ?

To your second question: yes, the customList is on a different site. To avoid having to manage multiple list with the same data; I thought creating one and use it for many forms + workflows. By this, I maintain only one customList.

MAYBE I AM CONFUSING / MISUNDERSTANDING THE VLOOKUP?!

By the looks of it, the lookup(“listA”, “ID”, 1, “Title”) simply returns the value of a field.

Example, when I type in CosterCentre: 121212 then the field would show the title of the cost centre: Mechanics

However, what I want to do is: to validate the entered CostCentre -> with the customList and confirm if that number exist in there?! If Yes, great .. if not, it should say "invalid number"!

Is that even possible with the vlookup??

Badge +11

UPDATE

Right, with the calculated field I achieved the following:

lookup("http://domain.com/services/Site|Cost Centre","Title",CostCentre,"Cost_x0020_Centre_x0020_Descript")

The result shows the Decription of the entered cost centre, using the Title as a reference-point to the customList.

... but is there a similar way to validate the entered number and display error if not present in the custom list.

for those who struggle(d) as me finding out what does what:

  • the URL is pointing to the customlist (called: Cost Centre) on a different page.
  • the "Title" is a default entry whenever you create a new customList (even if you rename it, still points to Title)
  • CostCentre in red, is the actual fieldname on the form (via Item Properties, again depends how you name it)
  • "Cost_x0020_Centre_x0020_Descript" is also the field name on a customList
Userlevel 5
Badge +14

Read carefully, I've written lookup field, not choice. They are two different things.

Lookup fields are exactly for use cases like you describe - you have one single evidence of some data (code list) and you reference it from as many list fields and/or form controls as you need. once you change anything on source code list, it's immediately seen in all the references with no further effort.

looksup() function looks up for some data in other list you ask it for,. if it finds the data it returns some senseful output, if not it returns nothing. so, this is your driver you can base validation on - if it return (eg) description for a given CC number, you are sure CC exists on the list, if it returns nothing CC was wrong.

Userlevel 5
Badge +14

isNullOrEmpty(lookup(...)) - will return TRUE if cost centre is not on the customList

Badge +11

yes, this will be close happy.png

can it also make it say "C.C NOT VALID" if it's empty or Null ?

Userlevel 5
Badge +14

do you mean if user do not fill in any CC number?

Badge +11

no no for that I made a requirement anyway ... and made it so it has to start with 25055 or 2599 which is ok

but lets assume they mistype by 1 digit and the "description" is not shown (blank) -

Can your advise code make it show "invalid code" when isNullOrEmpty

Userlevel 5
Badge +14

if you mean validation, the you can configure validation message however you like.

if you mean to show either description or 'invalid number' message in some calculation value control, you can do it like this

if(isNullOrEmpty(lookup(...)),'Invalid number',lookup(...))

Badge +11

this will probably work happy.png
since I am not able to validate the actual costCener field, there is no other options for me

but to use the calculated field as you advised

if(isNullOrEmpty(lookup(...)),'Invalid number',lookup(...))

I will be on holiday soon and shall be testing it in May.

Thanks for your help and i's hommmmmeee timeeeeee. lol

Userlevel 5
Badge +14

why it couldn't be validated ???

just the same formula that you make up for calculation control would work in validation rule as well.

so I do not understand your point.

Reply