Getting the Most from Lantica's Sesame Database Manager
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . November 19, 2017  5:57 pm. PST

Inside Sesame Issue Index
Switch to Keyword/Topic Index | Subscriber Express Index

2004  2005  2006  2007  2008  2009  2010  2011  2012  2013  2014  2015   2016

As issues of Inside Sesame are published, their contents are added to our Keyword/Topic index and Issue (this) index. The Issue index is organized by year and month of issue and includes a description of the contents of each issue. The Keyword/Topic index is organized by keyword, much like the index in the back of a book. A special Subscriber Express Search Index is also available to Inside Sesame subscribers. This database can be searched by keyword, topic, article title, author and various other criteria to locate the issues in which the search criteria occur. Subscribers can then view those issues immediately.
 

Ordering Back Issues...
Back issues are $25.00 each. (Current subscribers receive a 20% discount.) Note the order code(s) of the back issue(s) you wish to order. At the Order Form, you will be able to select them from the Back Issue pick-list.

Note: Beginning with this issue, we are on a quarterly publication schedule. New issues will be posted in January, April, July and October by the 5th of the month. If you're a subscriber, you'll continue to receive your email notifications when new issues have been posted and be entitled to the total number of issues you had with your bi-monthly subscription. New subscriptions and renewals will be for the quarterly issues.

January 2017 issue summary. . . [order code JAN 17]

A Street-Smart Sesame Service Calls Calendar
For businesses that schedule service calls by day of the week, starting time and duration, a pop-up panoramic calendar-like view of an entire week’s appointments can make life a whole lot easier. Even better when that calendar is natively sharable by all and enables schedulers to see ahead for an entire month or more. A calendar like this is hard to come by in Sesame. See what the boys at the the Inside Sesame Labs cooked up.

List Files by Date Created/Last Modified
Sesame's @LocalListDirectory and @ServerListDirectory list all the files in the specified folder, sorted alphabetically by filename with no additional information. But what if a filename sort is isn’t what you’re after? Suppose you need the files sorted by the date they were created or last modified so that the most recent files appear at the top of the list and the oldest ones at the bottom. You might even want such a list to show when the files were created or last modified.

Copying Prospects To Customers
It isn’t uncommon for an application to have both a Prospects/Leads database along with a Customers database. You work a prospect over time in that database. Then, when they place their first order it’s time to move them over to Customers. There are a variety of ways to copy a record from one database to another. Our compact program copies both the main form along with the data in a Notes subform to Customers and assigns the next customer number to the new record.

Derived Column Sort Workaround
Q&A reports could be sorted on a Derived (programmed) column. Sesame reports can't. It's too late because in Sesame the sort order (if any) is determined long before any programmed columns’ values are processed in the report. So if the same derived-value sort order is essential to a translated Q&A report — what can you do?

Sesame as a Utility Program
Every so often a veteran IT type will call and bend our ear about what a great little utility program Q&A was. A “Utility program”? In the hands of a go-to guy with a knack for resolving awkward data issues between non-Q&A programs, Q&A was a magic wand. We took on a tough utility job recently. This one would not have been doable in Q&A by any stretch, but it was right in old Sesame’s wheelhouse.


_______________You ask. We answer.


  • Mass Formatting Your Forms — Be cautious when using  Sesame's Advanced Element Selector followed by Perform Selected Action. You could get more than you bargained for.

  • Report Writing Made Easier  — We walk you through designing a fairly sophisticated report. Now that wasn't so bad, was it?

  • Blank Fields and Date Range Searches — Unlike Q&A, Sesame will return records with blank dates if you perform a date search for records before a certain date. What options do you have?

  • Line Numbers in Table View Subforms — Users new to Sesame think those line numbers have some kind of relationship with the data in the corresponding rows. They don't. But you can assign permanent line numbers to the subrecords.  Sample application included in this month's subscriber download file.

  • Importing/Exporting in Client/Server Mode — You just set up an export, supplied the destination filename and let 'er rip. But the file's not there.

  • Auto-Clear the Retrieve Spec — Users can lose track of their criteria when performing back-to-back searches and just frustrate themselves. The nuclear option is to have Sesame  automatically clear the Retrieve Spec after every search.


_______________Technical tidbits you can use today
 

  • Find All Dates in a Year — Ever need to search a date field for a particular year's worth of records? You can do it with 20-some keystrokes the old school way or get the same result with just a fraction of that. 

  • Programmed XResultSetSearch Criteria — Simple programming expressions can help dig up the records you're after.

  • Use Tooltips to Unhide Text in Tight Fields — An easy way to see the whole entry in a smallish field without having to click in it and arrow over or press F6 to expand it.

 


