Lantica Software, LLC - Support - Sesame Library

I'd like to introduce you to some of the new programming features in Sesame. "Oh", I hear you ask, "I thought that Sesame programming was going to be the same as Q&A's." Absolutely correct. It will be almost completely backward compatible with Q&A. The changes are mainly in the form of programming additions.

How important are these additions? Immensely. There are certain facets of Sesame that make it a much more powerful, far more flexible database manager than Q&A.

None of these is more important in this respect than Sesame's programming language called SBasic (Sesame Basic). SBasic was designed from the ground up as a complete self-contained programming language. It incorporates all the familiar Q&A functions and commands and adds quite a few new ones. These features address all the shortcomings of Q&A's highly useful but too limited programming language. Developers will find that they have all the tools they need to create more powerful database applications -- and easier than in Q&A. When you think about it, a large proportion of the articles in The Quick Answer over the years have been about the ingenious ways that developers such as Tom Marcellus and Bill Halpern have found to work around the limitations of Q&A. With Sesame, I'm sure the same process of extending what can be done with the product will occur. The difference is that Sesame is far richer and more flexible than Q&A to begin with, so the bar is raised quite considerably.

Let's take a look at these new features individually. I'm not going to attempt here to cover them in detail with their full syntax and detailed examples. This will be done in future articles.

For many, this easily accessible new feature will be the first they'll use. It has long topped the list of features requested by Q&A power users. Within programming you can define a variable, which is like a temporary "field" of a particular type that exists strictly in memory and automatically goes away when you are finished using it.

Many Q&A developers have used variables countless times without realizing it! How many times have you added fields -- "programming use only" fields -- to the last page of your database form? Each of these is likely to be used to store a value that's calculated or derived by programming and referred to elsewhere in programming.

This is exactly how variables are used in Sesame. But instead of using a field, you simply assign a name to the variable and give it an initial value if you want. You can then refer to it by name in subsequent programming.

For these kinds of temporary storage needs you will no longer have to add a field to the database to store temporary or calculation-only data. You will only need fields to store data that you want to keep in that record.

This is quite powerful since almost anything you can do with a field you can also do with a variable. For example, where in Q&A you might read a text file into a field to be parsed, then throw it away, like this:

TempField = @Insert("companies.txt");
RealField = @Left(TempField,25);
TempField = ""

In Sesame you can do the same thing without the overhead of an additional, always otherwise empty, database field:

var InsertCompany as String
InsertCompany = @Insert("companies.txt")
RealField = @Left(InsertCompany,25)

In this same vein -- and this is not strictly a programming change -- Sesame will also offer command buttons you can customize to activate programming or a macro. In for DOS, some people have done this by creating a "button" or "activation" field just for the purpose of having something to click on. A good example is the field you might use to activate the "WinClip" utility.

Variables are extremely useful, but by their nature they are volatile. They can be considered "short-term" storage containers. But some variables need to have a longer life. You might want to store something for reference from any part of your application on an ongoing basis. An example might be a tax rate that rarely changes.

Throughout Europe we have a tax called VAT (Value Added Tax) applied to all transactions-business as well as consumer-and it rarely changes. This is an example of a variable that you want to have global "scope." In other words, you want to put it in memory where it is available to any number of applications.

Another example is where you are sharing the same application among two or more company names you own. For a particular data entry session, you might want to be doing something for "Company A," while in another session you might want to be doing work for "Company B." At some point when starting your session, you could "declare" that you are working on "Company B" and have Sesame read "Company B's" variables into memory in a way that would make them available for use throughout the application. Or, you might need to create multiple @Number sequences, instead of only one per database.

For all these you would use Global Static variables. Global Statics retain their values even if the database is closed and reopened. They sound complicated, but in reality they are very simple and can be tremendously useful. (See the sidebar at the end of this article for a few examples.)

Arrays are completely new in Sesame, though the concept is not new to Q&A. Imagine a Q&A Lookup Table you use with a database. You have a Key column and four available data columns you can use to retrieve information based on a field value that matches an entry in the Key column. One example of where a Lookup Table might be used would be to enter each of the 50 state abbreviations in the Key column, the corresponding full state names in Column 1, then use a Lookup programming command to lookup a given state's full name based on entering just the abbreviation. A second column of the Lookup Table could be used to retrieve the sales tax rate for that state, a third for the shipping cost to that state, and so on.

