Getting the Most from Lantica's Sesame Database Manager
Getting the Most from Lantica's Sesame Database Manager |
|
| . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . November 23, 2024 2:05 am. PST | |
Introducing WordMerge Lite
"WordMerge Lite is really pleasant to use." —Eugene L. "Just spent the weekend incorporating WordMerge Lite into all our Sesame applications. It's going to make my life so much easier!... Thanks for an exceptional contribution to the Sesame community. [more] —Don N. [All comments unsolicited] |
A shorter version of the following review, which does not include the additional
sections listed below, appears in the February 2006 issue of Inside Sesame.
If you've already read that review, you can use the links below to skip to any section in
which you may be interested:
WordMerge, one of the most versatile ways to merge your Sesame data with documents and labels, has been through a number of revisions since it appeared in Appendix 2 of the Sesame 1.0 Programming Guide. The "Subscriber Express" index at www.insidesesame.com/indexes/index_search_form.asp (run a Universal Search on "wordmerge") will list the Inside Sesame back issues containing details on these changes. How WordMerge works Setting up WordMerge With the first step done, you then run the WordMerge program to generate an initial data source file to link to your first merge doc. Problem is, WordMerge won't run until you have at least one Word doc where WordMerge expects it. And you can't finish a merge doc in Word until you have that data source file to link it to. It's a kind of Catch-22. You have to put a Word doc (any Word doc will do) in the folder where WordMerge expects it, then run WordMerge and select it. Only then — now that you have a data source file to link to — can you finish "configuring" your first merge doc. Now finalize the merge doc by linking the data source file to it, inserting the merge fields, and becoming familiar with the various buttons and options on Word's Merge Toolbar. If any of the merge fields are dates, numbers or money, you're still not done. You'll need to apply Word formatting switches so dates don't come out like "2006/02/09" and numbers and money don't come out as "147.500000". Later, should you need to add some previously existing or new fields to WordMerge's data source file, you have to be careful how you go about it. A Word merge doc will reject a data source file that doesn't conform to what it expects. All this is lot to ask of someone who isn't very accomplished in Word or Sesame. WordMerge Lite simplifies things Calling it "WordMerge" Lite isn't really fair since, unlike WordMerge, all the merging is done in Sesame instead of in Word. But WordMerge Lite still involves Word documents, even though they're more like Q&A for DOS merge documents in the easy ("Lite") way you set them up to merge your data.
Like WordMerge, you add a Merge Print or similarly-named button to your form in Designer, then paste in the WordMerge Lite program. But with WordMerge Lite that's all you need at the Sesame end. And what you have to do at the Word end is vastly simplified.
A sample database and Word merge doc are included in this month's subscriber download file. In our informal testing, WordMerge Lite worked well in Windows XP with Word 2000 or later. We'd like you, Dear Reader, to give WordMerge Lite a try using the sample database and merge doc. Let us know how you like it, and tell us what, if anything, you think would improve it. Big Difference #1 Uses Q&A-like merge field tags (The WordMerge Lite program prints a field names reference list you can use when adding your field tags to your merge docs.) At the Sesame end, there's no need specify which form elements (fields) to include in the data source file because no such file is involved. There's no need to go into programming or update an elements list to add, remove or change the fields to include in your merge docs. WordMerge Lite is natively aware of all the elements on your form and simply replaces the field tags in the target document with the corresponding data from those fields in your Sesame records. If you want to include a new field, you simply add the tag for it to the applicable merge doc(s) And WordMerge Lite will merge to it. No changes are required at the Sesame end. But how does WordMerge Lite merge data with a doc that isn't a real Word merge doc? We'll get to that further ahead. For now, here are a few other ways that WordMerge Lite simplifies things. No more Word 'formatting switches' Supports double quotes and carriage returns More previewing/printing options Automatic saved copy of the merged doc Supports merged variables XLookups are just another mergeable variable. You can run any "@X"-family command from the WordMerge Lite program, manipulate the retrieved data to suit, and replace the corresponding merge field in the target doc with it. There’s a section in the WordMerge Lite program reserved for custom merge variables like these. In the sample database's WordMerge Lite program, you'll fine a commented out @XLookupAll demonstration example. When run in Sesame Runtime, the *vMyVar1* merge field in the WordMerge Lite Sample.doc will get the vMyVar1 variable containing the last names of all customers in the database whose last names begin with the same letter as the merged customer, with any duplicate names removed. Merged image support WordMerge Lite can even scale your merged images on-the-fly. By default, WordMerge Lite's vImgScale variable scales merged images to 75% of their actual size for merge printing, but you can adjust the scale factor by setting a larger or smaller value. Setting vImgScale to 100 merges your images at 100% of their actual size. Personal settings editable on-the-fly
WordMerge Lite's merge document selection menu includes an Other Options submenu with selections to view current personal preferences, change a preference, and print a list of element names to use as a guide when creating their own merge docs in Word. WordMerge Lite easier to deploy If you're the in-house developer at your company, you'll find that WordMerge Lite enables co-workers to easily create their own merge docs after a brief orientation. WordMerge Lite lets you change a number of key settings on-the-fly without having to go into the programming in Designer. You can easily change settings such as where your merge docs and any merged images are stored, the scale (size) factor for any merged images, and whether to display your output in Word or your browser. Big Difference #2 When you've finished composing the document in Word and have added your merge tags, you save the doc as a regular Word file in the usual way. Then you do one more thing — you use Word's Save As Web Page option to save it again, this time as an ".htm" file. It's this .htm or "Web page" file — a copy of the Word doc — that WordMerge Lite merges your data with. Sesame can't merge data with native Word files because they're binary files. Open a Word .doc file in Notepad and you can't make sense of it. Neither can Sesame. But Sesame can read and process a text-only .htm file created by Word because htm/html (aka "Web page") files are nothing more than ASCII text. Word 2000 and later versions do a good job of rendering native Word docs as .htm files. The .htm versions contain a lot of Word-centric HTML encoding — so that they'll look the same when opened in Word as the original Word docs they came from. But that's beside the point because you won't be using these .htm files on any Web site. All WordMerge Lite uses them for is to merge your data for printing. It's these .htm file versions of your Word docs that appear on your WordMerge Lite picklist (sans their .htm extensions) when you click the Merge Print button. And they're what the WordMerge Lite program chews through to produce your final merged docs. The only additional step involved is saving the Word doc as a Web page .htm file after you've designed or redesigned it. So if you can train your client (or co-workers) on how to add the merge tags to a doc then save it (no-brainer), then save it again to an .htm file (a couple of clicks), that's likely to be all the training they'll need. WordMerge Lite goes to work when you select one of your merge docs. It makes a copy of the doc then replaces the field tags with the data from your record(s). It saves the resulting merged doc with either a ".doc" or ".htm" filename extension (your choice) then launches Word (or your browser) to display it for printing. WordMerge Lite doesn't touch the original Word doc. It's always there for you to edit, save, then resave As Web Page to update the corresponding .htm file. WordMerge Lite it makes its own internal copy of the .htm file, then generates a new merged file with "_merged" tacked onto its filename. Unlike WordMerge, when printing to Word with WordMerge Lite, you won't get that irksome prompt to save the merged doc when you quit Word after printing. But you can still save a copy of it if you want. For all but the most complex merge docs, WordMerge Lite's setup is fast and easy. You simply compose the document in Word, adding your merge tags, and apply any fonts, enhancements or layout options you like (including Word tables). You save the doc to the specified folder, then save it again As Web Page with an .htm extension to the same folder. You can now merge print to it. When the original WordMerge is better
For most merge printing needs, WordMerge Lite is far easier than WordMerge to get going with, particularly if you've never used WordMerge or you're not skilled with Sesame or Word. Trying out the
sample files Wordmerge_Lite.dsr/.ddt is a modified version of the sample Customers.db application that comes with Sesame. A multiline field and an image field have been added to it (no images are supplied) so you can test how WordMerge Lite works with these if you like. You can try out WordMerge Lite without having to change any of its programming variables by following these steps:
The document should open in Word with the data merged. It might not look quite right because Word will probably be in Web Layout view, so click View / Print Layout to see what it'll look like when printed. Go ahead and print the document if you like.
The three merge docs should appear in Word. Click View / Print Layout to see what they'll look like when printed. (This view shows the page breaks as well.) To print a merged image to either .doc or .htm output, add one to a record's Photo field, then print the recordto the WordMerge Lite Sample merge doc. If the image prints too large or small, adjust the Set Merged Images Scale option then retry. If the image doesn't print at all, make sure you've got the path right. Double-click on it in the record to find out the image element's value, such as pics\bob smith.jpg., then use the Set Merged Images Path option to specify the beginning portion of the path, such as c:\sesame\ for images stored in c:\sesame\pics. To see how the merge doc looks in your browser, click Other Options on WordMerge Lite’s menu and select Set Output to .doc or .htm. Replace the .doc with .htm and click OK. Now merge print the doc. When it appears in your browser, select File / Page Preview to get a realistic view of what it will look like when printed. Any header and footer codes in your browser’s File / Page Setup will also print, and your browser’s page margins may supersede those in the doc. If you don’t want to change your browser’s Page Setup for WordMerge Lite, you can always do your merge printing in Word. You’ll probably find, though, that sending your merged output to the browser will be noticeably faster since browsers are generally more nimble and start faster than Word. After all, the .htm file you’re opening is native browser file anyway. After you’ve played around with the sample files enough to see how WordMerge Lite works, it’s time to start creating your own merge docs. Simply compose them in Word, adding your merge field tags as you go. If you're using asterisks to define your merge fields, Word may attempt to "AutoFormat" them, removing the asterisks and converting the text to boldface. You can turn this off via Format / AutoFormat / Options. When adding your merge fields, be sure not to leave spaces between the element name and the start and end delimiters:
When enhancing a merge field, be sure the enhancement includes both the start and end delimiters:
When you’re done composing the document, run a spell check on it. For any of your merge fields that Word flags as misspelled words, click the Add to Dictionary button. Save the document to the C:\Sesame\WMLDocs folder (or whatever folder you’ve
specified for your WordMerge Lite docs), then save it again to the same folder as a
"Web page" .htm file.
Big
Difference #3 — embedded images You've probably pasted or inserted an image in a Word doc — a .bmp, .jpg or .png file of your company logo, perhaps — then resized the image to fit. When you saved the doc, that image became a part of it, embedded in the doc. Now, WordMerge Lite can't work with your Word docs directly because they're binary files. WordMerge Lite works only with the ASCII .htm files that you create from your Word docs. But ASCII files, by definition, can't contain images — they can only contain links to them. So how does WordMerge Lite deal with this? When you save a Word document containing an embedded image to an .htm file (Save as Web page), Word automatically creates a subfolder under the folder where the document resides. It coughs up a copy of each embedded image in the doc and saves it to that new subfolder. For example, if Prospects.doc (containing an image) resides in a folder named MergeDocs, Word will name the new subfolder Prospects_files. The images that Word extracts from Prospects.doc and places in this new subfolder are assigned new names by Word, names like image001.jpg, image002.jpg, and so on. None of this alters the original Word doc, and it's transparent and automatic, requiring no additional know-how or steps on your part. This way, when Word generates the .htm file — Prospects.htm in the above example — it can insert in it links to the external image files that will tell the browser (and Word, if you opt to merge print your merge docs in Word) where to find them.
Word will also generate a filelist.xml file and put it into that same subfolder where it put the copies of the images. There's no need to get into xml here, but this ASCII file contains references both to the image filenames in the subfolder and to the .htm document that contains the links to them. Filelist.xml plays a security role and Windows needs it. If you attempt to rename or move the subfolder, Windows will warn you that the links to the .htm file that use the images referenced in it will be broken. If you do it anyway, or delete filelist.xml, you'll get a warning message every time you go to print a merge letter, and your images may disappear from it.
If there are no embedded images in the Word doc, then, of course, only the .htm file will be created — none of this other stuff will be generated or even matter. But if you do have embedded images in the docs you're using to make your merge docs, then here's a recap of what to expect:
If all this sounds complicated, it is. Fortunately, WordMerge Lite (and Word) takes care of it all for you so you don't have even have to think about it much less deal with it. The
three types of WordMerge Lite files
When editing/updating a merge doc, always make your changes to filename.doc. Save fileneme.doc when finished, then do a Save as Web Page to make a fresh copy of filename.htm. Don't edit filename.htm directly. Don't save filename_merged.doc expecting to get a real Word document. Save it only to another folder if you want to have an archive copy of it.
WordMerge Lite's user modifiable variables // Where the selectable/mergeable HTM docs are stored Where tour .htm merge docs are stored. This is also where WordMerge Lite will save it's output ("_merged") files. You'll probably want to also store your original Word (.doc) master merge docs here — the one for use with WordMerge Lite. That way, when you edit and resave them to .htm, Word will put them here automatically. Note: This setting can be modified on-the-fly from WordMerge Lite's Other Options menu. // Auto-generated list of available HTM merge doc filenames The path and name of the file that WordMerge Lite will use to generate the picklist of merge documents. // Command Line to generate above list The command line that generates the list of merge documents. // Send output to Word (".doc") or browser (".htm") Leave as ".doc" (default) if you want your outputted merged docs to open in Word. Change to ".htm" if you want them to open in your browser. Note: This setting can be modified on-the-fly from WordMerge Lite's Other Options menu. // Merge field tag(s) used in merge doc. Ex: *City* *State* *Zip* The starting and ending merge tags that denote a merge field. WordMerge Lite will replace these tags, plus what's inside them, with the data from the matching field. The starting and ending tags do not have to be the same, and each can be more than one character. // Path to merged image files in Sesame records If the path to your images files is, for example, "c:\sesame\pics\Al Smith.jpg", then the image element's value will be "pics\Al Smith.jpg", so the variable needs only the first part of the path. Note: This setting can be modified on-the-fly from WordMerge Lite's Other Options menu.
// Image scale factor for *merged" images The best setting for this variable can be found by merge printing a record with an image in it. This variable assumes that the images in your records are all approximately the same width and height. (Sesame reduces images to fit in the element, but that's not necessarily their actual size.) Note: This setting can be modified on-the-fly from WordMerge Lite's Other Options menu. // Protect settings (users can't change them) This variable determines the options available from the WordMerge Lite's merge document selection picklist. If left at its default "no", settings for the following items can be changed on-the-fly for and by any particular user: Merge documents path If changed to "yes", WordMerge Lite's settings will be protected — that is, they won't appear on the Other Options menu as editable user preferences. See the section below on Protecting WordMerge Lite settings for more information. // Optional custom variables to include in merge doc WordMerge Lite contains a default current date variable named "theDate" that replaces every occurrence of *theDate* in the merge doc. "theDate" is run through the built-in FD() function that formats dates like this: "February 20, 2006". // Declare any other merge variables below. Change the var vMyVar1 as String Add any custom variables in this section in place of the three "dummy" variables. If the variables are "simple" variables, you can optionally fill them here in the same way as the theDate variable is filled. But if your custom variables can't be initialized with a simple value assignment, declare them here, then perform the assignments either in the Main Program or in the space provided at the end of the MERGIT() subroutine
Protecting WordMerge Lite settings A simple four-element string array in [sesame working directory]\WMLSettings.txt stores the settings for these four variables on the PC of any user who modifies an one of them. The merge document selection menu includes a final ««««« Other Options »»»»» item which, if selected, displays a secondary menu with these options:
If any Set... option is selected, WordMerge Lite shows the current setting for it, prompts for the new setting, then saves it to the user's WMLSettings.txt file. Any changes are sticky, meaning that they are permanent until changed via the same procedure. Remember: Each WordMerge Lite user can set their own preferences. If you don't want to let users set their own WordMerge Lite preferences, change vProtectChanges to "yes" when you deploy. Figure 1 shows a sample WordMerge Lite merge doc selection menu. Figure 2A shows the Other Options submenu when vProtectSettings is set to "no". Figure 2B shows it when set to "yes".
Figure 1. Sample WordMerge Lite merge document selection menu.
Figure 2A. vProtectSettings set to "no".
Figure 2B. vProtectSettings set to "yes".
WordMerge Lite's built-in
User Defined Functions
These UDFs are called mostly from the MERGEIT() subroutine in the main program, where
the element values are picked up and may need custom formatting (dates, numbers, etc.).
You can move these UDFs to the main program, if you like, so that all the WordMerge
Lite programming is in one place . If you do, place them directly under the last
user-modifiable variable and above the first subroutine, as noted in the WordMerge Lite
program. FD() Converts date values in date elements and date variables from Sesame's internal YYYY/MM/DD format to the readable "February 8, 2006" format. FT() Converts time values to the "4:55 pm" style from the raw/military "16:55" format. It doesn't do seconds. FNM() Formats Number and Money values. FBool() For boolean fields such as Yes/No and checkboxes. If the value is 1, the function returns "Yes". Otherwise it returns "No". FImg() When the MERGEIT() subroutine encounters an image element, it first runs the image through the SCALE_PIC() subroutine which ascertains the image's width and height then scales it using the user-modifiable vImgScale variable (above). FImg() returns the full path to the image along with it's scaled width (w) and height (h) in a formal HTML-encoded format.
Requirements/compatibility Known WordMerge Lite issues and troubleshooting
Viewing/printing merged docs
File naming $ & + , / : ; = ? @ " < > # % ! ' ( ) * - . { } | \ ^ ~ [ ] ` £ When naming your merge docs, use only alphanumeric characters with an optional space or underscore between words.
Folder naming Page break problems Merge fields not merging data If it's a variable (such as *theDate*) , make sure that you've told the
WordMerge Lite program to merge to this merge field. You may have have declared and
populated the variable, but failed to tell WordMerge Lite to merge it in the
MERGEIT() subroutine. 1. Open the original Word doc.
If the field still doesn't merge — and the field name contains spaces — the problem could be a "broken" merge field. This can happen when a merge field is line-wrapped (with a hard carriage return) on one of its spaces by Word when you do a Save As to an .htm file. WordMerge Lite "flattens" the .htm file when processing it, temporarily replacing hard carriage returns with a special "^|^" string. You can make the WordMerge Lite program find merge fields containing this string and replace each one with a space. Here's how. In the MERGEIT() subroutine you'll find this statement: // Replace merge field tag with data Add the following lines under the above statement to fix instances where a field name containing one or more spaces is broken on the first or last space. This additional code temporarily adds a "^|^" to the field name, looks for a match in the doc and replaces any found with the value being merged. vFix = @Replfir(vName, " ", "^|^") You can declare the vFix variable at the top of the MERGEIT() subroutine. More recent versions of the WordMerge Lite program "flatten" the document in a slightly different way. Find the following lines in your copy of the program: // Flatten doc to prevent C/Rs in merge field tags Change the second line to read: vDoc = @Replace(vDoc, @NL() + " ", "^|^") This tells the program to replace each carriage return followed by two spaces with ^|^. (When generating the .htm file, Word, breaks lines at spaces, then indents the following line by two spaces. This is what causes the problem.) The aforementioned change, in conjunction with the vFix addition above, solved the "broken" field problem in all cases we've seen to date. If all else fails, open the .htm file (the one generated by Word, not the already merged one) in Notepad and locate the offending merge field. Chances are there will be some errant bit of HTML code inside the merge field tag put there by Word when it made the file. Clean it out and resave the file to the same name and .htm extension. Merges to that tag should now be successful. Words in the output document running together Please review the attachedcontract and notify us of any changes... Here the words "attached" and "contract" have run together. This problem is almost always caused by one or both of the following:
When you use tab indents or text justification in a merge doc, Word inserts its own proprietary "space-run" codes into the .htm (Web page) file in an attempt to account for HTML's lack of support of these formatting options. The "space-run" codes cause line breaks to occur at odd places and words to run together. The fix is to remove all tab indents and text justification from the original Word doc, save it, then remake your .htm file for use with WordMerge Lite. Merged image sizing In the MERGEIT() subroutine in the WordMerge program, you'll find this section: /* Word 2000 displays merged images about 3 times too
small, so If you're using Word 2000, uncomment the statement under (1) Word 2000... and comment the line under (2) Word 2002/3.... as shown. If you're using Word 2002/3 do just the opposite. You need to do this only if you're outputting to Word and merging images in your Sesame records. This is not for static images that are embedded in the original Word doc. Adding/Enhancing merge fields Incorrect: *First Name * or * First Name* (trailing / leading space) When enhancing a merge field, be sure the enhancement includes both the start and end delimiters: Incorrect: *Amount Owed*
(the asterisks aren't also in boldface) Turning off Word's Auto-Formatting for asterisks Conclusion |
|
