Discussions

Expand all | Collapse all

case sensitive filters

  • 1.  case sensitive filters

    Posted 08-31-2018 17:46
    I do not remember this always being an issue - but on a new app i created my filters do not recognize equivalent entries when they are not typed exactly the same (ie case sensitive)
    Usually, we use a drop down for this field so they can only choose the one format, however; sometimes the users need to upload from a spreadsheet. if they are not keeping the format - QB does not recognize the entry as an equivalent. any way to fix this without me having to go through and clean up every month or so?

    see photo for a much better representation of what i am talking about here. ref :avnet/Avnet/AVNET


  • 2.  RE: case sensitive filters

    Posted 08-31-2018 23:16
    It could be hidden characters from the spreadsheet that appear to be new entries. I wouldn't expect the values to sort alphabetically as shown. Have you tried a trim formula, or analyzing the data in the spreadsheet? I had a similar issue to this recently, and could not locate the hidden characters. I had to create a formula with a trim function to remove the hidden characters, and sort/filter off of that field to get the alphabetized list.  That field is a unique value in my App, so this issue you show here looks a little different, as Vendor appears to be a text field, possibly a dropdown?

    ALLIED
    ANIXTER
    AVNET
    Anixter
    Avnet
    BISCO
    HEILIND
    Heilind

    Create a new field - Vendor Fx -  Trim([Vendor]) 
    Sort by that field and see if the data matches this format
    ANIXTER
    Anixter
    AVNET
    Avnet

    If so, I am guessing hidden characters, and its a matter of cleaning up data in the spreadsheet before importing. (and a clean up effort in your table via grid edit or export/import)

    If not, then maybe the field is case sensitive and the sort order remains a mystery.



  • 3.  RE: case sensitive filters

    Bronze
    Contributor
    Posted 08-31-2018 23:38
    Glad I refreshed before replying... Slider covered most of it.

    If you want to maintain both dropdown and spreadsheet import functionality, the best low-tech solution is going to be a helper field. I'd have the helper field enforce both trim and upper on the data entry field, and then use the helper field for reporting etc.

    Except I think I disagree about hidden characters. First report I looked at in my app - it looks like in this context QB natively treats upper/lower case as in different alpha order! See my example: ARK... comes before Acu...


    So, yeah, helper field, and hope your team doesn't mind all-uppercase. Goodness forbid that you are required to maintain mixed case styling such as AVnet.

    Or open a support ticket and/or uservoice suggestion!