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
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
    Posts: 1127
    Veteran Member
    7/28/2016 3:06 PM
      rateOfpay=parseFloat(New_Value_## ) ; rateOfpay=rateOfpay/100
      David Williams
      agersh
      Veteran Member
      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
        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
          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
            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