|
This how-to explains the basics about using the SeekBy methods of the various SDK objects correctly and efficiently. |
|
Remark |
 |
The code samples here are not fully functional programs, they are short sections of code describing the problem. |
 |
Sample code is Pseudocode only, transcribing the code to your programming language is straightforward. |
|
|
You will find one of more SeekBy methods in each SDK object that represents a database file or table.
For the sorting keys defined in the table, you will find a SeekBy method matching that sorting key.
|
If it succeeds, a SeekBy method determines a position in the file and a sorting order (GetNext and
GetPrev will be relative from this) and the record contents is read into memory.
|
The code samples in this how-to will all use the Sales object; the samples will use
the following data set:
|
|
SysNum |
Year |
Book |
DocNum |
DocDate |
CstNum |
ClassCode |
IsTicked |
Remark |
1 |
2022 |
WEB |
001.679 |
03/08/2022 |
14367 |
WEBSHOP |
false |
|
2 |
2022 |
SHOP |
000.439 |
24/11/2022 |
437 |
SLS.CHRIS |
false |
|
3 |
2023 |
WEB |
000.001 |
01/01/2023 |
7384 |
WEBSHOP |
true |
Paid with Gift card #000497349 |
4 |
2023 |
WEB |
000.002 |
01/01/2023 |
37 |
AMAZON |
true |
Paid with VISA, reference: bvaa-3971-qizn |
5 |
2023 |
WEB |
000.003 |
02/01/2023 |
493 |
EBAY |
false |
|
6 |
2023 |
WEB |
000.004 |
03/01/2023 |
557 |
WEBSHOP |
true |
Paid with Gift card #000496966 |
7 |
2023 |
SHOP |
000.001 |
03/01/2023 |
3379 |
SLS.CHRIS |
true |
Replacement for broken screen |
8 |
2023 |
SHOP |
000.002 |
03/01/2023 |
297 |
SLS.LAUREN |
false |
|
9 |
2023 |
SHOP |
000.003 |
05/01/2023 |
437 |
SUP.CARLOS |
true |
Reparation item from invoice 2022 WEB 000.439 |
|
|
Remark |
 |
Note that the above table shows the records in the order as they would be obtained via the
Sales::SeekBySysNum method. Depending on which of the SeekBy methods
you use, the ordering will change.
|
|
|
How to use the SDK is very similar to how things work in Venice. The Venice SDK object
represents the Main Menu. The Dossier and Year objects represent selecting a dossier or fiscal year in
the Main Menu. The Custm object represents the Customer module, etc.
The same is true for the SeekBy methods, they have a strong analogy to the Venice Selections in the work
sheet modules. You will apply the same basic concept in your code when you want to find one or a set of
records in a table matching certain criteria.
|
|
|
|
 |
1 |
Determining the sorting order and what properties you can set a limit on. You do this by choosing which
of the available SeekBy methods best matches your needs.
|
 |
2 |
(Optional) Setting the From-limit. You do this by passing the right values to the parameters of the SeekBy method. |
 |
3 |
(Optional) Setting the To-limit. You do this by testing the To-limit as part of your loop termination condition. |
 |
4 |
(Optional) Additional filtering on properties that are not available as parameters of the SeekBy method.
You do this by applying the filter condition on each record inside the loop.
|
|
|
Doing this in code using the SDK has a similar set of steps:
|
bool more = ObjSales.SeekByClassCode(smGreaterOrEqual, "WEBSHOP"); // 1 (sorting order) and 2 (From-limit)
while (more &&
ObjSales.pClassCode=="WEBSHOP") // 3 (To-limit)
{
if (ObjSales.pRemark.Contains("Gift")) // 4 (Filter)
{
DoSomething(ObjSales);
}
more = ObjSales.GetNext();
}
|
|
The above code will call the DoSomething() method only for the record with SysNum 3 and 6,
since that is the only record that satisfies all the conditions. You could write this code differently
with seemingly the same result.
|
|
// Warning not ideal. When possible, avoid writing the code like this.
bool more = ObjSales.SeekByClassCode(smFirst, "");
while (more)
{
if (ObjSales.pClassCode == "WEBSHOP" &&
ObjSales.pRemark.Contains("Gift"))
{
DoSomething(ObjSales);
}
more = ObjSales.GetNext();
}
|
|
This too will only call the DoSomething() method for the record with SysNum 3 and 6.
Even though it is a bit harder to program and understand, you will usually want to use the first method despite
the bit of extra complexity because it makes a significant difference in performance.
The first method seeks the first record where the ClassCode is "WEBSHOP", then iterates only over the other
records where the ClassCode is "WEBSHOP". On those records it applies the filter if Remark contains "Gift". For
this specific data set, this means we only fetch the 5 records with ClassCode equal to "WEBSHOP", plus 1 extra
for the termination.
The second method will iterate over every record (9, plus 1 extra for termination), and apply the filter.
In this specific instance, 9 vs 5 records this is not going to make a noticeable difference, but in a real situation
with a larger table, the difference could be only needing to read a few hundred records vs reading thousands.
|
|
The first parameter in every SeekBy is always the SeekMode.
|
|
smEqual is the SeekMode that you will use if you want to search for
one specific record. For example, you have a Sales document, and you want to fetch the matching customer
record. For smEqual to find a record, all the parameters must match exactly with
the record you want to find.
If there is no record exactly matching the given parameters, the SeekBy will return false and no
positioning will have occurred.
|
|
Using smFirst will position the table on the first record for the given key. The other parameters
supplied in this method are irrelevant, but required valid for the type. You use smFirst if you want to
iterate over the entire table.
If there are no records in the key (if the key is a null-key, this does not
mean there are no records in the file), the SeekBy will return false and no positioning will have occurred.
|
|
smGreater (smGreaterOrEqual) will find the first record that is greater (or equal) to the given
parameters.
If there are no records with a record greater than (or equal to) the given parameters, the SeekBy
will return false and no positioning will have occurred.
|
|
Remark |
 |
With the given data set, ObjSales.SeekByClassCode(smGreaterOrEqual, "TEST") will
position on the first record with ClassCode equal to "WEBSHOP", since this is greater or equal to
"TEST" and no values less than "WEBSHOP" are present.
|
|
|
The remaining SeekMode values are the reverse of the ones we already
handled, they will seek from the end of the table towards the start.
smLast is the reverse of smFirst, smLess is the revers of smGreater and smLessOrEqual is the reverse of
smGreaterOrEqual.
|
|
You will first need to clearly define what you want to achieve.
|
|
Fetch the unique record referenced to (Many-to-One or One-to-One relation). |
You have an object, and you want to fetch the unique record that object refers to.
Example: You want to fetch the customer matching a sales document. This is a Many-to-One relation, because many
sales documents can simultaneously reference to one single customer.
Which SeekBy method to use for such a scenario is found in the help topic
Links between the Venice files.htm.
Locate the entry for Sales documents" in the column Reference file and
Customer in the Related with column. You will see that the SeekBy method
column tells you to use the SeekByCstNum method.
|
|
Building on the previous code example, suppose that instead of wanting to filter on the sales documents
where the remark contains "Gift", we want to obtain the sales documents where the customer lives in Brussels
(postal code 1000). Postal code is not a property of the sales document, so for each sales document, we will
need to fetch the matching customer object and test its postal code property.
|
|
bool more = ObjSales.SeekByClassCode(smGreaterOrEqual, "WEBSHOP");
while (more &&
ObjSales.pClassCode=="WEBSHOP")
{
if (ObjCustm.SeekByCstNum(smEqual, ObjSales.pCstNum, ObjSales.pCstSubNum) &&
ObjCustm.pPostalCode=="1000")
{
DoSomething(ObjSales);
}
more = ObjSales.GetNext();
}
|
|
Fetch the list of records that reference to an object (One-to-Many relation). |
The reverse of the above scenario is also possible. You have an object and want to find all the other objects
referring to this object. Expanding the previous example, this would be the scenario where you have
a customer, and you want to fetch all the sales documents for that customer.
Which SeekBy method to use for such a scenario can again be found in the help topic
Links between the Venice files.htm.
Locate the entry for Sales documents" in the column Reference file and
Customer in the "Related with" column. The possible SeekBy methods is found in the
Reverse SeekBy column, which in this case gives us 3 possibilities: SeekByCstNum,
SeekByLawState and SeekByUnPaid.
The Parameters column also tells us that we should try to match by the properties CstNum and CstSubNum, however none of
the 3 SeekBy methods has CstSubNum as parameter. This is normal because in accounting, additional customer cards are
rarely used, and even if they are, it does not change the general account being used, that is only determined
by the customer number. If you really want to restrict the list of sales documents to the customer sub number, that extra
condition will need to be done using a filter condition.
That still leaves the dilemma of what SeekBy to use. Any of the 3 options will work, it just changes the returned order
of the sales documents, which may or may not matter for your program. However, note that both
SeekByLawState and SeekByUnPaid are a
null-key, this also may or may matter depending on what records you
expect to find.
|
|
Remark |
 |
Some of the Reverse SeekBy columns are empty, this only means that there is no available
SeekBy method where you can apply a limit. You can always use any SeekBy you want and apply a filter.
|
 |
The Reverse SeekBy method, often has extra parameters that are not needed for matching the Reference file.
You will need to set these to LowValue.
|
|
|
// Assuming we have established a positioning on ObjCustm...
bool more = ObjSales.SeekByCstNum(smGreaterOrEqual, ObjCustm.pNumber, LowValue_Date);
while (more &&
ObjSales.pCstNum==ObjCustm.pNumber)
{
if (ObjSales.pCstSubNum==ObjCustm.SubNumber) // Optional, only if you want to match on CstSubNum as well
{
DoSomething(ObjSales);
}
more = ObjSales.GetNext();
}
|
|
Fetch a list (or one) of records that match a custom condition. |
Which SeekBy to use boils down to 2 criteria: performance, and the order of the records.
Where possible, your first choice should always be to pick the SeekBy that will allow you to use a limit.
if your condition is such that 2 or more SeekBy methods allow you to use a limit, pick the one that returns
the smallest amount of records, this will require a bit of "know your data" knowledge.
If no key allows you to use a limit, use the SeekBy that will return the records in an order that makes most
sense.
|
|
How do you know if a SeekBy allows to use a limit?
Note that a null-key automatically has a hard limit on its null value,
so that is a good first indicator. If you are only interested in sales documents that have
not yet been paid, then the SeekByUnPaid is a good option, even if you
do not have further limiting parameters.
Additionally, check if any of the available SeekBy methods have a first field parameter (after the
SeekMode) that matches one of the fields you want a condition on. If yes, those SeekBy methods are
candidates.
After that, you can then further improve if you can also use the second (and third, ...) field parameter in
your condition. The more consecutive parameters you can limit on, the better.
While it is possible to skip a parameter and still use a limit, this is an advanced feature. The code will
quite rapidly become complex, and the performance gains are usually minimal.
|
|
Example: We want to find all the unpaid sales documents from year 2023, sold via our own web site
(ClassCode = "WEBSHOP"), we also know that any website sales is done on Book "WEB", but so are other sales
via external websites.
We want to find the records where:
- Year = 2023
- Book = "WEB"
- ClassCode = "WEBSHOP"
- IsTicked = 0
|
 |
SeekByBookDate could be a possibility, if we first obtained the starting
and ending date of the year. Sometimes a bit of lateral thinking like this is handy, but we can probably do better.
After all, we would still find almost all the sales document in the table, since (hopefully) only a small number
of sales documents are being transferred to the next year.
We can achieve the limit on Year by using a limit on BookDate, and filter the other conditions.
bool more = ObjSales.SeekByBookDate(smGreaterOrEqual, ObjYear.vBegin);
while (more &&
ObjSales.pBookDate<=ObjYear.vEnd)
{
if (ObjSales.pBook=="WEB" &&
ObjSales.ClassCode=="WEBSHOP" &&
ObjSales.pIsTicked==true)
{
DoSomething(ObjSales);
}
more = ObjSales.GetNext();
}
|
 |
SeekByClassCode allows us to limit on ClassCode.
bool more = ObjSales.SeekByClassCode(smGreaterOrEqual, "WEBSHOP");
while (more &&
ObjSales.pClassCode=="WEBSHOP")
{
if (ObjSales.pYear==2023 &&
ObjSales.pBook=="WEB" &&
ObjSales.pIsTicked==true)
{
DoSomething(ObjSales);
}
more = ObjSales.GetNext();
}
|
 |
SeekByDocNum allows us to limit on Year and Book.
bool more = ObjSales.SeekByDocNum(smGreaterOrEqual, 2023, "WEB", LowValue_ULong);
while (more &&
ObjSales.pYear==2023 &&
ObjSales.pBook=="WEB")
{
if (ObjSales.ClassCode=="WEBSHOP" &&
ObjSales.pIsTicked==true)
{
DoSomething(ObjSales);
}
more = ObjSales.GetNext();
}
|
 |
SeekByUnPaid is a null key and has a built-in limit on IsTicked is false.
bool more = ObjSales.SeekByUnPaid(smFirst, LowValue_Date);
while (more)
{
if (ObjSales.pYear==2023 &&
ObjSales.pBook=="WEB" &&
ObjSales.ClassCode=="WEBSHOP")
{
DoSomething(ObjSales);
}
more = ObjSales.GetNext();
}
|
|
Which of the above is best? When there are multiple possibilities, it is not always possible to give a clear-cut answer
as to which one is best. It will need a bit of "know your data".
In this scenario, SeekByDocNum will always do better than SeekByDocDate since it limits on Book as well as on Year.
This could be drastically different however, if you were only interested in documents of 1 month of the year.
For this scenario we also know that all sales documents with ClassCode "WEBSHOP" will be in book "WEB", but the other way
around sales documents for Book "WEB" have multiple different values for ClassCode, the number of transferred documents is
assumed low, so SeekByClassCode will most of the time outperform SeekByDocNum as it will result in fewer documents
found.
We can safely assume that most documents each year are paid or that company
has a serious issue. So SeekByUnPaid will likely outperform all the others for this specific scenario.
|
|
You do not necessarily need to work with a full string value in a limit. It is easy to also set a limit on a partial
string on condition that this partial string value is the start of the string field.
Setting a limit on a 'Contains' condition is generally not possible and will need filtering. Limiting on a
substring at a fixed position in a string field is technically possible, but complex.
Example, we want to get the list of all sales documents where the ClassCode starts with "SLS.":
|
|
bool more = ObjSales.SeekByClassCode(smGreaterOrEqual, "SLS.");
while (more &&
ObjSales.pClassCode.StartsWith("SLS."))
{
//if (Additional filtering)
//{
DoSomething(ObjSales);
//}
more = ObjSales.GetNext();
}
|