Thursday, October 4, 2018

Convert Amounts or Currency Values in Words


Image

This can be achieved by using formulas. On a transaction search, one should use a Formula (Text) column and insert this formula:
 
CASE WHEN {amount}=0 THEN 'ZERO' ELSE TO_CHAR(TO_DATE(TO_CHAR(TRUNC({amount}, 0)),'J'),'JSP') || ' ' || (CASE WHEN LENGTH(TO_CHAR(REGEXP_REPLACE({amount}, '^[0-9]+\.', ''))) = 1 THEN TO_CHAR(REGEXP_REPLACE({amount}, '^[0-9]+\.', '')) || '0/100' ELSE TO_CHAR(REGEXP_REPLACE({amount}, '^[0-9]+\.', '')) || '/100' END) || ' ' || {currency} || '(s) Only' END
 
The result of this formula will be like this:
 
 
 
The customer could also use a Custom Transaction Body field in order to display the amount in words on the record itself. The Custom Transaction Body field should have this formula on the Validation and Defaulting section:
 
CASE WHEN {total}=0 THEN 'ZERO' ELSE TO_CHAR(TO_DATE(TO_CHAR(TRUNC({total}, 0)),'J'),'JSP') || ' ' || (CASE WHEN LENGTH(TO_CHAR(REGEXP_REPLACE({total}, '^[0-9]+\.', ''))) = 1 THEN TO_CHAR(REGEXP_REPLACE({total}, '^[0-9]+\.', '')) || '0/100' ELSE TO_CHAR(REGEXP_REPLACE({total}, '^[0-9]+\.', '')) || '/100' END) || ' ' || {currencysymbol} || ' Only' END
 
And here is the result:
 
 
 
Use Case:
 
1. Amount in words can be used in printing bank checks. The format generated by the formulas is also the standard one.
2. This formula can also be used for workflows that will convert amounts to words.
 
on

7 comments:

  1. fortniteSeptember 4, 2019 at 6:35 AM

    Hey There. I found your blog using msn. This is a very well written article. I’ll be sure to bookmark it and come back to read more of your useful info. Thanks for the post. I’ll definitely return. convert currency

    ReplyDelete
  2. D. James AndersonJune 9, 2020 at 4:56 AM

    You may find that cryptocurrency is for you or you may not however in any event you'll have the option to talk with a level of conviction and information that others won't have. Getnode Test

    ReplyDelete
  3. albina N muroJune 30, 2020 at 1:54 AM

    Please let me know if you’re looking for a article writer for your site. You have some really great posts and I feel I would be a good asset. If you ever want to take some of the load off, I’d absolutely love to write some material for your blog in exchange for a link back to mine. Please send me an email if interested. Thank you! get free instagram likes famoid

    ReplyDelete
  4. D. James AndersonJuly 20, 2020 at 1:10 AM

    Quick exchanges - Bitcoin is moved in a flash over the Web. BTC escrow

    ReplyDelete
  5. xAugust 17, 2020 at 11:51 PM

    Hello, why does this give me an invalid expression when the number exceeds (more or less) 5.5Million?

    ReplyDelete
  6. 123456789August 19, 2020 at 1:05 PM

    In a dispensed cryptocurrency mechanism, the cash is issued, controlled and encouraged by using the collective network peer network - the continuous pastime of which is known as mining on a peer's gadget.How to trade forex and cryptocurrency

    ReplyDelete
  7. UnknownJanuary 24, 2021 at 4:22 AM

    CASE WHEN {total}=0 THEN 'ZERO' ELSE TO_CHAR(TO_TIMESTAMP(LPAD(TRUNC({total}, 0), 9, '0'), 'FF9' ), 'FFSP') ||' ' || (CASE WHEN {total}-TRUNC({total}, 0) > 0 THEN ' AND ' || (CASE WHEN LENGTH(TO_CHAR(REGEXP_REPLACE({total}, '^[0-9]+\.', ''))) = 1 THEN TO_CHAR(TO_DATE(TO_CHAR(TRUNC(REGEXP_REPLACE({total}, '^[0-9]+\.', ''), 0)*10),'J'),'JSP') || ' FILS ' ELSE TO_CHAR(TO_DATE(TO_CHAR(TRUNC(REGEXP_REPLACE({total}, '^[0-9]+\.', ''), 0)),'J'),'JSP') || ' FILS ' END) ELSE ' ' END) || ' ' END

    ReplyDelete

Subscribe to: Post Comments (Atom)