Solved

Number formatting

  • 8 September 2023
  • 6 replies
  • 562 views
J
Userlevel 3
Badge +12

I am curious if anyone has any tricks to formatting a number (or number as string) to currency, so with commas and 2 decimal places, natively in a bot. I am working with a customer in a locked down environment that does not want to install any Bot Store packages, and both the python and javascript packages are woefully inadequate as they neither allow me to pass in a variable or get a variable as a result.

I have mocked up a couple of working methods, one using PowerShell to format and then write out to a temp file and another splitting the number on the decimal and then working out the commas manually. But, honestly, both are extremely ugly and nothing I’d be proud to share with the customer. Not to mention, I would like to avoid having to write to temp files if possible.

 
 
icon

Best answer by LoganPrice 15 September 2023, 17:05

View original

6 replies

LoganPrice
Rank
Userlevel 3
Badge +9

Hey @J.Logan (nice name btw),

*** I edited my original response as I found an error in my logic ***

This turned out to be a lot tricker than I thought it would be.

I managed to create AA code that doesn’t rely on any non-native packages or programs outside of Automation Anywhere.

It essentially splits a number into the integer and decimal portions. It takes the integer portion and converts it to string, then rebuilds the integer string one digit at a time from right to left. Every three digits, the code adds a comma. It then adds the decimal (rounded to two places) at the end, resulting in a full comma decimal number stored as string.

Code:

 

Results:


You seem pretty savvy, so in the six days since you posted this, you may have found a similar method. Please let me know if this helped you out.

Thanks,

Logan P.

Automate Everything
S
Userlevel 3
Badge +6

@J.Logan Did you try Vbscript ?
You can pass AA String/Number and it should return you the expected value.
@LoganPrice NumberUtils is not listed as a package on AA docs.

Function FormatAsCurrency(inputNumber)
    ' Check if the input is a numeric value
    If IsNumeric(inputNumber) Then
        ' Format the input as currency
        FormatAsCurrency = FormatCurrency(inputNumber)
    Else
        ' Return an error message if the input is not numeric
        FormatAsCurrency = "Error: Invalid input. Please provide a valid number."
    End If
End Function

 

J
Userlevel 3
Badge +12

Hi, @LoganPrice thanks for the response and the code. I did start off with something very similar, good to see that Great Minds (and names) do Think Alike :) Unfortunately, I ran up against a deadline with the customer and was forced to break my own rule on using temp files. I ended up just calling PowerShell in a hidden window, then reading back in from the resultant temp file 🤢

-Window Hidden -Command "'{0:C}' -f $sDGTotal$ |
   out-file '$inp_sSaveLocation$/tmp_files/DGTotal.txt'"

 

Now that the process has moved into UAT, I have a little more time to revisit the matter for future enhancements. I’m sure your code will provide a great place to start. Much appreciated!

 
J
Userlevel 3
Badge +12

@Sumit.K7 Thanks for the suggestion. In this case, the customer informed me they have an SRP in place blocking some scripting file types, vbscript among them, so I didn’t even look at it. Even if it did run (running the script in memory from the Bot Agent rather than a vbs file) I didn’t want to overstep their obvious security concerns.

Thanks for the code, though, I will keep this in case I can use it in the future.

 
LoganPrice
Rank
Userlevel 3
Badge +9

@J.Logan Did you try Vbscript ?
You can pass AA String/Number and it should return you the expected value.
@LoganPrice NumberUtils is not listed as a package on AA docs.

Function FormatAsCurrency(inputNumber)
    ' Check if the input is a numeric value
    If IsNumeric(inputNumber) Then
        ' Format the input as currency
        FormatAsCurrency = FormatCurrency(inputNumber)
    Else
        ' Return an error message if the input is not numeric
        FormatAsCurrency = "Error: Invalid input. Please provide a valid number."
    End If
End Function

 

@Sumit.K7, you are right, thanks for pointing that out. I just checked with our team and it is an artifact of our v11 migration. However, that action could be removed and replaced with AA code pretty easily. It is not essential to the portion which adds commas to the left of the decimal point.

Automate Everything
LoganPrice
Rank
Userlevel 3
Badge +9

Hi, @LoganPrice thanks for the response and the code. I did start off with something very similar, good to see that Great Minds (and names) do Think Alike :) Unfortunately, I ran up against a deadline with the customer and was forced to break my own rule on using temp files. I ended up just calling PowerShell in a hidden window, then reading back in from the resultant temp file 🤢

-Window Hidden -Command "'{0:C}' -f $sDGTotal$ |
   out-file '$inp_sSaveLocation$/tmp_files/DGTotal.txt'"

 

Now that the process has moved into UAT, I have a little more time to revisit the matter for future enhancements. I’m sure your code will provide a great place to start. Much appreciated!

 

Sorry to hear! I understand the struggle though. Good luck to you in the future!

Automate Everything

Reply


Terms & ConditionsCookie settings