• Search Forums
    Advanced Search Topics Posts
Prev
Next
Forums Integration / Customization IPA/ProcessFlow

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

 5 Replies
 2 Subscribed to this topic
 52 Subscribed to this forum
Sort:
Author
Messages
agersh
Veteran Member Send Private Message
Posts: 81
Veteran Member
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 Send Private Message
Posts: 1127
Veteran Member
7/28/2016 3:06 PM
rateOfpay=parseFloat(New_Value_## ) ; rateOfpay=rateOfpay/100
David Williams
agersh
Veteran Member Send Private Message
Posts: 81
Veteran Member
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 Send Private Message
Posts: 136
Veteran Member
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 Send Private Message
Posts: 136
Veteran Member
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 Send Private Message
Posts: 136
Veteran Member
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