[Lantica Software Support Center] Recently Changed Articles

Good Programming Practices

Wed, 17 Mar 2010 14:55:49 GMT

Good programming practices are not just arbitrary rules. They are guidelines designed to help you get the most out of SBasic and Sesame. Regardless of your level of programming experience, following good programming practices will help you to write code that is more reliable and easier to maintain. You can write SBasic that does not follow these guidelines, but your long term experience with programming Sesame will be more productive if you do follow them.

^Q&ASesame offers many more options for formatting and creating your code than Q&A. You have more tools and more ways to use them. You also don't have any space limits on how much programming you can have, as you had in Q&A, so you can afford to do it right.

Rule 1: Make It Pretty

Code written with a clean and consistent style is much more readable. This may not seem important at first, but, it makes all the difference when you are looking at something you wrote six months ago.

Put each statement on a separate line. It makes it much easier to follow your program.

Not recommended:
Name = "Bob";Age = 25;Gender = "Male"

Recommended:
Name = "Bob"
Age = 25
Gender = "Male"

Use White Space. Spaces, blank lines and tabs don't interfere with your programming unless they are inside double-quotes. White space spreads out your statements so that you can see what is going on. Put a space between an operator and an operand. Put a space between arguments. Use a blank line between sections of your program.

Not recommended:
Name="Bob"
Age=25
Gender="Male"
@MsgBox("This is a","message from","the programmer")

Recommended:
Name = "Bob"
Age = 25
Gender = "Male"
@MsgBox("This is a", "message from", "the programmer")

Indent your code. Indenting makes it clear which statements go together. Indenting is also very helpful for finding where chunks of code inside control blocks, such as If..Then..Else and Loops begin and end.

Not recommended:
While n < 100
{
vAmount = n * 5
vTotal = vAmount * vQty
}

Recommended:
While n < 100
{
vAmount = n * 5
vTotal = vAmount * vQty
}

Rule 2: Be Consistent

Whatever style choices you make, stick with them. If you are writing code for your own use, consistency will make it much easier to figure out what you did when you want to change or update something. If you are a consultant writing code for others, the next person who has to read your code will be very appreciative.

Pick a naming convention for your variables and stick with it. This includes how you capitalize. SBasic is not case-sensitive, but your eyes are. It is easier to separate variable names from layout element names in your code if you name them consistently. Make the variable names descriptive enough that you can remember what they are for.

Not recommended:
var a as Int
VAR B AS string
Var vCt as INT

Recommended:
var vApple As Int
var vBoy As String
var vCat As Int

Pick a bracing style and stick with it. There are several legal bracing styles, plus BEGIN/END. They are all acceptable, but choose one and always use it. The same goes for semicolons at the ends of lines. SBasic can manage with or without the semicolons, but you should decide which you will use.

Not recommended:
While vN < 100
{
   vNAmount = vN * 5;
   vNTotal = vNAmount * vNQty
}
While vX < 100 {
   vXAmount = vX * 5;
   vXTotal = vXAmount * vXQty;
}
While vY < 100
BEGIN
   vYAmount = vY * 5
   vYTotal = vYAmount * vYQty;
END

Recommended:
While vN < 100
{
   vNAmount = vN * 5
   vNTotal = vNAmount * vNQty
}
While vX < 100
{
   vXAmount = vX * 5
   vXTotal = vXAmount * vXQty
}
While vY < 100
{
   vYAmount = vY * 5
   vYTotal = vYAmount * vYQty
}

Decide how you are going to use white space and stick with it. If you are going to put spaces between operators and operands, always do so. If you decide that you don't do that, never do it.

Not recommended:
Name = "Bob"
Age= 25
Gender = "Male"
@MsgBox("This is a","message from", "the programmer")

Recommended:
Name = "Bob"
Age = 25
Gender = "Male"
@MsgBox("This is a", "message from", "the programmer")

Rule 3: Use Variables

Regardless of the many Application design reasons to use variables, there are also good programming practice reasons to use variables. While it may seem easier to simply work directly with Layout Element values, there is good reason not to do so. This is an code example from the programming manual:

var vBonus as Double

    If Salary < 80000 Then
    {
        vBonus = Salary * .02
    }
    Else
    {
        vBonus = Salary * .035
    }
    BonusAmount = @ToMoney(vBonus)

You may wonder why the value is not simply assigned to BonusAmount as it is calculated, rather than using the vBonus variable, like this:

If Salary < 80000
BonusAmount = .02 * Salary
Else
BonusAmount = .035 * Salary

While the reasons to use a variable may not be obvious in such a simple piece of code, you should use variables rather than working directly with your Layout Element values.

Speed - It is much faster to operate on a variable than a Layout Element value.

Data integrity - You are doing all the calculations in a variable and not affecting the actual Layout Element value until the last step. This way, if something goes awry, such as division by zero, you have not saved a half calculated value or corrupted your original value.

Code maintainability - No matter how many calculations you perform, BonusAmount is only assigned in one place. If something is wrong, you don't have figure out which of the assignments is the problem.

Predictability - Sesame has strong data typing. Your temporary value may not always meet type requirements. Assigning it to the Layout Element will force it to the correct type and may change the value you thought you had.

These considerations may seem minor when dealing with a simple case like the one above, however, with even a small amount of complexity, this becomes very important. The example below is only a little more complex than the previous examples, but the advantages of waiting until the calculations are complete to assign the value to BonusAmount should be more obvious.

var vBonus as Double

    vBonus = Salary * .02
    If Position = "Manager" Then vBonus = vBonus + (Salary * .025)
    If YearsWorked > 10 Then
    {
        vBonus = vBonus + (vBonus * .05)
    }
    Else
    {
        vBonus = vBonus + 10
    }
    BonusAmount = @ToMoney(vBonus)

There are several kinds of variable scope available in SBasic. In general, local variables are safer than global variables because everything that can affect the variable's value is there in the same procedure. See the Variables section for a detailed explanation of variable scope.

Rule 4: One Way In, One Way Out

Having more than one way in and one way out of any particular procedure or control block is asking for trouble. Not only does it reduce the reliability of your code, but it also makes it far more difficult to figure out which part of your code is actually running. This can make maintaining and debugging your code unnecessarily difficult.

GoTo is not the recommended way to stop code from running. If you GoTo a different Layout Element, none of the code after the GoTo will run. If you are using GoTo with code after it, you are better off setting a "quit" variable or using a control statement to control whether that code runs. A "quit" variable is especially useful if you have a case where more than one condition may cause the GoTo.

Not recommended:
If Amount > 1000
{
  GoTo LargeInvoice
}
// The following lines will never run if Amount > 1000
{
  vLineTotal = Amount * Qty
}
vInvoiceTotal = InvoiceTotal + vLineTotal
InvoiceTotal = vInvoiceTotal

Recommended:
vQuit = 0
If Amount > 1000
{
   vQuit = 1
}
// Note that in this example, you could have checked
// any number of conditions before this point to decide
// whether to quit.
If vQuit = 0
{
   vLineTotal = Amount * Qty
   vInvoiceTotal = InvoiceTotal + vLineTotal
   InvoiceTotal = vInvoiceTotal
}
Else
{
   GoTo LargeInvoice
}

