How can you insert invoice lines into Sage 50 automatically?

Question

I'm trying to find a way to automate data entry into the raise invoice screen in Sage 50.

All of our order data is held in a different system and we could easily pull together the line items, customer data, etc. automatically but our accounts team currently have to manually select each row, enter the SKU and quantity which is very time consuming.

It appears that the clipboard isn't functional in the Product Code field either - which is really annoying!

Are there any reasonable ways to inject data like this into Sage 50?

Answer 1

All of the current commercial products will require you to put your data into a specific format (column order and file type) anyway, so if you can do that, then bring everything into Excel, and then adapt the code listed above for VB.Net into VBA. It's fairly straightforward, mainly passing data to an array and then looping through. If you want specific assistance, show us the structure of your Order data, and then we can do something Cheers Paul

Answer 2

Here is an example using VB.Net:

'Declare objects
Dim oSDO As SageDataObject230.SDOEngine
Dim oWS As SageDataObject230.WorkSpace
Dim oSOPRecord As SageDataObject230.SopRecord
Dim oSOPItem_Read As SageDataObject230.SopItem
Dim oSOPItem_Write As SageDataObject230.SopItem
Dim oSOPPost As SageDataObject230.SopPost
Dim oStockRecord As SageDataObject230.StockRecord

'Declare Variables
Dim szDataPath As String

'Create SDO Engine Object
oSDO = New SageDataObject230.SDOEngine

