Software Development Kit (SDK) Development Utilities Guide

Sage ERP MAS 500 ERP

(SDK)

Development Utilities

Guide

Table of Contents

Chapter 1: Development Utilities ... 1

Application Wizards... 1

Installing and Using the Application Wizards ... 2

Table Design ... 2

Data Dictionary... 4

Applications ... 5

Sage ERP MAS 500 Application Architecture ... 5

Customization Project Generation ... 7

Dependencies ... 9

Wizard Behavior ... 10

Step 1 - User Interface ... 11

Step 2 - Project Details ... 11

Step 3 - Select Columns ... 13

Step 4 - Review Choices ... 16

Control Types and Data Types ... 18

Line Entry Application Wizard ... 20

Example Projects ... 22

Report Wizard... 25

Starting the Wizard... 25

Building the Project ... 30

Error Rigging ... 31

Error Rigging User Interface ... 32

Visual Basic 6.0 IDE - Conditional Compilation ... 33

Ranges ... 33

Overview ... 34

Local Strings ... 36

Overview ... 36

Local Messages... 38

Overview ... 38

Lookup Definition Maintenance... 41

Overview ... 41

Connecting to the Database... 41

Creating Custom Modules ... 50

Step by Step ... 50

Assigning Tasks to Custom Modules ... 51

Table Population... 52

Data Dictionary ... 53

Task Editor ... 58

Task Selection ... 58

Schema Browser ... 61

Standards Checker... 65

Installation/Registration ... 65

The Application... 65

Maintain Report Sort Selection Information ... 68

Introduction... 68

Development Utilities

Development Utilities

This document provides an overview and detailed explanation of different utilities used in the Sage ERP MAS 500 development environment. Every utility has a purpose that supports Sage development strategies.

This chapter discusses the following development utilities:  Application Wizards

 Line Entry Application Wizard  Report Wizard  Error Rigging  Ranges  Local Strings  Local Messages  Lookup Definition  Module  Data Dictionary  Task Editor  Schema Browser  Standards Checker

 Maintain Report Sort Selection Information

A p p l i c a t i o n W i z a r d s

Application Wizards are used to create Visual Basic projects that run from the Sage ERP MAS 500 Desktop.

Application Wizards build wizard files (files with an AAW extension). These are plain text files containing data collected during each wizard session. The name of the Application Wizard file is the same name that is used to create the VBP file. Both the Application Wizard file and the VBP file are stored in the same location. The Application Wizard file also contains other data, particularly the Data Dictionary information obtained from the system

The Application Wizard collects information used for naming components of the project as well as the sources of data used in the application. The wizard accesses the Data Dictionary to obtain the properties for each column the application binds to so the correct control and configuration are used. When binding to a grid, the Data Dictionary determines cell dimensions and other setup information.

The intent is that, following the wizard session, a new application compiles and runs as a Sage ERP MAS 500 task from the Sage ERP MAS 500 Desktop and the coding of the task’s business logic can begin immediately. At the end of each wizard session a To Do List displays indicating what additional steps are necessary in order for the new project to run. Some of these items are optional and dependent on the nature of the project being built.

I n s t a l l i n g a n d U s i n g t h e A p p l i c a t i o n W i z a r d s

P r e p a r a t i o n

The Application Wizards make many assumptions about the nature of the applications being built. Success depends on the accuracy and richness of the Data Dictionary information that describes the data. Become familiar with the entire design and build process described in this guide.

D e v e l o p m e n t E n v i r o n m e n t

You must first install the source code. After the source code is installed, run the Ranges utility and contact Sage ERP MAS 500 Support to obtain your company code and data ranges for messages and strings.

T a b l e D e s i g n

For more information, see Schema Browser.

P r i m a r y K e y

Every table used in Sage ERP MAS 500 must have a Primary key.

S u r r o g a t e K e y

A Primary key may or may not be a Surrogate key.

N a t u r a l K e y

A Natural key defines uniqueness for each row in a table. Typically, it is a multi-column key that consists of the IDColumn for the table and one or more Foreign keys. In certain cases for performance reasons, the Natural key is defined in a Clustered index. In this case, it is important to ensure that the Uniquekey and OrderBy properties of any Data Manager objects use the Natural key exactly as defined in the clustered index to deliver optimal performance with applications that use grids. If the OrderBy property is not the same as that defined by a Clustered index, SQL Server creates a temporary table for the result set.

I D C o l u m n

A table’s IDColumn is the only column that is part of the Natural key, but not a Foreign key. This column is generally used as the Lookup control’s Bound Column property. In the Sage ERP MAS 500 database there are, however, instances of tables that do not have an IDColumn. In these cases, all of the Natural key columns are Foreign keys and the wizards may have difficulty determining which of the Natural key columns should be used by the Lookup control. For example, the IDColumn for the table tarCustomer is CustID.

E x a m p l e S c r i p t

The following example script produces two tables that work as the parent and child tables in a Line Entry project. Note that in each case, the Primary key is created as a NONCLUSTERED index.

if exists (select name from sysobjects where name = ‘tglAllocCJK’ and type = ‘U’) Drop table tglAllocCJK

go

CREATE TABLE tglAllocCJK ( AllocateKey int NOT NULL, AllocID char(10) NOT NULL, AllocMeth smallint NULL DEFAULT 0

CHECK (AllocMeth IN (0, 1, 2, 3, 4, 5, 6)), AllocType smallint NULL DEFAULT 1

CHECK (AllocType IN (1, 2, 3, 4, 5)), CompanyID char(3) NULL,

Description varchar(40) NULL DEFAULT ‘ ‘, Financial smallint NULL DEFAULT 1

CHECK (Financial IN (1, 0)), LastPostDate datetime NULL,

PostCmnt varchar(255) NULL DEFAULT ‘ ‘, UpdateCounter int NOT NULL DEFAULT 0 )

go

GRANT REFERENCES , SELECT , INSERT , DELETE , UPDATE ON tglAllocCJK TO public

GO

CREATE UNIQUE INDEX XAK1tglAllocCJK ON tglAllocCJK (

CompanyID, AllocID )

go

ALTER TABLE tglAllocCJK

ADD PRIMARY KEY NONCLUSTERED (AllocateKey) Go

if exists (select name from sysobjects

where name = ‘tglAllocDetlCJK’ and type = ‘U’) Drop table tglAllocDetlCJK

AllocPct decimal(5,2) NULL DEFAULT 0, GLAcctKey int NULL,

GLAcctMask varchar(100) NULL,

PostCmnt varchar(255) NULL DEFAULT ‘ ‘, SeqNo int NOT NULL

) go

GRANT REFERENCES , SELECT , INSERT , DELETE , UPDATE ON tglAllocDetlCJK TO public

GO

CREATE INDEX XIE1tglAllocDetlCJK ON tglAllocDetlCJK (

AllocateKey, SeqNo )

