Formula Text field sorting in Dynamic Search

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • In Progress
I have this Formula Text field to search on a range of prices, but it sorts numerically and I can't see how I can influence it.

It sorts, by 0, then 1,000,000 and then 100,000?

If([Price] < 100000, "0 - 100,000",
[Price] >= 100000 and [Price]< 200000, "100,000 - 200,000",
[Price] >= 200000 and [Price]< 300000, "200,000 - 300,000",
[Price] >= 300000 and [Price]< 400000, "300,000 - 400,000",
[Price] >= 400000 and [Price]< 500000, "400,000 - 500,000",
[Price] >= 500000 and [Price]< 600000, "500,000 - 600,000",
[Price] >= 600000 and [Price]< 700000, "600,000 - 700,000",
[Price] >= 700000 and [Price]< 800000, "700,000 - 800,000",
[Price] >= 800000 and [Price]< 900000, "800,000 - 900,000",
[Price] >= 900000 and [Price]< 1000000, "900,000 - 1,000,000",
[Price] >= 1000000 and [Price]< 2000000, "1,000,000 - 2,000,000",
[Price] >= 2000000 and [Price]< 3000000, "2,000,000 - 3,000,000",
[Price] >= 3000000 and [Price]< 4000000, "3,000,000 - 4,000,000",
[Price] >= 4000000 and [Price]< 5000000, "4,00,000 - 5,000,000",
[Price] > 5000000, "Over 5,000,000")
Photo of Michael Graham | Insight Global UK

Posted 2 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,296 Points 50k badge 2x thumb
Spaces sort before numbers and two spaces sort before one space. So you will have to contrive your results to have leading spaces for the results that you want to sort higher up in the sequence.
I used a formula on the [Price] field to the sort...

[Price Sort]
If([Price] < 100000, "1;            Under 100,000",
[Price] >= 100000 and [Price]< 200000, "2;      100,000 - 200,000",
[Price] >= 200000 and [Price]< 300000, "3;      200,000 - 300,000",
[Price] >= 300000 and [Price]< 400000, "4;      300,000 - 400,000",
[Price] >= 400000 and [Price]< 500000, "5;      400,000 - 500,000",
[Price] >= 500000 and [Price]< 600000, "6;      500,000 - 600,000",
[Price] >= 600000 and [Price]< 700000, "7;      600,000 - 700,000",
[Price] >= 700000 and [Price]< 800000, "8;      700,000 - 800,000",
[Price] >= 800000 and [Price]< 900000, "9;      800,000 - 900,000",
[Price] >= 900000 and [Price]< 1000000, "91;   900,000 - 1,000,000",
[Price] >= 1000000 and [Price]< 2000000, "92;  1,000,000 - 2,000,000",
[Price] >= 2000000 and [Price]< 3000000, "93;  2,000,000 - 3,000,000",
[Price] >= 3000000 and [Price]< 4000000, "94;  3,000,000 - 4,000,000",
[Price] >= 4000000 and [Price]< 5000000, "95;  4,000,000 - 5,000,000",
[Price] >= 5000000 and [Price]< 9000000, "96;  Over 5,000,000"
)

Then created another formula to remove sorting and it works after a fashion.
[Price Range]
Right([Price range],";")

I had to fiddle a lot with spaces like you said Mark. 
I'd like to think there's a better way though :)