IM script to find Item's GL account for SOP 1

[barbola]

I used a script that came with Integration manager and changed it a bit and I'm getting an error. I am trying to override Segments 3 and 4 with the values in two columns in my csv file.
I have a script for the Distributions Account Number that works up to the sql statement.

DOC 1 ERROR: [Microsoft][ODBC SQL Server Driver][SQL Server]The column prefix 'IV00101' does not match with a table name or alias name used in the query.
DOC 1 ERROR: Error Executing Script 'Distributions.Account Number' Line 68:

sSQL = "SELECT Rtrim(ACTNUMBR_1) 'Segment1', "
sSQL = sSQL & "Rtrim(ACTNUMBR_2) 'Segment2', "
sSQL = sSQL & "Rtrim(ACTNUMBR_3) 'Segment3', "
sSQL = sSQL & "Rtrim(ACTNUMBR_4) 'Segment4'"
sSQL = sSQL & "FROM " & sDatabaseName & ".dbo.IV00101, "
sSQL = sSQL & sDatabaseName & ".dbo.GL00100 WHERE " 
sSQL = sSQL & "IV00101.ITEMNMBR= '" & sItemNumber & "'"
sSQL = sSQL & " AND IV00101." & sItemAccountFieldName 
sSQL = sSQL & "=GL00100.ACTINDX"

In Query Analyzer it works:

SELECT Rtrim(ACTNUMBR_1),Rtrim(ACTNUMBR_2),Rtrim(ACTNUMBR_3),Rtrim(ACTNUMBR_4) FROM TST.dbo.IV00101,TST.dbo.GL00100 WHERE IV00101.ITEMNMBR= 'PROD0001'
AND IV00101.IVSLSIDX=GL00100.ACTINDX

Any ideas?

 

[barbola]

Okay I got this to work. I had to add the database in front of all the table names.

The problem I am having now is, I want the default accounts to go into the distributions first and THEN change segment3 and 4. The reason for this is because if I only use the accounts from the Integration, I am missing the Accts Receivable account and the distributions are out of balance. This account isn't part of my data, because it's not associated with an item, it is just a default.

How else would I get another line of distributions integrated without having to have a separate source? Would I use a before query script or something?

thanks.

 

[barbola]

Isn't it fun answering your own threads? I should give myself a star!

I found the option in the distributions to "Default UnImported accounts" way cool!

b

 

[VictoriaYudin]

I'm giving you a star!!

Victoria
Flexible Solutions, Inc.