your database front-end

Server Outage Notice: will be transfering to a new Server on Friday 25th @ 7pm MST

Wizard for Data Entry

This topic explains how to create a wizard in dbFront for data-entry and maintenance.  Wizards make complex tasks easier and more accurate by:

  • breaking complex tasks into bite-sized pieces with focused instructions,
  • sequencing the steps and allowing prompts for additional information at crucial points,
  • leading users from start to successful completion.

NOTE: We are going to assume that you want to create a wizard for a table named SalesOrderHeader.  The following structures are for example purposes only and should be modified to suit your environment.

The button that starts off the Wizard is described at the end.

1. Add a Page/Step field

The first requirement is to add a page/step column.  Rather than a simple integer or string column, the best solution is to create a lookup table based on a string code value.

The string code value allows for meaningful page/step code values, e.g. [new,  errors, complete].   The string code can double as a record status field once the wizard is complete.

CREATE TABLE SalesOrderWizard (
    WizardPage varchar(7) NOT NULL,       /* Step/Page code */
    Caption varchar(50) NOT NULL,            /* Shown to the user */
    HelpText varchar(1024) NULL,               /* Step/page specific user help */
    ShowPrev bit NOT NULL,                      /* if the Prev button should be visible */
    ShowNext bit NOT NULL,                      /* if the Next button should be visible */
    ShowFinish bit NOT NULL,                   /* if the Finish button should be visible */
 CONSTRAINT PK_SalesOrderWizard PRIMARY KEY (WizardPage))

After creating the table it would be related to your SalesOrderHeader table.

ALTER TABLE SalesOrderHeader
    ADD CONSTRAINT FK_SalesOrderHeader_SalesOrderWizard
    FOREIGN KEY (WizardPage) REFERENCES SalesOrderWizard (WizardPage)

2. Wizard Step View

Next, create a view on the SalesOrderWizard created above to make the relevant view fields available to the SalesOrder table.  Rename all fields except the key field to make it clear which fields came from a view.

CREATE VIEW vSalesOrderWizard AS 
        WizardPage, Caption as vCaption, HelpText as vHelpText,
        ShowPrev as vShowPrev, ShowNext as vShowNext, ShowFinish as vShowFinish
    FROM SalesOrderWizard

Once the view is created you should add it to your SalesOrderHeader table as a table view.  For instructions see: Table Views.

3. Create Prev / Next buttons

Once the current wizard page information is available you should then create two action buttons to allow the user to select the next or previous page.

  • Both buttons should be of type RunProcedure,
  • You could call the same procedure and simply pass a "+" or "-" to tell the procedure to validate the current page and calculate the next page code.
  • Set the visibility of the Prev button based on the vShowPrev view field,
  • Set the visibility of the Next button based on the vShowNext view field.
  • Set a  CSS classname on the Prev/Next buttons to give them a unique style.

4. Create Page/Step specific Help

The next step would be to display the context-specific help.  The context-specific help can be used to provide progress and warning prompts to the user.

  1. Open the table properties and add the view field vHelpText to the top of the form and save,
  2. Open the vHelpText field properties.
  3. Change the Caption to the help text you would want to see if a "New" record was on screen.   e.g. "Save this record to continue processing."
  4. Switch to the "Layout" tab.
  5. Change the Label Position to "Inline"
  6. Optionally set a Css Class name for the Help Text
  7. Save your changes.

The "inline" Label Position will cause the vHelpText to display full width with the caption hidden.  If no record or an empty vHelpText is selected then the user will see the Caption you specified.  You can add a custom CSS file to further format the help text based on the CSS Class you assigned in the last step.

NOTE: At this point, you could also add tooltips on fields and buttons for additional user help.

5. Create Page/Step specific Field Groups

Now that the Prev / Next buttons and the Help are working, the next step is to group all of the fields and only show them on their appropriate wizard page.

Create a Layout Group

To create a Layout Group open the properties on any field and switch to the layout tab.   From the layout tab you can create a new Layout Group or assign a field to an existing group.  Once a Layout Group is created you can open the Layout Group properties and easily add more fields.  

Set Layout Group Visibility

Open the Group properties and in the Visibility tab set the Layout Group to show when then field WizardPage is equal to the appropriate value.

6. Child Records

A table that is setup as a Wizard can have multiple different types of child records.  In the case of the SalesOrderHeader, a logical child would be the SalesOrderDetails.  As the user works through the wizard, the Next or Finish button could be coded to require, validate and process the child records.

7. Add a Finish button to validate and complete the record.

Create a stored procedure named spSalesOrderWizardFinish(wizardPage varchar(7)) that does all activities needed to validate and complete the record.

Add a new action button and set the caption to "Finish" and the type to RunProcedure, and specify the procedure spSalesOrderWizardFinish.

The procedure has the ability to validate the current record and all of its child records.  In addition, the store procedure can return a resultset showing a list of issues that need to be dealt with.  The list of issues can be returned directly from the stored procedure or stored in a specific column or table and returned via a view.

Ensure that the "Finish" button is only visible on the correct steps by setting the visibility based on the view field vShowFinish.

Starting the Wizard

A user can start the wizard simply by creating a record in the SalesOrderHeader table but that is not very intuitive.

A more intuitive option is to create a "New Sales Order" button on the appropriate vendor or product table.   The "New Sales Order" button can trigger a procedure that takes the current Vendor or Product as a starting point, creates a new SalesOrderHeader record with that vendor or product preloaded and navigates the user directly into that SalesOrderHeader wizard.

Enhanced Wizard Functionality

The current wizard functionality does not implement:

  • an automatic progress meter,
  • automatic Prev / Next buttons,
  • better placed Prev / Next / Finish buttons,
  • prompted, inline entry of child detail records.

There is an existing Feature Request to add deeper Wizard functionality in dbFront.  Please vote on that feature here: Advanced Wizard Functionality

Content you want the user to see goes here.