I'm using a "Calculated Value" form control with a lookup function along with the parseLookup function to pull information from a Vendor list. Sometimes the Vendor list does not have values in all the fields (ie: "Contact Name" or "Fax" may be empty) which results in #Value! on my form.
I have unsuccessfully tried IsNullorEmpty(ControlName) rules to hide any blank answers.
Examples of my lookup function:
lookup("PReq XTbl Vendor", "Name - Biz Partner No", parseLookup(Vendor), "Contact Name")
How can I either hide the #Value! on my form when there is no returned value from the lookup function
How can I only run the lookup when there is a value to return
Either of the above should be viable solutions in my scenario.
Solved! Go to Solution.
I currently use a solution that would, when applied to your formula, look like:
String(lookup("PReq XTbl Vendor", "Name - Biz Partner No", parseLookup(Vendor), "Contact Name")).replace (/^null/gi,"")
So really, just open up the formula builder and paste:
at the end.
Please do notice however that there is a 'space' between the word replace and the first parenthesis. This is important, otherwise Nintex Forms will try to use it's built in replace function which does not use regex!
(Note 1: I have updated this post to correct an oversight that I have made, and have explained in a reply to the OP's reply.)
I have spent a lot of time on this issue. I have applied your solution to the end of my formula paying special attention to the 'space' between the word replace and the first parenthesis. I no longer get the #Value! on the form - however, now I get a webpage message that states "The form is reporting the following errors: Unable to get property 'replace' of undefined or null reference" -- this message only appears when the value being returned is of an empty string. When there is data within the field, I obviously do not get this message.
Uuggg! Thoughts? Suggestions?
Yep. I messed up.
I forgot that when 'nothing' is returned from the lookup function, it returns the native type null.... which is not a String.
So, I have edited the code above to encase the lookup function (and subsequent result) in the Global String Object.
All that does is convert whatever is inside of it into a string. This means that null becomes "null". Because the replace() function only works on Strings, it was running into an error because null does not equal String()
Please update your code and tell me if that solves the problem.
An alternative approach is to encapsulate your lookup function inside a trim(). The trim() function also returns a string and, conveniently, strips out the null, resulting in an empty string instead of the annoying #Value! placeholder.
So for the original post, amend your formula to the following:
trim(lookup("PReq XTbl Vendor", "Name - Biz Partner No", parseLookup(Vendor), "Contact Name"))
And that should do the trick.
However, if the leading and trailing spaces actually matter in your returned values, then using trim() obviously won't be ideal.