case sensitive filters

  • 0
  • 1
  • Question
  • Updated 3 months ago
  • Answered
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
Photo of mkosek

mkosek

  • 776 Points 500 badge 2x thumb

Posted 3 months ago

  • 0
  • 1
Photo of Slider

Slider

  • 1,326 Points 1k badge 2x thumb
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.

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!