[Resolved] How to sort or order custom field value having dollar sign and commas as number field value using views sorting

[Author]

Posts

[marcusC-4]

I have a custom post type called Listings that was created using WP Types. I need to be able to use the Number field type for sorting purposes.

However, on the backend, when someone is adding a new listing, it looks very strange to enter 10000000 instead of $10,000,000 -- plus who can keep up with all those zeros?!?

Is there a way to allow my client (the website owner) to enter their listing numbers using dollar signs & commas & have a function that removes those before they are queried so that those fields don’t appear empty on the front-end when displaying the listing?

I see that someone did a similar thing on this thread but I don’t understand all of that code: https://toolset.com/forums/topic/number-field-for-price-on-properties-views-filter-not-sorting-properly/

OR, if there’s a better way to set this up, please let me know.

Thanks

[marcusC-4]

Actually, if it’s possible, I’d rather use the Single Line field option for these values & then force them to sort properly rather than using the Number field option.

So, here’s an example of 3 values
$1,000,000
$450,00
$10,000,000

When we sort these in a table on the front-end, they are only sorting by the first digit, not the entire number. Is there a way to fix that? If so, that solves all our issues.

Here’s a link to the page with that table on it so you can see what I mean: hidden link

[Minesh]

Hello. Thank you for contacting the Toolset support.

Well - is it possible for you to send me temporary access details so that I can check your view setup that will help me to guide you in right direction.

*** Please make a FULL BACKUP of your database and website.***
I would also eventually need to request temporary access (WP-Admin and FTP) to your site. Preferably to a test site where the problem has been replicated if possible in order to be of better help and check if some configurations might need to be changed.

I would additionally need your permission to de- and re-activate Plugins and the Theme, and to change configurations on the site. This is also a reason the backup is really important. If you agree to this, please use the form fields I have enabled below to provide temporary access details (wp-admin and FTP).

I have set the next reply to private which means only you and I have access to it.

[Minesh]

Could you please check now: hidden link

I've added following code to your current theme's functions.php file:

function func_orderby_asking_price( $orderby ) {
	global $WP_Views;
	
	 if($WP_Views->current_view == 907){
		 $orderby = str_replace( 'wp_postmeta.meta_value', "cast(replace(trim( leading '$'  from wp_postmeta.meta_value),',','')  AS UNSIGNED)", $orderby );
	}
	
    return $orderby;
}
add_filter('posts_orderby', 'func_orderby_asking_price' );

What we are doing is replacing the $ sign as well as the comma to make the field as numeric and cast the field as unsigned. I can see its correctly sorting the asking price column now. Could you please confirm.

[marcusC-4]

Minesh,

It’s working great -- and I see that the change has been applied to the other columns as well. Thank you!