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.
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!
