Skip to content
+1-888-319-3663

EONE BLOG

Triggering Sanscript to run anywhere in Dynamics GP


This is perhaps the best kept secret about Dynamics GP. You can use eXtender to trigger small pieces of code from anywhere you like, just like you can trigger an eXtender window to open. The hardest part of an implementation is usually the last 10% of functionality and this is made easy by eXtender Logic.

This example adds logic to an existing Great Plains form. In this scenario, we need to add logic to the Great Plains Sales Order Processing screen that checks to see if duplicate Customer PO number has already been entered for the specific customer. In this example we use the Logic Routine to create a trigger on an existing Great Plains form. In the screenshot we can see that the trigger has been placed on the Sales Order Processing – Customer PO Number – Field change event.

After the Customer PO number has been entered, the script runs which executes a query of the SOP work, open, and history tables to see if that particular Customer PO Number combination has been entered previously. If it finds it, it will open a dialog box asking if you want to “Open Inquiry, Continue, or Delete”.

If the user selects “Open Inquiry” it will open the Sop Document Inquiry window with the Invoice number that contains the duplicate PO Number. If the user clicks “Continue” it will allow the user to continue to enter the duplicate Customer PO Number. If the user hits delete, it will actually delete the current SOP document being entered.

The code window looks like this.

The actual code is posted below so you can see exactly what happens. Again I repeat that if you do not understand the code below – then don’t try and write logic scipts. If this makes perfect sense then there is so much you can do within GP with eXtender logic. The best part is you can deploy this code by sending a script to be imported, and you never need to cnk up or upgrade dictionaries again for small customisations.

local string ls_po_number, ls_customer, ls_sop_number;
local integer li_sop_type;
local text lt_sql;
local long ll_sql_connection, ll_status;
ls_customer = ‘Customer Number’ of window SOP_Entry of form SOP_Entry;
ls_po_number = ‘Customer PO Number’ of window SOP_Entry of form SOP_Entry;

if empty(ls_po_number) or empty(ls_customer) then
abort script;
end if;
li_sop_type = ‘SOP Type’ of window SOP_Entry of form SOP_Entry;
ls_sop_number = ‘SOP Number’ of window SOP_Entry of form SOP_Entry;
SQL_Connect(ll_sql_connection);
lt_sql = “USE ” + ‘Intercompany ID’ of globals;
SQL_Execute(ll_sql_connection, lt_sql);
{check if customer and po combination exists in work table}

lt_sql = “select SOPTYPE, SOPNUMBE from SOP10100 where “;
lt_sql = lt_sql + “CUSTNMBR = ” + SQL_FormatStrings(ls_customer) + ” and “;
lt_sql = lt_sql + “CSTPONBR = ” + SQL_FormatStrings(ls_po_number) + ” and “;
lt_sql = lt_sql + “not (SOPNUMBE = ” + SQL_FormatStrings(ls_sop_number) + ” and SOPTYPE = ” + str(li_sop_type) + “)”;
ll_status = SQL_Execute(ll_sql_connection, lt_sql);
if ll_status = 0 then
ll_status = SQL_FetchNext(ll_sql_connection);
if ll_status = 0 then
SQL_GetData(ll_sql_connection, 1, li_sop_type);
SQL_GetData(ll_sql_connection, 2, ls_sop_number);

case ask(“A document has already been entered for this customer with this purchase order number.”, “Open Inquiry”, “Continue”, “Delete”)

in [ASKBUTTON1]

open form SOP_Document_Inquiry;
if isopen(form SOP_Document_Inquiry) then
‘Sort By’ of window SOP_Document_Inquiry of form SOP_Document_Inquiry = 1;
run script ‘Sort By’ of window SOP_Document_Inquiry of form SOP_Document_Inquiry;

‘All Or Range’ of window SOP_Document_Inquiry of form SOP_Document_Inquiry = 1;
run script ‘All Or Range’ of window SOP_Document_Inquiry of form SOP_Document_Inquiry;
‘Start SOP Number’ of window SOP_Document_Inquiry of form SOP_Document_Inquiry = ls_sop_number;

