Using Inside Sesame's PostWiz

Posting makes permanent changes to the TARGET database
Always back up your application before running a post

What PostWiz Does

Sesame doesn't come with Q&A for DOS-style batch posting, so Inside Sesame Labs created PostWiz to fill the gap. In an easy-to-use pop-up wizard, PostWiz helps you quickly create, save and run batch posting specs that mimic Q&A's Post feature — complete with the ability to run target database calc statements with each post.

PostWiz can also generate portable drop-in posting programs from your posting specs. You can pop-up the wizard from any form in any Sesame application to run your routine posting tasks, or have PostWiz generate posting programs you can paste into your own command buttons. It can even post between external applications.

Setting Up PostWiz

PostWiz is supplied as a small Sesame application. You simply merge it into your existing application in Sesame Designer (Application / Merge Application). Then, on your application's main menu or in any form, you add a button named "Batch Posting" or "PostWiz" and program its On Element Entry event with just the two lines shown below. You're ready to start posting.

var vPopup as String
vPopup = @FormAsDialog("PostWiz")

PostWiz supports a variety of user mode startup parameters.

Creating PostWiz Posting Specs

ΠStarting PostWiz
When you start PostWiz in normal or admin mode the Source DB Application Path and Target DB Application Path fields are auto-filled with the current application filename. 

To select different source and target applications, click the folder icons to the right of these dropdowns. (PostWiz is easiest to use when both databases involved in a posting operation are in the same application as PostWiz.)

When starting PostWiz, if you've saved any posting specs, they will appear on the Saved Specs dropdown. You can select a saved spec and click the Load button to load it.

When modifying a saved spec, be sure to click Populate Dropdowns because PostWiz does not save these lists with the spec. (New fields could have been added to the Source or Target database after the spec was last saved.)

Note: PostWiz obtains the database field names through their forms. If more than one form for a database exists, a popup menu displays prompting you to select the form that contains the most layout elements. This would normally be the "master" form.

Source DB (Post From) / Target DB (Post To)
These dropdowns contain the databases in the current application. If you've selected a different (external) application, type in the pertinent database names in that external application.

� Making a Post
When posting to databases in external applications, the dropdowns under Make Post and Source DB Retrieve Spec cannot be populated. You'll have to manually supply the fieldnames from those external databases.

Like Q&A, PostWiz posts to the first matching record.

If you're creating a new Posting Spec, click Populate Dropdowns after selecting your SOURCE and TARGET databases.

If you're modifying a saved posting spec, click Populate Dropdowns after loading the spec.

Make your post by using the dropdowns for the Post Source Field, the To Target Field, the When Source Field and the Matches Target Field.

Chose the posting operation type: REP (Replace), ADD, SUB, MUL or DIV. The default is REP.

Your field types must be compatible for the type of operation you are telling PostWiz to perform. PostWiz warns you if they are not.

When you've specified the post to be performed, click the Add This Post to Posting Transactions button. The post will appear on the Posting Transactions list.

To remove a post from Posting Transactions, select it, then click Remove Selected Post.
 

Ž  Source DB Retrieve Spec
The Source DB Retrieve Spec tells PostWiz which records to retrieve from the SOURCE database to post to matching records in the TARGET database.

Select an AND or OR search for the SOURCE records.

An AND search meets ALL your restrictions (default).
An OR search meets ANY of your restrictions.

Select the fieldname from the Field dropdown, then type a restriction for it in the adjacent Spec field. Up to four fields/restrictions can be specified.

Here are a few examples of SOURCE database retrieval restrictions:

Additional Source Retrieve Options
To specify a date range, you can use the 'AND' Search Option with two Spec fields. For example, to retrieve just the January 2010 Source records:

Field1: InvoiceDate [D]   Spec1: >=2010/01/01    Field2: InvoiceDate [D]  Spec2: <2010/02/01

Or, you can use a date range shortcut that requires just one Field/Spec. Here's one that specifies the Source records whose contract date falls in the fourth quarter of 2009:

Field1: Contract_Date [D]   Spec1: 2009/10/01..2009/12/31

Simple valid retrieve expressions may also be used as long as they do not reference any fields. For example, Source records with today's date in the Order_Date field:

Field1: Order_Date [D]    Spec1: {@Date}

Source records more than 30 days old with a balance due (using an 'AND' search):

