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… 

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

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

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.

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

yes, this will be close

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

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

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

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

this will probably work
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

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.