Skip to main content

We have a purchase request form that we have created with Nintex classic forms (on-premise SharePoint 2019). The form has three fields that are NOT connected to the SharePoint list.

The fields are as follows:

  • UIC - calculated value with a specific value we need to prepend our PR's with.
  • Julian date - text field
  • Series - text field

The user is required to fill out the Julian date and the Series. The above three fields are used in a calculated field titled "Tracking Number". The results of the Tracking Number field are stored in the SharePoint List column "Tracking Number".

 

We would like to WARN the user when they input a Julian date and series that's already in use. I found this thread and it helped me. https://community.nintex.com/t5/Nintex-for-SharePoint-Forum/How-can-I-compare-the-input-in-a-Nintex-Forms-field-to-already/td-p/10588

 

And I used this blog to ensure I was putting the right info in the Lookup function. https://www.enjoysharepoint.com/nintex-forms-lookup-function-example/ 

 

I need some help though. I added the calculated value control and put in the below formula and it seems to be almost working. When I put in a Julian date and series that doesn't exist in the list in the list, the value is blank which is good, it is what I want. When I put in values that I know exist on the list, a value appears but it's not the text I put in the If function. It shows up as #Value!

 

26125i73EA66A14AA6BAEC.png

 

Formula I'm using:

If(isNullOrEmpty(lookup("Purchase Requests","Tracking Number",TrackingNumber,"Tracking Number")),"","Please pick a new series number as this one is already in use!")

 

Screenshot of my control: 

26124i951FBBD61C671ECB.png

 

Video of me filling out the form is attached. The list already has tracking number with 0425 and H410. I change it to random 8888 and you can see it blanks out my calculated value. But when I change it back to 0425 instead of showing my warning text. 

Hi @dgraves 



 



Can you send a screenshot of the entire formula?



 



If(isNullOrEmpty(lookup("Purchase Requests","Tracking Number",TrackingNumber,"Tracking Number")),"","Please pick a new series number as this one is already in use!")



 



Is the TrackingNumber - your calculated control - inserted using the Name Control?





 



When you insert the named control - Correct!!





 



When you didn't use the named control - You get the #Value



Yes, I did use the named control tab. If I don't use it and just type it in, the form won't even load in Preview. Here is screenshot: 





And for additional info, here's a screenshot of the "TrackingNumber" calculated control. 



Hi @dgraves 



 



The isNullorEmpty function works when you use "8888" - non-existing code.



but not for existing code "0425". Do you also get the #Value for other existing code?



 



What value does the lookup return? (for the existing tracking number with 0425 and H410)



Add a Calculated Field with just the lookup formula.- lookup("Purchase Requests","Tracking Number",TrackingNumber,"Tracking Number")



 



 

that is a great idea. I added a calculated value control with just my lookup in the formula. Looks like my lookup is finding the value in the list but it's presenting it as #Value!. 



I'm going to try a few things to get my lookup to work. 



 



Fixed! The fourth item in the lookup function is supposed to be the "column" so I had selected it from the "Item Properties" tab. I decided to try typing in the name instead and that worked! See new value in formula here under arrow. And resultant form in below screenshot. 





 



Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings