Customization DS1358 Contract Number Additional Field

Customization DS1358

Contract Number Additional Field

Problem Definition:

ACME needs a Contract Number field added to the Receivables Transaction Entry and Bank Transaction Entry windows. The field needs to hold 50 characters that is not connected to another ID field within GP. This field does not need to be validated nor does it require a lookup.

The Contract Number must also be visible from the corresponding inquiry windows.

Solution Overview:

This solution will use a Virtual Field to add Contract Number to the requested windows.

Virtual Fields are added to windows at runtime and do not require modified or alternate forms. The Contract Number entered on the windows will be stored in a custom table with key fields to link back to the GP transaction tables.

The table will consist of the following fields:

Table Name: wContractNumbers

Field

Description (* Indicates Key Field)

Key String*

Required Key Field. Since this table will be used for both Bank Transactions and RM Transactions, this field will contain EITHER the Customer Number for RM Transactions or Checkbook ID for Bank Transactions.

Document Number*

The Bank Transaction Number or RM Transaction Number.

Document Type*

The Document Type. Values will match those in the Bank Transactions table or RM Transactions table.

Contract Number

The 50 character string field that stores the contract number.

Additional information regarding using the table for reporting is included in each section below.

Design Features:

Receivables Transaction Entry/Zoom

Graphical user interface, application

Description automatically generated

Graphical user interface, application

Description automatically generated

Field

Function

Contract Number

50 Character field that will be added just above the existing Currency ID field. This field will be editable on the Entry window, view-only on the Zoom window. The value will be saved to the database when the user clicks SAVE.

Table Name: wRMContractNumber

Field

Description (* Indicates Key Field)

Document Type*

The Document Type

Document Number*

The RM Document Number

Contract Number

The 50 character string field that stores the contract number

When a Receivables Transaction is created (and unposted), GP saves the record into the RM_Sales_WORK (SQL Name: RM10301) table. The primary key of this table is RM Document Type and RM Document Number. When a Contract Number needs to be entered, it will be saved to our custom table (wRMContractNumber).

The two tables can use the following relationship to be joined for reports that use the data in the RM_Sales_WORK table.

* Indicates Unique Record Key

RM_Sales_WORK

wRMContractNumber

RM Document Type-All*

————–

Document Type*

RM_Document Number-WORK*

————–

Document Number*

Customer Number

Contract Number

Etc.

After the Receivables transaction is posted, the document is moved into the RM_OPEN (RM20101) table. A unique record can also be found using the RM Document Type and Document Number. Any reports referencing the RM_OPEN table can also use the custom table by using the following join:

RM_OPEN

wRMContractNumber

RM_Document Type-All*

————–

Document Type*

RM_Document Number-WORK*

————–

Document Number*

Customer Number

Contract Number

Etc.

Bank Transaction Entry/Zoom

Graphical user interface, application, Word

Description automatically generated

Graphical user interface, table

Description automatically generated

Field

Function

Contract Number

50 Character field that will be added below the existing Type field. This field will be editable on the Entry window, view-only on the Zoom window. The value will be saved to the database when the user clicks POST.

Table Name: wCMContractNumber

Field

Description (*Indicates Key Field)

Record Number*

This is an auto-generated record number for the Bank Transaction. It is not displayed on the window but is a unique identifier for the record.

Contract Number

The 50 character string field that stores the contract number.

When a Bank transaction is posted, GP saves the record into the CM_Transactions (SQL Name: CM20200) table or CM_Receipts (CM20300). The primary key of these tables is Record Number. When a Contract Number needs to be entered, it will be saved to our custom table (wCMContractNumber).

The tables can use the following relationship to be joined for reports that use the data in the CM_Transactions or CM_Receipts table.

* Indicates Unique Record Key

CM_Transactions

wCMContractNumber

Record Number*

————–

Record Number*

CM Transaction Number

Contract Number

CM Transaction Type

Etc.

CM_Receipts

wCMContractNumber

Record Number*

————–

Record Number*

Receipt Number

Contract Number

Receipt Type

Etc.

GP Transaction Entry/Zoom

Graphical user interface, application, email

Description automatically generated

Graphical user interface, application

Description automatically generated

Field

Function

Contract Number

50 Character field that will be added below the existing Type field. This field will be editable on the Entry window, view-only on the Zoom window. The value will be saved to the database when the user clicks SAVE.

Table Name: wGLContractNumber

Field

Description (*Indicates Key Field)

Journal Entry*

The JE number from the transaction

Contract Number

The 50 character string field that stores the contract number.

When a journal entry is saved, GP saves the record into the GL_TRX_HDR_WORK (SQL Name: GL10000) table. The primary key of this tables is Journal Entry. When a Contract Number needs to be entered, it will be saved to our custom table (wGLContractNumber).

The tables can use the following relationship to be joined for reports that use the data in the GL_TRX_HDR_WORK table.

* Indicates Unique Record Key

GL_TRX_HDR_WORK

wCMContractNumber

Journal Entry*

—————-

Journal Entry*

Etc.

Contract Number

After the GL Transaction is posted, the document is moved into the GL_YTD_TRX_OPEN (GL20000) table. A unique record can also be found using the Journal Entry. Any reports referencing the GL_YTD_TRX_OPEN table can also use the custom table by using the following join:

GL_YTD_TRX_OPEN

wCMContractNumber

Journal Entry*

—————–

Journal Entry*

Etc.

Contract Number

After the GL Transaction is moved to a historical year, the document is moved into the GL_YTD_TRX_HIST (GL30000) table. A unique record can also be found using the Journal Entry. Any reports referencing the GL_YTD_TRX_HIST table can also use the custom table by using the following join:

GL_YTD_TRX_HIST

wCMContractNumber

Journal Entry*

—————–

Journal Entry*

Etc.

Contract Number

Assumptions/Requirements:

  1. The functionality described above will not work with eConnect, any software that uses eConnect, or any software that directly writes to, updates, or deletes from SQL tables.
  2. The functionality described above is intended for the GP Desktop client.
  3. National Accounts functionality is NOT used.
  4. Advanced Distribution functionality is NOT used.
  5. Unless otherwise noted in this document, reporting is not included in this estimate.
  6. Unless otherwise noted in this document, Word Template functionality is not addressed.
  7. Unless otherwise noted in this document, the enhancement will not integrate with 3rd party products. Some examples of 3rd party products would be:
  • An ISV plug-in product including WilloWare products
  • A dexterity customization designed by another developer
  • Dynamics GP Modules including, but not limited to:
  • Project Accounting
  • MDA
  • Analytical Accounting
  • Copy functionality found in SOP, POP and Inventory
  • Field Service
  • Extended Pricing
  • Manufacturing

For Information on this design, or any other WilloWare customization or product, please contact us:

www.willoware.com/contact-me