In Q&A, you have to create the table in advance of using the database. In Sesame, you can create it (as an array in memory) on-the-fly with programming, or by reading it into memory from a delimited text file or other source. The advantages in Sesame are that there is no limit to the number of columns, no limit to the number of rows, you can update and otherwise manipulate the array values with programming commands, and it is very fast.

Don't for a minute think that I am saying that you have to use arrays instead of lookup tables. No. All of these new features are in addition to the established and supported Q&A techniques. You pick up on these new Sesame features as and when you want to -- just like you used more and more advanced techniques as you grew in confidence.

Whenever you want to do something a certain number of times or until some condition is met (or no longer met), you create a loop. In Q&A this, again, is done by adding a field, setting a value, and using a subroutine to repeat an action, change the value in the counter field, and check this field to determine when to stop the action. One example would be when referring to (looping though) line-items in a database record that contains invoice-like lines, each of which contain a product code, description, price, quantity, and extended amount. Another example is incrementing a date to the next business day -- adding a day to the delivery date until the delivery date is not a Saturday or Sunday.

Q&A supports subroutines via the Gosub/Return commands, but they are inflexible because you need to use a field and the programming in this field needs to be on-field-entry. Then, you need to ensure that users can't enter this field by accident, generating the "Too many returns" error message. In Sesame, subroutines are much more straightforward: you simply write your subroutine program in-place, without the imposed complications of involving trigger fields and field navigation.

In all these examples, in Q&A, there will never be anything you want to actually save in these programming only fields, but you need them nonetheless. This is clearly a waste of storage space. For a database of 1,000 records you have 1,000 storage units that will never store anything. In Sesame you use variables in memory, actual command buttons, loops and subroutines -- a far more efficient solution.

When you want to see a calculated field, such as an amount including tax, or the extended price of a sales item (price multiplied by quantity), you don't actually need to store that value in a predefined database field in Sesame as you have to in Q&A. It is more efficient to simply calculate it as required, such as when a particular record is viewed in a form or printed in a report.

In Sesame you can create unbound fields on forms or reports to show any such derived/calculated data. By unbound, I mean that the data is not stored in or part of the record itself. It's a little like a report derived column where you have Q&A calculate a value and include it in the report, but that value is not saved in the database.

GUI stands for "Graphical User Interface," a term that describes, for example, all Windows programs. GUI functions in Sesame allow programmers to alter the appearance of the form being viewed. One obvious use of this is to change the color of a field, to show negative balances in red text or with a distinctive field background color. This can be done in Q&A 5.0 by using the @Color programming command. But in Sesame you can do far more. You can make a field visible or invisible via programming. Likewise a picture field. You can even control the size and position of any fields and change their labels.

Right now you might struggle to find uses for some of these features, but take it from me -- in a not too distant future you will see some imaginative and essential techniques described in the successor publication to The Quick Answer!

In Q&A it is common to add programming to validate a record. This might consist of checking that certain fields have been filled, and maybe checking the values in one field against those in others in this record or even in another database using XLookup. This programming is typically on-record-exit programming. All very well, except for one thing: the programming is literally on record exit -- you've already left that record by the time the validation has been performed. So, in a way, it's too late!

In Sesame you can set a flag to permit the record to be advanceable, or not. This way, your form programming validations can control whether you can save the data in the record or block saving it. In other words, you can prevent a user from saving incomplete or incorrect data.

WriteLn (write line) is essentially a diagnostic tool. You can program Sesame to "write" whatever you want (text, field values, variables, or a combination of these) and display this in a pop-up window. This can be useful as a window into what's going on at a particular stage in your programming. The WriteLn buffer is cleared on closing the window, but if you leave the window open then the contents of it are cumulative -- that is, subsequent WriteLn writes are shown in the window, too. This makes it more versatile.

Though designed to serve as a diagnostic tool during development of your Sesame applications, WriteLn can also be used as an ongoing window to show results or otherwise convey meaningful context-sensitive messages to the user.

As you are probably aware by now, in Sesame you can place subforms on your form. The subform shows child records, such as line-items for an invoice or sales order, individual donations from a donor, parts that make up an assembly, or tracks on a music album -- the examples are limitless.

