TransLution supports the ability to edit any database table from the TransLution client front end. This functionality has been built to allow custom tables that contain master data to be maintained without any additional implementation effort being required. Often a customer implementation will have custom data that should be maintainable by users. Examples of this include - Defining receiving tolerances, specifying paths for files, setting defaults to min and max values.

For example, if TransLution is being used to do some scanning of items into boxes, we may want the user to select the box type they are packing into. A box type table with box dimensions would be a good example of a table that can be maintained using this functionality.

 

Setting Up Master File Maintenance

 

The initial setup of master file maintenance is done by adding rows to a database table telling the system which tables support master file maintenance. There is nothing stopping one from simply adding the master file table to this list and then adding additional tables from the master file maintenance form.

 

An extract of the data in the MASTER_TABLE_LIST

 

 

MasterFileId - This is an automatic ID field

MasterFileUpdate and MasterFileQuery - both of these MUST be the same and are the name of the database table that will be updated using the Master File Maintenance functionality.

MasterFileDisplay - This is the text that the user will see as a description of the file they will be maintaining

UniqueId - It is not possible to maintain a file that does not have a unique ID field and this is the name of that column. It must match the unique table column name exactly and will not be shown on the grid for maintenance (since it can't be maintained).

Active Indicator - This is to be set to True for files we want to see on the maintenance form

Audit Time and username - this is audit data and not required for the maintenance to work

Sequence - Simply defines where on the list we want to see this file

SortColumn - This is the column that data is sorted by. It must be shown on the grid therefore the sort can not be by ID.

 

 

Using Master File Maintenance

The Master File Maintenance form is accessed from File - System Setup - Master Data Maintenance

 

 

 

On selecting this option, the user will see the following form with each master file that has been defined listed as shown

 

 

 

Upon selecting a file, they will then see each column in the database table for editing purposes. The user can now either edit existing rows or add new rows. It is not permitted to enter blank data - if data is not known when the row is being created use a 0 or a - but blank data cannot be entered.

 

 

Master File Table Rules

 

  • Each table MUST have an ACTIVE_INDICATOR column
  • Column names may NOT have spaces. If we use mixed case e.g GradeDescription in the column name, the grid will display Grade Description but the column name may not have a space.
  • The table may not have date fields for editing purposes. Dates are not part of master data and cannot be maintained using this form.
  • Each custom table that is maintained via Master File Maintenance must have an ID column, This is how we ensure that a row is unique.
  • If the table being maintained has a field called AuditDate then the date that it was modified will be logged here. This column will not be visible to users.
  • Also, if the table being maintained has a field called AuditUser then the ID of the logged on user will also be logged for each change.

These two Audit columns, while not required, are the best way to audit changes to master data tables and it is strongly recommended that they are used at all times.