Adding columns to the Sales order grid

By | January 23, 2009
0 Comment

Adding columns to the Sales order grid on the ‘Enter Sales Order and Quotes’ screen in MAS 500 can be challenging work.

Below we provide instructions and sample code of adding additional columns in the Sales order Grid on the ‘Enter Sales Order and Quotes’ screen in MAS 500:

You will need to add the ‘New Field’ coulmn to ‘tsoSOLine’ table and insert records in the following tables:

  1. tsmString
  2. tsmLocalString
  3. tsmCustomGridField
  4. tsmCustomGrid

You will also have add code in the source file of Sales Order Entry form.

Example:

Below is the SQL Code user needs to run in SQL Query analyzer against the MAS500 Database to add a column named ‘New Field’.

  1. Adding the column to ‘tsoSOLine’ table.
    ALTER TABLE tsoSOLine
    ADD NewField VARCHAR(30)
  2. Inserting records in tsmString and tsmLocalString tables for the new field.
    You need to assign a StringNo to be used for the new field. In our example we shall use a variable called @_lStringNo.
    DECLARE @_lStringNo INT
    SELECT @_lStringNo = 1
    INSERT INTO tsmString (StringNo, ConstantName) VALUES @_lStringNo, ‘kDDUser’+CONVERT (VARCHAR (10), @_lStringNo ))INSERT INTO tsmLocalString (StringNo, LanguageID, LocalText) VALUES (@_lStringNo , 1033,‘NewField’)where,‘LocalText’ is the column name.
  3. Inserting the records in tsmCustomGridField and tsmCustomGrid tables:
    The below code is for example purposes only.
    INSERT INTO tsmCustomGridField (CustomGridFieldKey, CustomGridFieldGroupKey, DisplayOnly, EnterInGridRequired, GridFieldDescStringNo, GridFieldSeqNo, HostFormFieldName, PrimaryCustomGridFieldKey)
    VALUES (@_lNextKey, 5, 0, 0, StringNo, 62,'NewField', NULL)    INSERT INTO tsmCustomGrid (CustomGridKey, CompanyID, CustomGridFieldKey, CustomizationLevel, EnterInGrid, FreezeInGrid, GridSeqNo, UserGroupID, UserID)
    VALUES (@_lNextKey, NULL, @_lNextKey, 1, 0, 0, 62, NULL, NULL)
  4. Coding in the Sales Order Form:
    You need to write the code into the following procedures.
  • SetupColumns ()
    Set up the column index variables for new field.
  • MapTabControlsToGrid ()
    Map the tab controls to the associated grid column for new field.
  • BindForm ()
    Bind the controls to the database i.e Bind New Field control to the ‘NewField’column of ‘tsoSOLine’ table.
  • FormatGrid ()
    Format the column.

Hope this helps.

In case of any assistance feel free to contact us at sage@greytrix.com.

About Us
Greytrix is a one-stop solution provider for Sage ERP and Sage CRM needs. We provide complete end-to-end assistance for your technical consultations, product customizations, data migration, system integrations, third-party add-on development, and implementation expertise.

Greytrix has some unique solutions of Sage 100 integration with Sage CRM, and Salesforce.com along with Sage 100 Migration from Sage 50 US, Sage Intacct, QuickBooks, Sage Business Vision, and Sage Business Works. We also offer best-in-class Sage 100 customization and development services to Sage business partners, end users, and Sage PSG worldwide.

For more details on Sage 100 Services, please contact us at sage@greytrix.com. We will be glad to assist you.