IDEAScript editor randomly destroying my variables

Joel Edwards

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

Brian Element Thu, 02/16/2023 - 13:29

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.


query(0) = "SELECT  Entities.[Name] ,PostGL.[AutoIdx] ,PostGL.[TxDate] ,PostGL.[Id] ,Acc.[AccountLink] ,Acc.[Master_Sub_Account] AS LedgerAccount ,Acc.[Description] ,"
query(0) = query(0) & "PostGL.[JobCodeLink] ,PostGL.[TrCodeID] ,Case WHEN PostGL.JobCodeLink > 0 Then JTP.TxType Else TC.Code End As TrCode ,"
query(0) = query(0) & "Case WHEN PostGL.JobCodeLink > 0 Then JTP.[Description] Else TC.[Description] End As TrCode_Description ,PostGL.[Description] As TrDescription ,"
query(0) = query(0) & "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 ,"
query(0) = query(0) & "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 ,"
query(0) = query(0) & "case when (Currency.[Description]) is NULL THEN 'South African Rand' else Currency.[Description] END AS CurrencyDescription ,PostGL.fExchangeRate ,PostGL.fForeignDebit ,"
query(0) = query(0) & "PostGL.fForeignCredit ,PostGL.Reference ,PostGL.Order_No ,PostGL.ExtOrderNum ,PostGL.cAuditNumber ,PostGL.Tax_Amount ,PostGL.fForeignTax ,PostGL.Project ,"
query(0) = query(0) & "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 ,"
query(0) = query(0) & "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 ,"
query(0) = query(0) & "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 "
query(0) = query(0) & "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  "
query(0) = query(0) & "ON PostGL.TrCodeID = JTP.idJobTxTp LEFT OUTER JOIN dbo.TaxRate Tax  ON PostGL.TaxTypeID = Tax.idTaxRate LEFT OUTER JOIN dbo.Currency  ON PostGL.iCurrencyID = "
query(0) = query(0) & "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"