Monday, May 9, 2011

Microsoft GP Dexterity: Create Custom Tables in GP Using Dexterity Script

Create Table Procedure

Run Dexterity, create new procedure type name “wodCreateTable” and copy the code below

This procedure creates the specified named table in the current database,
and then grants privileges to the table and its associated stored procedures}

{Input parameter table name}
in string table_name;

{Output Boolean variable result}
out boolean result;
local anonymous table working_table;

{Create the table}
result = Table_SetCreateMode(true);
open table working_table with name table_name;

{Grant access to the table}
result = GrantAccess(physicalname(table working_table), false, "DYNGRP", 'Intercompany ID' of globals) of form 'SQL Maintenance';

{Grant access to the table auto-generated procedures}
result = GrantAccess(physicalname(table working_table), true, "DYNGRP", 'Intercompany ID' of globals) of form 'SQL Maintenance';

{Close the table}
close table working_table;
result = Table_FlushCache();

{Turn off table auto-create}
result = Table_SetCreateMode(false);

Calling Procedure to Create Tables

Create new procedure “wodSetupTable” and use the following code below to call CreateTable procedure

This procedure will create the tables for the application, if the SQL database manager is being used. The procedure will perform these actions only when the "sa" or "DYNSA" user logs into the system.}

if 'SQL Server' of globals > 0 then
            {Check if User login as an Administraor}
            if ('User ID' of globals = "sa") or ('User ID' of globals = "DYNSA") then
{First parameter is createTable procedure script name and seconds parameter is table name not physical or display name}
call FrtPlus_CreateTable, "wodSOPAddionalDetails";

{Call above script with different tables to create more tables}                   
            end if;
end if;

Trigger Registration in Startup Procedure

In Dexterity, create a new procedure “Startup” only if not already been created. Copy the code below in Startup procedure script to register create table procedure caller.

{- local Variables -}
local integer l_result;

l_result = Trigger_RegisterProcedure(script Add_Successful_Login_Record, TRIGGER_AFTER_ORIGINAL, script Setup_SQLTables);

if l_result <> SY_NOERR then
            warning "Procedure trigger registration for setting up tables failed.";
end if;

1 comment:

  1. This is a good article & good site.Thank you for sharing this article. It is help us following categorize:
    healthcare, e commerce, programming, multi platform,inventory management, cloud-based solutions, it consulting, retail, manufacturing, CRM, technology means, digital supply chain management, Delivering high-quality service for your business applications,
    Solutions for all Industries,
    Getting your applications talking is the key to better business processes,
    Rapid web services solutions for real business problems,
    Web-based Corporate Document Management System,
    Outsourcing Solution,
    Financial and Operations Business Intelligence Solution,

    Our address:
    2002 Timberloch Place, Suite 200
    The Woodlands, TX 77380