Solved

How to display percentage in the UI (when value in DB is in decimals)?

  • 28 June 2022
  • 5 replies
  • 196 views

Userlevel 2
Badge +2

I am looking for a way to display a percentage in the UI, while the column contains decimal values in the database. E.g. 0.51 in database displays 51 in UI.

I expected that the domain control field 'Percentage’ would do the trick, but it doesn't. The documentation is not very specific about what the Percentage control does when it is selected in a domain:

I cannot be the first to deal with this issue, hence I hope that somebody here can point me in the right direction.

Thanks in advance!

icon

Best answer by Freddy 1 August 2022, 15:01

View original

This topic has been closed for comments

5 replies

Userlevel 5
Badge +16

I am looking for a way to display a percentage in the UI, while the column contains decimal values in the database. E.g. 0.51 in database displays 51 in UI.

I expected that the domain control field 'Percentage’ would do the trick, but it doesn't. The documentation is not very specific about what the Percentage control does when it is selected in a domain:

I cannot be the first to deal with this issue, hence I hope that somebody here can point me in the right direction.

Thanks in advance!

 

You can make an expression field with and use the FORMAT(t1.field,'P') function. 

https://docs.microsoft.com/en-us/dotnet/standard/base-types/standard-numeric-format-strings#PFormatString (for more info on the format specifier). 

Userlevel 7
Badge +23

Hi Dennis,

Within the Windows GUI, the control PERCENTAGE does not add a “%” character behind the number in the UI, it does do that when exporting the data to for example Excel. The control does add the functionality that you are able to group on this value - not all controls can do that.

In Universal GUI, this control does not do much as the GUI simply shown the value as a number.

Best way of showing it the way you would like is to add an expression field that takes the percentage value as input and then multiplies the value by 100 and adds a "%” character behind it. With that it is possible to convert 0.51 to 51%.

Does that help you out?

Userlevel 7
Badge +23

Maybe in addition to my previous reply, you could check out the control Progress bar. That will allow you to present a progress bar inside your forms like so:

Progress bar (icon not included)

 

Userlevel 2
Badge +2

Apparently I haven't been clear enough in my initial question. Sorry for that.

I am looking for a way to show percentages in the UI (e.g. 50) while the database holds the fractional value (e.g. 0.5): 

  • User enters: 60 → Database value becomes 0.6
  • Database holds 0.6 → UI displays 60

An expression field will not do the trick, since the user has to be able to enter a value too.

I already thought of making a view and the using an instead of trigger to update the entered values, but that feels like a far-fetched solution.

Any other possible solutions are welcome.

Userlevel 5
Badge +16

Apparently I haven't been clear enough in my initial question. Sorry for that.

I am looking for a way to show percentages in the UI (e.g. 50) while the database holds the fractional value (e.g. 0.5): 

  • User enters: 60 → Database value becomes 0.6
  • Database holds 0.6 → UI displays 60

An expression field will not do the trick, since the user has to be able to enter a value too.

I already thought of making a view and the using an instead of trigger to update the entered values, but that feels like a far-fetched solution.

Any other possible solutions are welcome.

I think the easiest way is to use one input field where the user can enter the % .. you can use logic to transform 60 to 0.6 ..   and an expression field that for for example uses the proposed format() function to show a percentage.  .. using a layout procedure you can fiddle with the visability of the fields.