Skip to main content
A
adamr73245379
Known Participant
March 29, 2018
Answered

Convert letter input to numerical value for calculation

  • March 29, 2018
  • 1 reply
  • 1173 views

Hello all,

I have started some online basic javascript classes to try to lessen the amount of simply questions I have but in the meantime, my fields guys had one request that I have been working on for a couple days now with no success. On our timesheet with days of the week where users input the hours they worked for the day. I currently am running a document level script and it is working great except some users would like to enter an "X" instead of a zero for the days they dont work. I believe there is a way to pull the value of the field and use an if/then statement but everything I have tried to date is not working or gives a NaN result. Because of it being a weekly calculation, there are multiple variables I am using so I wasnt sure if I was to run an if/then statement for each day or one time toward the end of the script. Below is my script and what I have tried to date. I know some scripts are still pulling the value as a number with the "+" but remember I was just trying to get the first one working.

function calcreg_total(){

// Get the field name array of the field that triggered this script

var fna = event.target.name.split(".");

// Get the row, which is the third element of the array

var row = fna[1]

// Get first field value,

var v1 = getField("mon1_str."+row).value;

if (v1 == X) event.value = 0,

else event.value = "";

// Get second field value,

var v2 = +getField("mon2_str."+row).value;

// Get third field value,

var v3 = +getField("tue1_str."+row).value;

// Get forth field value,

var v4 = +getField("tue2_str."+row).value;

// Get fifth field value,

var v5 = +getField("wed1_str."+row).value;

// Get sixth field value,

var v6 = +getField("wed2_str."+row).value;

// Get seventh field value,

var v7 = +getField("thur1_str."+row).value;

// Get eigth field value,

var v8 = +getField("thur2_str."+row).value;

// Get ninth field value,

var v9 = +getField("fri1_str."+row).value;

// Get tenth field value,

var v10 = +getField("fri2_str."+row).value;

// Calculate and set this field's value to the result

event.value = v1 + v2 + v3 + v4 + v5 + v6 + v7 + v8 + v9 + v10;

}

Also, I ran the console window using this script and got a 0 value when X was entered onto the sheet and a "false" value when any numerical value was entered. Thats as close as I got. Console script I was using was the following: if(getField("mon1_str.0").value == "X") {event.value = 0;} else {event.value == "";}

Thanks in advance,

Adam

This topic has been closed for replies.
Correct answer MatLac10492407

I have this crazy solution for you

In a custom keystroke event, have an "X" value entered by a user changed for 0 (this will render calculation easier)

if (event.willCommit == true){

if (event.value == "X") event.value = 0

}

then, in a custom format event, have the value you just changed to 0 appear as the original X:

if (event.value == 0) event.value = "X"

and just to make things even better, have your kestroke event test against a regular expression that permits only numbers and "X".  No risk of error after that.

1 reply

M
MatLac10492407Correct answer
Inspiring
May 10, 2018

I have this crazy solution for you

In a custom keystroke event, have an "X" value entered by a user changed for 0 (this will render calculation easier)

if (event.willCommit == true){

if (event.value == "X") event.value = 0

}

then, in a custom format event, have the value you just changed to 0 appear as the original X:

if (event.value == 0) event.value = "X"

and just to make things even better, have your kestroke event test against a regular expression that permits only numbers and "X".  No risk of error after that.

A
adamr73245379Author
Known Participant
May 11, 2018

MatLac,


Thanks. I am trying it out now and will let you know how it works. One question --- because I am still learning. How do I test against the expression to only permit numerical entry or the letter "X".

Thanks again,

Adam

M
MatLac10492407
Inspiring
May 14, 2018

We build our regular expression by putting the things we want between those ->  /  /

it will be:

/^$||\d/i;

^$ means it will accept an empty sting a.k.a erase

means it will accept an upperCase X

\d means it will accept any digits

you separate each part by this -> |     (equivalent of a boolean OR)

the i flag at the end means it is case-insensitive      (it will accept also the lowerCase x)

We switch the value back to UpperCase at the end

Custom keystroke in the format tab:

var re = /^$||\d/i;

if (!event.willCommit){

if(re.test(event.change) == false){

event.rc = false;

}

else{

event.change = event.change.toUpperCase();

}

}

You can find lots of tool and examples for regular expressions on the web.  Once you get used to RE, you feel like you want to validate everything.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices