Global Variables Returning Unexpected Results in After Document Succeeds Task
Tom asked 5 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?
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 5 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?
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 5 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.