‘End SOP Number’ of window SOP_Document_Inquiry of form SOP_Document_Inquiry = ls_sop_number;
‘Include GB’ of window SOP_Document_Inquiry of form SOP_Document_Inquiry = 0;
run script ‘Include GB’ of window SOP_Document_Inquiry of form SOP_Document_Inquiry;
run script ‘Redisplay Button’ of window SOP_Document_Inquiry of form SOP_Document_Inquiry;
end if;

in [ASKBUTTON3]
run script delayed ‘Delete Button’ of window SOP_Entry of form SOP_Entry;
end case;

abort script;
end if;
end if;

{check if customer and po combination exists in history table}

lt_sql = “select SOPTYPE, SOPNUMBE from SOP30200 where “;
lt_sql = lt_sql + “CUSTNMBR = ” + SQL_FormatStrings(ls_customer) + ” and “;
lt_sql = lt_sql + “CSTPONBR = ” + SQL_FormatStrings(ls_po_number);
ll_status = SQL_Execute(ll_sql_connection, lt_sql);
if ll_status = 0 then
ll_status = SQL_FetchNext(ll_sql_connection);
if ll_status = 0 then

SQL_GetData(ll_sql_connection, 1, li_sop_type);
SQL_GetData(ll_sql_connection, 2, ls_sop_number);
case ask(“A document has already been entered for this customer with this purchase order number.”, “Open Inquiry”, “Continue”, “Delete”)

in [ASKBUTTON1]
open form SOP_Document_Inquiry;
if isopen(form SOP_Document_Inquiry) then
‘Sort By’ of window SOP_Document_Inquiry of form SOP_Document_Inquiry = 1;
run script ‘Sort By’ of window SOP_Document_Inquiry of form SOP_Document_Inquiry;
‘All Or Range’ of window SOP_Document_Inquiry of form SOP_Document_Inquiry = 1;
run script ‘All Or Range’ of window SOP_Document_Inquiry of form SOP_Document_Inquiry;
‘Start SOP Number’ of window SOP_Document_Inquiry of form SOP_Document_Inquiry = ls_sop_number;

‘End SOP Number’ of window SOP_Document_Inquiry of form SOP_Document_Inquiry = ls_sop_number;
‘Include GB’ of window SOP_Document_Inquiry of form SOP_Document_Inquiry = 1;
run script ‘Include GB’ of window SOP_Document_Inquiry of form SOP_Document_Inquiry;
run script ‘Redisplay Button’ of window SOP_Document_Inquiry of form SOP_Document_Inquiry;
end if;

in [ASKBUTTON3]
run script delayed ‘Delete Button’ of window SOP_Entry of form SOP_Entry;
end case;
abort script;
end if;
end if;
SQL_Terminate(ll_sql_connection);

1 Comment

  1. john on July 29, 2010 at 9:48 am

    Bumping an older article, but is there a way to trigger on the Void button for a SOP Document? I found a post by Marion on using VST to do it, and I've used eXtender logic to trigger other buttons, but the new Action button is resisting my attempts to latch on to it.

Leave a Comment





RECENT POSTS


2021 Q4 Promotions
Connect with us at Directions EMEA 2021 in Milan!
eOne at Summit North America 2021
Employee Spotlight: Megan Tripp, Business Development Representative
eOne is now compatible with Microsoft Dynamics GP October 2021 Release

POPULAR POSTS


2016 SmartConnect Integration Bootcamps
Tech Tues: Automatically refreshing Pivot Tables in Excel Refreshable Reports
Happy Thanksgiving from the eOne team!
2017 SmartConnect Integration Bootcamps
New Releases of Extender and SmartList Builder

CATEGORIES

TAGS

Business Central CRM D365 Business Central Dynamics 365 dynamics crm Dynamics GP Dynamics NAV Econnect Employee Spotlight eone eOne News error Error Message Events Excel Excel Report Builder Extender Flexicoder GP integration Map Meet the Team Microsoft dynamics crm Microsoft Dynamics GP Navigation List Builder Office Relationships partners Popdock release Reporting SalesForce SalesForce.com SmartConnect SmartConnect.com SmartConnect Bootcamp SmartConnect Maps SmartConnect Office Hours SmartList SmartList Builder SmartPost SmartView SQL Tech Tuesday Templates training

Integrate & Automate without Any Code.

SmartList Data has Never Been Faster.

The Easiest Way to Report on GP Data.