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:
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????
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...
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??
Right, with the calculated field I achieved the following:
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:
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.
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