Background to this mini-tutorial:
The database being used includes these tables:

In this example, Names and Addresses are stored in the Names (Global) table (except for City, State, and Country which are derived from another Global table).  The Names table contains Prospects, Customers, and Vendors. The Subledger table maintains master records for Customers and Vendors (containing linked data from the Names table and local data dealing with payment terms, method of payment, etc.).

The purpose of this Procedure is to enable the user to enter the Amount, Prefix, (the GL Number 302 is predetermined through Sources) and then enter the Vendor's Name in whole or in part into the Description. The Procedure, triggered by disturbing the Description field, will then follow these sequential phases, as necessary:

• Phase 1 - Find a match or matches in the Subledger table to the text string entered into the Description field. If this fails, the Procedure goes to
• Phase 2 - and attemps to locate a match or matches in the Global Names table. If it succeeds, it will create a linked Subledger in the Trade Subledger, for the record located in the Names table.

  Please Note: The automatic subledger generation only establishes the necessary links to the source table (Names in our example). Any local fields in the Subledger template that carry data that is specific to each subledger account (e.g. Terms Code, Sales Person, etc.) must be updated manually through General Data Entry.

Cursor Movement and Template selection
Amount > GL Prefix > GL Number. We recommend these three fields as the first data entry fields in the "Common" Transaction template as the data contained in these three fields determines which Extra fields are needed for the transaction in progress. Any required change in transaction template will happen automatically. The fourth field in our example is the Description field which will trigger the Description Procedure.

Please Note:
In our example, we have used a single database table based on the AR Template to hold both Customer (Accounts Receivable) and Vendor (Accounts Payable) master data. To handle the various Source Documents (e.g. Accounts Payable Invoices, Credit Notes, and Payments) which may require unique data entry Procedures, we developed clones of the AR Base Template (the template that establishes the structure of the underlying database table). In this specific example, the Title bar of the template reads API (AR) Transaction Template Workspace which indicates the:

• name of the template = API,
• the Base template = AR (shown only if the template is a clone).

Note the same format in the Title bar of the Procedure.

The Description Procedure
The Procedure carries out up to two phases to enter the Vendor information into the transaction record.

• Phase 1 attempts to find a match or matches to the text string entered into the Description
  field, in the Trade Subledger table.
• Phase 2 attempts to find a match or matches to the text string entered into the Description
  field, in the Names Global table.

Explanations to the Procedure

For the sake of visual ease, we have inserted Phase markers in the Procedure's Workspace. This Procedure is aborted in the event that the "GL Number" is not 302 (see the first Conditional object.

Phase 1:
This phase of the Procedure utilizes the following executable objects:

• the Transaction template contains a view field ([Account Description])in which we display the
  description of the Prefix-Gl Number-Subledger combination. The Function "PrefixToText" determines
  the Prefix Name and the Description Retrieval from "AccName" gives us the GL Account Name.

• the next Retrieval object attempts to retrieve the Vendor's "Names ID" and "Name" from the "Trade"
  table using these Inspector settings:
  
  • Retrieval view: Query returns = Multiple rows (all);Stop and show = ON.
  • Search view: Name Like Description;
    GL Account = GL Number;
    GL Prefix = GL Prefix.

• if the retrieval is successful (if more than one match is found, all matching records are displayed for selection of the proper one), the Calculation object assigns values to three fields:
  
  • the Vendor field is set to the Names ID from the Trade table.
  • the Description field is set to the full Name retrieved from the Trade table.
  • the Account Description field is set to the concatonated text string consisting
    of the Prefix Name - GL Account Name - Vendor Name.

• if Phase 1 fails to return an acceptable value, the Procedure advances to Phase 2.

Phase 2:
This phase of the Procedure utilizes the following executable objects:

• The Retrieval object attempts to retrieve the Vendor's "Names ID" and "Name" and "Record Id" from the "Names" table using these Inspector settings:
  
  • Retrieval view: Query returns = Multiple rows (all);Stop and show = ON.
  • Search view: Name Like Description.

