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

{Summary:

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

{Summary:

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;