IDEAScript editor randomly destroying my variables
Forums
Hi all
I've got a weird issue with the IDEAScript editor when working on my script to do ODBC imports.
Using the below example of my function to import a general ledger, if I run the script it works 100% perfectly, giving me exactly the data I need. But if instead I open in IDEAScript editor then my query(0) line gets wiped out entirely and replaced with an exact copy of the line above it (ie: table = "PostGL")
It's specifically IDEAScript editor doing this as if I open the exact same script file in Notepad++ then it shows the correct values.
I've had to resort to editing my script in Notepad and testing as a pinned macro on the ribbon, otherwise editing in IDEA breaks all my queries.
The query has less than 2500 chars so shouldn't be hitting any char-length limits for a string as far as I can tell. any clues as to why this happens?
ORIGINAL SCRIPT SNIPPET:
______________________________________________
Function GLImport 'Import the GL
ReDim query(3)
dbName = "PostGL.IMD"
table = "PostGL"
query(0) = "SELECT Entities.[Name] ,PostGL.[AutoIdx] ,PostGL.[TxDate] ,PostGL.[Id] ,Acc.[AccountLink] ,Acc.[Master_Sub_Account] AS LedgerAccount ,Acc.[Description] ,PostGL.[JobCodeLink] ,PostGL.[TrCodeID] ,CASE WHEN PostGL.JobCodeLink > 0 THEN JTP.TxType ELSE TC.Code END AS TrCode ,CASE WHEN PostGL.JobCodeLink > 0 THEN JTP.[Description] ELSE TC.[Description] END AS TrCode_Description ,PostGL.[Description] AS TrDescription ,CAST(PostGL.Debit AS DECIMAL(38,2)) AS Debit ,CAST(PostGL.Credit AS DECIMAL(38,2)) AS Credit ,CAST((PostGL.Debit - PostGL.Credit) AS DECIMAL(38,2)) AS Amount ,PostGL.iCurrencyID ,case when (Currency.CurrencyCode) is NULL THEN 'ZAR' else [CurrencyCode] END as CurrencyCode ,case when (Currency.cCurrencySymbol) is NULL THEN 'R' else cCurrencySymbol END AS CurrencySymbol ,case when (Currency.[Description]) is NULL THEN 'South African Rand' else Currency.[Description] END AS CurrencyDescription ,PostGL.fExchangeRate ,PostGL.fForeignDebit ,PostGL.fForeignCredit ,PostGL.Reference ,PostGL.Order_No ,PostGL.ExtOrderNum ,PostGL.cAuditNumber ,PostGL.Tax_Amount ,PostGL.fForeignTax ,PostGL.Project ,PostGL.[Period] ,PostGL.DrCrAccount ,PostGL.DTStamp ,PostGL.UserName ,PostGL.cReference2 ,PostGL.iInvLineID ,CASE WHEN SalesRep.Code is NULL then '' ELSE SalesRep.Code END AS RepCode ,CASE WHEN SalesRep.[Name] is NULL then '' ELSE SalesRep.[Name] END AS RepName ,CASE WHEN BR.cBranchCode is NULL then '' ELSE BR.cBranchCode END AS TxBranchCode ,CASE WHEN BR.cBranchDescription is NULL then '' ELSE BR.cBranchDescription END AS TxBranchDescription FROM dbo.Entities,dbo.PostGL LEFT OUTER JOIN dbo.Project ON PostGL.Project = Project.ProjectLink LEFT OUTER JOIN dbo._btblJCMaster ON PostGL.JobCodeLink = _btblJCMaster.IdJCMaster LEFT OUTER JOIN dbo.TrCodes TC ON PostGL.TrCodeID = TC.idTrCodes LEFT OUTER JOIN dbo.JobTxTp JTP ON PostGL.TrCodeID = JTP.idJobTxTp LEFT OUTER JOIN dbo.TaxRate Tax ON PostGL.TaxTypeID = Tax.idTaxRate LEFT OUTER JOIN dbo.Currency ON PostGL.iCurrencyID = Currency.CurrencyLink LEFT OUTER JOIN dbo.SalesRep ON PostGL.RepID = SalesRep.idSalesRep LEFT OUTER JOIN dbo.Accounts Acc ON PostGL.AccountLink = Acc.AccountLink LEFT OUTER JOIN dbo._etblBranch BR ON BR.idBranch = PostGL.iTxBranchID"
query(1) = " ORDER BY PostGL.AutoIdx"
query(2) = " WHERE PostGL.TxDate >= "
query(3) = " AND PostGL.TxDate <= "
Select Case dialogMenu2.DateFilter
Case 0 'date filter off
Client.ImportODBCFile "" & Chr(34) & "dbo" & Chr(34) & "." & Chr(34) & table & Chr(34) & "", dbName, FALSE, ";DSN=" & sqlserver & ";UID=;Trusted_Connection=Yes;APP=IDEA;WSID=" & WSID & ";DATABASE=" & database, query(0) & query(1)
Case 1 'date filter on
Client.ImportODBCFile "" & Chr(34) & "dbo" & Chr(34) & "." & Chr(34) & table & Chr(34) & "", dbName, FALSE, ";DSN=" & sqlserver & ";UID=;Trusted_Connection=Yes;APP=IDEA;WSID=" & WSID & ";DATABASE=" & database, query(0) & query(2) & "'" & dialogMenu2.FromYear & "-" & dialogMenu2.FromMonth & "-" & dialogMenu2.FromDay & "'" & query(3) & "'" & dialogMenu2.ToYear & "-" & dialogMenu2.ToMonth & "-" & dialogMenu2.ToDay & "'" & query(1)
End Select
Client.OpenDatabase (dbName)
Set db = Client.OpenDatabase(dbName)
db.ControlAmountField "AMOUNT"
End Function
______________________________________________
WHAT THE EDITOR DOES TO IT:
______________________________________________
Function GLImport 'Import the GL
ReDim query(3)
dbName = "PostGL.IMD"
table = "PostGL"
table = "PostGL"
query(1) = " ORDER BY PostGL.AutoIdx"
query(2) = " WHERE PostGL.TxDate >= "
query(3) = " AND PostGL.TxDate <= "
Select Case dialogMenu2.DateFilter
Case 0 'date filter off
Client.ImportODBCFile "" & Chr(34) & "dbo" & Chr(34) & "." & Chr(34) & table & Chr(34) & "", dbName, FALSE, ";DSN=" & sqlserver & ";UID=;Trusted_Connection=Yes;APP=IDEA;WSID=" & WSID & ";DATABASE=" & database, query(0) & query(1)
Case 1 'date filter on
Client.ImportODBCFile "" & Chr(34) & "dbo" & Chr(34) & "." & Chr(34) & table & Chr(34) & "", dbName, FALSE, ";DSN=" & sqlserver & ";UID=;Trusted_Connection=Yes;APP=IDEA;WSID=" & WSID & ";DATABASE=" & database, query(0) & query(2) & "'" & dialogMenu2.FromYear & "-" & dialogMenu2.FromMonth & "-" & dialogMenu2.FromDay & "'" & query(3) & "'" & dialogMenu2.ToYear & "-" & dialogMenu2.ToMonth & "-" & dialogMenu2.ToDay & "'" & query(1)
End Select
Client.OpenDatabase (dbName)
Set db = Client.OpenDatabase(dbName)
db.ControlAmountField "AMOUNT"
End Function
It is hard to say what the
It is hard to say what the problem is. I pasted the original in my editor, saved it and then loaded it and it didn't make any changes.
My best guess is the line is too long for the editor and it is getting confused. Can you break it up into smaller lines, maybe something like this and see if that fixes the problem.