Getting the Most from Lantica's Sesame Database Manager
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . December 13, 2018  1:24 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   2017   2018

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.

January 2018 issue summary. . . [order code JAN 18]

Linking Emails to Database Records
A company was looking to resolve some email-related issues and wanted to know if their database system could be leveraged in some way to help out:

 “Is there any way we could access our sales engineers’ past email exchanges directly from our PLANTS (customers) records?

Indeed they could. With Plant XYZ’s record displayed, email exchanges with personnel there can be brought up for viewing with a button click or keyword search.

Control Data Entry with Form Attributes
Need to tighten things up during data entry ― make users follow a prescribed pattern before a record is allowed into the database? This goes beyond mere restrictions. It's like having an intelligent assistant guiding the process. Sample application included in this month's subscriber download file.

Photo Aging (Gracefully)
For databases that store photos of people, aging can be a factor in photo management. So a company might want to round up the folks whose photos are out of date and take new ones. But how to tell how old a photo on file is unless you’re looking at the person and their photo at the same time? Easy ― have Sesame ask the operating system.

How to Keep Related String Arrays in Sync
Sesame string arrays make handy organizers. They can assist with a variety of processing tasks by keeping track of multiple data values that can be manipulated in programming. Unlike formal arrays, though, string arrays are strictly one-dimensional. Or are they? String arrays can be grouped or “stacked” to make what amounts to a multidimensional array. This kind of power adds a new dimension to what you can do in Sesame.

Auto-Updating Outdated Product Descriptions
A company had improved on many of its product descriptions over the years. Now in Sesame, the law of unintended consequences caught up. To accommodate new features, all their old lineitem descriptions had to be brought up to date with the latest ones, and any future description changes would need to automatically update the previous ones.

Create a Mini Data Dictionary
A school district was being forced to standardize its databases on a product other than Sesame. Ironically, Sesame had the tools to make the conversion far less difficult than it otherwise would have been.


_______________You ask. We answer.


  • Find a Date Range in a String of Dates — It can be done. Here's how.

  • Popup Multiple Fields from External DB — When adding a new invoice, a company needed a window on the customer's record.

  • Must-fill Fields in Add Mode Only — When standard field restrictions won't do.

  • Retrieve Not Working in Keyword Fields — It worked OK in Q&A. Why not in Sesame?

  • Checking Checkboxes — From elsewhere, how do you know if they're checked?.

  • Archive Database Translation — Sensible archiving options for multiple translated databases with the same structure, reports and so on.


_______________Technical tidbits you can use today
 

  • Class-ify Those Reports — A quick-'n'-easy drop-in makes reports easier to follow.

  • Using @Frac for Varying Quantity Formats ― You've got Quantity field that must be formatted for 2 decimal places but you don't want a .00 to print.

  • When While-While Loops Beat For-While Loops — Your loop programming could be running far longer than it should.

  • Reports, Excel and Carriage Returns in the Data — You open a report in Excel and see more rows than the report actually has. What's up with that?.

 


April 2018 issue summary. . . [order code APR18]

What About that Recent Applications List?
A grumble is sometimes heard from folks new to Sesame over what winds up on their Recent Applications list. Handy when working as expected, this shortcut-like interface feature opens an application with a click. But it isn't quite so handy when there's no way to remove unwanted testing-only and sample apps from it. We take a fresh look at this and offer a sensible solution that doesn't involve turning the feature off altogether.

Make Reports Reveal Their Retrieve Specs
The Retrieve specs that produced a report don’t show in the report itself. But they can be helpful to know, particularly when those reports might be passed along to others. We show an easy way to print the retrieve specs right in the report's header so there's no doubt as to exactly what the report covers.

Synchronizing Key Values In a Parent/Child Structure
A new report exposed an underlying problem with a database's data structure. The report was pulling subform data from the wrong parent records. The fault wasn't in the report but in the database ― the report was merely exposing it. An investigation uncovered what had happened and when, and a finely-tuned Mass Update set things to right.

Translating Wonky Date & Money Fields (or How Not to Lose Data)
Q&A allowed just about anything in Money and Date fields. Users could simply hit Enter to blow off Q&A’s warnings and go on their merry way, leaving corrupted data behind that could be problematic in searches, reports, merges and other output. And even more problematic in a Sesame migration. We suggest how best to deal with something like this at both ends.

Create a Customer Activity Snapshot
A wholesale products company wanted to give its sales people instant access to a summary of a customer’s buying habits, typical order volume and recently-purchased products. Not a report, but a tightly-organized capsule view, in a popup, available from the customer’s record with the click of a button. Sample Application included in this month's subscriber download file.

Archiving Parts of a Huge Database
A recent Q&A-to-Sesame system migration brought up some size-related concerns ― starting with a new application that would be in the half-a-gigabyte range. This case study details how the app was reorganized to minimize its otherwise massive footprint and make it more efficient, including the ability to archive or restore records with a button click.


_______________You ask. We answer.


  • How to Check/Change the @Number — Sesame allows just one @Number per application. But where is it? How do you check it? How do you reset it? And why you have to be careful when doing anything involving it.

  • Internal Password-protected Operations — A company wanted to password protect certain database operations independently of the overall security scheme. Sample application included in this month's download file.

  • Sesame / Word MailMerge Rules — A detailed recap of the right way to merge print your Sesame data with a Microsoft Word merge document.

  • Windows 10 Updates Might Delete Scheduled Tasks — If you use a Scheduled Task to start Sesame Server and automatically unlock your application(s) following a server reboot, you'd be wise to incorporate this new info into your methodology.

  • Turn Off Anti-Virus Programs for Sesame Files — Tips on excluding certain files from A/V scanning to improve Sesame's performance.

  • Two Different XLookups From Same Element — How to configure a Sesame gizmo to run one of two different lookups depending on whether the lookup key is text or numeric.


_______________Technical tidbits you can use today
 

  • Hide All Reports in a Database — Or hide just certain types of reports, such as those for management eyes only.

  • @LocalFileDialog ― How to Find Multiple File Types in One Go ―  A clever way to  make Sesame show, say, .doc, .docx and .docm Word files in a get-a-file run. Adaptable to isolating different image file types as well.

  • Make a Report Total Text 'Money' Values — Money amounts in, say, a combo box, are really text values. So how to get a Sesame report to treat them as money so they can be totaled?

  • A Simple Report Auto-Sizer — A nifty way to enable farsighted users to make reports display for them in a larger font size.

 


July 2018 issue summary. . . [order code JUL18]

Databases-To-Go A Case Study
Data disasters happen. From a fire or natural disaster to Grand Theft Computers or a ransomware attack that hits even the off-site backups. It's hard to beat having an up-to-date copy of the database in hand off-site on a portable medium. How about you pop a thumb drive into your workstation, click a button, and off you go with a fully functional, up-to-the-minute copy of the company's database in your pocket.

Filtering Reports with Subrecord Data
The report the comes with the sample Charity Donors database includes all the subrecords whether you want them or not. No way to specify a date range like the previous month, last year or year to date. Jon McLaughlin's new report fixes this. And the technique is adaptable to most any form/subform database. Sample application included in this month's subscriber download file.

What's Up With the Fonts?
Sesame forms, fields, labels, buttons and reports are all custom-fontable. But fonts differ from other design attributes — they can be substituted by Sesame in ways that might never be seen by the application designer. We show how to find out (under the hood) which workstations  have which fonts on them, along with a program that scans all the forms in an application and comes back with a list of all the fonts used in each one.

Q&A Crosstabs to Sesame Totals-Only
Sesame skips over any crosstab reports when translating a Q&A database. But when they're fairly simple crosstabs, as they often are, chances are good that they can be reborn in Sesame's Report Designer with a similar summary format and the same totals.

How to Import XML files
You  know what plain ASCII or CSV files are — a more or less universal export/import format. But a more modern type of plain text export/import file format called XML is seeing increasing usage in companies these days. Sesame's import facilities don't accommodate XML, but an additional easy step will do the trick.