' Select company. The SelectCompany method takes the program install
' folder as a parameter
szDataPath = oSDO.SelectCompany("C:\Documents and Settings\All Users\Application Data\Sage\Accounts\2017\")

'Create Workspace
oWS = oSDO.Workspaces.Add("Example")

'Try to connect
If oWS.Connect(szDataPath, "manager", "", "Example") Then

    'Create objects
    oSOPRecord = oWS.CreateObject("SOPRecord")
    oSOPPost = oWS.CreateObject("SOPPost")
    oSOPItem_Read = oWS.CreateObject("SOPItem")
    oStockRecord = oWS.CreateObject("StockRecord")

    'Read an existing Sales Order
    oSOPRecord.MoveLast()

    'Populate the order header, copying fields from oSOPRecord to oSOPPost
    oSOPPost.Header("INVOICE_NUMBER").Value = oSOPRecord.Fields.Item("INVOICE_NUMBER").Value
    oSOPPost.Header("ACCOUNT_REF").Value = CStr(oSOPRecord.Fields.Item("ACCOUNT_REF").Value)
    oSOPPost.Header("NAME").Value = CStr(oSOPRecord.Fields.Item("NAME").Value)
    oSOPPost.Header("ADDRESS_1").Value = CStr(oSOPRecord.Fields.Item("ADDRESS_1").Value)
    oSOPPost.Header("ADDRESS_2").Value = CStr(oSOPRecord.Fields.Item("ADDRESS_2").Value)
    oSOPPost.Header("ADDRESS_3").Value = CStr(oSOPRecord.Fields.Item("ADDRESS_3").Value)
    oSOPPost.Header("ADDRESS_4").Value = CStr(oSOPRecord.Fields.Item("ADDRESS_4").Value)
    oSOPPost.Header("ADDRESS_5").Value = CStr(oSOPRecord.Fields.Item("ADDRESS_5").Value)
    oSOPPost.Header("DEL_ADDRESS_1").Value = CStr(oSOPRecord.Fields.Item("DEL_ADDRESS_1").Value)
    oSOPPost.Header("DEL_ADDRESS_2").Value = CStr(oSOPRecord.Fields.Item("DEL_ADDRESS_2").Value)
    oSOPPost.Header("DEL_ADDRESS_3").Value = CStr(oSOPRecord.Fields.Item("DEL_ADDRESS_3").Value)
    oSOPPost.Header("DEL_ADDRESS_4").Value = CStr(oSOPRecord.Fields.Item("DEL_ADDRESS_4").Value)
    oSOPPost.Header("DEL_ADDRESS_5").Value = CStr(oSOPRecord.Fields.Item("DEL_ADDRESS_5").Value)
    oSOPPost.Header("CUST_TEL_NUMBER").Value = CStr(oSOPRecord.Fields.Item("CUST_TEL_NUMBER").Value)
    oSOPPost.Header("CONTACT_NAME").Value = CStr(oSOPRecord.Fields.Item("CONTACT_NAME").Value)
    oSOPPost.Header("GLOBAL_TAX_CODE").Value = CShort(oSOPRecord.Fields.Item("GLOBAL_TAX_CODE").Value)
    oSOPPost.Header("ORDER_DATE").Value = CDate(oSOPRecord.Fields.Item("ORDER_DATE").Value)
    oSOPPost.Header("NOTES_1").Value = CStr(oSOPRecord.Fields.Item("NOTES_1").Value)
    oSOPPost.Header("NOTES_2").Value = CStr(oSOPRecord.Fields.Item("NOTES_1").Value)
    oSOPPost.Header("NOTES_3").Value = CStr(oSOPRecord.Fields.Item("NOTES_3").Value)
    oSOPPost.Header("TAKEN_BY").Value = CStr(oSOPRecord.Fields.Item("TAKEN_BY").Value)
    oSOPPost.Header("ORDER_NUMBER").Value = CStr(oSOPRecord.Fields.Item("ORDER_NUMBER").Value)
    oSOPPost.Header("CUST_ORDER_NUMBER").Value = CStr(oSOPRecord.Fields.Item("CUST_ORDER_NUMBER").Value)
    oSOPPost.Header("PAYMENT_REF").Value = CStr(oSOPRecord.Fields.Item("PAYMENT_REF").Value)
    oSOPPost.Header("GLOBAL_NOM_CODE").Value = CStr(oSOPRecord.Fields.Item("GLOBAL_NOM_CODE").Value)
    oSOPPost.Header("GLOBAL_DETAILS").Value = CStr(oSOPRecord.Fields.Item("GLOBAL_DETAILS").Value)
    oSOPPost.Header("ORDER_TYPE").Value = oSOPRecord.Fields.Item("ORDER_TYPE").Value
    oSOPPost.Header("FOREIGN_RATE").Value = CDbl(oSOPRecord.Fields.Item("FOREIGN_RATE").Value)
    oSOPPost.Header("CURRENCY").Value = oSOPRecord.Fields.Item("CURRENCY").Value
    oSOPPost.Header("CURRENCY_USED").Value = oSOPRecord.Fields.Item("CURRENCY_USED").Value

    ' Link header to items
    oSOPItem_Read = oSOPRecord.Link

    'Find the First Record
    oSOPItem_Read.MoveFirst()

    Do

        'Add the existing items to the order
        oSOPItem_Write = oSOPPost.Items.Add

        'Populate the Fields, copying the data from the existing records
        oSOPItem_Write.Fields.Item("STOCK_CODE").Value = CStr(oSOPItem_Read.Fields.Item("STOCK_CODE").Value)
        oSOPItem_Write.Fields.Item("DESCRIPTION").Value = CStr(oSOPItem_Read.Fields.Item("DESCRIPTION").Value)
        oSOPItem_Write.Fields.Item("NOMINAL_CODE").Value = CStr(oSOPItem_Read.Fields.Item("NOMINAL_CODE").Value)
        oSOPItem_Write.Fields.Item("TAX_CODE").Value = CShort(oSOPItem_Read.Fields.Item("TAX_CODE").Value)
        oSOPItem_Write.Fields.Item("QTY_ORDER").Value = CDbl(oSOPItem_Read.Fields.Item("QTY_ORDER").Value)
        oSOPItem_Write.Fields.Item("UNIT_PRICE").Value = CDbl(oSOPItem_Read.Fields.Item("UNIT_PRICE").Value)
        oSOPItem_Write.Fields.Item("NET_AMOUNT").Value = CDbl(oSOPItem_Read.Fields.Item("NET_AMOUNT").Value)
        oSOPItem_Write.Fields.Item("TAX_AMOUNT").Value = CDbl(oSOPItem_Read.Fields.Item("TAX_AMOUNT").Value)
        oSOPItem_Write.Fields.Item("COMMENT_1").Value = CStr(oSOPItem_Read.Fields.Item("COMMENT_1").Value)
        oSOPItem_Write.Fields.Item("COMMENT_2").Value = CStr(oSOPItem_Read.Fields.Item("COMMENT_2").Value)
        oSOPItem_Write.Fields.Item("UNIT_OF_SALE").Value = CStr(oSOPItem_Read.Fields.Item("UNIT_OF_SALE").Value)
        oSOPItem_Write.Fields.Item("FULL_NET_AMOUNT").Value = CDbl(oSOPItem_Read.Fields.Item("FULL_NET_AMOUNT").Value)
        oSOPItem_Write.Fields.Item("TAX_RATE").Value = CDbl(oSOPItem_Read.Fields.Item("TAX_RATE").Value)
        'We now need to ensure that the TAX_FLAG is set the same as the item being read otherwise it will be re calculated
        oSOPItem_Write.Fields.Item("TAX_FLAG").Value = CInt(oSOPItem_Read.Fields.Item("TAX_FLAG").Value)

        'Loop until there are no more existing items
    Loop Until oSOPItem_Read.MoveNext = False

    'destroy the oSOPItem_Write object
    oSOPItem_Write = Nothing

    'write a new item
    oStockRecord.MoveLast()
    oSOPItem_Write = oSOPPost.Items.Add

    ' Populate other fields required for Invoice Item
    ' From 2015 the update method now wraps internal business logic 
    ' that calculates the vat amount if a net amount is given.
    ' If you wish to calculate your own Tax values you will need
    ' to ensure that you set the TAX_FLAG to 1 and set the TAX_AMOUNT value on the item line
    ' ***Note if a NVD is set the item line values will be recalculated 
    ' regardless of the Tax_Flag being set to 1***
    oSOPItem_Write.Fields.Item("STOCK_CODE").Value = oStockRecord.Fields.Item("STOCK_CODE").Value
    oSOPItem_Write.Fields.Item("DESCRIPTION").Value = CStr(oStockRecord.Fields.Item("DESCRIPTION").Value)
    oSOPItem_Write.Fields.Item("NOMINAL_CODE").Value = CStr(oStockRecord.Fields.Item("NOMINAL_CODE").Value)
    oSOPItem_Write.Fields.Item("TAX_CODE").Value = CShort(oStockRecord.Fields.Item("TAX_CODE").Value)
    oSOPItem_Write.Fields.Item("QTY_ORDER").Value = CDbl(2)
    oSOPItem_Write.Fields.Item("UNIT_PRICE").Value = CDbl(50)
    oSOPItem_Write.Fields.Item("NET_AMOUNT").Value = CDbl(100)
    oSOPItem_Write.Fields.Item("FULL_NET_AMOUNT").Value = CDbl(100)
    oSOPItem_Write.Fields.Item("COMMENT_1").Value = CStr("")
    oSOPItem_Write.Fields.Item("COMMENT_2").Value = CStr("")
    oSOPItem_Write.Fields.Item("UNIT_OF_SALE").Value = CStr("")
    oSOPItem_Write.Fields.Item("TAX_RATE").Value = CDbl(20)

    'Destroy the oSOPItem_Write object
    oSOPItem_Write = Nothing

    'Post the order
    If oSOPPost.Update() Then

        MsgBox("Order Updated Successfully")

    Else

        MsgBox("Order Update Failed")

    End If

    'Disconnect and destroy the objects
    oWS.Disconnect()
    oSDO = Nothing
    oWS = Nothing
    oSOPRecord = Nothing
    oSOPItem_Read = Nothing
    oSOPItem_Write = Nothing
    oSOPPost = Nothing
    oStockRecord = Nothing

End If

Exit Sub

Answer 3

as far as i know there is a Excel2Sage Tool or App which can handle mass-importing. i did not used the commercial software last year, but the year before.

i'm actual not know about a free solution for this without developing it. As alternative you could use AutoIt or something.

best

Eric