String manipulation in Nintex forms

  • 5 November 2015
  • 5 replies
  • 24 views
B
Badge +8

Greetings:

 

The SQL Request control is a wonderful addition but it is very limiting in that it only returns two fields, one for display and one for value data. Since I need more data from ther query I've tried concatenating columns in the SQL query.  For example:

 

select (lastname + ', ' + firstname) as displaydata,(phone + ', ' + email) as  valuedata from staff where status=1

 

which gives me the user's full name and a variable with the phone number and email separated by a comma, which I can retrieve with the parseLookup runtime function.  What I'd like to do is separate the phone and email values in two calculated fields. But how to do this?  The only useful function for string extraction is substring(string, start, length) which  works fine when the fixed-length phone number is present:

 

phone number: subString(parseLookup(DBConnection, false),0,10)

email address: subString(parseLookup(DBConnection, false),11,length(parseLookup(DBConnection, false))-10)

but fails miserably when there is no phone number in the data.  What I need is a function, like the java indexOf() method, to tell me the position of the comma so I can use that in combination with the length() function to get the email even when there is no phone number. Is there such a function? If so, why isn't it listed among other runtime functions?

 

Thanks, in advance.

5 replies

M
Badge +6

Hi Bruce,

I don't have Enterprise Forms, but can I suggest that you pad the data returned to your request, to the full phone number allowance? That way you will have the consistency of knowing where your delimiter starts in the returned field.

Maybe something like:

select (lastname + ', ' + firstname) as displaydata,(left( rtrim(phone) +'               ', 20) + ', ' + email) as  valuedata from staff where status=1

where the 20 represents the length you want to pad to.

Cheers,

Mark

B
Badge +8

Mark:

This was a good suggestion and it did work (except that I had to use '----------' instead of  '          ' otherwise '' was returned) and it solves the immediate problem. However, in cases where one or more of the columns that I'm trying to extract is not fixed length (like email, street_address) it would not work.  I'm still hoping there is a function like indexOf out there that would solve this for data of any arbitrary length.

Thanks,

Bruce

P
Badge +7

Hi Bruce,

 

In fact, you'd be able to use indexOf() JavaScript function.

Try to set a form variable with this formula :

 

(toLower(parseLookup(DBConnection, false))).indexOf(",")

 

Hope this helps

B
Badge +8

Pierre:

Thank you very much for this helpful answer.  I have tested it and it works as expected.  Moreover, since the indexOf() function is not listed as an available function when defining form variables but works nonetheless, this suggests that ANY JavaScript function would be available when defining form variables.  Is this the case? If so, what is it about the architecture of Nintex forms that makes form variables different in this respect than, say, the calculated field control.  Understanding this difference would go a long way toward helping me develop better and more efficient forms in the future.

B
Badge +8

Coming back to this many months later. I have been following Pierre's suggestion and have used both IndexOf() and split() functions.  However, I've run into an issue in which a non-fatal warning occurs when I apply these functions to an object that is not yet defined, as in a value that is picked from a list once the form loads.  Even when surrounding this call with conditional statements that one would expect would prevent the function from executing on a null value, this warning occurs.  I suspect that it is because the Javascript calls execute before logic in the form.  Is this the case?  Is there a way to suppress this warning?

Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings