How to use the SeekBy methods correctly

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	2023	WEB	001.679	03/08/2023	14367	WEBSHOP	false
2	2023	SHOP	000.439	24/11/2023	437	SLS.CHRIS	false
3	2024	WEB	000.001	01/01/2024	7384	WEBSHOP	true	Paid with Gift card #000497349
4	2024	WEB	000.002	01/01/2024	37	AMAZON	true	Paid with VISA, reference: bvaa-3971-qizn
5	2024	WEB	000.003	02/01/2024	493	EBAY	false
6	2024	WEB	000.004	03/01/2024	557	WEBSHOP	true	Paid with Gift card #000496966
7	2024	SHOP	000.001	03/01/2024	3379	SLS.CHRIS	true	Replacement for broken screen
8	2024	SHOP	000.002	03/01/2024	297	SLS.LAUREN	false
9	2024	SHOP	000.003	05/01/2024	437	SUP.CARLOS	true	Reparation item from invoice 2023 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 2024, 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 = 2024
- 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==2024 && 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, 2024, "WEB", LowValue_ULong); while (more && ObjSales.pYear==2024 && 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==2024 && 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();
}