Monday, June 13, 2011

Select Blogs Where Smartlist Begins With [%]

I ran in to one of those DOH! moments this past week.  Simple as this...I needed to search for all payables transactions where the document number begins with a percent sign.  Why?  Well, this was how the client was identifying certain invoices that needed additional processing.

So, I dutifully built a Payables Transaction SmartList (Microsoft Dynamics GP menu--SmartList--Expand Purchasing--Expand Payables Transactions--Click on Asterisk--Click on Search button) using the following criteria:

So, I am sure you SQL gurus out there already know my dilemma.  The smartlist returned ALL records that met the other three criteria, seemingly ignoring the first criteria for document number.  Why?  Well, because SQL uses the % as a wildcard in a LIKE statement.  So in this example, it was essentially looking for everything, since the only parameter was the wildcard.  Ugh.  But easily fixable once I thought about.  If, in SQL, I wanted to use LIKE to find something with an actual percent sign it, I would use brackets around the % sign like this [%].  And, well, it works in SmartList too!

And, again, I am sure the SQL gurus already know this....but this actually returned the correct results.  Woohoo.  Party On.  Excellent.

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a supervising consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.

No comments: