PrevPrev Go to previous topic
NextNext Go to next topic
Last Post 07/28/2016 12:48 PM by  Dave Curtis
Use Javascript to format Rate of Pay and FTE values from database table to include decimals
 5 Replies
Sort:
You are not authorized to post a reply.
Author Messages
agersh
Systems Analyst
Martin Health System
Veteran Member
(143 points)
Veteran Member
Posts:73


Send Message:

--
07/28/2016 10:59 AM

    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
    Private
    Private
    Veteran Member
    (3064 points)
    Veteran Member
    Posts:1032


    Send Message:

    --
    07/28/2016 11:06 AM
    rateOfpay=parseFloat(New_Value_## ) ; rateOfpay=rateOfpay/100
    David Williams
    agersh
    Systems Analyst
    Martin Health System
    Veteran Member
    (143 points)
    Veteran Member
    Posts:73


    Send Message:

    --
    07/28/2016 12: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
    HRIS Lead
    University of Maryland Medical System
    Veteran Member
    (335 points)
    Veteran Member
    Posts:117


    Send Message:

    --
    07/28/2016 12: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
    HRIS Lead
    University of Maryland Medical System
    Veteran Member
    (335 points)
    Veteran Member
    Posts:117


    Send Message:

    --
    07/28/2016 12: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
    HRIS Lead
    University of Maryland Medical System
    Veteran Member
    (335 points)
    Veteran Member
    Posts:117


    Send Message:

    --
    07/28/2016 12: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

    You are not authorized to post a reply.