Forum Discussion

MichaelGraham2's avatar
MichaelGraham2
Qrew Assistant Captain
8 years ago

Formula Text field sorting in Dynamic Search

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")
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    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.
  • MichaelGraham2's avatar
    MichaelGraham2
    Qrew Assistant Captain
    8 years ago
    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 :)