Import sales orders from a csv file

Sage 100 ERP

Sage 100 ERP
Welcome to the product group for Sage 100 ERP. Share discussions, questions, and best practices with other Sage 100 ERP users. Sage 100 ERP is the most comprehensive feature-rich management suite for mid-sized and smaller distributors and manufacturers.

Import sales orders from a csv file

  • Hi Forum,

     

    I've trying to have this script working but i guess i need to post it to get help from you guys....

    What i am trying to do is import sales orders from a csv file.  some orders have more than 1 line, most of them are single line.

    i need to get the Customer Number doing a find by customer Name. Once i get the customer number i build the S/O header and lines. My goal is to have one script... i have one script that creates new customers and new ship to codes.  The S/O code will be appended to the other script, but that's a different story.

    i cannot make it loop if the order has more than 1 line.

     

    Here is the code... what's left of the code, i've been adding and deleting lines...

     

    'Create ProvideX COM Object
    Set oScript = CreateObject ("ProvideX.Script")
    
    'The Init method must be the first method called
    ' Please remember to correct the MAS90\Home path
    oScript.Init("C:\Sage Software\MAS 90\VERSION4.4.6\Mas90\home")
    
    'The Session object must be the first MAS 90 object created
    Set oSS = oScript.NewObject("SY_Session")
    
    mdate = oSession.sModuleDate
    'sdate = oSession.sSystemDate
    'ucode = oSession.sUserCode
    
    ' Set the user for the Session
    r = oSS.nLogon()
    If r=0 Then
    	' Please remember to correct the username, password
    	r = oss.nSetUser("im","metals")
    End If
    
    ' Set the company, module date and module for the Session
    r = oss.nsetcompany("FIT")
    r = oSS.nSetDate("S/O",mdate)
    r = oSS.nSetModule("S/O")
    
    ' Instantiate a Sales Order business object
    oSEC = oSS.nSetProgram(oSS.nLookupTask("SO_SalesOrder_ui"))
    Set o = oScript.NewObject("SO_SalesOrder_bus", oSS)
    
    ' Instantiate a AR_Customer business object
    'Set oCust = oScript.NewObject("AR_Customer_bus, oSS)
    
    'Open the CSV file
    
    dim fs,objTextFile
    set fs=CreateObject("Scripting.FileSystemObject")
    dim arrStr
    set objTextFile = fs.OpenTextFile("C:\Akatz\Fitness First\orders.csv")
    
    Do while NOT objTextFile.AtEndOfStream
    arrStr = split(objTextFile.ReadLine,",")
    strOrderNo = arrstr(0)
    strName = arrStr(1)
    strPhone = arrStr(2)
    strAddress1 = arrStr(12)
    strAddress2 = arrStr(13)
    strAddress3 = arrStr(14)
    'strCity = arrStr(15)
    'strState = arrStr(16)
    strZip = arrStr(17)
    strCountryCode = arrStr(18)
    strItem = arrStr(3)
    strQty = arrStr(4)
    strPrice = arrStr(6)
    'strFreight = arrStr(8)
    strFreight = strFreight + arrStr(8)
    
    'Read AR_Customer to find if Customer exists
    
    retval = 0
    name = ""
    custExist = 0
    ARDivisionNo = "00"
    
    retval = oCust.nSetIndex("KNAME")
    retval = oCust.nFind(strName)   ' this will fail
    retval = oCust.nMoveNext()           ' go to next record
    if Not(cBool(oCust.nEOF)) then
    		retval = oCust.nGetValue("CustomerName$", name)
    		if UCase(strName) = UCase(name) then
    				custExist = 1
    		end if
    end if
    
    If custExist = 1 then
    
    ' Create a new header record using the next available order number
    OrderNo = ""
    r = o.nGetNextSalesOrderNo(OrderNo)
    r = o.nSetKey(OrderNo)
    r = o.nSetValue("ARDivisionNo$", "00")
    r = o.nSetValue("CustomerNo$", "0179854")
    r = o.nSetValue("CustomerPONo$", "BUSOBJTST")
    
    ' Create a new line for the item and write to memory file
    
    r = o.oLines.nAddLine()
    r = o.oLines.nSetValue("ItemCode$", strItem)
    r = o.oLines.nSetValue("WareHouseCode$", "000")
    r = o.oLines.nSetValue("QuantityOrdered", strQty)
    r = o.oLines.nSetValue("UnitPrice", strPrice)
    r = o.oLines.nWrite()
    
    
    ' Write header and lines to disk
    r = o.nSetValue("FreightAmt", strFreight)
    r = o.nGetValue("NonTaxableAmt", nontax)
    r = o.nSetValue("FreightAmt", freight)
    r = o.nSetValue("DepositAmt",nontax+freight)
    r = o.nWrite()
    
    strFreight = 0
    
    
    end if
    
    loop

     

    Doing it this way creates and order per line read... there are orders with 2 o more lines, therefore i am creating 2 or 3 orders instead of 1 order with 2 or 3 lines...

     

    Any suggestions?

     

    Regard,

    Manuel

  • Hey Manuel:

     

    When you have a header object and an associated line object, save the write statement for the header object  " r = o.nWrite()"  until you have written all of the lines pertaining to the same header object.  This statement will flush all of the lines to the detail table and then update the header table.  So move the  "loop" statement up to follow the Lines object write statement.  "r = o.oLines.nWrite()"

     

    This will work only if you have all of the lines related to the same order together in the input file.  If the line objects are not sorted by header object, then you must reread the header object record on every row from you input table, using the "r = o.nSetKey(OrderNo)" command. 

     

    Other comments:  The way your import is constructed you are creating a new sales order for every row of your input file, using this statement:  r = o.nGetNextSalesOrderNo(OrderNo).  It's customary to have a column in your input file that identifies lines that are associated with the same order number. On each row you must check to see if the previous row is associated, if so, reuse the same order number.  If you plan to assign a sales order number from MAS 90, only do so when the previous row and the current row are not associated.  You must also sort the input file by this column to assure the lines are in the correct sequence.

     

     

    Best of Luck,

    Doug.

     

  • Hi Doug,

     

    Thanks for the info... my probem now is the follwuing:

     

    On each row you must check to see if the previous row is associated, if so, reuse the same order number.  If you plan to assign a sales order number from MAS 90, only do so when the previous row and the current row are not associated

     

    This is what i am not able to acomplis, how to tell when the current row is or not associated  with the previous row.

     

    Regards,

    Manuel

  • I finally got it to work... kind of... goes thru the files ok and i can see the correct items and s/o numbers but it is not writting data to the mas90 tables....

    here is my code..:

     

    'Create ProvideX COM Object
    Set oScript = CreateObject ("ProvideX.Script")
    
    'The Init method must be the first method called
    ' Please remember to correct the MAS90\Home path
    oScript.Init("C:\Sage Software\MAS 90\VERSION4.4.6\Mas90\home")
    
    'The Session object must be the first MAS 90 object created
    Set oSS = oScript.NewObject("SY_Session")
    
    mdate = oSS.sModuleDate
    'sdate = oSS.sSystemDate
    'ucode = oSS.sUserCode
    
    ' Set the user for the Session
    r = oSS.nLogon()
    If r=0 Then
    	' Please remember to correct the username, password
    	r = oss.nSetUser("im","metals")
    End If
    
    ' Set the company, module date and module for the Session
    r = oss.nsetcompany("FIT")
    r = oSS.nSetDate("S/O",mdate)
    r = oSS.nSetModule("S/O")
    
    msgbox "I am In"
    
    ' Instantiate a Sales Order business object
    oSEC = oSS.nSetProgram(oSS.nLookupTask("SO_SalesOrder_ui"))
    Set o = oScript.NewObject("SO_SalesOrder_bus", oSS)
    
    ' Instantiate a AR_Customer business object
    oSEC = oSS.nSetProgram(oSS.nLookupTask("AR_Customer_ui"))
    Set oCust = oScript.NewObject("AR_Customer_bus", oSS)
    
    'Open the CSV file
    
    dim fs,objTextFile,objTextFileLine
    set fs=CreateObject("Scripting.FileSystemObject")
    dim arrStr,arrStrLines
    set objTextFile = fs.OpenTextFile("C:\Akatz\Fitness First\orders.csv")
    set objTextFileLines = fs.OpenTextFile("C:\Akatz\Fitness First\orders.csv")
    
    msgbox "CSV Files are opened"
    
    strHdrOrderNo = ""
    
    Do while NOT objTextFile.AtEndOfStream
    arrStr = split(objTextFile.ReadLine,",")
    strHdrOrderNo = arrStr(0)
    if strHdrOrderNo <> prevOrder then
    strOrderNo = arrStr(0)
    strName = arrStr(1)
    strPhone = arrStr(2)
    strAddress1 = arrStr(12)
    strAddress2 = arrStr(13)
    strAddress3 = arrStr(14)
    'strCity = arrStr(15)
    'strState = arrStr(16)
    strZip = arrStr(17)
    strCountryCode = arrStr(18)
    
    
    'Read AR_Customer
    msgbox "Let's read the Customer"
    
    retval = 0
    name = ""
    custExist = 0
    OrderNo = ""
    prevOrder = ""
    CustAdd1 = ""
    CustCity = ""
    CustCustNo = ""
    ARDivisionNo = "00"
    
    retval = oCust.nSetIndex("KNAME")
    retval = oCust.nFind(strName)   ' this will fail
    retval = oCust.nMoveNext()           ' go to next record
    if Not(cBool(oCust.nEOF)) then
    		retval = oCust.nGetValue("CustomerName$", name)
    		retval = oCust.nGetValue("CustomerNo$", CustCustNo)
    		retval = oCust.nGetValue("Address1$", CustAdd1)
    		retval = oCust.nGetValue("City$", CustCity)
    		if UCase(strName) = UCase(name) then
    				custExist = 1
    		end if
    end if
    
    msgbox "Customer Exists ?: " & custExist & " " & name
    
    If custExist = 1 then
    
    msgbox "Customer: " & CustCustNo & " " & name & "-" & strName & " " & CustAdd1 & " " & CustCity
    
    
    ' Create a new header record using the next available order number
    OrderNo = ""
    r = o.nGetNextSalesOrderNo(OrderNo)
    r = o.nSetKey(OrderNo)
    r = o.nSetValue("ARDivisionNo$", "00")
    r = o.nSetValue("CustomerNo$", CustCustNo)
    r = o.nSetValue("Comment$", strHdrOrderNo)
    prevOrder = strHdrOrderNo
    
    ' Create a new line for the item and write to memory file
    
    Do while NOT objTextFileLines.AtEndOfStream
    arrStrLines = split(objTextFileLines.ReadLine,",")
    LinesOrderNo = arrStrLines (0)
    strItem = arrStrLines (3)
    strQty = arrStrLines (4)
    strPrice = arrStrLines (6)
    'strFreight = arrStrLines (8)
    strFreight = strFreight + arrStrLines (8)
    
    if LinesOrderNo = strHdrOrderNo then
    
    r = o.oLines.nAddLine()
    r = o.oLines.nSetValue("ItemCode$", strItem)
    r = o.oLines.nSetValue("WareHouseCode$", "000")
    r = o.oLines.nSetValue("QuantityOrdered", strQty)
    r = o.oLines.nSetValue("UnitPrice", strPrice)
    r = o.oLines.nWrite()
    
    msgbox "Amazon Order Number is: " & LinesOrderNo & " " & strItem
    
    end if
    
    Loop
    
    
    msgbox "Finished Looping Amazon #: " & strHdrOrderNo
    
    ' Write header and lines to disk
    r = o.nSetValue("FreightAmt", strFreight)
    r = o.nGetValue("NonTaxableAmt", nontax)
    r = o.nSetValue("FreightAmt", freight)
    r = o.nSetValue("DepositAmt",nontax+freight)
    r = o.nWrite()
    
    msgbox "Writting S/O Number: " & OrderNo
    
    strFreight = 0
    
    
    end if
    
    
    'Close CSV file
    objTextFileLines.Close
    set objTextFileLines = Nothing
    set fs = Nothing
    
    'Open the CSV file
    'dim fs,objTextFileLine
    set fs=CreateObject("Scripting.FileSystemObject")
    'dim arrStrLines
    set objTextFileLines = fs.OpenTextFile("C:\Akatz\Fitness First\orders.csv")
    
    
    end if
    
    loop
    
    objTextFile.Close
    set objTextFile = Nothing
    set fs = Nothing

     

     

    Any suggestons...?

     

    Regards,

    Manuel

  • mroman98

     

    After a quick look, you are clearing "prevorder" after you pull in a new row.  It needs to retain its value after each row.

     

    Doug.

  • hi manuel,

     

    Check to see if your write is successful.  If r = 0 then display o.sLastErrorMsg.  This should be one of your first steps in debuggging any problems where the data is not getting into the Sgae 100 system.  If your write is not success (1) then check the error message.

     

    My guess is that it'll tell you, you are missing required fields or something.

     

    Thanks

    Elliott

  • Hi,

     

    The write is not succesful because.. i was in fact missing fields...

     

    Now, i am getting the following message:

     

    The Column is not in the IOList

    Item: 4406 doesn't exist...

     

    The Item exist.  Do i need to trim the item numbers???

     

    Regards,

    Manuel

  • Yes you will need to strip the trailing spaces off the item code if there are any.

  • I am still gettng the same error...

     

    Regards,

    Manuel Roman

  • Coming late to the party.  Not sure if it is your problem, but you reference a variable "freight" a few times without defining it or its value.  You also set "FreightAmt" twice, the second time setting it to this "freight" variable that has no value.

     

    r = o.nSetValue("FreightAmt", freight)
    msgbox "Finished Looping Amazon #: " & strHdrOrderNo
    
    ' Write header and lines to disk
    r = o.nSetValue("FreightAmt", strFreight)
    r = o.nGetValue("NonTaxableAmt", nontax)
    r = o.nSetValue("FreightAmt", freight)
    r = o.nSetValue("DepositAmt",nontax+freight)
    r = o.nWrite()
    
    msgbox "Writting S/O Number: " & OrderNo
    
    strFreight = 0

     

  • The line

     

    r = o.nSetValue("FreightAmt", freight)

     

    Shoulde be:

     

    r = o.nGetValue("FreightAmt", freight)

     

    I set the value then i get iback to use it on the last line:

     

    r = o.nSetValue("DepositAmt",nontax+freight)

     

    My problem is that i am geting an error reading the Item Number... i get the message that it doesn't exist and the item is in the CI_Item table....

     

     

    Regards,

    Manuel Roman

     

     

     

  • I know you've tried TRIM, but just for giggles can you throw up a msgbox with the itemcode value to see what it is actually trying to look up?  You've probably done that though.

     

    hrm.

  • Yes, I've done that... the item displays correctly.

     

    Regards,

     

    Manuel Roman

  • Hi Forum,

     

    I got an example of an script to create SO... i am not seeing anything different compared to what i already have.

    one thing that i noticed it that...:

     

    r = oLines.nAddLine()

     

    the r value is '0' - zero.... it doesn't make any sense.... i am setting the oLines by doing the following..

     

    ' Instantiate a Sales Order business object
    oSEC = oSS.nSetProgram(oSS.nLookupTask("SO_SalesOrder_ui"))
    Set o = oScript.NewObject("SO_SalesOrder_bus", oSS)
    oLines = 0
    Set oLines = oScript.NewObject("SO_SalesOrderDetail_bus", oSS)
    
    ' Instantiate a AR_Customer business object
    oSEC = oSS.nSetProgram(oSS.nLookupTask("AR_Customer_ui"))
    Set oCust = oScript.NewObject("AR_Customer_bus", oSS)

     Am i missing something here???

     

    Regards,

    Manuel Roman

     

  • manuel,

     

    You cannot instantiate the detail lines object by itself, you need to use the object handle within the header object.  The error message you are probably getting is that you have an invalid header, because you are trying to use the lines object standalone.

     

    Set oLines = o.oLines

     

    instead of how you are defining the lines object.

     

    Hope this helps

     

    Elliott