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:
.replace (/^null/gi,"")
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.)
N M,
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?
Kathy
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.
Perfect, the String encasement solved the issue!
Thank you,
Kathy
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.
Super helpful! Thank you!
