Discussions

Expand all | Collapse all

Formula Text field sorting in Dynamic Search

  • 1.  Formula Text field sorting in Dynamic Search

    Posted 04-12-2017 08:37
    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")


  • 2.  RE: Formula Text field sorting in Dynamic Search

    Posted 04-12-2017 10:33
    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.


  • 3.  RE: Formula Text field sorting in Dynamic Search

    Posted 04-12-2017 13:42
    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 :)