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  2017

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/February 2016 issue summary. . . [order code JAN 16]

Monster Picklist Magic
Half a second. That’s all it should take to fill an order entry picklist/dropdown with 25,000 part numbers, descriptions and prices from a Parts database. You don't believe it's possible? Sample application included in this month's download file.

Get a Quick Count... Redux
As soon as "Get a Quick Count of Each Unique Item in a Database" appeared in the November/December 2015 issue, we had a use for it. But our volume was much greater than the article presumed, so we had to make a few alterations. Sample application included in this month's download file.

Turn Q&A Field Restrictions Into Sesame Dropdowns
A Q&A for Windows system we took on recently was loaded with fields containing lengthy restrictions. They showed up on the Q&A/Win forms as ordinary text fields until the cursor was in them. Then they morphed into dropdowns similar to Sesame combo boxes. Because translating the databases produced plain fields in Sesame, we had our work cut out converting them to combo boxes. But we managed to simplified it.

When XLookup Beats XResultSet
Who'da thunk? It depends on certain factors. Grabbing the data from the source database is one thing. It's what you do with it after that point that determines whether XLookupSourceListAll is the best tool for the job.


_______________You ask. We answer.

  •  

  • Windows 10 Update Issues (cont'd) — If you're on Windows 10 or are planning to be, this is definitely worth a read.

  • Reconcile Issues  — A mini-primer on the right way to Reconcile changes to a database.

  • Choose Customer by Name or Number — A company needed both options rolled into a single simple user action.

  • Multiple Fonts, Formatting and Colors in Static Text Fields? — Sorry, no can do. But a custom form can fill the bill nicely.

  • Attach Documents to Sesame Records — Design a little no-overhead facility to simplify attaching external files of all types to database records. Sample application included in this month's download file.


_______________Technical tidbits you can use today
 

  • Split vs. @AccessStringArray — No contest when you're crunching big data.

  • Get Your (Whole) Combo Box Back — A lengthy list can extend down behind the Windows task bar where the final item can't be read or reached. Easily fixed.

  • Processing a Huge @XLookupAll — A handy alternative to the @AccessStringArray bottleneck.

  • QuickMacros to the Rescue — Ever try these little guys? Handy. But you gotta keep 'em simple.

  • View/Zoom for 'Reports Too Small' Complaints —  Amazing that some folks don't know to work their browser to make text more legible.

 


March/April 2016 issue summary. . . [order code MAR 16]

E-Bill with Outlook
WordMerge for Word docs has been around since Sesame 1.0. Why not an OutlookMerge for emails? That is, a direct Sesame-to-Outlook merge with its own data source file for the To, Subject, Message and Attachment. Is that even possible?

Get External File Info with Regex
If you needed to know when a file was last modified, you’d navigate to it in your file system, click on it, and Windows Explorer would tell you. But how would a Sesame database with linked files in the records get that info on its own?

Automate Alphabetic Searches
Back in the day, Q&A for Windows came bundled with a Contacts database that sported tiny A, B, C through Z command buttons for shortcut searching. A company using this scheme in their own database for 20 years wanted it kept in Sesame. Sample application included in this month's download file.

Restrict that 'Universal Search'
Sesame's Universal Search is a terrific tool. But in some cases it can be a bit too universal. Suppose you've got a multiple set of fields for contact name, phone and email and you want the records where a contact's name might be in any of the name fields? Here's a handy shortcut that's simpler than the Meet Restrictions / Any option.

Rip Export Files with Regex
When exporting data you get raw formats — money with 4 decimal places, numbers with 8, dates in YYYY/MM/DD and times of day as 24-hour hour:minutes:seconds. Don't care for that? If only there was a way to make the export use the layout formats. Sample application included in this month's download file.

'Print to Disk' Reports ΰ la Q&A
Some "can't-live-without" Q&A features have no Sesame counterpart. Printing a report To Disk is one of them. Sesame, of course, prints all reports to disk — but in HTML, not plain text. So we had to make it walk and chew gum at the same time.

Match.com — Sesame Style
Searches — we use them all the time. They're among the most important aspects of a database. In Sesame, they're pretty straightforward until the data you’re searching for contains special characters.


_______________You ask. We answer.

  •  

  • Easily Mark/Unmark Fields as Read-only — Sesame taking too long to make a bunch of fields conditionally read-only? Get the lead out.

  • Custom Menu Buttons  — Miss these few details and your your custom main menu programming will test OK but won't work.

  • Smart Separator for Multiline Notes fields — A way to identify who wrote a particular note in a sea of notes and make all the notes easier to read. Sample application included in this month's download file.

  • Use Traffic Lights to Indicate Filled Tab Data — Avoid profitless clicks. Have your form tell you in advance which tab pages are barren.

  • Don’t Backup or Image Files During Sesame Use — Yet another way to lock everybody out of the database.

  • When Unlocking 'Doesn’t Work' — Nine times out of ten, here's your culprit.


_______________Technical tidbits you can use today
 

  • Print a Report 'Quietly' — You run a report and nuthin' happens. Maybe that's just want you wanted.

  • Can't Reformat Report Elements — After translating, a report element or two can be mis-formatted, but Sesame won't let you fix them in the usual way. 

  • Auto-Delete All Files In a Folder — Don't try this at home.


May/June 2016 issue summary. . . [order code MAY 16]

Quick Reports Simplified
Sesame's Quick Reports are popular with folks who take the time to learn the ropes. But there are a few things — mostly in the cosmetics department — that could do with a little improvement. This handy popup makes everything easier with its intuitive interface for both designing and running eye-pleasing reports and without involving Sesame Designer.

Add Page Headers to a Pagebreak Report
With a page-break report, the idea is to start a new page when a primary value, such as a customer's name or ID, changes. But if you go for what seems like the right report section — Page Header for a page header— you could be left scratching your head. We tweak the report's HTML a bit to get custom page headers, even multi-line ones. 

Great Big String Arrays (And Dynamic Ones, Too)
Super-sized string arrays can come in super handy when you need to isolate, retrieve, and process a large batch of records. We tell you the advantages, disadvantages and options you have to generate those big string arrays lickety-split. Sample application included in this month's download file.

Eye-Catching Quick Reports
Like your fast-and-easy Quick Reports but wish you could spruce them up a bit? By running them from a command button instead of the usual default way, you can have Sesame transform them practically into works of art. Sample application included in this month's download file.


_______________You ask. We answer.


  • More on Printing with Alternate Forms — We review this method of form printing and work around a few issues to automate the task and produce better-looking results.

  • Importing Excel Data with Leading Zeros  — Invoice numbers, account numbers, zip codes can have leading zeros that you can't afford to lose. But when you  save out an Excel file to .csv format to import into your database, Excel strips them out.

  • Pseudo Boolean Fields Solve Problem — You need some Boolean fields to do a little multi-tasking but Sesame's strict data-typing is getting in the way.  Any options here?

  • Import Incorrectly  Formatted Fields From Q&A — Tips and tricks for when you're faced with migrating Q&A data that isn't in a proper or consistent format.

  • Make Sesame Accommodate Monitor Size — Back to basics on this one.

  • Word Merge Docs for Multiple Users — MS Word won't permit merge docs or merge data files to be shared. Anything you can do about that?

  • Translated Database Lookup & Calc Issues — Neither is working right and you're at  wits end trying to find where the #@%$! problem is.


_______________Technical tidbits you can use today
 

  • Make a Livelier Main Menu — A few bells & whistles never hurts. Try out this nifty little option to liven up your application's main menu.

  • A New WordMerge for Subforms — This version of the venerable Sesame drop-in uses XResultSet for the data source and is designed for processing and outputting line-items-type subform data to Word merge docs. Definitely worth a look.

  • Report Borders — Thin is In — Like your reports with borders, just not too thick? How about those thin 1 pixel borders that Microsoft Excel uses?


July/August 2016 issue summary. . . [order code JUL 16]

Map 'Em — Sesame Does Google Maps
Plumbers, appliance repair, on-site computer service, cable guys, hauling, security system technicians, local delivery services. They all travel to your home or office. Travel time is a big issue for companies with outside service people.  So the trick is to keep each service person working within the smallest geographical area possible on a given day. What better way to see the big picture than a popup area map with all the day’s calls pinpointed by street address, color coded by service person, and showing each call's start and finish time? Sesame and Google Maps can give you the big picture with a click of a button.

Excel Data Import Problem Solved
Microsoft Excel is widely used for exchanging data. Though Sesame can’t import Excel files, it can import files saved out of Excel in the common .csv (comma-separated values) format. Problem is, Excel is quirky with numbers with leading zeros — zip codes, account codes, invoice numbers, or the like. It strips out those leading zeros. We've been looking for a solution to this for years and have finally found it.


_______________You ask. We answer.


  • Aging Database Records and Reports — A reader wants to know how his database records can show the correct aging when he opens them for viewing.

  • Programming Messed Up After Translation  — Big trouble if the Q&A database you translated is named KEYS and contains a field of the same name.

  • Save Record Mistakenly Entered in Search Mode — We've all done it. Is there a way to capture all that carefully entered data and get it into an Add Mode record?

  • Sorting by Street Address — Some types of businesses require this. Problem is,  addresses aren't conducive to normal database sorting. You need to get inventive.

  • How Come My Lookups Don't Work? — We go back to school on the five likeliest goofs.

  • Translated Programming Not Working — Here are the most common culprits.


_______________Technical tidbits you can use today
 

  • Set No Repeat on Multiple Report Columns — Getting a Sesame report column to not repeat values is easy. But what if you want to nix repeating values in adjacent columns as well?

  • Quick 'n' Easy Subform Notes Review — Reviewing notes in a Table Subform can be slow and awkward when some of the notes extend beyond the field's width. Get your game on with this simple solution.

  • Make a Popup Appear Over Subtable — Use this trick to make an @PopupChoiceList or @PopupMenu reliably appear right at the subform where you're working.

  • Paste a Report Into Excel — Run a report, have it start Excel for you, then press Ctrl-V.


September/October 2016 issue summary. . .[order code SEP 16]

Tracking Down Double Bookings
A midwest country club needed an easy way to check for inadvertent double-bookings on their banquet and meeting facilities. In Q&A they did it the old-fashioned way — searched the database for the date in question and eyeballed the records, sometimes missing actual conflicts. In their new system they wanted a pop-up screen that would show all the room bookings for a date in one place.

Speeding Up Subform Operations
Performance needed to be improved for a company whose people occasionally worked over a VPN with a Sesame app in another city. The local users had no issues. But the remote users were seeing dog-slow performance in one particular database. Find out what we learned and what we did to improve things.

XResultSetSearch With 'And' and 'Or' Criteria
A big advantage of an @XResultSetSearch over an @XLookup... style search is that the former lets you specify multiple search criteria on multiple fields for tighter control over which records are retrieved. You can specify an AND search or an OR search — but not a combination of the two.  Or can you? That's what we needed and here's how we got it.

Colorizing Reports
Because Sesame makes reports so easy to run and outputs them to the browser by default, they’re often not printed, just reviewed on-screen for whatever information might be needed. Adding a bit of eye-catching color to them can make these view-only reports easier to read and more informative.

Reports — 'Cancel Subcalculations'
Q&A reports with subtotals and totals will sometimes have a field/column with an AS, CS in the design. This means: sort on this column but do not break to print any subtotals for it (cancel subcalculations). After translating the database, such a report might not wind up with a Group Header/Footer for that AS, CS column in Sesame. Here's how to fix that.

Warning! — Sesame is Already Running
A company was having trouble with multiple Sesame sessions running on a shared 'hot-desk' computer on the sales floor. For reasons you'll see, this could spell trouble. At the very least, users needed to be warned of the situation when starting a new Sesame session..


_______________You ask. We answer.


  • Windows 10 Anniversary Update & Sesame 2.6.2 — A significant Windows update along with a new Sesame release. Any issues here?

  • Multiple Condition Lookups  — Need to run an XLookup on multiple search criteria? Just how do you do that?

  • Stuck On a Tab — Tabbed forms can be tricky to navigate. Here's some help on that.

  • On-Form-Entry Issues — Why you don't want a combo box as the first element on a form.

  • Make a Drop-Down Search List — A follow-up on the above On-Form-Entry issue. How to mimic a combo box that can be used at the Retrieve Spec for searching. Sample application included in this month's download file.

  • Get the Initials from Full Names — A little Mass Update that fills an initials field from the content of a full name field.


_______________Technical tidbits you can use today
 

  • Time it Precisely — Sesame can time a program/procedure down to the second. Only a nitpicker would want tenths of seconds.

  • @PrintAForm — Reduce Margins to Increase 'Text' Size — An unusual solution.

  • Min/Max Search New Meaning — A wee gotcha that might have you scratching your head one day.

  • Sesame Clipboard Mystery Solved — A Word add-in was emptying the Sesame-filled clipboard without so much as a howdy-do.


November/December 2016 issue summary. . . [order code NOV 16]

Auto-Emailing the Day’s Work Orders to Outside Techs
A security company was looking to run its Sesame service calls/work order report at 7:00am each morning, copy it up to a private folder on its web site, then email the web link to its outside service techs so they could review their day's work assignments. No big deal except that the company wanted all this to happen with a click of a button.

Insert Blank Rows Anywhere in a Table Subform
A company in the wholesale cosmetics business wanted cosmetic invoices, too. They needed to be able to plant a blank row in the invoice lineitems table on-demand when adding items. They also needed a way to insert a blank row (or two or three) anywhere in the table after the items were all added, and have the positions of those blank rows maintained. Can you say "tricky"?

2.6.2 Update — Supercharge Any XResultSetCreateNewRecord Routine
If you've ever run an @XResultCreateNewRecord procedure on a fairly large volume of records, you might have been disappointed with the performance. Now, with the latest Sesame update, all that has dramatically changed. We're seeing a 10X speed improvement.

Go Green with this Drop-in Report Gizmo
Because Sesame makes reports so easy to run and outputs them to the browser by default, they’re often not printed, just reviewed on-screen for whatever information might be needed. Adding alternating row shading (like green bar paper) can make these reports classier-looking and easier to read.

E-Billing with Outlook Revisited
A "wish-we-had-known-this" update to our March/April 2016 article rounds out  the procedure.

Debugging @Sendmail Issues
With Sesame's ability to fire off emails under the hood, you need as much debugging info as you can get when something goes wrong. Here's how to get immediate context-sensitive failure feedback.

Predicting Report Filenames
Would you like a particular Sesame report to come up in Excel,  Open Office Calc, or another program instead of your browser? Now you can make that happen.


_______________You ask. We answer.


  • Sesame 2.6.2 Now Available — What's in the new release? Any issues to look out for?

  • Save Programming Before Making Changes  — Every Sesame developer has made this mistake at least once. And it can absolutely ruin your week.

  • Combining On-Form-Change Programming — Roll all of your OFC programming into a single slot and you'll (1) get better control over what executes when and (2) have an easier time of it when programming updates are needed.

  • Training People How to Search — Sesame newbies can destroy valuable data in their fumbling quest to find what they're after. Here are some tips to make life easier for all concerned.

  • Tab Design Basics Part II — A continuation of last month's primer on incorporating tab pages into your form designs.


_______________Technical tidbits you can use today
 

  • Creating a Who-Ran-a-Report-When Log — Interested in knowing who's running your sensitive reports, from which PC, and when, without them knowing you know?

  • Sesame and SD Card Photos — How does Sesame perform when a database gets its images from a modern compact SD card instead of the computer's hard drive? You might be surprised. We were.

  • More Vertical Space for Report Rows — Reports with lengthy narrative-style text in a  column can look cramped and be tough to follow. The solution is to add some vertical spacing between the report rows. It takes only a minute.

  • Numbering Report Rows — A company wanted the rows in some of its lengthier reports to be consecutively numbered. How easy is that?

  • URL Encoding Sesame Style — Should you ever need to pass some Sesame-generated data to a web page for processing, you'll need to "web"-encode it. Here's a program that does the trick.