go

ALTER TABLE tglAllocDetlCJK

ADD PRIMARY KEY NONCLUSTERED (AllocateDetlKey) go

D a t a D i c t i o n a r y

When new tables are imported using the Data Dictionary, the Data Dictionary is updated with information that the wizards use to create new tables. The wizards are dependent on this information and typically inform you when essential information is missing; however, the wizards are tolerant, to some extent, of variations to ideal table design and allow the flexibility to normalize the database in a variety of ways.

K e y s

The Data Dictionary defines the Primary key, Surrogate key, Natural key components, and which columns in each table are Foreign keys. The Data Dictionary utility maintains this information and the Application Wizard is dependent on this information.

D o m a i n s

The Data Dictionary utility also allows you to assign DomainIDs to each column. This often determines which controls are used to bind to these columns.

G r i d S e t u p P r o p e r t i e s

When data is presented in a grid, the Data Dictionary defines how each grid column is set up. Column width, caption, and cell type properties are obtained from the Data Dictionary.

R e p o r t S e t t i n g s

Report projects use Data Dictionary information for the default Sort/Select column usage.

L o o k u p D e f i n i t i o n s

The Lookup Definition utility is used to create or change Lookup definitions. You use the Data Dictionary utility to assign Lookups to selected columns.

A p p l i c a t i o n s

Each type of project has its own unique requirements and dependencies. For more information, see Dependencies.

B l a n k P r o j e c t

A Blank project has no data binding specified during the Application Wizard session. Use this project type to build a task that only has the basic Form controls: Toolbar, Status Bar, and references for the commonly used controls and objects including Customizer components.

S i n g l e R o w M a i n t e n a n c e

This type of project maintains a single row from a table using the framework’s CompanyID value as the key into the table. Typically, Option type application tasks use this methodology.

M u l t i R o w M a i n t e n a n c e

Multi Row Maintenance tasks use a Lookup to locate a row in a table. The row’s data is then populated into the controls on the form for editing. You can add new rows and delete existing rows.

D a t a S h e e t

Data sheets present data in a grid for editing. You can add new rows or delete existing rows.

L i n e E n t r y

Line Entry tasks maintain the values in a parent/child relationship. The parent table is the header and its values are maintained in bound controls and the child table contains the detail. Child table data is presented in a grid and edited in controls bound to the grid using the LineEntry class.

R e p o r t

The Report Wizard creates standard Sage ERP MAS 500 style report projects.

S a g e E R P M A S 5 0 0 A p p l i c a t i o n A r c h i t e c t u r e

F o r m L a y o u t

Each task, depending on its type, has a standard set of features, and provides the basic means of controlling the form’s state, navigation, data validation, and customizing the form during runtime. The form layout consists of the principal UI components and supporting classes that characterizes the look and behavior of Sage ERP MAS 500 applications. Each project type contains both common and unique form layout components. The Application Wizards reproduce these styles before adding any bound controls and other task-specific UI items.

 Status Bar - The status bar navigation buttons are visible in some project types, but not in others

 Customizer controls and the Customizer code  Validation Manager

In addition, and for each project type, you can add the following layouts:  Blank Project

No other components  Single Row Maintenance

Tab control to contain bound controls  Multi Row Maintenance

No other components-all bound controls are placed on the form.  Data Sheet

Grid, all data is bound to the grid.  Line Entry

Tab control. Tab 1 contains controls bound to the parent table. Tab 2 contains a grid and the Line Entry frame. Child table data is bound to controls on this tab. Tab 3 is empty and reserved for calculated or derived data.

A p p l i c a t i o n C l a s s M o d u l e

This module is created by the Application Wizard code generator and provides the task’s interface to the Framework. The code contained in the class module is always the same except where references are made to names that are specific to the task (for instance, the name of the application’s main form module).

A p p l i c a t i o n B a s i c M o d u l e

This module is created by the Application Wizard code generator and provides the Sub Main(). The code contained in the basic module is always the same except where references are made to names that are specific to the task (for instance, the name of the application’s main form module).

C o m m o n C o d e

Each task, depending on its type, uses many of the Common code modules. The Application Wizard adds these into the project.

D a t a M a n a g e r

Each bound table requires a Data Manager or Grid Manager. Data Managers deal with the complexity of Control or Grid binding to the database. Data Manager, for most situations, handles the maintenance of data for Insert, Update, and Delete. Business logic requires other techniques, perhaps, for special validations, lookups, and processing.

C o d i n g S t a n d a r d s

Coding standards for programs fall into four major categories:

 Good Practice - Including a consistent naming convention for controls, variables, and file names

 Classic Constructs - Techniques developed as reference methods of implementing functionality

 Design Intent - How controls, common code, and objects are designed to interact with each other

 Error Handling

Adherence to these coding standards and conventions is highly recommended.

C u s t o m i z a t i o n P r o j e c t G e n e r a t i o n

W i z a r d S e s s i o n s

During each wizard session, data is collected that is used to build both the user interface and code required to create a Visual Basic® project that runs in the Sage ERP MAS 500 environment. The wizards collect information used to name the files that are created and to determine a project directory in which to store them. The kind of project being built determines the information

collected and how to build the UI and bind controls to data.

B u i l d i n g t h e U I

The following steps are used to create the new task’s user interface:  Build the form and add form controls appropriate to the type of task

(including the grid, if required)

 Add custom controls (their Left property is set to -10000)  Add other bound controls if required

 Add Customizer components

As controls are added to the form, any event handlers along with initialization and termination code they require are added to the form module on the fly. This is also part of the code generation process. For more information, see Code Generation.

Controls are added to any of the main elements of a form as prescribed by the project type:

 To the form

 To the form’s main tab control

 To the Line Entry frame in a Line Entry project (on Tab 2 of the Line Entry’s Main tab)

 To each of the tabs in a report project

C o d e G e n e r a t i o n

The Application Wizard generates code in three ways:  Boilerplate code fragments specific to each project type  Boilerplate code with token replacement - The code fragments

maintained in the Application Wizard contain tokens that are replaced with data collected from the Application Wizard session.

Example:

sMyName = “[*FormName*]” Might become:

sMyName = “frmCIzng001”

The Data Manager binding calls are also made in this way.

 Generated code - This is typically produced when adding certain types of controls to a form. When adding a Lookup control, for example, the control must be initialized in SetupLookups(), there must be a call to TerminateControls() in PerformCleanShutdown() if one does not already exist, and the control’s LostFocus() event must be created.

W h a t Y o u S e e I s N o t W h a t Y o u W a n t

When the Sage ERP MAS 500 Software Development Kit (SDK) builds a project, it constructs the user interface and code to make the application run in the Sage ERP MAS 500 environment, and bind to the data. The SDK uses a simple algorithm for placing controls on the form and no attempt is made to layout controls using any aesthetic rules. After coding has started, it is easier to add new controls manually than to regenerate your application and have your modifications overwritten.

