As part of a SmartList Search series (which can be found here), we will discuss advance search requirements AND using wildcards.
Wildcards are available in the SmartList search.  They are a few that I know of… someone once told me there were others but I have yet to figure that out… if I have missed any, leave a comment below and TEACH ME!!!

Wildcard Options for SmartLists

Since the search window for SmartList is restricted to 4 different search definitions, sometimes we have to get a little advanced.
First, let’s talk about the ANY FIELD search.

ANY FIELD

In the SmartList definition field lookup, you will find a field called ‘ANY FIELD’.  I won’t over complicate this… if you choose that as the field name for your search criteria, SmartList will look at any field in the SmartList and compare it to the criteria listed and return the appropriate results.  It’s not a hard concept, I know, but hey, it’s MISSED A LOT!! AND I LOVE THIS!! In my easy example below, I am using the ANY FIELD to look for the word Allen – note it returned the Vendor ID containing Allen AND a different vendor that has Allen in the street address.  SWEET!!

Wildcard Options

This is going to be fun, and I hope to get your attention with some of my examples 🙂
Remember, these are just examples to show how they are used… the options are endless and I would love to see your advanced searches!
 

Wildcard % (Percent Sign)

This closely relates to the asterisk wildcard * – basically meaning zero or more characters, regardless of their value.  It can be used with other characters, before and/or after a requirement.  To some degree this is handled the same as the search type ‘contains’.    BUT it’s not exactly the same… what if you wanted to return GL Accounts (My format is XXX-XXXX-XX) that start with 000 but has a last segment that is 01.  Sure, you could use two search definitions but what if you want more than 4 search criteria?  Remember, we are talking advanced here 🙂  These are helpful when circumventing the 4 definition restriction.  Notice that I put the dash (-) in to make that a required value.  This works well given my account format above.

Wildcard _ (Underscore)

The underscore wildcard is a specific placeholder variable.  Unlike the %, it specifies a specific character space.  In the example above, looking for GL accounts that start with 000 but have a last segment of 01, I can also use the underscore.  In some cases this might actually make more sense… My new search would be 000-____-01 (an underscore for each of the character spaces of my middle segment).  But let’s look at something different.  What if I wanted all the GL Transactions that started with 000 AND ended with 00 AND had a middle segment that has a second character value of 1.  I know, that’s a lot, but it’s a real scenario and folks are using this ALL THE TIME.  Here we go!  000-_1__-00

HOT STUFF!!!!  It’s ok, you can get excited too!

Wildcard [] (open and close bracket)

This signifies a specific character match.  The open and close ends the search.. with a twist.  You can use more than one set of brackets consecutively but each set of brackets refers to that character space.  Within each bracket (character space) you can indicate a match restriction.  For example, if I have [0] this is saying that the first character of the field in the search must be a ‘0’ (when I say first, I am assuming our search type is “begins with”… this character search, like other wildcards, goes hand in hand with the search type).  If I say [0][1] – this is saying that the first character must be a zero, and the second character must be a 1.  What about this?  [01]  – what does this mean?  The first two characters must be ’01’?   Not quite.. it actually means the FIRST character can either be a 0 OR a 1.  How about [01][23]?  Yup, it’s a little hard to decipher, but that means the first character can either be a 0 or a 1, AND the second character can either be a 2 or a 3.  SHEW!  That’s a lot!  Cumbersome, I suppose, but once you understand it and set your search, it’s very VERY helpful!
So here’s an example.  Again, using XXX-XXXX-XX as our sample structure… what if I wanted the first character to either be a 1 or 2.  The second character must = 0.  The 3rd character must = 0.  Deep breathe.  That’s it for the first 3 characters (first segment in my example).  But then I want the first character of my second segment to be a 5… and the second character or my second segment (6th character for those who are keeping count – don’t forget the dash is a character too) to be a 1.  I’ll give you a minute… I needed one too.  How about [12][0][0][-][5][1] ?

HOT DIGGITY DOG!!
Uh oh, now my boss is asking for me to do all that AND add where JE # is greater than 691.  No PROBLEM!  Notice our first definition has an OR (first character match = 1 OR 2) but now we need to add an AND definition.  I promised you advanced right!

BOOM!
Uh oh… we have one more final tweak to add… the same restrictions above… BUT the third character of my second segment cannot equal a 5… and that’s the last wildcard option.

Wildcard [^] (open and close bracket with caret)

The caret added to the bracket indicated ‘does not equal’.  Otherwise it works just the same as the previous wildcard (open/close bracket without caret).  So let’s just add one more value to our already ‘simple’ search criteria…  [^5]

HOLY MOLY!!!  We DID combine the AND search logic with the OR search logic and THEN SOME MORE!!!   #MOVEFORWARDWITHDORWARD
Take a long lunch… you deserve it!
Wildcards are WILD!!! You can do so much!  What advanced searches are you working on?  Share them below!

PRO TIP!

Be sure to save your SmartList searches by creating or modifying a SmartList favorite!  Not sure how to do that?  Send me a message and I will be happy to help!
I hope you enjoyed this series – click here to go back to the main page.
Thanks for reading!
Shawn

Shawn Dorward
Microsoft MVP, Business Solutions
LifeHacks365.com | LinkedIn | Twitter | GPUG | MSDYNGP
 
Original Post: https://lifehacks365.com/2018/05/16/smartlist-search-options-advanced-search-using-wildcards/