Introduction
So far as we’ve explored the Sage 300 ERP .Net API we’ve either read rather specific records via their key segments or we’ve browsed through all the records in a table. But more generally applications are interested in processing through subsets of the entire table in an efficient manner. For instance to get all invoices for a given customer or all G/L Accounts for a specific region. In this article we are going to look at some of the mechanisms that we provide to do this.
Generally you browse through a set of records by calling the View’s Browse method to set a filter on the records and then you call the various methods like Fetch, GoTop, GoNext, etc. to iterate through the records. There are also the FilterSelect and FilterFetch routines to iterate through a selection of records. First we’ll talk about the actual filters, then we’ll return to the methods in the API that use them.
Browse Filters
Filters are strings that are passed as parameters to various methods in the API to restrict Views according to various criteria. This would be used when a user enters selection criteria to locate data.
The format of the string expression is:
expression ::= [(...] condition [)...] [Boolean-operator [(...] condition [)...]...]
where:
- condition ::= field-name relational-operator operand
- Boolean-operator ::= AND | OR
- operand ::= <field-name | constant>
- relational-operator ::= > | < | = | <= | >= | != | LIKE
An example of an expression is:
LASTNAME = "SMITH" AND AGE < 30 OR AGE > 40
where LASTNAME and AGE are field names within the View.
Brackets are allowed. Expressions are evaluated strictly from left to right unless brackets are put in. Thus, in the previous example, the expression is evaluated as:
(LASTNAME = "SMITH" AND AGE < 30) OR AGE > 40
This is true for SMITHs under 30 years of age, and for any person over the age of 40. Put in brackets if you want the other order. For example:
LASTNAME = "SMITH" AND (AGE < 30 OR AGE > 40)
returns only SMITHs, under 30 years of age or over 40.
All the relational operators work with all the field types in the expected way, except for Boolean fields, where only the = and != operators apply.
Note that both operands can be fields. If they are both fields, they must be the same type. When the expression is parsed, if the second operand is a constant, it is converted to the type of the first operand (which is always a field).
The filter mechanism handles all the field types, but since filters are strings, you have to know how to represent the various values as strings. Boolean fields are always TRUE or FALSE. Date fields are always formatted as YYYYMMDD. Time fields are formatted as HHMMSSHH (the last HH is hundredths of a second).
The use of white space around operators, field names and constants is necessary. If a constant is a string and contains white space then it must be enclosed in quotes. The quote character (") can be embedded by using \" (no other replacements are performed).
The LIKE operator is like the = operator, except the second operand can contain the wild cards % and _, where:
- % matches any group of characters.
- _ matches any single character.
For example, LASTNAME LIKE “SM%” is true for any LASTNAME that begins with SM. LIKE can only be used with string field types.
Optimization
The internal mechanisms of our API will optimize the processing of filter strings to either convert them to SQL where clauses when processing the requests via a SQL database or will process them based on choosing the best key segment and traversing that key segment if it needs to do record based processing. Note that you should still pay attention of indexes when using SQL, SQL databases aren’t magical and do require good indexes when filtering on large tables, or the query could take quite a long time.
Using Filters
Sage 300 version 1.0A only ran on Btrieve as a database and the only methods that used filters were Browse and Fetch. You can still use these today and they are very useful methods. As the API evolved and we added more and more SQL support we added to these basic methods to help with performance and to make the API easier to use. The Fetch method behave very much like the Btrieve API behaves. The consequence of this is that it will sometimes do a get greater than equal to and sometimes do a get next. It will do the get GE (or get LE for going backwards) after a browse call or after one of the key segments has its value set. For UI programmers this was annoying since they are often changing the direction frequently and had to handle the case when Fetch would return the current record. To simplify this we added GoTop, GoNext, GoPrev and GoBottom. GoTop will get the first record, GoBottom the last and GoNext will always get the next record, never the current record, similarly for GoPrev. Generally I find these new methods far easier to use, and tend not to use Fetch anymore. Note that these Go methods are part of the COM and .Net APIs and are not part of the regular View API and so may not be available to other API sets, internally the .Net API translates these to the correct Fetch calls to do the job.
To aid in SQL performance we added FilterSelect which will actually issue a SQL query to retrieve a results set with the indicated records. Then FilterFetch browses through these records. For Pervasive.SQL these methods work under the covers just like Browse/Fetch. But note that you can’t update records that you retrieve this way. If you want to update such a record you need to call Read first. This is due to multi-user issues as well as the semantics of how SQL result sets work.
There are FilterCount which will return a count of the records that match the given filter and FilterDelete that delete’s the records that match the filter. Beware that these two functions will translate into SQL statements for SQL databases and will execute as fast as the database server can process them (which might not be fast if there aren’t indexes to exploit), and that Pervasive will iterate through the records to perform the operation. Typically you call FilterDelete only on header or flat type Views, and leave the header to call FilterDelete on the details for you.
Sample Program
In the sample program we add a search box where you can type a string and then the program fills the list box with any records where either customer number or customer name contains the string you typed. The sample does the search first using Browse/GoTop/GoNext and then does the same loop using FilterSelect/FilterFetch. Note that this search isn’t scalable, since the database will need to search the entire customer table looking for these substrings. But often SQL Server can do these sort of searches reasonably quickly as long as the data isn’t too big. Note that adding an index won’t help because we are looking for substrings. The more scalable way to do this would be via full text indexing or via a google like search tool.
Boolean gotOne;
string searchFilter =
"IDCUST LIKE %" + SearchBox.Text + "% OR NAMECUST LIKE %"
+ SearchBox.Text + "%";
// Fill up the list box using Browse/GoTop/GoNext
ResultsList.Items.Clear();
arCus.Browse( searchFilter, true );
gotOne = arCus.GoTop();
while (gotOne)
{
ResultsList.Items.Add(
arCus.Fields.FieldByName("IDCUST").Value + " " +
arCus.Fields.FieldByName("NAMECUST").Value );
gotOne = arCus.GoNext();
}
// Go through the records using FilterSelect/FilterBrowse but
// just print them to the console.
arCus.FilterSelect(searchFilter, true, 0, ViewFilterOrigin.FromStart);
while (arCus.FilterFetch(false))
{
Console.WriteLine(arCus.Fields.FieldByName("IDCUST").Value +
" " + arCus.Fields.FieldByName("NAMECUST").Value);
}
Summary
This was a quick introduction to Browse Filters in the Sage 300 ERP API. These form the basis for selecting sets of records and we will be using these extensively in future articles.