S a g e C o n t r o l s

This section describes how the Application Wizard code generator handles the Sage controls when they are added to a project.

L o o k u p

A main Lookup is added to Multi Row Maintenance and Line Entry projects. Both project types initialize the control in the same manner:

 The LookupID property is set at design time. These are automatically set during project generation.

 The BoundTable and BoundColumn properties are set at design time. These are automatically set during project generation. This applies only to the Natural key Lookup control, not the Foreign key Lookup control.  Code is added to the SetupLookups() procedure to initialize the control.

This applies only to the Natural key Lookup control, not the Foreign key Lookup control.

 The control is bound to the moDmHeader object.

 Code is added to the HandleBrowseClick procedure to set up the control when the status bar navigation buttons are used.

D r o p d o w n

The Dropdown control must be initialized with any static list data during task initialization. When a static list is required in the grid of a Line Entry

application, specific procedures required to perform the correct display of selected items in the grid and the Line Entry frame.

C a l e n d a r , M a s k e d e d i t , C u r r e n c y , N u m b e r

These controls are bound to data in BindForm() and/or BindDetail() in the same manner as any non-Sage control; there is no special initialization generated by the Application Wizard.

D e p e n d e n c i e s

*Indicates that columns are bound to controls appropriate to their data type

Project Type Blank Project Single Row Multi Row Data Sheet Line Entry Report

Tool Bar Yes Yes Yes Yes Yes Yes

Status Bar Yes Yes Yes Yes Yes Yes

Validation Manager

Yes Yes Yes Yes Yes Yes

Data Manager

No Yes Yes Yes Yes Yes

Main Lookup No No Yes No Yes No Grid Manager No No No No Yes Yes Line Entry Class No No No No Yes No

Tab Control No Yes No No Yes Yes

Bound Controls *

No Yes Yes No (just Grid) Yes Yes

W i z a r d B e h a v i o r

An Application Wizard has two visual elements:

 The panel on the left, which is used to navigate between wizard steps  The panel on the right, which is used to collect information for each

wizard step

N e x t B u t t o n

The Next button is enabled if all of the required information on the current step is valid. If this button is enabled, you can click it to proceed to the next step.

P r e v i o u s B u t t o n

Move to the previous step by clicking this button.

C a n c e l a n d E x i t

Cancel and exit the wizard by clicking this button.

F i n i s h B u t t o n

This button is enabled when the wizard has all of the information required to generate a project (usually on the last step).

S t e p N u m b e r s

When a step number is highlighted, it is the current step. You can select a step number to move between steps if the required information is present and valid between the current step and the new step (you can move multiple steps if the intervening steps are valid).

When you start a wizard session, you must move sequentially through all of the steps until the last step is reached and the Finish button is enabled. When the last step is reached, you can select any of the previous steps to make changes by selecting a step number.

K e y b o a r d S h o r t c u t s

When focus is on the left panel’s navigation buttons, use the Left and Right cursor keys to move between the Previous, Next, and Finish buttons. TIP

If the wizard warns you that there is insufficient information to build the project you can load this file by selecting Load Project Definition from the menu (the Application Wizard file is already built). Although the project does not build correctly, the wizard generates all of the code and UI that it can with the given information. You can then manually fix the code.

S t e p 1 - U s e r I n t e r f a c e

In the wizard pages that follow, a data sheet application will be built as an example. The specifics relating to each of the project types that can be built with this wizard are also detailed.

First, you need to select the project style to use for the new task.

S t e p 2 - P r o j e c t D e t a i l s

