Forum Discussion

MichelleCoslet's avatar
MichelleCoslet
Qrew Assistant Captain
7 years ago

case sensitive filters

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
reports and charts
  • KevinSlider's avatar
    KevinSlider
    Qrew Trainee
    7 years ago
    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.

  • AlexCertificati's avatar
    AlexCertificati
    Qrew Cadet
    7 years ago
    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!