Or:
If Amount > 1000
{
   GoTo LargeInvoice
}
Else
{
   vLineTotal = Amount * Qty
   vInvoiceTotal = InvoiceTotal + vLineTotal
   InvoiceTotal = vInvoiceTotal
}

Never alter a loop control variable from inside the loop. The only exception to this is when you simply increment it inside a While loop. Altering the loop control variable from inside the loop virtually guarantees unpredictable behavior and extremely difficult debugging. If you need to end a loop early because a condition has been met, use a While loop with a quit variable.

Not recommended:
var sStr as String
var vLoop as Int

While vLoop < 100
{
  vStr = LastName
  If vStr = "Morgan" vLoop = 100
  vLoop = vLoop + 1
}

Recommended:
var vQuit as Int
var sStr as String
var vLoop as Int

vQuit = 0
While (vLoop < 100) And (vQuit = 0)
{
   vStr = LastName
   If vStr = "Morgan" vQuit = 1
   vLoop = vLoop + 1
}

Rule 5: Use Procedures

Create user defined functions and subroutines for repetitive calculations. Not only does this save you a lot of typing, it also means that, if you want to change how something works, you only have to change it in one place.

Not recommended:

LastName - OnFieldExit

var vLast as String
var vMiddle as String
var vFirst as String
var vFullName as String
var vSep as String

  vLast = LastName
  vMiddle = MiddleName
  vFirst = FirstName

  // Prevents an extra space if no Middle Name
  If Len(vMiddleName) = 0
  {
    vSep = ""
  }
  Else
  {
    vSep = " "
  }

vFullName = vLastName + ", " + vMiddleName + vSep + vFirstName
FullName = vFullName

FirstName - OnFieldExit

var vLast as String
var vMiddle as String
var vFirst as String
var vFullName as String
var vSep as String

  vLast = LastName
  vMiddle = MiddleName
  vFirst = FirstName

  // Prevents an extra space if no Middle Name
  If Len(vMiddleName) = 0
  {
    vSep = ""
  }
  Else
  {
    vSep = ""
  }
  vFullName = vLastName + ", " + vMiddleName + vSep + vFirstName
  FullName = vFullName

MiddleName - OnFieldExit

var vLast as String
var vMiddle as String
var vFirst as String
var vFullName as String
var vSep as String

  vLast = LastName
  vMiddle = MiddleName
  vFirst = FirstName

  // Prevents an extra space if no Middle Name
  If Len(vMiddleName) = 0
  {
    vSep =
  }   Else
  {
    vSep =
  }
  vFullName = vLastName + ", " + vMiddleName + vSep + vFirstName
  FullName= vFullName

Recommended:

Global Code

Subroutine SetFullName()
var vMiddle as String
var vFirst as String
var vFullName as String
var vSep as String

  vLast = LastName
  vMiddle = MiddleName
  vFirst = FirstName
  // Prevents an extra space if no Middle Name
  If Len(vMiddleName) = 0
  {
    vSep = ""
  }
  Else
  {
    vSep = " "
  }
  vFullName = vLastName + ", " + vMiddleName + vSep + vFirstName
  FullName = vFullName
End subroutine

LastName - OnFieldExit
SetFullName()

FirstName - OnFieldExit
SetFullName()

MiddleName - OnFieldExit
SetFullName()

Rule 6: Be Specific
It is never a good idea to make a computer quess at what you mean. When performing operations of values of different types, use casting to make it clear to Sesame what you want. The casting functions are those like @Str and @ToNumber, theat specifically convert a value to a particular type. If you make the computer guess , 10 + "Mary" may produce a different result than "Mary" + 10.

Not recommended:
var vStr as String

vStr = "Mary" + 10

While these guidelines may seem unnecessary when you are writing a few simple lines of code, keep in mind that it rarely stops with a few lines. As you learn more about Sesame and get more comfortable with programming, you will want to improve your Application. Many of those improvements are likely to take the form of SBasic code, both in event programming and Mass Updates. As you write more code, and your code becomes more complex, you will be glad that you employed good programming practices right from the beginning.

Will Sesame run on my operating system?

Thu, 11 Mar 2010 21:15:16 GMT

Sesame is a 32 bit application that runs on the following operating systems:

Windows 95
Windows 98
Windows NT
Windows 2000
Windows ME
Windows XP
Windows Vista (32 bit)
Windows Vista (64 bit as a 32 bit application)
Windows 7 (32 bit)
Windows 7 (64 bit as a 32 bit application)

As well as:

Linux (for Intel)

Merging Your Data Into Word Perfect (Versions 11 & 12)

Mon, 21 Dec 2009 21:47:35 GMT

1. Export data from Sesame with Field Name Headers

2. Open Corel Word Perfect

3. In the "Insert" menu select "Spreadsheet/Database" and then click on "Import"

4. From the "Data Type" menu choose "ASCII Delimited Text"

5. From the "Import as" menu choose "Merge Data File"

6. In the "Filename" type in or browse for your export file

7. Set the Field Delimiter to ","

8. Set the Record Delimiter to "[CR][LF]"

9. Check the "First record contains field names" checkbox and then click on "OK"

10. From the "File menu select "New"

NOTE: Saving MergeInfo.dat will save all your merge settings so you do not have to repeat the above steps.

11. From the "File" menu select "New"

12. From the "Tools" menu select "Merge"

13. Click on the "Form document" button and select "Create Form Document..."

14. Select the "Associate a data file:" radio button and type or browse for your "MergeInfo.dat" file that was previously saved and then click "OK"

15. Without closing the "Merge" window click on the "Insert Field..." button on the top toolbar and place the fields in the appropiate spots by clicking "Insert", when completed click on "Close"

16. In the "Merge" window click on the "Merge" button

Merging Your Data Into Labels (Microsoft Word 2000)

Mon, 21 Dec 2009 21:47:27 GMT

1. Export data from Sesame with Field Name Headers

2. Open Microsoft Word

3. In the "Tools" menu select "Mail Merge..."

4. Click on the "Create" button and from the provided menu choose "Mailing Labels..."

5. In the window that pops up select the "Active Window" button

6. Click on the "Get Data" button and from the provided menu choose "Open Data Source..."

7. In the Files of Type: drop down at the bottom choose "All Files (*.*)" and browse to where your ASCII file is and select it

8. Set the Field Delimiter to ","

9. Set the Record Delimiter to "(enter)" and click "OK"

10. Click "Setup Main Document" and choose what label you have and click "OK"

11. Insert your merge fields in the "Sample Label:" that is shown and then click on "OK"

NOTE: Saving your document will cause Word to remember all your merge settings so you can begin from this step the next time you open your merge document.

12. Click on "Merge..." and then click on "Merge"

Merging Your Data Into Letters (Microsoft Word 2000)

Mon, 21 Dec 2009 21:47:14 GMT

1. Export data from Sesame with Field Name Headers

2. Open Microsoft Word

3. In the "Tools" menu select "Mail Merge..."

4. Click on the "Create" button and from the provided menu choose "Form Letters..."

5. In the window that pops up select the "Active Window" button

6. Click on the "Get Data" button and from the provided menu choose "Open Data Source..."

7. In the Files of Type: drop down at the bottom choose "All Files (*.*)" and browse to where your ASCII file is and select it

8. Set the Field Delimiter to ","

9. Set the Record Delimiter to "(enter)" and click "OK"

