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)
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.
Solved! Go to Solution.
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.
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.
Try to set a form variable with this formula :
Hope this helps