Use Javascript to format Rate of Pay and FTE values from database

agersh

Veteran Member

Posts: 81

7/28/2016 2:59 PM

I am working on a ProcessFlow that uses an SQL Query Node to query the PERSACTION table. Two of the New_Value_## fields the query is referencing are the values for Rate of Pay and FTE. The values are stored in the table without decimals. For example Rate of Pay is stored as 000000000032050000+ and FTE as 000000000000900000+ . Using Javascript I am trying to find a way to format these values so that my ProcessFlow could use these values in their valid/correct decimal format.

Any suggestions?

David Williams

Veteran Member

Posts: 1127

7/28/2016 3:06 PM

rateOfpay=parseFloat(New_Value_## ) ; rateOfpay=rateOfpay/100

agersh

Veteran Member

Posts: 81

7/28/2016 4:18 PM

Hi David,

Your suggestion does not format the values correctly. Rate of Pay value 000000000032050000+ needs to be formatted to look as 32.050000 and FTE value 000000000000900000+ as .900000 .

Dave Curtis

Veteran Member

Posts: 136

7/28/2016 4:20 PM

You could also do it within your SQL node

I use the following

For FTE
to_number(trim(substr(new_value_02,13,7))))/1000000

Cut and paste from an actual SQL command run

Output new FTE

0.9

Original Table Value

000000000000900000+

For Rate of Pay
to_number(trim(substr(new_value_02,6,14))))/1000000

Works for up to 99999999.9999 pay rate - if you have pay rates bigger - just adjust the 6,14 in the code above.

Here is a cut and paste example of an actual SQL command run I just did

Output New Rate

38.8244

Original Table Format

000000000038824400+

You just would need to change the new_value_02 to whatever new_value_## you are pulling from.

Dave Curtis

Veteran Member

Posts: 136

7/28/2016 4:43 PM

I have not tested it but if you want to use Javascript something like this should work for rate of pay

YourVariableName.substr(5,13))/1000000

Similar would work for FTE as well. Probably have to play around a bit with the substr setting but should be something like

YourVariableName.substr(12,12))/10000000

Like I said - I have not tested those but they should work - maybe with some tweeks...

Dave Curtis

Veteran Member

Posts: 136

7/28/2016 4:48 PM

I just did a quick test run

I used a JavaScript Expression Builder and did this...

var newrate = strNewRate;

newrate = (newrate.substr(5,13))/1000000 ;

Original value was 000000000038824400+

Javascript output 38.8244