|
News From The Suntower! 'The Electronic Newsletter For
Users
Volume V #8 |
IN THIS ISSUE: |
|
| You are receiving this e-mail because you asked for it, either
because you have requested information about our products and services and given us your
e-mail address (Thanks!) or because you are a current customer of ours (Double Thanks!)
TO UNSUBSCRIBE, MAKE SUGGESTIONS or
to CHANGE ADDRESS: Send a message to: webmaster@suntowersystems.com |
||
SAFE SQL
Feature: Advanced Query Functions
Ed. Note: This is the
seventh in a series of articles on the major new features in SAFE SQL. The
following describes the new abilities of SAFE's Query facility.
Queries have always been one of SAFE's most powerful (and mysterious) abilities. This has struck as a bit odd in that Microsoft Office applications have had a very similar Query function for years for sorting/filtering data from spreadsheets and databases. In any case we have certainly worked at convincing customers to dive in and explore what can be done.
However, there was one ability sorely lacking from previous versions of SAFE's Query engine. And it was lacking, frankly because many of you already felt overwhelmed by Queries. This ability is 'variables'.
A great American once said that 'fear is for losers' (probably 'The Rock' but never mind that) so the following is a quick tutorial on how you can use variables in Queries.
So What Is A Variable?
A variable is simply a thing which can change. As opposed to a constant
which is always the same (duhhhh!) For example: Today's Date is a variable in
that it changes every day. (Remember: come tomorrow 'Today's Date' will be
different from 'Today's Date' today! However, if I refer to a date as
April 18th, 2003, that's a constant. It has a distinct value which can only mean
one thing.
So Why Should I Care?
Because there are many, many Queries which can be made far more useful if
you can make comparisons using variables. Currently in SAFE, you can only use
constants.
For example: in SAFE 5.1 I could create a Sales Order Summary Report showing 'All Invoices For Invoice Date = 04/18/03'. '04/18/03' is a constant.
What I could not do was say something like: 'All Invoices For Invoice Date = Payment Date'. And the reason I couldn't is because 'Payment Date' is a variable. Get it? Another way of saying this is that previously, you had to have a constant single value on the right side of every Query Expression.
So Now What?
Well now you can use variables on the right side of a Filter
Expression, which means you can do neat things like the following on a Sales
Order Summary Report:
|
Invoice Date >= TODAY() - 7 |
Show me all invoices printed within the past week |
|
Employee ID = Employee ID2 |
Show me all invoices where the Employee ID is the same as Employee ID 2 |
|
Credit > Invoice Total |
Show me all invoices where the customer overpaid |
|
Payment Date > Due Date |
Show me all invoices where the payment was received after the due date |
| Terms ID <> ARC.TermsID | Show me all invoices where the Terms ID was not the customer's standard terms. |
|
Employee ID <> ARC.EmployeeID
|
Show me all invoices where the Employee ID was not the customer's standard Employee ID. |
Are you feeling it yet? This is an incredibly powerful enhancement. Note that you can make comparisons to variables in the file you are reporting on (Sales Orders), related records (Customers and Terms) and use various internal functions such as 'TODAY()' as you can in a spreadsheet like Excel.
Caveat Queror?
There are some things to watch out for. The most immediate being
your spelling. Unlike the Field Selector on the left of the Filter Options,
there is no drop down list to help you choose what to type into the Value
section. So it is up to you to get it right! The worst that can happen, though,
is that you will get the dreaded 'Error 801' when you attempt to actually use
the Query in a report. This will tell you that you have made a spelling mistake.
The other big gotcha is that it is up to you to learn more about the capabilities of each report. In other words, every report has the ability to work with certain tables and not others. Most of you have sent us questions wondering if you could get a particular Filter to work in a certain report. Knowing which tables are used in each report and how to refer to each field by it's proper name from our Dictionary is the key.
In Conclusion
Virtually every report and browse Query in SAFE just became many times more
useful. Understanding that you can use the name of any field in any table used
by a report, as well as various functions opens up a whole new world of
possibilities for SAFE reporting. We will make it a point of publishing
particularly useful new expressions as the weeks/months go by. We trust you will
find lots of new ways to use Queries in SAFE.
Happy Easter To
Everyone!
Suntower Systems will
be closing for Good Friday at 3:00PM PST and will be closed for the rest of the
weekend. To all of you, regardless of faith we wish you a very happy weekend.
Happy Easter! Pesach Tov!
Ciaran's
Corner: Spyware Everywhere!
We did a fairly interesting test here last weekend. We installed a 'spyware
detector' on each PC at our four offices and let it run. And what it found was
pretty darned shocking. Out of seventy four computers (not including servers),
every single one was running some sort of hidden program designed to transmit
some sort of information somewhere unbeknownst to the PC's user! I was
expecting quite a few, but not all. Now I admit that we probably have a
bit more internet access than most of you but still.
For those of you unaware, there is an alarming emerging whereby marketers secretly transmit small programs to your PC when you go to their web site. The program then runs in the background, collecting various bits of information about you and then transmitting it back to the company to aid them in their sales efforts. Sound like a virus? You bet! But you're smart: you have Anti-Virus software on all your PCs, so why are these things able to get through? A couple of reasons, but most commonly (big shock coming) because Microsoft allows it. Turns out there is a programming interface built into Windows itself which allows a web site to transmit such programs back to your system and be considered 'OK' by the operating system. When Windows gives it's Imprimatur to such a program, the AV software considers the program to be a part of the OS and let's it on it's merry way! Needless to say, MS has taken some heat from this, but for the same reasons that Spam has not been dealt with by the law, MS is loathe to take action. (By the way, that reason is M-O-N-E-Y, it would appear that the 1st Amendment Rights of marketers almost always trumps your 4th Amendment right to privacy.)
SpyBot To
The Rescue!
In the meantime, PC
Magazine just finished reviewing a number of anti-spyware utilities and by a
stroke of good fortune, it turns out that the best program of this type
is also free! free! free! To get your copy of SpyBot Search And Destroy click
here.
I can't recommend strongly enough that you get this utility and run it on all your PCs periodically. The fact that anyone is doing anything to your gear without your expressed consent and foreknowledge is bad enough, but when you combine that with the fact that you don't know what information they are taking from you and that you have no guarantee that these programs are well-written and indeed truly harmless! This makes it imperative, to us, that every effort be made to eliminate Spyware.
Til Next Time,
Ciarān Marron
Technical Support Manager
cm@suntowersystems.com
End of E-News From The Suntower, Volume V #8