Now, wouldn't it be nice if you could add up the value of the line-items, total the donations, or count the subrecords? In Sesame, you can. It has programming functions that give you access to the subform data records, and all the tasks you're likely to want to do such as the above examples can be done.

As I said earlier, virtually all Q&A programming is compatible with Sesame. So, not only will it convert to Sesame, but you can carry on using Q&A programming techniques and syntax, virtually unchanged in Sesame. Many of the Q&A techniques can be replaced by superior Sesame methods, but as with so many things in the new program, you don't have to use them!

There will be some changes that need to be made, and these will be covered in more detail in forthcoming articles in The Quick Answer. For example, field names that equate to, or include, a reserved Sesame word are not permissible in Sesame programming. The foremost example is a field name "Date." This is the name for a data type in Sesame and so is not allowed -- you'll have to change it to "Sales_Date" or something similar.

You will need to use field names in programming instead of LFN's (Logical Field Numbers) commonly used in Q&A. Once you get used to this, you'll find that it is actually far easier to use field names. Not only will you be able to read your own programming (you can't -- and neither can anyone else -- read "#330 = #420 + #180"), but it often overcomes the common problem of sequencing -- that is, wrong values being used because the field referred to hasn't yet been calculated.

The Sesame translation process will convert field numbers in your Q&A programming to field names. Also there are a few syntax changes -- but very few. The most notable is that the construction whereby you perform multiple XLookups in one XLookup statement will need to be replaced by separate statements.

XLU("ADDRESS", ADNO, "ADNO", "Account Code", ACCOUNT, "VAT", VAT, "Address1", ADDRESS1,...)

XLU("ADDRESS", ADNO, "ADNO", "Account Code", ACCOUNT)
XLU("ADDRESS", ADNO, "ADNO", "VAT", VAT)
XLU("ADDRESS", ADNO, "ADNO", "Address1", ADDRESS1)

Notice that in Sesame that you don't need a semicolon to separate the programming statements.

Sesame offers not only superb compatibility with Q&A, but greatly augments 's programming language, turning it into a rich development environment. Furthermore, inconsistencies between Q&A's various multiple programming languages (Program Spec, Mass Update Spec, Navigation Spec) are removed, so that there is one universal programming language.

Alec Mulvey is a director of Lantica Software, LLC and also owns Keyword Software & Consultancy in Ascot, near London, England. Alec has been building applications and training clients for 12 years. Keyword Software is the UK distributor for the International English edition of . alec@keywordsoftware.com, http://www.keywordsoftware.com.

A Few Sesame Programming Tricks
with Variables & Loops

In this article you've read about some of the new Sesame programming features such as variables and loops. Here are a few brief programs (courtesy of the Sesame development team) that will give you some hints about how you might use them in a Sesame application.

Creating a variable with the full name from data in the FirstName and FamilyName fields, then finding the length (number of characters) of that full name:

var FullName as String
FullName = FirstName + " " + FamilyName
FullNameLength = @Len(FullName)

Calculating a bonus amount for the BonusAmount field:

var Bonus as Int
If Salary < 80000 Then Bonus = Salary * 0.02 Else Bonus = Salary * 0.035
BonusAmount = @ToMoney(Bonus)

ShipDate = OrderDate + 7
While((@DOW$(ShipDate) = "Saturday") Or (@DOW$(ShipDate) = "Sunday"))
{ ShipDate = ShipDate + 1 }

Assigning a Global Static variable then changing it:

GlobalValue("gsCompanyName", "ABC Corporation")
@MsgBox("You are working with", @GlobalValue("gsCompanyName"),"") GlobalValue("gsCompanyName", "XYZ Corporation")
@MsgBox("Now you are working with", @GlobalValue("gsCompanyName"),"")

Notice in the above example that the Global Static CompanyName is prefixed with a "gs". This is entirely optional. When creating variables and Global Statics, it's a good practice to prefix them with a "code" that tells you that they're variables, such as vBonus for a bonus variable and gsCompanyName for CompanyName as a Global Static. This way, if you also had a regular local variable named vCompanyName along with a field named CompanyName, there would be no confusion as to which was which.

Sesame requires you to "declare" your variables in advance of using them and say what type of data they are to contain. This helps you (and Sesame) keep track of them as distinct from other named elements such as fields.