Alphanumeric data – best field for sorting

  • CJW

    (@cjw)


    7 months ago

    Hi Roland/all,

    I’m looking to sort records containing an alphanumeric data field (‘plot number’) and wondered what the best field type in PDB would be? This would be for a range of data starting at 1 through to 130 with some records having a or b appended to the number (so 1a, 2, 3, 4, 5a, 5.100b, 101, 102a and so on) I’ve experimented with text-line and rich text but once my data goes higher than 10, the sort no longer works. I have re-created a new table from scratch as there was a suggestion that the table I was using might have been corrupted in some way but that hasn’t changed the result. I have read some information on VARCHAR but not clear on how this would translate in PDB.

    This alphanumeric data (‘plot number’) is used as the primary record identifier (the data represents plots of land and is a mostly static list of plots with the occasional plot being split resulting a single plot number then being appended with ‘a’ or ‘b’ and also changed back to a single number).
    As occupancy of the plot records change, the remaining fields in the record are edited as required.
    I use the ‘plot number’ as the Duplicate record check field when importing changes to the database which works well as there are two teams that update the table I import and we all use plot as the

    Any suggestions are most welcome 🙂

    Thanks,

    Craig

Viewing 1 replies (of 1 total)
  • Plugin Author rolandbarker

    (@rolandbarker)

    7 months ago

    There are two things I am unsure of in your question: the context in which this sorting is done (using a shortcode? In the admin ?) and also what the desired sort order is and what you’re getting instead.

    Generally speaking, sorting on a text-area type field isn’t allowed, I suggest you keep your plot number value in a text-line type field. This will be sorted alphabetically, and I am guessing this will give you what you want. It is not possible to sort numerically if there are non-numeric characters in the data.

    If you want to implement a custom sort of some kind, it is possible to write custom code to alter the database query that the plugin uses to generate a list of records.

Viewing 1 replies (of 1 total)

The topic ‘Alphanumeric data – best field for sorting’ is closed to new replies.