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);