April 2017 issue summary. . . [order code APR 17]

Check it. Grab it. Import it ― A Comprehensive Sesame FTP Download Program
Most commercial websites today have fill-in forms of one kind or another, from simple contact forms to elaborate order forms. Company sites might also have private/restricted forms for their outside sales or service people to fill in and submit from their mobile devices. But how do you funnel that remote data down to your Sesame database with no more than a mouse click? We point the way.

Include/Exclude Options When Copying Table Subform Content
A company had Proposals and Orders databases. When a proposal was accepted by the customer, it was turned into a new order by copying it to Orders. Problem was, some of the lineitems in the table subform needed to be excluded. So they needed a fast and intuitive way to pick which items not to copy.

Cook Up a Custom Password Box
A company was looking to restrict access to certain sensitive reports, procedures and form elements without having to add overall security to the application. They wanted a password field at the bottom of the form. Only a select few people would know the password. When supplied, command buttons and other form elements would appear, enabling an admin person to perform the special tasks. The password masking you normally see with password boxes didn’t come up, but we wondered how that might be done. Sample application included in this month's download file.

Require Password for Record Deletes
There are a variety of ways to prevent records from being deleted from a database. This one hides the delete options on the commands panel, disables the F3 Delete key, and prompts for a special admin password before zapping the record. But it does something more. It quietly copies the contents of the record to an external text file on the server an instant before the delete so that the data can be restored. The file tells you precisely when the record was deleted, who deleted it, and from what workstation.

Son of WordMerge
The 300-line WordMerge add-in that comes with Sesame might seem like overkill when all you need to merge are a few fields — perhaps along with some subform data — to a specific document. Using just a fraction of the WordMerge code, our savvy little substitute creates a merge data source file (just like WordMerge with header and data rows) then opens the doc with the data neatly merged in.

When FileWriteLn Beats FileOverWrite ― The Strange Case of PDF995
You might think that either method for outputting text files winds up with the same result. But if you're using PDF995 on a 64-bit machine to generate PDF docs, you might be wrong. In Windows' security model, creating a new file with FileOverWrite is a whole lot different than modifying an existing file with FileWriteLn


_______________You ask. We answer.


  • Reports vs. Database Design — Why you should always design a database with the kinds of reports you'll need in mind.

  • Update Multiple Records with a Click  — Reducing a complex update procedure to a simple point-and-click.

  • Truly Secure Your Data — Steps you can take to keep prying eyes away from where they ought not to be.

  • Make Updating Sesame Easier — How to apply a Sesame upgrade to  all the PC's in your company in one fell swoop.

  • Set Sesame Screen Sizes — A tutorial on getting just the right size Sesame window  particularly on laptops.

  • Convert Non-Y2K Dates with an Export/Import — Back to the future.


_______________Technical tidbits you can use today
 

  • Images in Table View — All it takes is a sesame.ini file setting. 

  • Checkboxes — Only One State Matters ― A checkbox is either on or off, right? Sesame checkboxes have three states, but only one matters.

  • Hide Form Elements Geographically@ContainedElementList ― what's that? It's an  convenient and efficient way to work with a group of form elements.

  • Find the Latest Date in a Table Subform — This might not be as easy as you suppose.

  • Make Report Breaks Easier to See — Subtotals In a lengthy report can be hard to spot. This enhancement makes them really stand out.

  • Son of WordMerge 2 — (See Son of WordMerge above) This version merges multiple database records, is runnable from anywhere in the application, and can be adapted to include subrecord data.


July 2017 issue summary. . . [order code APR 17]

Range Lookups vs. XResultSet ―Bridging the Gap / Nesting Commands
Is it possible to pare a 16-line program down to a one-liner and get the same result? It is, as you’ll see. But why would anybody need or want to do such a thing? After all, Sesame doesn’t have a programming limit as Q&A did.

Adding Subrecords via Programming
A  complex Sesame application can be home to a maze of form view subforms, some with embedded table view subforms. We needed to come up with a way to navigate such a structure in order to import a new row of data into a table subform located on the last (most recent) instance of a particular form view subrecord.

WordMerge Special Ops — Subforms, Etc.
WordMerge has a history of tweaks lengthy enough for a Subscriber Express Index search at insidesesame.com to turn up more than 60 tips and articles on it. We add to that list here by improving on an adaptation first explored more than 10 years ago — how to merge both parent and subform data into a Word doc. We expand on that original approach to make it more flexible and reliable.

WordMerge Unlimited Subform Records
Two issues with Sesame’s drop-in WordMerge program crop up from time to time. The first centers on what data can be included in a merge. The other has to do with the merge document itself. For merge docs requiring both parent and child data, it’s best to swap out the traditional WordMerge program for a simpler (and much smaller) program that can do the job with less fuss. In this expanded version of last month’s Son of WordMerge article, we show how to do that.


