Skip to content
+1-888-319-3663

EONE BLOG

Tech Tuesday: How to update a form field from totals on detail lines


We have a form where we are tracking Customer POs, a Current Amount which is the total of the detail lines.  As the user enters additional amounts to the detail line, we need to update the Current Amount to be used in a report.

Estender Window

With Extender Enterprise edition, we can write code as the detail line changes, update the current amount as the amounts are entered in the scrolling window. While we can automatically show the total of the scrolling window, this is not stored in a field in the Extender tables to be able to be pulled onto a report or into a SmartList.  With the scrolling window, we are using a temporary table to store these amounts until the record is updated. This causes a timing issue on being able to query the SQL tables to get the amounts from the scrolling window and automatically update the current amount as the user tabs off the detail line. To solve this issue, with a little bit of code, and knowing the temporary table is being used, we can get the total of the amounts form the temporary table and store the value in the Current Amount field.

We will create an action on the Detail Line Change to update the current amount.  To accomplish this we will write a GP Script with some code we call Pass Thru SQL. We need to know the field number of the amount field in our form detail window. In the example below, the field number is 761.

 


 

local string ls_compiler_error,ls_intercompanyid, ls_TempTableName;

local long status,SQL_connection,status2,SQL_connection2;

local text lt_code,SQL_Statements,SQL_Statements2;

local text lt_execute;

local currency lc_CurrentAmount, lc_AppliedAmount;

 

lc_CurrentAmount = 0.00;

{Get the SQL database name for the company we are logged into}

lt_code=”inout text ls_intercompanyid;”;

lt_code=lt_code+”ls_intercompanyid=’Intercompany ID’ of globals;”;

 

if execute(0,lt_code,ls_compiler_error,ls_intercompanyid)<>0 then

                {A compiler error occurred. Display the error.}

                error ls_compiler_error;

end if;

 

{get the physical name of the SQL temp table ##9999999 }

ls_TempTableName =  Table_GetOSName(table EXT_Data_TEMP);

 

{Open SQL Connection}

status=SQL_Connect(SQL_connection);

{Build SQL Statements to set the SQL database name}

SQL_Statements=”use “+ls_intercompanyid;

status=SQL_Execute(SQL_connection,SQL_Statements);

 

{Build the SQL statement to get the total from the detail lines in the temp table}                              

SQL_Statements=”select sum(New_Total) CurrAmt  from ” + ls_TempTableName + ” where Field_ID = 761″;

 

status=SQL_Execute(SQL_connection,SQL_Statements);

status=SQL_FetchNext(SQL_connection);

 

while status<>31  do

                status=SQL_GetData(SQL_connection,1,lc_CurrentAmount);

                status=SQL_FetchNext(SQL_connection);

end while;

 

status = SQL_Terminate(SQL_connection);

 

{Set the field Current Amount on the window}

<Current Amount> = lc_CurrentAmount;

 


 

This code will keep the current amount up to date every time the detail line has been saved in the temporary table.  There are a couple of points to describe in more detail. As we are doing Pass Thru SQL Coding, we need to set the correct database in use. Dynamics GP stores that SQL database name in the ‘Intercompany ID’ of globals variable. We get that value and store it in the local variable ls_intercompanyid to set the correct SQL database to run our SQL query.

 

This line of code below gets us the physical name of the SQL temp table we are using in the detail window, which is EXT_Data_TEMP, but on SQL Server would be something like ##99999999, and we need that to do our SQL query from that table to get our sum.

 

ls_TempTableName =  Table_GetOSName(table EXT_Data_TEMP);

 

 

This section of code gets the value of the current amount from the SQL code, status<> 31 means we have successfully run the SQL command and we can get the value. We use a while loop in case there is more than one record returned, but in our case we know only one value will be returned. That value will be store in the local variable lc_CurrentAmount.

 

while status<>31  do

                status=SQL_GetData(SQL_connection,1,lc_CurrentAmount);

                status=SQL_FetchNext(SQL_connection);

end while;

 

 

Happy Coding!

Leave a Comment





RECENT POSTS


Tech Tuesday: Building My First Matrix Report
Overcoming the Top Challenges of Zendesk Integration
Accessing Historical Dynamics GP Data in NetSuite: Using Popdock
Popdock's Top 10 New Features
New Webinar: Getting Started with Matrix Reporting in Popdock

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 Microsoft dynamics crm Microsoft Dynamics GP Navigation List Builder Office Relationships Partner All Hands Call Popdock promotions release SalesForce SalesForce.com SmartConnect SmartConnect.com SmartConnect Bootcamp SmartConnect Maps SmartConnect Office Hours SmartList SmartList Builder SmartPost SmartView SQL Tech Tuesday Templates training Zendesk

Integrate & Automate without Any Code.

SmartList Data has Never Been Faster.

The Easiest Way to Report on GP Data.