Can we sort text field which is containing percentage numbers?

💬 General Discussion
1

Hi All,

I have a text field in the opportunity module that contains percentage values. Since we can’t delete and recreate this field, is there any way to sort it without deleting it? Currently, it’s a text field, so it doesn’t sort the percentages correctly.

image
image133×765 13.1 KB

Kindly help

2

If you use up or down arrow which is next to percentages(%) field, does it sorting column data?

Edit: usually, you need to click arrow button twice or thrice.

3

No, for ref I shared screenshot

4

yes I did it multiple times

5

Can you please try the following

  1. Take backup of database in case to revert the code changes.
  2. Update the custom opportunities_c table to format the number to a two digit number as shown in the following example
    update opportunities_cstm set percentage_c=concat(LPAD(REPLACE(percentage_c,'%',''),2,'0'),'%')
  3. Add a new logic hook entry at custom\modules\Opportunities\logic_hooks.php as show below
$hook_array['before_save'] = Array(); 
$hook_array['before_save'][] = Array(1, 'format percentage', 'custom/modules/Opportunities/FormatPercentage.php','FormatPercentage', 'formatPercentageDigits');
  1. Create a new php file at custom\modules\Opportunities\FormatPercentage.php to add logic hook class and function with the following code
<?php
if(!defined('sugarEntry') || !sugarEntry){
	die('Not a valid entry');
}
class FormatPercentage{
	public function formatPercentageDigits(SugarBean $bean, $event,$args){
			$bean->percentage_c = sprintf("%02d", preg_replace('/[%]/', '', $bean->percentage_c)).'%';
	}
}
  1. Test

image

1 Like
6

Hi @Harshad,

Its working but only on 2 digit value, suppose we have 3 digit value like 100% , for that i updated query with 3 but it is showing 090% which we dont want, can we show normal values in list view
update opportunities_cstm set percentage_c=concat(LPAD(REPLACE(percentage_c,‘%’,‘’),3,‘0’),‘%’);

can we apply sorting without adding any aditional zero in table?

7

I am not 100% sure but it looks there is only one approach to this issue. If possible try creating a new custom field of type integer and copy the old values without the % sign and converted to int using an update SQL query as in previous suggestion. Use the new column at places of old percentage field. Dont forget to take the backup of php and database.