_______________You ask. We answer.


  • Sesame Version 2.6.3 — A few things to watch for in this latest release.

  • Windows 10 version 1703  — Microsoft's latest release has some really cool features. Anything else you need to know Sesame-wise? [See critical last minute update p. 25]

  • My Big XLookup Stopped Working! — Bad things can happen when adding one or more fields to a working XLookupSourceList command. Here's the only way we know of to isolate and resolve the problem.

  • Open Reports with Excel Redux — The 'Open in Excel' option for Sesame reports disappeared in MS Office a few years ago. Here's how to get it back.

  • 'Export' with Quick Reports — In a client/server environment, Sesame's Export feature puts the export file on the server. This quick 'n' dirty workaround will let you get at the data locally.

  • Retrieve Spec in XResultSetSearch Program — A refresher course on the proper use of @Date to retrieve a set of records.

  • Partial Sort of Dropdown Lists — Need a picklist to sort some of the items a certain way but not all of them. Here's the trick.


_______________Technical tidbits you can use today
 

  • Make Lookup Tables Default to Table  View — Don't you just hate it when you open a lookup table and it's in Form view? Here's how to make it default to Table view.

  • Programming All Those Date Fields ― Got a slew of date fields on a form and need to program them to pop up @Calendar automatically? This will save you time.

  • Copy, Rename, Move External Files On-the-Fly — When it comes to external file management, Sesame’s programming tools are somewhat limited. But where there's a will there's a way.

  • Get a Fifth Column for Your Lookup Table Subform — A Q&A Lookup Table migrated to Sesame gives you the same Key column plus the four related data columns. As occasionally came up back in the Q&A days, perhaps you could use an additional data column or two.

October 2017 issue summary. . . [order code OCT17]

'Stacked' or 'Snaked' Three-Across Reports
Sesame reports output your database information to HTML-encoded (web browser) format.  Picturing the encoding needed for a two- or three-across arrangement wasn’t hard. The problem was getting the report engine to accommodate it. Sample application included in this month's subscriber download file.

Custom Exporting with Mass Update
Sesame has perfectly good data export facilities. So why would anyone look at “Mass Update” as an alternative? When certain conditions and requirements are in play, there could be a method to the madness.

Drop Photos Into Forms On Demand
A bound Image field in a database shows pictures in the records. All well and good if those pictures always need to be seen. But since even a small image file can be 20K — more than all the data in the record — why not make them optional? Here's how to drop an image onto a form, only as needed, with a click of a button.

Charts & Parts Sales Trends at a Glance
Graphics and visuals communicate faster than words and numbers when comparing elements in an array of data. A company owner wanted to pull up any product record and get an instant comparative recap of its sales over the years of interest. It was the trend she was after — and a perfect task for Sesame’s StaticChart capabilities. Sample application included in this month's subscriber download file.

Homegrown Dialogs
Sesame comes with a variety of built-in dialogs, along with two do-it-yourself varieties. A company wanted a "simple report" that turned out to be anything but. A custom dialog was the perfect solution. Sporting auto-fill Product ID and Product Description dropdowns, along with a date range selector, it's  adaptable to a variety sales order, invoice or purchase order  needs. Sample dialog form included in this month's subscriber download file.


_______________You ask. We answer.


  • Programming 'Sometimes' Calculations — A method for adapting programming to deal with different situations, including temporarily disabling On Form Change programming.

  • Search with Multiple 'Ors'  with Options Or this and/or Or that. It can get real confusing real quick. This mini-tutorial aims to keep it simple.

  • Run a 'Category' of Reports from a Custom Menu — Simplified report running when the only difference is, say, the customer your running it for.

  • 'Undo' Record Damage Done by Improper Mass Update — In some cases it is possible to reverse a botched Mass Update.

  • Printing a Table View — If you've ever wondered how, here are some options.


_______________Technical tidbits you can use today
 

  • Is [fill in the year] a Leap Year? — A couple of math methods to determine if February of a given year has 28 or 29 days, along with Sesame's own ability to know when a leap year occurs. Application included in this month's subscriber download file.

  • Quick Dupe Check on Multiple Values ― To prevent entry of a value that already exists in the same field in another record in the database, you can set the field to Unique in the Read Only properties in Designer. To detect when two or more values in record match the same fields in the current or another database, this single-shot method can do the trick.

  • When ThrowFocus Won't — Under some conditions, Sesame's ThrowFocus command won't move focus to the specified field. Neither will a GoTo. A simple workaround takes care of the problem.