10. Click "Edit Main Document"

11. Insert your merge fields in the document by selecting the "Insert Merge Field" and then selecting the appropriate fields

NOTE: Saving your document will cause Word to remember all your merge settings so you can begin from this step the next time you open your merge document.

12. In the "Tools" menu select "Mail Merge..."

13. In the "Mail Merge Helper" select the "Merge..." button near the bottom and then select "Merge" button in the window that pops up

Merging Your Data Into Letters (Microsoft Word 2003)

Mon, 21 Dec 2009 21:47:05 GMT

1. Export data from Sesame with Field Name Headers

2. Open Microsoft Word

3. In the "Tools" menu select "Letters and Mailings" and then choose "Mail Merge Wizard..."

4. On the right hand side select the "Next: Starting documents" button

5. On the right hand side select the "Next: Select recipients" button

6. On the right hand side select the "Browse" button

7. In the Files of Type: drop down at the bottom choose "All Files (*.*)" and browse to where your ASCII file is and select it

8. Set the Field Delimiter to ","

9. Set the Record Delimiter to "(enter)" and click on "OK"

10. On the window that pops up select "OK"

11. On the right hand side select the "Next: Write your letter" button

12. Write out your letter as you wish and then click on the "More Items..." button on the right hand side

13. Insert your database fields in the appropriate spots and when you are done, click the "Close" button

NOTE: Saving your document will cause Word to remember all your merge settings so you can begin from this step the next time you open your merge document.

14. On the right hand side select the "Next: Preview your letters" button

15. On the right hand side select the "Next: Complete the merge" button

16. Click "Print..." to print the letters, or click "Edit individual letters..." to merge to a new document

Merging Your Data Into Labels (Microsoft Word 2003)

Mon, 21 Dec 2009 21:46:53 GMT

1. Export data from Sesame with Field Name Headers

2. Open Microsoft Word

3. In the "Tools" menu select "Letters and Mailings" and then choose "Mail Merge Wizard..."

4. On the right hand side select the "Labels" radio button and then near the bottom select "Next: Starting document"

5. On the right hand side select the "Label options..." button

6. Choose the label that you will be using and select "OK"

7. On the right hand side select the "Next: Select recipients..." button

8. On the right hand side select the "Browse" button

9. In the Filed of Type: drop down at the bottom choose "All Files (*.*)" and browse to where your ASCII file is and select it

10. Set the Field Delimiter to ","

11. Set the Record Delimiter to "(enter)" and click on "OK"

12. On the window that pops up select "OK"

13. On the right hand side select the "Next: Arrange your labels" button

14. Click into the first label on your document and then on the right hand side select the "More Items..." button

15. Insert your database fields in the appropriate spots by selecting the field and then selecting the "Insert" button, when completed click on the "Close" button

16. On the right hand side click the "Update all labels" button

NOTE: Saving your document will cause Word to remember all your merge settings so you can begin from this step the next time you open your merge document.

17. On the right hand side select the "Next: Preview your labels" button

18. On the right hand side select the "Next: Complete the merge" button

19. Click "Print" to print the letters, or click "Edit individual labels" to merge to a new document

Merging Your Data Into Letters (Microsoft Word 2007)

Mon, 21 Dec 2009 21:46:45 GMT

1. Export data from Sesame with Field Name Headers

2. Open Microsoft Word

3. Click the "Mailings" toolbar at the top, Click on "Start Mail Merge" and choose "Letters".

4. At the top click on "Select Recipients" and choose "Use Existing List..."

5. In the Files of Type: drop down at the bottom choose "All Files (*.*)" and browse to where your ASCII file is and select it

5. Write out your letter as you wish and then click on the "Insert Merge Field" button at the top

6. Insert your database fields in the appropriate spots and when you are done, click the "Close" button

NOTE: Saving your document will cause Word to remember all your merge settings so you can begin from this step the next time you open your merge document.

7. Click "Finish & Merge " at the top. Choose "Print Documents..." to print the letters, or click "Edit Individual Documents..." to merge to a new document

Merging Your Data Into Labels (Microsoft Word 2007)

Mon, 21 Dec 2009 21:46:36 GMT

1. Export data from Sesame with Field Name Headers

2. Open Microsoft Word

3. Click the "Mailings" toolbar at the top, Click on "Start Mail Merge" and choose "Letters".

4. Choose the label that you will be using and select "OK"

5. At the top click on "Select Recipients" and choose "Use Existing List..."

6. In the Files of Type: drop down at the bottom choose "All Files (*.*)" and browse to where your ASCII file is and select it

7. Click into the first label on your document and then on the toolbar at the top click on "Insert Merge Fields"

8. Insert your database fields in the appropriate spots by selecting the field and then selecting the "Insert" button, when completed click on the "Close" button

9. On the toolbar at the top click on "Update Labels"

NOTE: Saving your document will cause Word to remember all your merge settings so you can begin from this step the next time you open your merge document.

10. Click "Finish & Merge " at the top. Choose "Print Documents..." to print the letters, or click "Edit Individual Documents..." to merge to a new document

How do I change the port number for @SendMail()?

Mon, 21 Dec 2009 20:33:26 GMT

By default most SMTP servers accept connections on port 25 for sending email. Lately however many companies have been switching to a different port to try and combat spam email. If your SMTP server uses a port other than 25 you will need to change a file on your computer so the SBasic command @SendMail() will work. The file you will need to edit is the services file which can be found in the C:\WINDOWS\system32\drivers\etc. Open this file with Notepad. You will see a line that says smtp 25/tcp mail #Simple Mail Transfer Protocol Change the 25 to the port number that your SMTP server uses and then click File->Save. The computer may need to be restarted in order for this change to take effect.

@SendMail() Error Codes.

Mon, 21 Dec 2009 17:42:28 GMT

@Sendmail is an SBasic programming command that sends an email message using the information in your database. @SendMail returns 0 if the function succeeded (the email was sent). A non-zero return value indicates a failure. The list of these return values are below.

-1 Unable to connect to SMTP server on SMTP port. Check internet connection, spelling of SMTP server name and SMTP port number of SMTP server.
-2 Failure from SMTP server after initial connection (after HELO was sent). Possible causes are trying to access a SMTP server that requires a login and password without specifying one.
-3 Failure from SMTP server after sending from address. Check the From address to verify that it is a valid email address.
-4 Failure from SMTP server after sending recipient list. Check the To, CC and BCC addresses to verify that they are a valid email address.
-5 Failure from SMTP server after sending body and subject of email
-6 Failure to open attachment files for sending. Check file path, that the file exists and that the current user has read permission to the file
-7 Failure from SMTP server after sending end of message tag.
-8 Failure from SMTP server after sending log off command.
-9 Authorization failure. Check spelling of username and password

Programmed Retrieve Specs

Fri, 04 Dec 2009 14:26:49 GMT

What is a Programmed Retrieve?

Q&As database search options are extensive. When these run out you can always use a programmed Retrieve Spec. This more recent capability programmable Retrieves, added in Q&A version 4.0is not used nearly as much as it ought to be. The good news is that Sesame supports Retrieve Spec programming just like Q&A. There are some relatively minor differences between the two, however. I want to tell you what these are, and in the process show you how you can use Retrieve Spec programming to good effect.

Retrieve Programming: two methods

