Skip to content
+1-888-319-3663

COMMUNITY FORUM

Global Variables Returning Unexpected Results in After Document Succeeds Task

Tom asked 3 years ago
I’m importing AR Cash Receipts from a SQL table into Dynamics GP.  In some cases I need to modify the check number by adding “.01” (or .02, .03…) to the end of the check number.  This is working correctly and I can see AR Cash receipts going into Dynamics GP with the correct new check number.
The problem is when I try to write the new check number back to the SQL Table.  I have two after success tasks that I user for this.  The first simply displays the values in the Global Variables used to store the INITIAL and NEW check numbers:  (I’ll delete this when I get everything working of course)
TASK #1: Run Script
messagebox.show(“N=” & GBL_CHECK_NUMBER_NEW)
messagebox.show(“I=” & GBL_CHECK_NUMBER_INITIAL)
return TRUE
 
The Second is a SQL Task to update the source table’s Status Flags, Payment Number and Check Number
TASK 2: (Run SQL Command)
DECLARE @DBID VarChar(10)
DECLARE @CustID Varchar(15)
DECLARE @CheckNumberInitial VarChar(25)
DECLARE @CheckNumberNew VarChar(25)
DECLARE @File VarChar(255)
DECLARE @PayNo VarChar(20)
DECLARE @dDate Date
 
SET @DBID =  ‘GBL_DBID’ 
SET @CustID =  ‘GBL_CUSTOMER_ID’
SET @CheckNumberInitial = ‘69548811’ –‘GBL_CHECK_NUMBER_INITIAL’
SET @CheckNumberNew =  GBL_CHECK_NUMBER_NEW
SET @File = ‘GBL_HR_SOURCEFILE’
SET @PayNo = ‘GlobalRollingColumn’
SET @dDate = ‘GBL_DEPOSIT_DATE’
 
UPDATE STAGING..HR_Payments 
SET 
Integrated = 1, 
IntegratedDateTime = getdate(), 
GP_PaymentNumber = @PayNo, 
CheckNumber = @CheckNumberNew 
WHERE 
GP_Company = @DBID AND 
SourceFile = @File  AND 
CheckNumber = @CheckNumberInitial and 
InvoiceCustomerID =  @CustID AND 
DepositDate = @dDate
 
When I integrate a test check (#69548811) two thing’s happen
1) Task # 1 shows the expected values for both global variable (i.e. 69548811 and 68548811.01)
2) IF I use SET @CheckNumberInitial = ‘GBL_CHECK_NUMBER_INITIAL’ THEN the source table does not get updated at all
If I use SET @CheckNumberInitial = ‘69548811’ THEN the source data gets updated BUT the check number in the source table is set to “_NEW”
 
I’m stumped.  Is there a limit to the number of global’s I can pass into a sql script?   Why is @CheckNumberNew geting set to “_NEW”?  Why does @CheckNumberInitial seem to not get set at all?
 
 
 
 
Answers
Tom answered 3 years ago
File under “Are you kidding?”
Changing the check number global variable names from “GBL_CHECK_NUMBER_NEW” and “GBL_CHECK_NUMBER_INITIAL” to “GBL_CHECKNUMBER_NEW” and “GBL_CHECKNUMBER_INITIAL”  resolved the problem.
Apparently SmartConnect doesn’t like too many underscores in the global variable names.  Who knew? 
Ethan Sorenson Staff replied 3 years ago

Tom,

I just tried your scenario and had no issues with the long Global Variable names.

The more likely cause is there are other Global Variables with a full name the same as the start of your variable.

For example, if you have two Global Variables
GBL_CHECK_NUMBER
GBL_CHECK_NUMBER_INITIAL

SmartConnect will always return GBL_CHECK_NUMBER. The way SmartConnect parses scripts to look for Global Variables means it will stop searching once it has found a matching Global.

This is why changing the name of the variable to not use the second underscore made the variable visible to SmartConnect.


If you would like to submit an answer or comment, please sign in to the eOne portal.