1. Glenn Barber
  2. PowerBuilder
  3. Thursday, 19 April 2018 01:58 AM UTC

We have reports where the currency value needs to be displayed with the appropriate currency symbol.  We can provide each row with the currency code and the currency symbol.

How do we construct a dynamic format 

The Base format is #,##0 ;[RED](#,##0);   how can we dynamically insert the currency symbol  in the format so it ends up like  $#,##0 ;[RED]($#,##0);   or €#,##0 ;[RED](€#,##0) utilizing the value of currsymb in the row?

 

 

Accepted Answer
mike S Accepted Answer Pending Moderation
  1. Thursday, 19 April 2018 14:12 PM UTC
  2. PowerBuilder
  3. # Permalink

you must prefix (escape) all non-numeric values with a backslash in the format (or resulting format from the expression):

\€#,##0 ;[RED](\€#,##0)

$ doesn't actually need that, but others do.  you can use \$ and it works.

note that this is not supported in appeon web server,  i assume it also doesnt work in mobile

 

Comment
  1. mike S
  2. Thursday, 19 April 2018 14:15 PM UTC
i mean all values that you want to simply display  as part of the format must have the \ in front. 

  1. Helpful
There are no comments made yet.
Roland Smith Accepted Answer Pending Moderation
  1. Thursday, 19 April 2018 03:28 AM UTC
  2. PowerBuilder
  3. # 1

The Format property allows for an expression. Set an expression that uses the currency type column to return the proper format. If you have a currency table in your database, you could add a format column and bring it into the resultset. The expression could just point to the format column. Doing it that way would allow you to adjust the format without changing any code.

Comment
There are no comments made yet.
Glenn Barber Accepted Answer Pending Moderation
  1. Thursday, 19 April 2018 05:11 AM UTC
  2. PowerBuilder
  3. # 2

Posting this as an answer because replies seem to get hidden and not responded to in this community format.

I tried using an expression, but the only thing I could do that seem to work was an If statement condition.  I tried constructing the format string by appending values cursymb + "#,###"- but all that did was return the expression text and not the value formatted.  So I might be doing something wrong but I cant find an example that doesnt require an if on specific values which is not useful - so please if you know the format that works - please illustrate.

Comment
There are no comments made yet.
Glenn Barber Accepted Answer Pending Moderation
  1. Thursday, 19 April 2018 05:24 AM UTC
  2. PowerBuilder
  3. # 3

OK - I figured out what I was doing wrong - something stupid - putting the expression in the format cell and not in the supporting expression statement.

This works...

currsymb + '#,##0 ;[RED](' + currsymb + '#,##0); '

 

Comment
There are no comments made yet.
Glenn Barber Accepted Answer Pending Moderation
  1. Thursday, 19 April 2018 05:41 AM UTC
  2. PowerBuilder
  3. # 4

Actually I spoke too soon - while #,##0 ;[RED](#,##0);    produces a number 950,000 the expression

currsymb + '#,##0 ;[RED](' + currsymb + '#,##0); ' produces €9,500    

Is this a bug in the expression engine?

Comment
There are no comments made yet.
Roland Smith Accepted Answer Pending Moderation
  1. Thursday, 19 April 2018 12:49 PM UTC
  2. PowerBuilder
  3. # 5

What is the datatype? If it is a whole number, that might explain the missing zeros.

Comment
  1. Glenn Barber
  2. Thursday, 19 April 2018 16:38 PM UTC
Its decimal data - the introduced character causes the format to misbehave



 

  1. Helpful
  1. Glenn Barber
  2. Thursday, 19 April 2018 16:38 PM UTC
Its decimal data - the introduced character causes the format to misbehave



 

  1. Helpful
There are no comments made yet.
Glenn Barber Accepted Answer Pending Moderation
  1. Thursday, 19 April 2018 16:33 PM UTC
  2. PowerBuilder
  3. # 6

Yes - That is the answer - introducing the \ makes it work  .  Thanks

'\' + currsymb + '#,##0 ;[RED](' + '\' + currsymb + '#,##0); '  is producing the correct results.

Comment
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.