Field1: InvoiceDate [D]    Spec1: <{@Date-30}   Field2: Balance    Spec2: >0

Runtime Prompt Options
You can optionally select the Source records by having Sesame prompt for input at runtime, using one of the following three choices:

• Date (@Calendar() popup)
• Date Range (back-to-back @Calendar() popups)
• Text Input (@PromptForUserInput() popup)

For example, to retrieve a date range of Source records (this month to date, last month, current quarter to date, or whatever, you can use the Prompt for Date Range option.

When you select any of the prompt options, PostWiz will ask you (1) which field it's for among those already specified (2) the parameter (greater than, lesser than, etc.) then (3) display a dialog for you to enter a brief runtime instruction so that the user knows what he or she is expected to provide. In the case of a Date Range, two dialogs will display, one each for the starting and ending @Calendar prompts.

If your Source record retrieval parameters are more complex than PostWiz supports, you can always use the Code Builder to output a starting program, then modify it to suit. Remember that XResultSetSearch(), which PostWiz uses, does not support combination AND / OR searches. You must use one or the other. AND is the default.

Important: If you supply no Source database restrictions, PostWiz will retrieve all the records in the Source database. If there are many thousands of Source records, posting could take a very long time. So always limit your Source records to just those with data that needs to be posted. (When testing, you can terminate a posting operation by pressing Ctrl-Shift-End. This will not "undo" or rollback any records already posted.)

Test your Retrieve spec using the Test Spec button. (This does not run a post; it only counts the Source DB records that met your restrictions.) If the count doesn't look like what it should be, do a manual retrieve in the Source DB itself (using the same restrictions) to confirm the total record count.

Optional Target DB Calc Program
You can optionally paste or type in calc program statements to execute as each record in the Target database is posted. Be sure to use database field names in your calc statements. These may or may not be the same as your form LE names. After supplying any calc statements, click the Test Program button to confirm that the programming is runnable on the Target database. 

Using the Code Builder
You can run your posting specs directly from the PostWiz popup or have PostWiz generate the posting program for you to use elsewhere. When you've finished creating your posting spec, click the Code Builder button to generate the code for the current spec. The code will display in Notepad, where you can copy and paste it into the On Element Entry event of a command button.

Running a Post
To run the current posting spec, click the Run Post button. Before testing your posting specs on a live in-use application, be sure to make a backup copy of the application.

Like Mass Updates, posting specs are best run when nobody is in the Target database. If, during a post, a Target DB record is in use (locked), PostWiz will be unable to post to it.

Optional PostWiz startup parameters

To control what users can do with PostWiz, you can specify optional user mode startup parameters:

var vLetsPost as String
ClientLocalValue("PostWiz", "[specname],[specstate],[autorun],[autoclose]")
vForm = @FormAsDialog("PostWiz")
ClientLocalValue("PostWiz", "")

The table below describes what the four PostWiz startup parameters do.

Example 1
ClientLocalValue("PostWiz", "pwz_My Posting Spec.pwz,1,0,1")

Loads the saved spec named pwz_My Posting Spec. Disables everything except the Run Post button. Automatically closes PostWiz when the posting has been completed. User initiates post.

Example 2
ClientLocalValue("PostWiz","pwz_Post Daily Receipts,0,1,1)

Loads and runs the pwz_Post Daily Receipts spec then closes PostWiz. No user interaction.

Example 3
ClientLocalValue("PostWiz", ",1,0,0)

By leaving param #1 (the spec filename) blank, PostWiz disables everything except the Saved Specs dropdown, the Load button and the Run Spec button. All the user can do is load a saved spec and run it. The spec itself can't be modified. In this case, PostWiz remains open after the post.

You can control who has access to PostWiz with a conditional like the following:

If @Group = "admin" [or If @UserID = "Sherry"]
{
@ClientLocalValue("PostWiz", ...)
vForm = @FormAsDialog("PostWiz")
@ClientLocalVAlue("PostWiz", "")
}

For "normal" or "admin" mode, do not use (or comment out) the ClientLocalValue()

PostWiz does not store any data or return anything required by the calling program. When you create and save a posting spec, PostWiz saves it in an external file named pwz_[specname].pwz in your Sesame working directory. Specs can be tested, modified, resaved and, of course, run.