Retrieve programming must be enclosed by what are called French braces { }. They contain the programming expression you want Q&A to evaluate, which will determine which record or records Q&A returns to your desktop, or to a mail merge, Mass Update, Report or other task that includes record selection as part of it. Your expression can take one of two forms: Either a value, such as a number, a date or a text string, or a Boolean expression that evaluates to either True or False. (Q&A's "Yes/No" fields are Boolean). As an example of a value expression, lets take a search for dates falling due within the next seven days, where the due date will always be in the future. At the Retrieve Spec, you could type the following into the Due Date or similarly named field, as long as its a proper date type field:

< { @Date + 7 }

Notice that the less-than operator (<) is outside the main expression enclosed by the French braces. In this case, the expression is evaluated by Q&A and returns a date seven days in the future from the current date. When coupled with the <, this tells Q&A to find all the records where the Due Date field contains a date that is earlier than the date that is seven days from today. Another example would be to find people with four-character surnames:

Surname: ={@Len(Surname) = 4}

This would produce records containing surnames such as Bird and Shaw.

This is the first way to use Retrieve programming. You could call it implied comparison, since the expression directly relates to the contents of the Retrieve Spec field in which youve typed it.

The second methodcall it truth-of-expressionis independent of the field into which it is typed. It can be considered universal to all the fields on the form. It starts with the French braces:

Surname: {@Len(Surname) = 4}

When the expression inside the French braces is evaluated and then tested against a particular record, it will result in either a True or a False. If true, the record is included; if false it is not. A programming expression used in this way does not relate to the field it happens to be put in. Place it in a different field and the result will be the same.

About Sesame

Q&A and Sesame support both these methods. But there is a difference you need to know.

We all know that Q&A is a very forgiving product. You can put Dont Know in a date field. You can name a field 150 and have a programming statement that says:

Total = #200 + 150

Q&A will use the value thats in field 150 rather than add the number 150. Thats neat. But its extremely difficult to replicate in a modern program that has a wider scope than Q&A.

Q&As forgiving nature extends to Retrieve Spec programming as well. Youve seen these two methods. If you use the first method, your programming expression produces a value. If you want that value to be compared to a field, you can put it in that field and put an equal sign in front of the French braces. In Q&A, if you omit the equal sign, Q&A will handle it anyway. Likewise if you do the opposite, and put an equal sign in front of the French braces when using method two, Q&A accommodates these errors. Sesame doesn't.

The following are valid in Sesame:

Rating: ={Key - 14}

Any Field: {Rating = Key - 14}

However, if you swap the equal sign to the second example, it is incorrect syntax and wont work in Sesame, even though this improper practice is tolerated in Q&A.

One thing you wont be able to carry forward to Sesame is the use of logical field numbers in programming. You have to use field names, plain and simple. Logical field numbers are actually a carryover from Q&A 3.0, where you couldnt use field names in programming and were forced to assign a logical field number to any field you wanted to reference in programming.

Again, this can be justified by stating that Q&A is extravagantly accommodating regarding such numbering. The numbers used are just for the Program Spec. So, you can have a numbering scheme in the Program Spec, another in the Navigation Spec, another in each of many saved Mass Update Specs, more yet in saved Retrieve Specs, and so on.

Using field names is ultimately just simpler

Why would you want Retrieve Spec programming?

Being able to use Retrieval expressions can come in handy in various situations. They come into their own when you use them as Saved Retrieves. This can be as standalone Retrieve Specs, or as the Retrieve Spec saved as part of Reports or Print Specs.

Compare to current date

As you saw in the first example, you can compare a date field to the current date. This is invaluable for finding records for the next week, for the last 14 days, for next month, for this month, for the past 90 days, and so forth. You dont have to type in a new range of dates every time you perform the task, such as:

Date Due: >=8/14/2003..<= 8/21/2003

or simply this:

Date Due: 8/14/2003..8/21/2003

Here are a few more examples of using Retrieve Spec expressions with date ranges:

{JobDate >= @Date - 7 And JobDate <=@Date + 7}

(JobDate 1 week either side of today.)

{@Month(JobDate) = @Month(@Date) And @Yr(JobDate) = @Yr(@Date)}

(JobDate this month.)

{@Replace(DOB, @Yr(DOB), @Yr(@Date)) >= @Date and @Replace(DOB, @Yr(DOB), @Yr(@Date)) < @Date + 7}

(Birthday (DOB) within the next week.)

Searching for non-standard characters

Retrieve Spec programming can help you find characters that shouldn't be in a field. For example, to search for ñ in a field name Reading1:

{@Instr(Reading1, @Chr(241)) > 0}

Complex string manipulation

To find records where the third-from-last character in the Item field is a backslash:

{@Mid(Item, @Len(Item)-2, 1) = \}

To find field values of a certain length

Short, or especially, long values, which might cause problems with mailing labels, merge documents or exports:

{@Len(Comments) > 255}

Although the @Len function is commonly used to determine the length of a text string, it can also be used though to find the length of a number field. An integer from 0 to 9 will have a length of 1, an integer from 10 to 99 will have a length of 2, and so forth. A definite case of a solution in search of a problem!

Mixed search logic

Where Retrieve Spec programming really comes into its own, is where you have mixed And and Or conditions across fields. Such retrieves are impossible to do without programming.

The best example is the Chase Creditors situation. You want to print out a list of, or send letters to, all those people who are overdue on their payments. Who do you the send the letters to?

You want those who are overdue a long time who owe a moderate amount, and you also want those who owe you a lot even if they are less severely overdue. In Retrieve Spec terms you want:

Overdue moderate time AND amount due is large

or...

Overdue long time AND amount due is moderate

You need criteria in two fields, and you need a mixed And and Or across them. Assuming theres a Date Paid which is blank if not paid, and fields for Invoice Date and Amount, the programmed Retrieve would look like this:

Date Paid:

Any Field: { (Invoice Date < @Date-30 AND Amount > 5000 ) OR (Invoice Date < @Date - 60 AND Amount > 2000 ) }

To repeatyou create and then save these Retrieve Spec programs, either with Reports or as standalone Saved Retrieve Specs. The example above will produce different records each day because the dates are being compared to the current date, no matter what date that is.

Furthermore, all these programmed Retrievesif they dont start with an equals sign, and if they use Field Nameswill not only translate into Sesame from your Q&A database, but will work in Sesame to give you the same consistent results.

New simple case-sensitive search support

Q&A offers you a way to perform a case-sensitive search: but it's pretty clumsy and hardly anyone knows it. Sesame does very much better. In Sesame, all you have to do is to prefix your retrieve parameter with the ' symbol (called an apostrophe), and the whole text string is searched case sensitive. So, to find "BBC" in lower case:

Company: 'bbc

You'll get a match on bbc but not BBC or Bbc

This article by Alec Mulvey is adapted from one published in the August 2003 issue of The Quick Answer.

Reserved Words

Thu, 03 Dec 2009 17:30:02 GMT

The following words are reserved by Sesame. You cannot use them as the name of something else. Sesame is case-insensitive, so 'clear', 'CLEAR' and 'Clear' are all the same reserved word.

@	@Ab	@Abs
@Ad	@Add	@As
@Asc	@Askuser	@Av
@Avg	@Beenhere	@Cgr
@Ch	@Chr	@Closecommandarea
@Color	@Currentelement	@D
@Da	@Date	@De
@Decimals	@Del	@Di
@Ditto	@Dm	@Dom
@Dow	@Dow$	@Dw
@Dw$	@Error	@Ex
@Exit	@Exp	@Fedit
@Field	@Filename	@Fn
@Formfieldvalue	@Formresultsetcurrentposition	@Formresultsettotal
@Frac	@Fv	@Globalvalue
@Group	@Height	@Help
@Hgt	@Hp	@In
@Insert	@Instr	@Int
@Ir	@It	@Label
@Layout	@Left	@Len
@Loadcopyspec	@Loadexportspec	@Loadimportspec
@Loadmassupdatespec	@Loadretrievespec	@Loadsortspec
@Lookup	@Lookupr	@Lt
@Lu	@Lur	@Macro
@Max	@Md	@Mid
@Min	@Mn	@Mod
@Modified	@Month	@Month$
@Msg	@Msgbox	@Mt
@Mt$	@Mx	@Newline
@Nl	@Nmb	@Notifyform
@Num	@Number	@Pageextentx
@Pageextenty	@Platform	@Play
@Pmt	@Popupmenu	@Pv
@Readonly	@Recordmodifieddate	@Recordmodifiedtime
@Replace	@Replfir	@Repllas
@Rest	@Resultsetcurrentposition	@Resultsettotal
@Right	@Rnd	@Round
@Rt	@Save	@Select
@Selecttreeitem	@Sendmail	@Sgn
@Shell	@Sq	@Sqrt
@Std	@Str	@Sum
@T	@TD	@Text
@Time	@TM	@Tme
@TN	@Todate	@Tomoney
@Tonumber	@Totime	@Toyesno
@TT	@Txt	@TY
@Ud	@Update	@Us
@Userid	@Userselect	@Usl
@Var	@Visibility	@Width
@Wth	@Xlookup	@Xlookupr
@Xlr	@Xlu	@Xpos
@Xus	@Xuserselect	@Xuserselectr
@Xusr	@Year	@Ypos
@Yr	And	Array
As	Beenhere	Begin
Boolean	Booleanelement	Char
Charelement	Clear	Closecommandarea
Cls	Createnewrecord	Date
Dateelement	Dimlimit	Dims
Double	Doubleelement	Downto
Elementtype	Else	End
Eq	Exit	False
Fileclose	Filedelete	Fileexists
Filegeterrormsg	Filegetlasterror	Fileopen
Filepos	Filereadchar	Filereadln
Fileseek	Filesize	Filewrite
Filewriteln	Finishpage	For
Forceredraw	Formfieldvalue	Formresultsetcurrentposition
Function	Ge	Globalvalue
Gosub	Goto	Gt
Height	Hgt	If
Int	Intelement	Label
Le	Loiter	Lookup
Lookupr	Lt	Lu
Lur	Navigate	Ne
Newpage	Next	No
Not	Notifyform	Of
On	Or	Printstring
Random	Readchar	Readln
Readonly	Rem	Removerecordfromresultset
Resultsetcurrentposition	Return	Set
Setbooleanelement	Setcharelement	Setdateelement
Setdoubleelement	Setintelement	Setstringelement
Settimeelement	Step	Stop
String	Stringelement	Subroutine
Tab	Then	Time
Timeelement	To	Tolower
Toupper	True	Us
Userselect	Usl	Var
Visibility	While	Width
Write	Writeln	Wth
Xlookup	Xlookupr	Xlr
Xlu	Xor	Xpos
Xpost	Xpostr	Xus
Xuserselect	Xuserselectr	Xusr
Yes	Yesno	Ypos

Get Rid of IE 8 Warnings about Sesame

Thu, 03 Dec 2009 16:25:08 GMT

From the Inside Sesame Help Desk

I updated my computer to install the new Internet Explorer from Microsoft. It's version 8. Ever since I did this I get a warning about opening Sesame and a question as to whether I want to run this software. It happens every time I start Sesame. Is there a problem with Sesame and IE8? I know that there were other compatibility issues with programs like McAfee antivirus and IE8 but I thought all of the issues had been fixed in the final IE8 release.
Debra

This is a problem caused by the security settings in both IE8 and Windows XP (Sp2 or 3), or Windows Vista. It's a known issue that's all over the Microsoft support web sites. There are all kinds of proposed registry hacks and fix-it tools. It isn't limited to Sesame. It affects other network or local programs running on your system that Windows isn't fully aware of. The reason it's happening now is that IE8 reset your IE7 security settings in a strange way. Regardless of all of the proposed fixes you can find on the web, there's a really easy way.

Start IE8. Choose Tools / Internet Options / Security. Click on the Local Intranet icon and then the Sites button. A dialog box like the one below will appear.

IE8 Local Intranet site security settings.

The rest of this will sound crazy but it works. Unclick the Automatically detect intranet network checkbox. Make sure the other three checkboxes remain checked (they should be by default). Click OK and OK again. Close IE8 and all warnings about Sesame will go away. You'll have to do this on each computer that's running Sesame and has IE8 installed on it.

Totals Only Reports

Thu, 03 Dec 2009 16:16:41 GMT

From the Inside Sesame Help Desk

My guess is that this has been asked before, but one of the things I miss from Q&A is the ability to make a Totals Only report simply by setting an option at the Print Option screen. What I want to do is see a total of all my orders paid by check, credit card or PO for a given time period. I don't need all the details. I just want the totals for each so I can confirm my deposits. I read the approach on the Lantica Software website about using two different views in a report design but, to be honest, it's confusing to me. Isn't there a simpler way to accomplish what I need?
Bill S

You're not the only one who wants to practice the KIS (keep it simple) principle. It's the best design policy around. If you can accomplish the same end with a simpler approach - do it. It will make your life easier today and will allow the person who updates your work to have a much easier time of it.

So here goes - a simple way to do a totals only report.

The reason the sample at lantica.com uses multiple views is to be able to get the field value and the total on the same line. But here's an easier way to do the same thing.

Layout your regular report with the subtotals and totals you want. The easiest way is to use Sesame 2.0's Report Wizard. You can add a static text LE to tell the user what the totals are on the bottom line.

Now what we need to do is get rid of the detail lines and move Type down to the subtotals line so it lines up with the subtotal amount. You get rid of the detail lines by making them Not Visible in the Property Editor.

In order to get those Types to appear once in the footer where the subtotals are, you need to capture the value of the Type before you start each group, then copy that value into the footer. To do this, create two unbound value fields - one in the header area (we can use Group Header 2 since it also breaks by Type) and one in the subtotal footer - TypeCopy and TypeCopy1 respectively. Make the one in the header invisible.

One more step. Open the program editor and add this program to the TypeCopy LE:

TypeCopy = Type
TypeCopy1 = TypeCopy

Basically the steps are (1) hide the detail data, (2) copy the first description of each line to a header, then (3)copy that value to the footer where the subtotal will appear. The sample database includes totals only reports for Types of Donation, Donors, and even the dreaded Totals by Month.

Group 'Then' Conditions by Bracketing Them

Thu, 03 Dec 2009 15:52:40 GMT

From the Inside Sesame Help Desk

I thought I had my application in Sesame just about done but I can't seem to get my programming right. What I want it to do is make a certain three fields visible for only me and not for the rest of my staff. What's happening is they are all invisible to them but only one is visible even to me. I'm using On-Form-Entry programming like this:

Visibility(Field1, 1)
Visibility(Field2, 1)
Visibility(Field3, 1)

If @UserId <> "MyLogon" Then
Visibility(Field1, 0);
Visibility(Field2, 0);
Visibility(Field3, 0);

Can you help me?
Larry

This is a simple but common error that people have been making since the dawn of the If/Then statement. If the result (the Then portion) of your test condition (the If portion) contains multiple statements, they must be separated and grouped. You successfully separated them with semicolons but you didn't group them. What your statement really says as it stands is this:

If it's me, Then leave field one visible. It goes on to say that regardless of who it is, make Field2 invisible and make Field3 invisible.

Change the If/Then portion of the statement to this:

If @UserId <> "MyLogon" Then
{
     Visibility(Field1, 0);
     Visibility(Field2, 0);
     Visibility(Field3, 0);
}

Now you are saying, when my condition is met do everything enclosed in the brackets. You can leave out the word Then and you don't need the semicolons when each statement is on a separate line.

See More Data in Multi-Line Fields

Wed, 02 Dec 2009 16:44:02 GMT

From the Inside Sesame Help Desk

In my old Q&A database, if I put more data than could be displayed in a multi-line field, or even a single line field, I'd see an arrow at the end of the field indicating that there was more data and that I should press F6 to expand the field to see it all. Is there a way I can get the same indicator in Sesame?
Warren

There is no way in Sesame to have an arrow displayed at the end of a field to indicate that there is more data than you can see in the field. However, with a little clever design you can make the field do just what you need. In a single line text field this really isn't an issue. You will see that there's more data because what shows will butt up against the right edge of the layout element. In a multi-line element you have a valid argument, but here's the easy solution: make the field a bit larger in height, about half a line larger than the defaults (or half a line larger than the amount of lines you want to see.

Your settings will vary based on the number of lines you want to see displayed and the font size you're using. Use the Property Editor's Look/Position tab to get the current height of the field and then try some data. If you need to make it higher use the Property Editor Position tab Height setting to increase it by 8 to 10 pixels.

Now you can see at a glance that there's more data in the field.

Programming Events and Combo Boxes

Wed, 02 Dec 2009 16:36:55 GMT

From the Inside Sesame Help Desk

Is there a way I can make my programming run when I select something from a combo box list rather than having to wait until I click out of the combo box into another field. In other words, I want to be able to see the results of my selections as I click on them so I can decide if I've made the right choice. I've programmed something like this in the combo box (called Type) in the on-element-change event:

If Type = "Machine1" Then
{ Hours = "12"; Technician = "Clay"; Schedule = "ASAP"; Priority = 1}
Else 
If Type = "Machine2" Then
{ Hours = "1"; Technician = "Bill"; Schedule = "CALL FOR APPT" ; Priority =2 }
Else
If Type = "Machine3" Then
{ Hours = "TBD"; Technician = "TBD"; Schedule = "As Available" ; Priority = 3}

I'd like to be able to click and see the results so I can change them without leaving the field and coming back.
Jason

You're using the On-Element-Change event which doesn't trigger until you leave the field. So even though you've made a selection and changed what is showing in the combo box, Sesame doesn't recognize it until you move on. You should use the On-Element-Immediate-Change event instead and you'll see the programming fire as you select a drop down entry from the list. When you use this event in a combo box - where you can also type an entry in the box - remember that the program will fire with each keystroke. As long as you take this into account it should give you just what you need.

Change Button and Tab Titles Without Renaming

Wed, 02 Dec 2009 15:18:26 GMT

From the Inside Sesame Help Desk

I have almost finished my design and I am very proud of what I have been able to accomplish. I have downloaded version 2.1 and really like the new button menus. But I don't like the names that appear on them or on the tabs at the bottom of each form as it opens. I realize that most of this is my fault in that I didn't do very good planning. If I had, I would not have named one of my forms "OUTEXP," I would have named it "EXPERT CASE DATA" so that's what would show on the menus and tabs. That is only one example of the bad habits my prior relationship with Q&A got me into (no more than eight characters, etc.). So, here is my question. Since I have so much programming and so many lookup fields that use these field names, is there any way I can change the names on the buttons and tabs without renaming my forms and then redoing my programming? Please say there is!
Steve

Of course there is. There's almost always a way to do anything in Sesame. Sometimes it takes some imagination and creative programming and sometimes it is simply taking advantage of built-in programming commands. The latter is all you need contend with here.

You make a valid point. If you have a complex application with many databases and forms, changing a form name or database name could have far reaching consequences.

The best way to address this is to change what you see, not what Sesame sees and works with. The tool for this is the AddToTranslationTable() command. It has been around since version 1. You can use the translation table to change strings that appear on the parts of the screen you normally can't control into something else.

If you place AddToTranslationTable() programming in the Application Programming (Application / Application Property Manager / Program Application) you will change how items are displayed throughout your entire application, including the button menus. AddToTranslationTable does not change the actual content of the item, only its appearance as drawn on the screen. It does not replace anything in memory, so it has no effect on any programming statements using the original names.

// Change Tab Titles and Buttons on Menus (Lines are CASE sensitive)
AddToTranslationTable("OUTEXP", "EXPERT CASE DATA")
AddToTranslationTable("Search/Update OUTEXP", "Search/Update EXPERT CASE DATA")

Remember that the strings you use in AddToTranslationTable() are case sensitive both in what you are replacing and what you want to see as the replacement. Also remember to do your translation changes in all places you feel are necessary. For example, in your case you might also want to replace the titles in the Sort and Mass Update spec windows.

Retrieve Records For a Date Within a Range Of Dates

Wed, 02 Dec 2009 15:03:53 GMT

From the Inside Sesame Help Desk

Is there a way I can retrieve a record with a certain day in a field that falls between a starting and an ending date? In other words, if I have a date field that shows when people are arriving and another when they are leaving. Can I retrieve the records where a date or group of dates are between the arriving and departing dates? I've tried to use complex retrieve programming but there is no way my users will be able to do that.
Moshe

There are a few ways to do this but the approach we took was to give you something that was easy for your users. The best solutions for the user usually mean a little extra work on the part of the application designer. That is the case here.

The idea was to come up with a way for the user to search the database by being prompted for dates, from a pop-up calendar, to search for. No programming and no confusion for a data entry person. To accomplish this you would add one new field and a command button to your form. Both of these items will only be visible to the user at the Search (retrieve spec) screen. There is a sample application supplied with this month's issue named Included Dates.DB so you can see how this will work for you.

We are going to add a Keyword field called Search Date. This will be where your users will search for single or multiple dates without the need for date math. Right next to it we will put a command button called CmdSearch. Change the LE Label of the button to @2> so it displays a small down arrow. Size it and move it right next to the Search Date element.

Now to the rest of your work â€“ programming the elements to do what you want.

First, in your form's On-Form-Entry event:

//Set Visibility of Search LE's - Hide them except at Search
If @Mode()<2
{
	Visibility(Search Date, 0)
	Visibility(CmdSearch, 0)
}

Next in the form's On-Retrieve Spec-Open event:

//Set Visibility of Search LE's to visible
If @Mode()=2
{
	Visibility(Search Date, 1)
	Visibility(CmdSearch, 1)
}

Then in the Search Date (Keyword Field) On-Form-Change event:

var vDate as Date
Var i as Int
var n as int

// This makes a 'hidden' search field with all of the attendance dates for
// this specific record in a format that they can easily be searched
// as a keyword field complete with semicolons in the proper space
 
i = 0         // How Many Days between Arrival and Departure
n = 0        // Use as a Loop counter

// Check that Arrive and Depart Dates are valid
If @IsBlank(Arrive On)=0 and @isblank(Depart On)=0 And @TD(Depart On)>=@Td(Arrive on)
{
	Clear(Search Date)
	i = @Tn(@Td(Depart On)-@td(Arrive On))
	If i = 0 
	{	
		vDate = Arrive On
		Search Date = @AsFormattedByLE(Arrive On, 1, vDate) 
	}
	Else
	{
		For n = 0 to i
			vDate = Arrive On
			Search Date = Search Date + @AsFormattedByLE(Arrive On, 0, vDate+n) + ";"
		Next
	
		Search Date = @ReplLas(Search Date, ";", "")
	}
}

Lastly, in the command button On-Element-Entry event:

var vChoice as Date

// Ask the Users what they want to do and pop up calendars to help them do it
If @Mode()=2                   // Only do this at Search mode 
{
	Clear (Search Date)
	If  @AskUser("Do You Want To Find Records By","Dates of Arrival and Departure?","")
	{
		// Pops up a calendar so you can select a date. 
		vChoice = @Calendar(@Date, "Select START Search Date")

		// Make sure you selected a date.
		If Not @Error
		{
			// put selected date in the LE
			Search Date = @AsFormattedByLE(Arrive On, 0, vChoice) + ";"
	
			// Message to go on to the end date choice.
			@MsgBox("You Have Selected a Start Search Date, Now Select an End Date","If You Only Wish to Search For 1 Day","Make the End Date the Same as the Start Date")
	
			// Pops up calendar AGAIN to select END date. Starts at already selected date
			vChoice = @Calendar(vChoice, "Select ENDING Search Date")
	
			// Make sure you selected a date.
			If Not @Error
				
			// Show the selected date on the Form.
			Search Date = Search Date + @AsFormattedByLE(Arrive On, 0, vChoice)
		}
	}
}

Does that seem like a lot of work? It really isn't considering it is only copy and paste for you and then button clicking for your users.

They will click on the Arrow (command Button) and be asked if they want to search for attendees by date. If they answer yes, they will be presented with calendars to select their starting and ending search dates. Once they do that, they enter whatever other criteria they need to refine their search and press F10. They will get the records for those people who will be in attendance during the range of days they entered. They can print out a list with a count (quick report) or - and here is a neat side benefit - you can get a Keyword Report of attendees by individual day based on that new Search Dates field.

Security Can Do More Than Protect Your Data

Wed, 02 Dec 2009 14:43:18 GMT

From the Inside Sesame Help Desk

I have a hopefully easy requirement that I just can't find a way to accomplish. Is there a way for Sesame to know which computer it's working on? What I mean is can it tell what workstation is entering or modifying data? I want to add a field that tells me who was the last person to add or modify a record and when they did it. I can figure out the when but I can't get Sesame to tell me who did it.
Pat

This is just one of the reasons you should be using security in your databases. Of course it will protect access to your databases but maybe you feel that you don't need that type of protection. That's OK. Let's look at some uses for security other than protection.

Before we begin - a word of caution. If you are using security and you have multiple databases in your application where you are doing external lookups from one to the other, you must set an external lookup userID and password. This is done on the Application Settings tab of the Security Manager. The external lookup password is not one that a user needs to log on with. It's an internal password sent from one database to the other that says it is OK to provide your data. You need to create the external user as if they were a real person and then give them a password. After that's done, you re-enter that information on the settings tab. While this is an internal function, remember that it can also be used to log onto the application, so give it a level of complexity that matches that required by your overall application security.

In addition, even if you are not really concerned with true security, please establish one administrative group with an ID and Password that you do not share with everyone. Also set this as the only group that can redesign the application. This is just insurance against future problems such as a disgruntled employee changing all the passwords and locking all of you out of the system.

Now, on to your question. Once you have assigned security id's and passwords to each of your users, they will have to use them to log into your application. Once they do, Sesame will know who they are. If you want to keep it simple (and not really secure) you could assign each person an id and password of their first and last name, respectively.

On your form add a new text field called Changed By and a corresponding date field called On. Program your form On-Form-Change event as follows:

Changed By = @UserId
On = @Date

That's all it takes to do what you asked for but, now that you have security instituted, here are a few more ideas.

Make certain fields visible only to certain people or groups. In your Form's On-Form-Entry event:

If @Mode()<2        // Adding or Updating records
{
	If @UserId="Bill" or @UserId="Tom"  
// You could also do this by @Group rather than person by person
	{
		// Hide the following form elements
		Visibility(Customer Comment, 0)
		Visibility(Profit, 0)
		Visibility(Another LEName, 0)
	}
	Else     // If it is not these users
	{
		Visibility(Customer Comment, 1)
		Visibility(Profit, 1)
		Visibility(Another LEName, 1)
	}
}

Make records read only for certain groups of people In your Form's On-Form-Entry event:

Var vElements as String
Var vCnt as Int
Var vLoop as Int
Var vName as String
Var vNN as Int		//For Special Read Only Set

//Use On Form Entry to set all elements to Read only (not grey) for low level users
If @mode()=1 and @Group="users" 
{	
	vElements = @StringArrayElementList()
	vCnt = @CountStringArray(vElements)
	vLoop = 0

	While vLoop < vCnt
	{
		vLoop = vLoop + 1
        		vName = @AccessStringArray(vElements, vLoop)
        		SetThisElement(vName)
		vNN = @ElementType(ThisElement)
		If (vNN > 999 and vNN < 1009) 
		{
    			ReadOnly(ThisElement, 2)
		}
	}
	UnSetThisElement()
}

These are just a few of the things you can do once you have added security to your application. Use your imagination and you will find even more.

Limit Field Data Lengths and Words

Wed, 02 Dec 2009 14:27:09 GMT

From the Inside Sesame Help Desk

I have two specific needs concerning the data my people enter. First I would like to be able to set a specific length for certain fields to make sure that they always enter meaningful data. For instance, an automobile VIN (vehicle Identification Number) is always 17 characters long. It can contain numbers and letters but the length never varies. I would like to make sure the users can't enter any more or less characters than that in the VIN field. In another field, I want to control their verbosity. I have a field called "Short Comment". I don't want my employees to enter a book. I want to see 25 words or less in this field. I can't find a way to do these things but I am sure there is one. Can you give me a clue?
Christian

These are both variants on the same theme. The best way to do both is by using On-Element-Exit programming. We are supplying a sample database called Lengths.DB so you can see and copy the programming and play with variations. Let's look at the rules we need to consider for each case.

In the first scenario if the user has entered anything (the form element is not empty) then it must be exactly 17 characters long. If it is not, tell the user about it and keep him or her in the element until they make it the correct length or blank it.

The second scenario "if anything has been entered in the element, it cannot be more than twenty-five words" is a bit trickier. It is easy to count the number of characters but how do we know how long twenty-five words is? Well, we can assume that each word has a space after it. To a computer a space is not a blank but a real character. It is represented by the ASCII character 32. So, if we can count the character 32's, we will know how many words there are in the field. This will work as long as we remember that sometimes people use spaces instead of tabs and sometimes they will put a couple of spaces between sentences. We have to take care of these issues. Like in the first case, if there are more than 25 words we need to tell the user and keep him or her in the element until they correct it or blank it.

Now that we know the rules let's use Sesame's Trap function to implement them. Trap is the best way to control a variable user input if you do not want them to leave the data element until they get it right.

In both cases (letters and word counts) the first thing we want to do is set the trap when the user enters the element. So, in the On-Element-Entry events for both elements put this programming statement:

// Set the Trap 
     Trap(ThisElement, 1)

Now, for the first case (exactly 17 characters) program the On-Element-Exit of the element VIN like this:

// Check if trap has been set
     If @Trap(ThisElement) = 2
     {
	     // Check that something has been entered
             If @IsBlank(ThisElement)=0 	
 	     {
		     // If they did it right clear the trap
		     If  @Len(ThisElement)=17				
                     {
			// Release the trap
			Trap(ThisElement, 0)
                     }
	 	     //If they didn't
 		     Else
		     {
			// Show error message
			@MsgBox(@ElementName(ThisElement) + " must be 17 characters long!", "", "")
		     }
 	      }

	      // Very Important - If the element is empty also release the trap
	      Else Trap(ThisElement, 0)
     }

Now let's look at the On-Element-Exit statement for condition 2 and the element named WORDS. It is the same concept as condition 1 but we need a way to count the number spaces or @Chr(32)'s. If we had a string array, or a set of values separated by semicolons, Sesame could easily count them. So we will make our words into a temporary string array by converting the spaces to semicolons:

var nn as Int
var vData as String

// Set the variable equal to the data in the element. 
// Then use the variable for the program calculation
vData = Words

// Check if trap has been set
If @Trap(ThisElement) = 2
{
	// Check that something has been entered
	If @IsBlank(ThisElement)=0 	
	{
		// Replace any semicolons that might be in the data
		vData = @Replace(vData, ";", "|")
		
		// Make sure there is no space at the end of the words
		If @Rt(vData, 1) = @Chr(32)
		{
			vData = @ReplLas(vData, @Chr(32), "")
		}
		
		// Replace the Spaces with semicolons to 
		// make the data look like a string array
		vdata = @Replace(vData, @Chr(32), ";")
		
		// Count how many 'strings' (words) there are
		nn = @CountStringArray(vData)
		
		// If they did it right (less than 26) clear the trap
		If nn < 26				
		{
			// Release the trap
			Trap(ThisElement, 0)
		}
		// If they didn't (more than 25)
		Else
		{
			// Show error message
			@MsgBox(@ElementName(ThisElement) + " must be no more than 25 WORDS long!", "It is now " + nn + " WORDS long.", "")
		}
	}

	// Very Important - If the element is empty also release the trap
	Else Trap(ThisElement, 0)
}

There you have it. Count your characters and your words. Modify these statements to match your exact needs. Remember, to use the Trap function you must have both an On-Element-Entry and On-Element-Exit statement.

Manage Changes to Your WordMerge Documents

Tue, 01 Dec 2009 21:15:37 GMT

From the Inside Sesame Help Desk

As you've pointed out in the past, MS Word does not allow multiple people to open the same merge document on different stations. We followed all the rules and we maintain a separate directory of master documents on the server. We always set them to merge with a file that resides in the C:\Sesame2\Docs folder. And we copy these files to all of the users C:\sesame2\docs folder about once a month. Therein lies the problem. Some of our users have gotten so good with the applications that they will make changes to a document and even save a copy back to the master folder. However, they don't tell anyone about it and their changes won't appear on the other users' computers until the next copy routine. This gets complicated by the fact that we can now get two different versions of the same letter printed from two different computers. Is there a way we can use our master documents or a way to automate our copy routine?
Betsy

Can you use the master documents? No, and you stated the reason - Microsoft Word won't let you. Can you better handle you copy routine and automate it so everyone always has the latest merge documents? Yes - you can do it every time a user opens the Sesame application, and very easily.

The secret is to use the ability Sesame has to program the Application. This means that when Sesame opens your application you have it run programming. And since Sesame can run Windows operating system commands via the @Shell() command, you can make it do things outside the Sesame program. In this case, we want to copy all the files that are on the server in the C:\Sesame2\docs folder (along with all subfolders) to the local user's computer every time he or she starts Sesame. Start by selecting Application / Application Property Manager from the Designer menu. In the Application Property Manager dialog, select Program Application.

The only Element you can program is Application and the only event is On-Application-Open. Place the following statement there:

var n as Int
 
// Copy The Docs from Server to Local Drives
// Substitute your server network name below
n = @Shell("xcopy \\ServerName\sesame2\docs C:\Sesame2\Docs /S /Y")

Make sure you substitute your server computer Network Share Name for ServerName. Also make sure that the server's Sesame2/Docs folder is shared and permissions set for read and write on the network. The /S and /Y switches in tell Xcopy to copy (S)ubdirectories and to say (Y)es to any overwrite confirmation prompt. Save the programming, Reconcile your design, and you now will be copying your master documents to each user every time they open the application.

Check Whether a Form is Open

Tue, 01 Dec 2009 20:43:49 GMT

From the Inside Sesame Help Desk

I need to make sure that a certain form in my Sesame application is closed before I allow some programming to run from another form. It involves a rather large import of data, a bunch of calculations, and a posting routine. Because I am doing so many records at one time I want to make sure that the receiving database is closed before the process starts. Is there a way to do this?
Tom

Sesame's @FormResultSetTotal() returns a count of the number of records currently open in a specified form. If there are no records open in that form, it will return -1. This can be used to check if a form is open.

For this example let's assume we have an application with two databases that each have a single form. We'll call the first one Residents and the second one Receipts. We need to post all of our monthly receipts to a balance field on each resident's record. This answer will not address the actual posting, just the conditional statement to make sure that the Residents database isn't open.

In whatever element you are using to do the posting, place this conditional statement in front of the rest of your programming:

var vcnt as int 

vcnt = @FormResultSetTotal("Residents") 
If vCnt = -1 
{
     // Put the posting operation here
     // make sure it all is within this set of brackets
} 
Else 
{
     @MsgBox("Residents is open.","Operation will not continue.","Please close Residents.")
}

Spell Checker in Sesame 2.1 Not Working

Tue, 01 Dec 2009 20:32:28 GMT

From the Inside Sesame Help Desk

I downloaded the new Sesame 2.1 upgrade for one reason only. I want to use the spell checker. I don't want to use it in interactive mode, I just want to be able to check spelling in certain fields. I installed the new executables and restarted Sesame but the spell checker doesn't seem to work.
Shawn

If you downloaded the executables only for updating your Sesame it should also have created a file named dictionary.txt in your Sesame2 folder. What seems to be the problem here is that you then copied your Sesame executables to each of your other computers but didn't copy the dictionary file. Make sure that each machine has a copy of dictionary.txt in the Sesame start in folder and your spell checker should work just fine.