• If no match is retrieved, the Procedure ends after giving an appropriate Alert message.

• If a match is found, (if more than one match is found, all matching records are displayed for selection of the proper one), the next Retrieval attempts to find a Subledger in the Trade table based on the Names ID from the Names table:

• if a Subledger is found, the Calculation object assigns values to three fields:
  • the Vendor field is set to the Names ID from the Names table.
  • the Description field is set to the full Name retrieved from the Names table.
  • the "Account Description" field is set to the concatonated text string consisting of the Prefix Name - GL Account Name - Vendor Name.

• if a Subledger is not found, the "AddLinkedSL" Function will attempt to add a linked Subledger for the Vendor to the Trade table.

• if successful, the Calculation object assigns values to three fields:

• the Vendor field is set to the Names ID from the Names table.
• the Description field is set to the Full Name retrieved from the Names table.
• the "Account Description" field is set to the concatonated text string consisting of the Prefix Name - GL Account Name - Vendor (Full) Name.

• if not successful, the Procedure ends after giving an appropriate Alert message and the cursor is placed into the Vendor field.

Subject to the accuracy and uniqueness of the text string that was entered into the Description field, you are now reasonably certain that the Name in question is not saved in the Names/Trade tables combination. To enter a new Name while in the middle of an Accounting Data Entry,

• enter a phoney number, higher than any current Names ID, into the Vendor field,
  this will display an Alert panel with three options: Lookup - Add - Retype;

• select Add new,
  this will display the Names data entry window with the phoney Names ID loaded:

• click on the "Record New" button in order to reset all fields,
  this will display an Alert panel warning of Unsaved data (the phoney number) with three options: Save - Do not dave - Cancel;
  
• select "Do not save" and enter the new data, Save the record,
  this will dismiss the Names window and load the just saved data into the Trade window;
  
• complete the Subledger fields in the Trade window and Save;
  this will dismiss the Trade window and redisplay the Accounting Data Entry window with the just saved Vendor number loaded into the Vendor field.This disturbance of the Vendor field will trigger this Procedure:

• as a result, the Description field will be completed with the Vendor Name and the "Account Description" field is set to the concatonated text string consisting of the Prefix Name - GL Account Name - Vendor Name.
  
• the Function "PrefixToText" determines the Prefix Name and the Description Retrieval from "AccName" gives us the GL Account Name.
• the next Retrieval object retrieves the Vendor's "Name" from the "Trade" table using these Inspector settings:
  
  
  • Retrieval view: Query returns = Single row.
  • Search view: Names ID = Vendor;
    GL Account = GL Number;
    GL Prefix = GL Prefix

• the Calculation object assigns values to two fields:
  
  
  • the Description field is set to the (Vendor) Name retrieved from the Trade table.
  • the "Account Description" field is set to the concatonated text string consisting of the Prefix Name - GL Account Name - Vendor Name.
    

Testing the Procedure
The following example shows the Testing of the Procedure showing the successful completion of Phase 1.

• click on Test button . This will display the Test Accounting Data Entry window. It is an exact copy of the actual window and has the same functionality with the exception that
  
  
  • transactions entered can not be saved, and
  • no other Procedures associated with any other fields will be triggered.
    
  
  
• enter an amount, enter the Prefix and GL Number, and enter all or part of the Vendor's name into the Description field

• this will display the Test panel. Press the Step button, one click for each executable object or instruction, and watch the progress until the Procedure exits at the Finish object.

On exiting the Procedure, the Test Transaction Data Entry window will be re-displayed with the specified fields filled in and the cursor advanced to the next input field:

If you would test this Procedure in a live setting, you would repeat the Test for each possible combination i.e. enter a name into the Description field that you not have in the Trade table but is set up in the Names table (to test Phase 2).

Go to:
Index
Table of Contents