_______________You ask. We answer.


  • Importing Q&A Data — Q&A's export options can be troublesome when the data is destined for a non-Q&A application. Here are some tricks developers use to migrate data from a Q&A database to Sesame.

  • Round Up to $5 — An unusual requirement to be sure. But it can be expertly done. Sample application included in this month's download file.

  • Improving the Drop-Down List — Don't let quirky drop-down behavior impede your performance. Try this innovative replacement option.

  • Check Multiple Fields for Similar Records — This example combines the date and  purchase order number to flag two or more records with the same ID number.

  • "I don't have a Thumbs database, but there's one in my Sesame Data folder!"

  • Semicolons in Lookup Data — Ouch! —What to check for if an XLookupSourceList program is pulling in some but not all of the data, or some of the data is landing in the wrong fields.


_______________Technical tidbits you can use today
 

  • Find Last Month's Records with a Fixed Retrieve (plus make your Saved Retrieves run a whole lot faster on large result sets).

  • Effortlessly Trash the Database ― A no-brainer. Just follow these easy steps.

  • Avoid Special Characters in String Arrays, Too — and you thought this was just for the key values in lookups.

  • //Rem Your Retrieve Specs — Why toss out a useful spec you might need again when you can simply temporarily disable it.

 


October 2018 issue summary. . . [order code OCT18]

Routine Report-RunningA Case Study
After running their reports the same way for 20 years, an engineering company’s transition to Sesame turned up a few rough spots. Lucky for them that their new system had options and capabilities far beyond their old one, including ways to make their routine report-running easier and more flexible than ever before.

Find Past & Future Dates by Whole Months
A tough date math issue came up. A date range was needed for a lookup that would find all the records where the date in a date field was 15 months to the day back from the record’s date. As if that wasn't enough, a routine was also needed to find an expiration/warranty date in years (2 years, or 24 months, for example) based on a known initial purchase date.

Anchoring Merge Fields for Precision Printing
A Q&A-to-Sesame migration turned up a printing challenge. Work orders for the company’s outside service people had been printed the same way for 20 years — on a tractor-fed dot matrix printer to rather expensive custom-designed three-part NCR-style forms. Now the company was in Sesame with Word Merge. The challenge was how to print the data to precise, tamper-proof X and Y positions on the work order.

Counting Lines, Words & Characters in a Multiline Field
The data in a multiline field had to be limited to 16 lines because that’s all that would fit on the output medium. Too much text  would overwrite other areas of the form and could trigger an unwanted page break. A line counter was added just below the field to alert the user when there was too much text in the field.

Printing Records to PDF
Common in the Q&A days, a company might have a purchase order database designed to look and print like a PO that they would snail-mail or fax to the supplier. The same for sales orders and routine invoicing. Increasingly these days, suppliers and customers accept or prefer an “electronic” document (a PDF).  We walk you through the process of generating a PDF from a database record.


_______________You ask. We answer.


  • Where Are My Merge Records? — No problem. You're just missing a little something.

  • Combine Multiple Merge Letters to Email — Is it possible to group merged documents in a way that they can be emailed in a single file?

  • Sesame and ISP Change — What you might need to know should you ever change your company's Internet service provider.

  • Export/Import Multiple Apps — An answer to the question, "How do I keep two or more apps in sync"?

  • Mass Update, Include All the Rules — Mass-updating a field can put related fields out of sync with the changes. Look before leaping.

  • Multiple Apps / Same Server  — What could possibly go wrong?

  • Set a Default Sort in a Database — A handy refresher on an all too common request.

  • Recover Lost Records From a Backup — And they just happen to be subrecords.

  • Merge Docs on Server — Yep, that's where you want 'em in a multi-user environment.

  • Where are My Fields? — "Let's see. The last time I was working on them...."


_______________Technical tidbits you can use today
 

  • See More Data in Table Subforms  ― The easy way to see lengthy information in subrecords without having to reach for the F6 key. (This trick is really cool.)

  • Read-Onlies & Restrictions ― Records were made read-only after posting them, but they still had to be browsable. Problem was, the browsing triggered restrictions.

  • The Strange Case of the Cranky Retrieve Spec — A curious issue came up with a report Retrieve Spec that worked fine in Q&A, but not so fine in Sesame. Desperate times call for desperate measures.

  • Rename/Copy/Delete a Poorly-Named Report — If you managed to translate a Q&A report with a slash in its name, trouble will follow should you ever need to rename, copy or delete that report.