Number formatting and Sorting in a Repeater

I’ve added sorting capability to a repeater table I created, and it works fine for text values, but when I’m trying to set it to number values that have some minor number formatting ($, commas) the sort doesn’t work.

Is this just a limitation of the feature for now or has someone figured out how to sort on formatted numbers or format the numbers so they’ll sort?

Thanks in advance!

-Chris

I ran into the same problem yesterday. You can sort when there are periods, but not commas.

Sorry, I’m afraid there is no way that I can think to do this right now. If we allowed the ability to Sort by a “Value” option where you could enter expressions this would become pretty trivial. I will add it to the list, thanks!

1 Like

It could be handled that way, or there could be a formatting tool that applies various number formatting to the fields when they are rendered (a la Excel and Numbers, where you can assign cells to have currency formatting, specify decimal spaces, etc). Not sure which approach would be better.

Thanks for the info and adding this to the list!

1 Like

I like this better because it allows you to store numbers as numbers. They’d always be sortable, comparable, and usable in arithmetic expressions, and any time you needed to display them you could apply whatever formatting is needed (commas, currency symbols, places after the decimal, etc.).

Jeff

1 Like

Of course, there’s always a way if you’re motivated.

Jeff
sortable commas.rp (62.7 KB)

4 Likes

Is it possible that you can round your numbers?, for example when sorting salary ranges these are generally expressed as £40,000 £22,000 etc. In which case you could format your repeater item thus which is much simpler way of dealing with the comma.

£[[Item.Salary]],000

This topic helped me get over the hump on an issue, thanks all.

In the process, I made a sortable, editable, comma-formatted number field in a repeater along with some instructions on using it yourself. Hopefully it’s not too complicated or difficult to use.
formatted repeater.rp (76.5 KB)

Almost 3 years later since this thread started, any progress on a feature?

Still facing the issue. Help needed. Let us know if there is any other solution or option ?

Hi brolloks and Titanlists,

You can sort numbers with commas by using the toLocaleString JavaScript method. The toLocaleString method will automatically insert commas at the appropriate places when called on a number. My coworker Anthony posted more information about this, here:

https://www.axure.com/c/forum/7-0-tips-tricks-examples/11615-number-format-thousands-separator-input-number.html#post63567

So let’s say you had a column called “Column0” with various long-string numbers. To format these with commas, edit the Set Text interaction of the repeater’s OnItemLoad to:

[[(Item.Column0*1).toLocaleString()]]

If you wanted the “$” character in there as well, add this to the front, outside of the double brackets:

$[[(Item.Column0*1).toLocaleString()]]

Any sort interactions will work with the above methods. Hopefully this helps!
NumberFormatting.rp (53.3 KB)

3 Likes

Great Job Jane…this really is a much simpler method that many that have been proposed.

I tried this and it works but unfortunately I have another element that relies on the figure and can’t read it because of the comma in the figure. Can you advise?

Hi colmcq,

Would it apply to use the replace() method in your element’s interaction? That function can replace a character (e.g. the comma) in a string and return a different character (e.g. a blank space). Here’s a reference:

https://www.w3schools.com/jsref/jsref_replace.asp

And a sample file to illustrate that in an interaction:

Replace.rp (58.6 KB)

Additionally, if the figure is contained in a repeater item, you could also consider targeting the original row value of the dataset which doesn’t include the commas yet. Feel free to attach a copy of your RP file though, and we can take a look. :slight_smile:

This is GREAT. I’ve used this string successfully. However, I have another question - how can this be applied to a sum? Here I’m adding a variable to a variable. I’ve tried this but it turned it into a date. $[[(LoanAdvAuthAmountSum + CommAuthAmountSum*1).toLocaleString()]]

Hi Luxxer,

Since you’re doing calculations, it looks like you’ll want to sum up the variables first by enclosing the sum calculation inside its own parentheses and then multiplying by 1 to convert that value into a number, e.g.:

$[[((LoanAdvAuthAmountSum + CommAuthAmountSum)*1).toLocaleString()]]

Thank you! Problem solved.