cancel
Showing results for 
Search instead for 
Did you mean: 
Workflow Hero

Validate a field in Nintex with a custom list on SP2013

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

0 Kudos
Reply
11 Replies
Workflow Hero

Re: Validate a field in Nintex with a custom list on SP2013

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.LookupFunctio... 

0 Kudos
Accept as Solution Reply
Workflow Hero

Re: Validate a field in Nintex with a custom list on SP2013

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  ?

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??

0 Kudos
Accept as Solution Reply
Workflow Hero

Re: Validate a field in Nintex with a custom list on SP2013

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
0 Kudos
Accept as Solution Reply
Workflow Hero

Re: Validate a field in Nintex with a custom list on SP2013

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.

Accept as Solution Reply
Workflow Hero

Re: Validate a field in Nintex with a custom list on SP2013

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

Accept as Solution Reply
Workflow Hero

Re: Validate a field in Nintex with a custom list on SP2013

yes, this will be close

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

0 Kudos
Accept as Solution Reply
Workflow Hero

Re: Validate a field in Nintex with a custom list on SP2013

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

0 Kudos
Accept as Solution Reply
Workflow Hero

Re: Validate a field in Nintex with a custom list on SP2013

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

0 Kudos
Accept as Solution Reply
Workflow Hero

Re: Validate a field in Nintex with a custom list on SP2013

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(...))

Accept as Solution Reply