Type the application title, project ID, module ID, form caption, and select the HTML Help check box, if applicable. The HTML Help check box is available only if the ModuleID selected is not a Sage delivered module. For SDK developed modules, you have the option to use WinHelp or HTML Help. Select this check box if the project uses HTML Help. Clear this check box if the project uses WinHelp. It is assumed that you use the Sage ERP MAS 500 naming conventions (for example, ProjectID must always begin with the two

The files generated from the example shown above are:  cizng001.vbp

 cizng001.frm  cizng001.bas

 cizng001.cls (the Visual Basic name for the class will be “clscizng001”)  cizng001.aaw (the Application Wizard data file)

All of these files are stored in the following directory:

<<Sage MAS 500 SDKSourceRoot>>\Src\App\CI\CIZNG001\ where the directory CI is taken from the first two letters of the ProjectID. These directories are automatically created or you can place the files in a different location; however, it is very important that the relative address to the “Common” directory is maintained so that the project can correctly reference it. Common files are located in:

<<Sage MAS 500 SDKSourceRoot>>\Src\App\Common\

The first two letters of the ProjectID are assumed to be the module identifier as defined in the system table tsmModule. For more information, see Directory Synchronization below.

The Application’s ProgID is <<ProjectID>>.<<ClassName>>. The ProgID for the example above is cizng001.clsFOBMaint.

D i r e c t o r y S y n c h r o n i z a t i o n

The Application Wizard needs to build projects within a known and controlled environment because each project requires common source files that are referenced in the VBP file using a relative pathname. The Sage ERP MAS 500 development team uses the same project directory structure enforced by the Application Wizard.

 A Sage ERP MAS 500 Source Root directory exists (for example, F:\Sage MAS 500 SDK\) that can be any directory name off the root of a specific drive.

 From this directory, the SRC subdirectory contains various types of source code.

 Application source is located in the SRC subdirectory App, which is where your source files reside.

 In the App directory, the source is divided into modules, such as GL, AP, and so on.

 Within each module, additional subdirectories contain the source for each project.

The final directory structure should look similar to the following: F:\Sage MAS 500 SDK\Src\App\GL

F:\Sage MAS 500 SDK\Src\App\AP WARNING

Ensure that you create a subdirectory exactly two levels down from Src\App\ so that Visual Basic can find the files in Src\App\Common\. It is highly recommended that you use the default behavior of this feature and the same naming convention and directory tree structure.

If a new module is created (for example, by entering a ProjectID of “QW” that does not exist), the Application Wizard prompts you to create the new Module subdirectory. If you made a mistake, click No and start again; otherwise, the new subdirectory is created as a container for the new module’s projects. When you enter the complete name of the new task in the ProjectID field and the control loses focus, the Application Wizard prompts you to create the Project directory. If you click Yes, the new directory is created and the complete directory tree for the new project is defined. This is the source code location for all Application Wizard generated Visual Basic files for the project you are creating.

If, when prompted to create a new directory, you click No, click the Create Directory button to define your own source code location. This feature adds another sub directory to that shown in the Project Location drive and file list boxes.

S t e p 3 - S e l e c t C o l u m n s

When this step appears you will notice that some columns are preselected. These are required columns and cannot be removed from the project. These columns are identified by prefixes with the following meanings:

 *ID* = IDColumn

 *SK* = Primary key column. This is determined by the Application Wizard and is obtained using the sp_pkeys stored procedure. All tables must have a Primary key defined.

 *UK* = Both a Foreign key and Natural key column (UK stands for Unique Key because it is returned by the stored procedure spGetUniqueKey). These columns must be selected into the project for a data sheet because the Data Manager must be physically bound to all elements of the Natural key. If you do not want to display or edit these values in a data sheet application, hide these columns by setting their ColHidden property to

Click the >> button to select all columns, or > to select individual columns. You can also double-click a column to move it the Selected Columns list box. Click the << button to remove all items from the Selected Columns list box or click < to remove individually selected columns. You cannot remove columns prefixed with the asterisk markers.

When the selected columns are generated into the project, the binding statements are in the same order as you defined them. Also, controls that are being created use the same order. The Application Wizard provides no way of changing the order after you make your selections; however, where this is important (for example, in a Data Sheet project), you can alter the order in which the data is bound to the grid columns by changing the Bind statements in the BindForm or BindDetail procedures in the generated project’s form module. You must also alter the constant definitions for the columns in the Declarations section of the form module. The numbers assigned determine the ordinal position of the columns in the grid. The constants have kCol as the prefix to the table column names, for example, kColVendID.

N a t u r a l K e y

The Natural key is obtained in the following manner:

 The Application Wizard calls the stored procedure spGetUniqueKey. If this returns any columns, they are used as the Natural key.

 The Application Wizard then queries tsmDataDictTbl for the Natural keys defined for the table.

 The query that produces the larger number of columns becomes the Natural key.

L o o k u p I D

This is obtained by calling the stored procedure spDDDataSelect with the table name. If this does not return a LookupID, tsmLookup is queried. If both of these queries fail to find a LookupID, the Application Wizard builds a Simple LookupID using the IDColumn for the name and a suffix to identify the LookupID’s owner. To identify the LookupID as belonging to your company or organization, tsmSDKInfo is queried for the value of the CompanyCode column. If tsmSDKInfo does not exist (because the Ranges utility has not been run) “_AVT” is used as the LookupIDs Company Suffix. The Company Suffix is appended to the column name and the name is truncated if it is too long before adding the suffix. If the name has already been used, numbers are used to make the ID unique, further truncating the name if necessary. You need to alter the Lookup definition using the Lookup Definition utility. A simple LookupID, however, allows the new task to build, run, bind to data, and navigate between rows.

Not all project types need a LookupID. The wizards do not create Lookups for Blank Project, Single Row Maintenance, Report, and Data Sheet. If a Lookup is not required, no attempt is made to find or make one.

O r d e r B y

This is, by default, the same as the Natural key and cannot be changed during the wizard session.

If the Natural key has been defined using a Clustered index, the Order By clause must not be changed. Doing so causes SQL Server to create a temporary table, thus limiting performance.

S p e c i f i c s

This section details the different requirements of the project types at this step.

B l a n k P r o j e c t

Blank Project tasks have no data binding. This step is not available for this project type.

S i n g l e R o w M a i n t e n a n c e

 No Lookup control is required for this type of project.  The Application Wizard preselects the IDColumn, “*ID*”.  The Application Wizard preselects the Primary key (“*SK*”).

M u l t i R o w M a i n t e n a n c e

 This type of project must have a Lookup.

 The Application Wizard preselects the IDColumn, “*ID*”.  The Application Wizard preselects the Primary key (“*SK*”).

D a t a S h e e t

 No Lookup control is required for this type of project.

 The Application Wizard preselects all of the Natural key components (required by the Grid Manager). These contain the markers “*ID*” and “*UK*”.

S t e p 4 - R e v i e w C h o i c e s

The Application Wizard session concludes by presenting the information collected. You can view the information; however, you cannot make any changes. Click Back to return to previous steps to make any changes. To exit the Application Wizard session and build the new project, click Finish. If errors are discovered during the project generation process, they display in a window. These errors fall into three categories:

 Failure to reference a control or object because it is not correctly registered

 Failure to locate Common source code because the relative path to ‘..\Src\App\Common\’ cannot be found

 Other miscellaneous errors that are raised by the Application Wizard or Visual Basic during generation

If the problem is a control or object registration problem, you can retry the build process without having to repeat the Application Wizard session. From the Add-Ins menu in Visual Basic, select Sage ERP MAS 500 Repository > Load Project Definition. Browse the choices and select the AAW file. This window defaults to the last Application Wizard file that was generated.

Example of the Print ‘To Do’ List window

After building the project, the ToDo list appears if no errors were detected. Use this information as a guide. Click the Print button to get a permanent record, as this window does not display again.

Example of a Data Sheet project

The data sheet shown on page 17 was built using the example information processed in the previous pages and demonstrates the following intent and goals:

 Build a new project without reporting errors during the generation process  Compile in Visual Basic without errors

 Register the project with Sage ERP MAS 500

 Launch and run the task (when registered) from the Sage ERP MAS 500 Desktop

 Save and edit data  Insert and delete rows

There are caveats to this; however, and these relate to the database table(s) to which the task is bound. During the wizard session, you can select as many or as few columns as required. If any columns are not selected into a project, they have required values that are not defaulted and cannot be NULL, and there is no Trigger to obtain the data, then such an application cannot insert rows, and cannot make updates.

This may be a valid situation as deriving these values may be part of the application’s business logic that the Application Wizard does not have the intelligence to code.

C o n t r o l T y p e s a n d D a t a T y p e s

The Application Wizard determines what controls are appropriate for each of the selected columns. If the columns are to display in a grid, this becomes the Grid Cell Type defined in the Data Dictionary. If a discrete control is required, as in Multi Row Maintenance, the Application Wizard determines which control is appropriate and defines the control’s ProgID for the column. The Application Wizard also provides an appropriate name for the control using a control type prefix (such as, txt, lku, and dat) and the column name from the table. The algorithm for this decision is shown in the following table and involves an analysis of the column’s Data Dictionary properties, Data Type, and its DomainID.

The analysis is performed in the same order as the items shown in the table such that a column with DomainID YESNO is hosted in a SOTADropDown if the Data Dictionary property IsStaticList is 1. If no conditions are met the default control is SOTAMaskedEdit.

Line Entry projects contain a child table that may have some identical column names to those in the parent table. Child table control names are given the extra prefix: “Child,” for example, txtChildVendorName.

B u i l d i n g t h e P r o j e c t

This section describes how to build a Sage ERP MAS 500 project using a Project Definition file created by the Report Wizard.

L o a d A P r o j e c t D e f i n i t i o n F i l e

Normally, a new project is built immediately after the Application Wizard session finishes. There are times; however, when you may want to repeat the last project build (or any other that has a .AAW file)

From the Add-Ins menu in Visual Basic, select Sage ERP MAS 500 Repository > Load Project Definition. Use Windows Explorer to locate the required AAW file. Select the file. During project generation there will be periods of apparent inactivity; at other times, the UI generator and code Data Dict.

Property DomainID Data Type Control

IsStaticList SOTADropDown LookupID <> ““ TextLookup IsNaturalKey TextLookup IsForeignKey TextLookup SQL_DATE SOTACalendar MINORENTITYID TextLookup ACCTSEGVALUE TextLookup YESNO CheckBox AMOUNTHC SOTACurrency AMOUNTNC SOTACurrency AMOUNT SOTACurrency SQL_NUMBER SOTANumber Default SOTAMaskedEdit WARNING Prior to generating a new project using the procedure described in this section, ensure that any project presently loaded in Visual Basic has been saved or abandoned as required. This procedure forces you to save any currently loaded project prior to generating any new projects. It is safer if you begin with a new project that

L i n e E n t r y A p p l i c a t i o n W i z a r d

This section assumes that you are already familiar with the general concepts of application wizards. The following description details only the specifics of the Line Entry Application Wizard.

1. Enter the project information for the new task. For more information, see Step 2 - Project Details in the Application Wizard section.

2. Select the parent table from the Parent Table Columns combo box.

The table is selected and the available columns list box is populated when the combo box loses focus. Select the columns required for the header that will be placed on Tab 1 of the Line Entry Tab control. The wizard creates a Lookup control that is placed on the form (not on the Tab Control) and that is bound to the IDColumn of the parent table.

3. Select the child table and columns from the Child Table Columns combo box.

Select the child table to use for the new task. The Child Table drop-down list only lists those tables defined as being children of the selected parent table. Child tables are determined by querying tsmDataDictRelatn. Use the Data Dictionary to set up a parent/child relationship to use in a Line Entry project.

Select other child columns that are required to be bound by the detail table Data Manager. You can define the Order By column by selecting it in the Order By drop-down list.

The ParentLink defined for the child table is the parent table’s Primary key and the corresponding Foreign key in the child table. The table

tsmDataDictFK defines the Parent Link columns.

4. Review your choices. Examine the details for the new project and click Back to make changes or click Finish to generate the project.

After the project is successfully created, the ToDo List appears.

Example of the Print ‘To Do’ List window

E x a m p l e P r o j e c t s

B l a n k P r o j e c t

S i n g l e R o w M a i n t e n a n c e

Example of a Single Row Maintenance project using a tab control to contain bound controls

M u l t i p l e R o w M a i n t e n a n c e

Example of a Multiple Row Maintenance project with all controls placed on the form

D a t a S h e e t

L i n e E n t r y

Example showing the controls on the Header tab

R e p o r t W i z a r d

The Report Wizard is similar to the Application Wizard. Its purpose is to create an Application Wizard definition file (.AAW) that is used to build a new report project inside Visual Basic's IDE. The wizard collects information about the project, and upon completion, creates the file in the location specified for the project files.

S t a r t i n g t h e W i z a r d

To start the Report Wizard, open Visual Basic. From the Visual Basic menu, select Add-Ins > Add-In Manager. Select the Sage ERP MAS 500 Repository check box and click OK. Then, select Add-Ins > Sage ERP MAS 500

Repository > Report Application Wizard. Perform the following steps:

1. Enter the project details.

 Application Title

Type the name for the report form. Do not type the “frm” prefix. This prefix is added by the Report Wizard. The form’s name is used elsewhere to create other names in the code that do not require the prefix.

 Project ID

Type the project’s name, such as CIZRY001.  Module

Type the two-letter module abbreviation, such as AP or AR.  Form Caption

Type the caption that appears on the form.  Stored Procedure

Type the name of the stored procedure that is used to obtain data for the report.

 HTML Help

Select this check box if the project uses HTML Help (available only if the module selected is not a Sage delivered module). For SDK developed modules, you have the option to use WinHelp or HTML Help.

 Project Location

Use the Drive and Directory controls to point at a directory to store the new project.

2. Select the available tables. Select tables from the Available Tables list box and move them to the Selected Tables list box using the > (Add) button. You can also double-click a table to move it to a new location.

To remove tables from the Selected Tables list box, select the table(s) and click the < (Remove) button. You can select any table in the Selected Tables list box and move it up or down in the list box by clicking the Up and Down buttons.

3. Sort/select the columns.

For each table selected in the previous step, define the Select and Sort columns to use. Sage ERP MAS 500’s Data Dictionary provides the default report columns that are used if no columns are selected. The columns shown in the Sort/Select list boxes are prefixed by “*”.  Tables

Select a table from this drop-down list to view the fields it contains. The fields display in the Available Columns list box.

 Available Columns

Select the columns required for the report and use the appropriate ‘>’ (Add) button to move them to the Sort or Select Column list box. Columns added in this manner have the “+” prefix denoting that they are not defined as Sort or Select columns in the Data Dictionary.  Sort Columns/Select Columns

These list boxes display the columns that have been selected for the report. You can arrange the items in either list box by clicking the Up and Down buttons.

You can remove columns from a list box by clicking the associated < (Remove) button; however, if a column is defined in the Data

Dictionary as being a Sort or Select column, you cannot physically remove them using this window. You can only change the “*” prefix to “-”, which is required by the code generator to override the automatic generation of these columns.

4. Select the work tables for the report.

Provide the names of the work tables for the report project to use. Type a table name in the Work Table text box and click Add to add it to the list of work tables. To remove a work table, select it in the Work Table list box and click Remove.

5. Select the Key columns. The Code generator needs to know which column in each real table is the Key column. Select each table from the Tables drop-down list and select the Key column in the Available Columns list box. The selected field appears in the Key Column field.

 Tables

This list contains the tables that were selected in step 3. Select a table from this list box in order to identify its Key column.

 Available Columns

This list box displays the column names for the currently selected table. Select the Key column for the table. The column name appears

 Temp Table

This field displays the name of the temporary table that the report project uses.

6. Enter the report options. This step determines the elements that appear on the Options tab of the report form.

 Format

If you select this option, a Format frame is created on the Options tab containing the Summary and Detail check boxes.

 Message Required

If you select this option, a Message frame is created on the Options tab containing five message lines.

 Include Option

Any entries you enter in this option create an Include Options frame on the Options tab. Each item you enter creates a check box inside that frame with the text entered appearing as the check box’s caption.  Add

After typing the caption in the Include Option field, click Add to add the new item to the list.

 Remove

7. Review your choices. In this step you can review the choices made in the preceding steps and, if necessary, go back to make changes.

 Tree View

The tree view presents the information used to build the project. Click any node to expand the branches to view the data items. You cannot use the tree view to make changes.

 Finish

Click Finish to create the Application Wizard file. The file uses the same name as the project name with an extension of “.AAW.” This file is stored in the location defined in step 1.

To build the project created using this wizard, select Load Project Definition from the menu, browse to the project definition file, and click OK.

To load the project into Visual Basic, see Building the Project below.

B u i l d i n g t h e P r o j e c t

This section describes how to build a Sage ERP MAS 500 project using a Project Definition file created by the Application Wizard or the Report Wizard.

L o a d i n g a P r o j e c t D e f i n i t i o n F i l e

To build the project created, from the Visual Basic Add-Ins menu, select Sage ERP MAS 500 Repository > Load Project Definition. Next, browse to the project definition file, and click OK.

Use Windows Explorer to locate the required AAW file and select it. You are prompted to confirm that you want the project generation to proceed. If you click Yes, the project is built.

During project generation, you might notice periods of apparent inactivity; at other times you will see the UI generator and code generator working. The process is complete when the To Do List displays. You can print this list, if required.

Example of the Print ‘To Do’ List window

When this window closes, the project is built. Follow the instructions in the To Do List to complete the project.

E r r o r R i g g i n g

Error Rigging is an automatic process that generates the standard Sage ERP MAS 500 error handler code. Error handler code varies according to the type of module and the type of procedure. Sage ERP MAS 500 Repository provides methods to manage Error Rigging using the following strategies:  Rig or unrig an entire module

 Rig or unrig procedures by type (Sub/Function; Event; Property)  Rig or unrig selected procedures (multiselect from a list box)

The Error Rigging features apply only to the current module’s code window. At this time there is no way to rig an entire project using this tool.

The code generated conforms to the standards of the version 4.0 product (then called Acuity) using the VB5.X methodology and is hard-coded in Sage ERP MAS 500 Repository.

There are three important considerations when using this tool:  Avoid unintended rigging/unrigging

 Ensure that the main class module for an application is identified to the Rig engine

 Protect individual procedures from the rig process

These consideration are discussed in more detail in the following sections. WARNING

Before generating a new project, ensure that any project loaded in Visual Basic has been saved or abandoned as required. This procedure will force a save of any currently loaded project before generating a new project. It is safest if users start with a new project that has not been altered in any way.

WARNING

The Error Rigging engine is supplied on an as is basis and is not yet supported. We welcome and appreciate your comments and feedback on any problems or anomalies found.

E r r o r R i g g i n g U s e r I n t e r f a c e

To start Error Rigging from the Add-Ins menu, select Sage ERP MAS 500 Repository > Error Rigging.

R i g S t y l e

 Sage ERP MAS 500

Select the Sage ERP MAS 500 (for ALL Sage ERP MAS 500 Projects) option, which is the default, without exception for any Sage ERP MAS 500 module.

 Generic

For non-Sage ERP MAS 500 programs, the Generic style generates a simple error handler that displays a message containing the module name, procedure name, and error description. This style may not be used for Sage ERP MAS 500 modules.

R i g T a r g e t s

 All

Rigging or unrigging is applied to all procedures, properties, and events in the current module. The Error Rigging process does not affect any procedures that have Custom Error Handlers, or that contain the following line of code:

‘+++ VB/Rig Skip +++  Selected Types

Rigging or unrigging is applied to the selected types:  Subs and Functions

 Events  Properties

 Prompt before Rigging/Unrigging

If you select this option, you are prompted for permission to process each procedure selected during the Rigging or Unrigging process. If you clear this option, all selected procedures are processed. Be aware that there is no way to cancel the process after it is started.

 Apply

Click Apply to begin the rigging process. Rigging is only applied to selected procedures that have no Error Handler code. Any procedure that has an error handler is skipped. You can place the following line inside any procedure that you never want to be rigged:

‘+++ VB/Rig Skip +++

If you want standard error handling, do not modify any of the code that is generated by the Error Rigging process as it may cause the code to be unriggable. If you need to change error handling code, create a custom handler and remove all of the generated comments. Ensure that error handler labels are different than those generated by the rigging process. Remove the following comment lines:

“‘+++VB/Rig Begin Push +++” and “‘+++ VB/Rig End +++”

 Remove

Click Remove to begin the unrigging process. Unrigging is not performed on procedures that contain custom Error Handlers. Unrigging is only performed on procedures that have been previously rigged using the Sage ERP MAS 500 SDK.

During the unrigging process, the following line of code is added to the Declarations section if the application’s Main Class Module is detected: Private Const kbClassIsMainClass = True

This is required so that special rigging code can be applied to the public methods of the module that are used by the Sage ERP MAS 500 Desktop.

If this line of code is missing from your application’s Main Class Module, it needs to be added before the Rigging process is applied.

 Exit

Click Exit to close the Error Rigging window.

V i s u a l B a s i c 6 . 0 I D E - C o n d i t i o n a l C o m p i l a t i o n

The Visual Basic 6.0 IDE presents the following problem:

 When code is excluded because of Conditional Compilation settings, this code is not visible to the Error Rigging engine. To error rig these

procedures, you must ensure that they are included by appropriate Conditional Compilation settings. Always verify that you have exposed all of the procedures subject to Conditional Compilation.

You can find the utility by selecting the Start menu, and then selecting Programs > Sage Software > Sage ERP MAS 500 SDK > Data Range Maintenance.

O v e r v i e w

Several tables in Sage ERP MAS 500 store system information that can be used for customization. These tables are:

 Strings  Lookups  Messages  Modules  Tasks  Menus  Dictionary information

Each module that Sage produces has reserved certain ranges of key values in the tables listed above. All add-on packages should also reserve a set of ranges for the above information in order to prevent conflicts with future Sage ERP MAS 500 data and other third-party packages.

The Ranges utility has been modified to allow a new range for module numbers: 1,000

–

32,767. The Ranges utility is used to enter the ranges you reserve. All of the Application Framework utilities use the entered ranges to restrict entry. To reserve your ranges, contact Sage ERP MAS 500 Support.

W h o S h o u l d U s e I t

Anyone creating an add-on package or any company that is developing multiple Sage ERP MAS 500 Customization projects on different databases should use the Ranges utility to avoid conflicts.

H o w I t W o r k s

The Ranges utility creates a table called tsmSDKInfo to store the ranges. Sage ERP MAS 500 uses the default ranges, which do not conflict with Sage ERP MAS 500 ranges but could conflict with add-on packages if you do not reserve specific ranges. All of the Application Framework utilities restrict input to the ranges defined in tsmSDKInfo.

R a n g e s I n p u t

 Company Code

The Company Code field is a universally unique identifier for your company. It is used to uniquely identify any system data with character keys. The standard code contains all character-based keys that end with an underscore followed by your company identifier. For example, if company ABC adds a Lookup to the system that would list shipments, it would be called SOSHIP_ABC.

 Modules From/To

These fields define the range of module numbers you are allowed to enter into Sage ERP MAS 500.

 Tasks IDs From/To

These fields define the range of task IDs you are allowed to enter into Sage ERP MAS 500.

 Strings From/To

These fields define the range of string numbers you are allowed to enter into Sage ERP MAS 500.

 Messages From/To

These fields define the range of message numbers you are allowed to enter into Sage ERP MAS 500.

 Transaction Types From/To

These fields define the range of transaction type numbers you are allowed to enter into Sage ERP MAS 500.

 Entity Types From/To

These fields define the range of entity type numbers you are allowed to enter into Sage ERP MAS 500.

 Data Alerts From/To

These fields define the range of base data alert numbers you are allowed to enter into Sage ERP MAS 500.

NOTE

All existing users need to add 1,000,000,000 to the Task Sequence # From and Task Sequence To values assigned to them and also enter these numbers into the Assign Data Ranges dialog box at the Task IDs From and Task IDs To fields.

L o c a l S t r i n g s

The Local Strings utility helps you maintain simple text strings for multilingual purposes.

O v e r v i e w

A string is text that displays on Sage ERP MAS 500 forms and reports. Strings can be in English, French, or Spanish.

You find all text strings in the tsmString and tsmLocalString tables in the Sage ERP MAS 500 system database.

Every task that displays a string can call a function that retrieves it.

The utility is located in the \Utilities folder under your SDK installation folder. The first time that you use the utility you need to:

1. Register the task in Sage ERP MAS 500 by using the Task Editor. The Object Prog ID of the utility is Strings.clsTaskClass. Name it Strings Maintenance.

2. Add the task to the Sage ERP MAS 500 Desktop. For more information, refer to your Getting Started guide.

3. Use System Manager’s Maintain Security Groups utility to set up permissions for the utility.

W h o S h o u l d U s e I t

Sage recommends that you save your text strings in an external table, although doing so requires more time than specifying it in code. Choosing not to use the utility does not compromise the application’s functionality, but the utility can be useful when upgrading to a different language.

H o w I t W o r k s

The Local String utility divides all of the available text strings in Sage ERP MAS 500 into modules. A number and a constant identify each string. Each module has a range of numbers that it uses (for example, Accounts

Receivable ranges from 150000 to 159999). Each module maintains its own strings separately from the other modules. This utility allows you to browse the list, add, delete, and edit your strings; however, you cannot delete and add strings that belong to Sage ERP MAS 500 modules. You can add strings to your own modules, which reduces the chance that your data will collide with other add-on modules. For more information about adding a module, see Module.

WARNING

You cannot use this utility to maintain strings in other languages.

TIP

The Local String utility file is saved in a special format.

L o c a l S t r i n g s I n p u t

The following section describes the Local Strings utility.

 Number

This field indicates the unique number that identifies the string. When adding a new string, the string number defaults to the next highest unused number within the module range.

 Constant

This field represents the constant name that is used during Visual Basic development to identify the localized string. When generating a string.bas file for your localized strings, the following line of code will be generated: Public Const <CONSTANT> = <NUMBER>

 Local String

This field represents the localized text string.

L o c a l M e s s a g e s

The Local Messages utility helps you maintain messages that Sage ERP MAS 500 uses. This utility is primarily used for multilingual purposes and for controlling the messages that appear without recompiling the task. The Sage ERP MAS 500 message boxes use these messages to take advantage of a message’s properties.

O v e r v i e w

Sage maintains messages in an external source for multilingual and control purposes. Messages are considered to be text that displays in a Sage-specific message box. You can display a message in English, French, or Spanish. Specifying the language ID displays all messages using Sage’s message box in that language. Every message has properties such as button types, message icon, and a default button.

You can find all messages in the tsmMessage and tsmLocalMessage tables in the Sage ERP MAS 500 database. Every task that displays a message box can call the message box and specify the message number and language ID. The utility is located in the \Utilities folder under your SDK installation folder. The first time that you use the utility you need to:

1. Register the task by using the Task Editor. The Object Prog ID of the utility is Messages.clsTaskClass. Name it Messages Maintenance. 2. Add the task to the Desktop. For more information, refer to your Getting

Started guide.

3. Use System Manager’s Maintain Security Groups utility to set up permissions for the utility.

W h o S h o u l d U s e I t

Sage recommends that you save your messages in an external table, although doing so requires more time than specifying messages in code. Choosing not to use the utility does not compromise the application’s functionality, but the utility can be useful when displaying consistent messages and when expanding to a different language.

H o w I t W o r k s

The Local Messages utility divides all of the available messages in Sage ERP MAS 500 into modules. A number and a constant identify each message. Each module has a range of numbers that it uses (for example, Accounts Receivable ranges from 150000 to 159999). Each module maintains its own messages separately from the other modules. This utility allows you to browse the list, add, delete, and edit your messages. You can add strings to your own modules, which minimizes the chances that your data will collide with other add-on messages.

WARNING

You cannot use this utility to maintain strings in other languages.

TIP

The Local String utility file is saved in a special format.

L o c a l M e s s a g e I n p u t

The following section is a description of the Local Messages utility.

 Message No

This number identifies the message.

 Help Context No

The Help ID is used by the display message Help.

 Message Icon

Example of the Maintain Messages window

 Comment

This information indicates a comment that explains the message and where it is used.

 Buttons

Select the button type that represents the message to display.

 Cancel

Select the button that is activated when the user presses ESC.

 Default

Select the button that receives the focus when the message dialog box appears.

L o o k u p D e f i n i t i o n M a i n t e n a n c e

Use Lookup Definition Maintenance to create new and update existing lookup definitions.

O v e r v i e w

The Lookup definition is used by the Lookup control to provide information on what data to retrieve and how to display it. The most important information that the definition provides is the following:

 The command name (Table or View) from which the records are retrieved  The columns to display

 The columns to return back to the application

The Lookup ID uniquely identifies the Lookup definition and is used by the application to assign to a Lookup control. The task that activates the control is responsible for informing it of the Lookup ID.

To properly display the column information, any new table used by a Lookup definition or in a SQL view of a Lookup definition should have its metadata in the Data Dictionary. Use the Data Dictionary utility to import any new table into the Data Dictionary. If this is not done, the Lookup control will still work, but usability and cosmetic features may be diminished. For any Lookup definition using a new SQL view, the SQL view should be registered in the Data Dictionary. You can do this from the SQL View Column Mapping tab in the Lookup Definition Maintenance utility.

The Lookup Definition Maintenance utility is located in the \Utilities directory under your Sage ERP MAS 500 Customization installation directory. The first time you use this utility, you need to add a shortcut task on the Sage ERP MAS 500 Desktop. For more information, refer to your Getting Started guide.

C o n n e c t i n g t o t h e D a t a b a s e

In the Lookup Definition Maintenance window, select the Connect menu to invoke the Database Connection dialog box to connect to the application database.

S c r i p t i n g

Script can be generated for all updates applied to the database that occur in this maintenance form.

G e n e r a t e S c r i p t

In addition to maintaining the Lookup Definition in the database, you can also generate script to be applied to other databases. Select the Generate Script check box to record your Lookup Maintenance script.

S c r i p t T o o l b a r B u t t o n

After the script is generated, this toolbar button becomes enabled to signify that the script has been generated. To view the script, click the Script button. The Lookup View Maintenance Script dialog box appears.

Example of the Lookup View Maintenance Script window

The following is a list of things you can do with the script:

 Save the script to a file. Click the Save button. The Save As dialog box appears in which you can enter a file name.

 Prepare an Outlook e-mail message containing the script. Click the Email button. A message dialog box appears prompting you to enter an e-mail address. Click OK. The Outlook message appears. The message is not sent - you must click Send in Outlook to send the message. This feature is available only if Outlook is on your system.

 Copy the script to the Windows Clipboard. With the text selected, press CTRL+C to copy the script to the Clipboard.

L o o k u p I n p u t / L o o k u p D e f i n i t i o n

The following section details the input fields on the Lookup Definition Maintenance window. The Save and Delete buttons apply only for entries described in this section.

Example of the Lookup Definition Maintenance window

 Lookup ID

To create a new Lookup ID, click the Add button. Type the name of the new Lookup ID and press the TAB key. After saving a new Lookup definition, the Lookup ID is added to the list of existing entries and the form goes into edit mode.

Click the Cancel button to clear the form.

To maintain an existing Lookup ID, select the Lookup ID from the Lookup ID drop-down list. You can start typing in the drop-down box to help in your search; you will need to press the DOWN ARROW key to make the selection appear as the first item below the control in the list. At this point, your form will not be in Edit mode.

 Module

Select a module name from the drop-down list to attach the Lookup definition. This is for information purposes, but is also required in order for a new Lookup definition to be saved.

 Command Type

The command type can either be a SQL view or a table.  Name

 LookupView ID

Lookup View ID is the name that identifies the initial view for this Lookup. The default is Standard. You can enter up to 30 characters. The

LookupView ID field differs from the Lookup ID field in that there may be many differently named admin and user-customizable views created for one Lookup definition.

 Immediate Results

This check box determines whether or not to display the results of the Lookup Definition query when the Lookup Form message dialog box appears. The default setting is selected to indicate results immediately. This option can be overridden by the administrator from the Maintain Lookup Views task.

 Restrict to Display Columns

This check box determines whether or not to restrict customization to the set of columns set up initially for display. The default setting for the check box is cleared to indicate the feature is unrestricted. This option can be overridden by the administrator from the Maintain Lookup Views task.

 Display Columns

To specify the display columns, click the ellipses button to display the Select Columns For Display dialog box from which to choose the set and the order of the display columns. The set of display columns may be mutually exclusive from the set of return columns.

Do not include System columns (columns having IsInternal=1 or IsSurrogateKey=1 in the Data Dictionary) because the columns will be hidden.

 Return Columns

To specify the return columns, click the ellipses button to display the Select Columns For Display dialog box from which to choose the set and the order of the return columns. The set of return columns may be mutually exclusive from the set of display columns.

 Quick Filter Column Name

Select the column name to use initially as the "quick filter" column. The list of quick filter column names is determined by the set of column names shown in Display Columns having string data types. The string data type requirement is needed because the only quick filter operator allowed from this maintenance form is Begins With.

S e l e c t C o l u m n s F o r D i s p l a y D i a l o g B o x

The Select Columns For Display dialog box is used for maintaining both the display columns and the return columns.

 Available Command Columns

This list box contains the entire set of column names from the command name minus any columns already selected.

 Display Columns

This list box contains the chosen set of columns.

 Move >

Adds a column to the end of the Display Columns list box. Select a column name in the Available Command Columns list box and click Move >.

 < Move

Removes a column from the Display Columns list box. Select the column name that you do not want and click < Move.

 Up

Reorders a column up a level in the Display Columns list box. Select the column and click Up.

 Down.

Reorders a column down a level in the Display Columns list box. Select the column and click Down.

 Clear

Removes all columns from the Display Columns list box.

 Cancel

Cancels any changes and returns to the maintenance form.  Done

Accepts changes and returns to the maintenance form with the new values.

S Q L V i e w C o l M a p p i n g T a b

The SQL View Column Mapping tab provides a maintenance utility of its own related only to the SQL view command used by the Lookup definition. This tab becomes visible and enabled when a valid SQL view command name has been entered or displayed.

This tab becomes useful if you have designed a new SQL view or modified an existing SQL view that is used by a Lookup definition.

For the Lookup Control to make use of Data Dictionary information, the SQL View columns must be mapped to their underlying tables and columns. This information is stored in the tsmDataDictViewCol table.

 View Script

The Create View script is displayed but cannot be maintained here. Use SQL Enterprise or another view creation tool to properly maintain SQL views.

 SQL View DataDict Column Mapping grid

The SQL View DataDict Column Mapping grid displays all the mapping data for the SQL view.

 SQLViewColName - The name of the column in the SQL view.  TableName - The name of the underlying table.

 ColumnName - The name of the underlying column.

 SQLViewColName - The column caption to use instead of what is in the Data Dictionary.

 Get SQL View Column Mapping from Create View Script

Click the Get SQL View Column Mapping from Create View Script button to determine the SQLView column mappings. This does not write to the database, but updates the SQL View DataDict Column Mapping grid, where you are allowed to view and maintain the data.

The column mapping is performed by executing the SELECT statement of the SQL view and using ADO field properties to get the

BASETABLENAME and BASECOLUMNNAME. This process works as long as the data is not manipulated with a COALESCE or CONVERT function, for example. If a SQL view is comprised of a UNION or other SQL views, the BASETABLENAME and BASECOLUMNNAME may not exist in the ADO Field Properties. In cases like this, you may need to maintain your own mapping rows for these columns.

An additional feature of this column mapping button is its ability to create new SQL view column captions if there is a discrepancy between SQLViewColName and the underlying ColumnName. The assumption is that the column must have been aliased and therefore the base column's caption is not descriptive enough. The new CommonDLL DDCommon contains an algorithm to create the new caption. It splits up the

SQLViewColName value by proper casing. For each proper cased word (most likely abbreviated word), it gets the unabbreviated word and joins them all together putting a space between each word to create the new caption.

Example:

RcvgWhse becomes Receiving Warehouse

 Save DataDict SQLView Column Mapping

Click the Save DataDict SQLView Column Mapping button, to save the mapping. This writes all the rows from the SQLView DataDict column mapping grid to the tsmDataDictViewCol table for the SQL Command.