Connect: Add text functions, such as UPPER() and REPLACE()

[AlfredBaldenweck]

I'm having a bear of a time with trying to get the functionality of basic text functions to work in Survey123 Connect.

I'm talking things like UPPER(), LOWER(), and REPLACE().

To get UPPER to work, the other day, I have to do an

if(selected(${quest1} , "enh"), "ENH", "")

I have no idea how I'd get a REPLACE() to work.

I know you can sometimes use native excel formatting to get this to work, but it is incredible to me that we don't appear to have anything that can just like. make everything uppercase.

Please add text functions.

[DeonLengton]

Hi Alfred

If you have a signed in user on Survey123 (and not a public survey), then you can use JavaScript functions to do that, and more.

[AlfredBaldenweck]

Hi Deon,

To be fair, I had not tried JavaScript for this. 

So, with a little help from w3schools and Stack Overflow, I gave it a shot.

Here are the functions I used:

/*
 * JavaScript functions for Survey123
 */
function upperWrap(arg1) {
    return arg1.toUpperCase();
}
function lowerWrap(arg1) {
    return arg1.toLowerCase();
}
function replace1Normal(arg1, arg2, arg3) {
    arg1 = arg1.replace(arg2, arg3);
    return arg1;
}
function replace2All(arg1, arg2, arg3) {
    arg1 = arg1.replaceAll(arg2, arg3);
    return arg1;
}
function replace3GlobalNoVar(arg1, arg2, arg3) {
    arg1 = arg1.replace(/e/g, arg3);
    return arg1;
}
function replace3GlobalVarFail(arg1, arg2, arg3) {
    arg1 = arg1.replace(/arg2/g, arg3);
    return arg1;
}
function replace3GlobalVarPass(arg1, arg2, arg3) {
    var regex = new RegExp(arg2, "g");
    arg1 = arg1.replace(regex, arg3);
    return arg1;
}

To summarize: replaceAll() does not work, so you have to construct a Regex object if you want to be able to replace all instances of a substring in a string. Ok, cool, learning moment.

So, to your point, yes, you can for sure use JavaScript to get the text functions. Takes some trial and error, but it is possible.

That being said, it feels pretty silly to have to use JavaScript just to capitalize my input, since all I did was create a wrapper function to use a normal string method. Kind of like breaking out a carpet cleaner for a bathmat. Yeah, it works, but more of a production than it needs to be. Just look at the calculation field for each of these questions, not even touching the JavaScript.

JavaScript	Ideal setup
pulldata("@javascript", "functions.js", "upperWrap", ${base})	upper(${base}
pulldata("@javascript", "functions.js", "lowerWrap", ${base})	lower(${base})
pulldata("@javascript", "functions.js", "replace1Normal", ${base}, "e", "a")	replace(${base}, "e", "a")

There's also the frustration that comes with developing in a program that can do all this stuff normally but not being able to use any of it for your survey since Excel is just the development medium. For a different example of this, see my Idea requesting a UNIQUE() function.

To be fair, I'm not sure how many people are using public surveys in general (I certainly am not), but like. I don't think you should be locked out of basic text functions just because the survey is public? Depending on the survey, you can do it as part of whatever your post-processing is, but again, that's a complication that probably doesn't need to be there. 

As an aside: The signed in thing, once I tested it, worked better than I thought it did, but I'm wondering why the JS will run if you're signed into any portal, not just the one the survey belongs to?

Anyway, all this to say that it is possible to get this functionality right now, but it adds complexity that could be avoided.