Inventory Import issue

[martincrowe]

Is there a problem with inventory batch import function ? I’ve been try to import some items but I cannot get the purchase and sales price to come through, I’ve done this in the past with no issues but now I can’t get it to work, I’ve formatted my spreadsheet as text, as numbers, and currency… all have failed,
I’ve used IOS numbers, open office spreadsheet and google sheets. Any help would be appreciated.

Thanks MC

[Tut]

Eliminate the Euro symbol from the spreadsheet you paste in. Prices for inventory items are simply numbers. That way, they will come across generally as being in your base currency. But if, for example, you have an inventory item only sold to customers with a specific currency, the numbers will be applied as though they were in the currency of the customer.

[martincrowe]

Hi Tut
Formatted as plain numbers, then as text… still not coming through

[lubos]

@martincrowe what is your number format under Settings ? I suspect your business has comma as decimal separator but when you are batch creating, you are trying to use dot as decimal separator.

[martincrowe]

That’s it… thanks very much.

[martincrowe]

Hi @lubos
Just noticed that the inventory starting balance column is missing, have I done something wrong on this ?

[lubos]

The new approach is to enter relevant transactions to establish starting balances.

[martincrowe]

We are migrating from an existing system, we have 4759 inventory items, what’s the most efficient solution to transfer these ?

[eko]

Batch create,

[martincrowe]

Hi Eko
From what I can see the batch create function has changed lately and now there is now option to add starting balances, I remember when we were testing back a few months ago it was an option but I think it has been removed.

[eko]

See Inventory Item Registration and Opening Balances - #3 by lubos

[martincrowe]

Hi eko
When batch creating inventory on hand quantities and values I’m having an issue… I may be doing something incorrect !! because I cannot get Manager to recognize the “inventory on hand” account

Thanks in advance.

[shahabb]

Manager will recognize these codes only

[martincrowe]

Hi Shahabb
Thanks for the reply, that works, Im trying to import multiple lines (inventory items) almost 5000 in one journal entry but I’m failing, help would be appreciated a lot.

[Joe91]

Journal entries have to be balanced

This is basic accounting - see www.accountingcoach.com

[Mabaega]

500 items in one journal, it looks like you have to arrange them sideways, not down.

for Unbalance status, you must specify the Debit account.

[martincrowe]

Thanks for that Joe…I’m trying to eliminate having to balance every individual line by instead adding one line at the end that would balance out all.

[martincrowe]

Thanks Mabaega
Could possibly take longer to update the spreadsheet than to manually do the transaction in Manager.

[Mabaega]

Yes, that’s why many users complain when the initial balance is removed from inventory.

If you understand using Excel Macros, it will be easier to do.

Try this. This code uses the old Api, but we can change it by utilizing the api2 feature.

Sub SendDataToAPI()
    Dim ws As Worksheet
    Dim dataRange As Range
    Dim rowData As Range
    Dim rowNum As Integer
    Dim payloadString As String
    Dim responseText As String
    Dim url As String
    Dim options As Object
    Dim jsonLines As String
    Dim currentDate As String
    Dim currentReference As String
    Dim currentNarration As String
    Dim hasLineDescription As Boolean
    Dim quantityColumn As Boolean
    Dim currentAccount As String
    Dim currentDebit As Double
    Dim currentInventoryItem As String
    Dim currentLineDescription As String
    Dim currentQty As Integer
    Dim currentCredit As Double

    ' Set the worksheet and data range
    Set ws = ThisWorkbook.Sheets("Sheet1")
    Set dataRange = ws.UsedRange
    
    ' Initialize the payload
    currentDate = dataRange.Cells(2, 1).Value
    currentReference = dataRange.Cells(2, 2).Value
    currentNarration = dataRange.Cells(2, 3).Value
    hasLineDescription = dataRange.Cells(2, 4).Value
    quantityColumn = dataRange.Cells(2, 5).Value
    
    payloadString = "{""Date"":""" & Format(currentDate, "yyyy-mm-dd") & """,""Reference"":""" & currentReference & """,""Narration"":""" & currentNarration & """,""HasLineDescription"":" & LCase(CStr(hasLineDescription)) & ",""QuantityColumn"":" & LCase(CStr(quantityColumn)) & ",""Lines"":["
    
    ' Loop through the rows to generate the payload
    For rowNum = 2 To dataRange.Rows.Count
        ' Read the data from each row
        currentAccount = dataRange.Cells(rowNum, 6).Value
        currentCredit = dataRange.Cells(rowNum, 11).Value
        currentInventoryItem = dataRange.Cells(rowNum, 7).Value
        currentLineDescription = dataRange.Cells(rowNum, 8).Value
        currentQty = dataRange.Cells(rowNum, 9).Value
        currentDebit = dataRange.Cells(rowNum, 10).Value
        
        ' Skip empty rows
        If currentAccount = "" Then
            Exit For
        End If
        
        ' Add the line to the payload
        If currentInventoryItem = "" Then
            jsonLines = jsonLines & "{""Account"":""" & currentAccount & """,""Credit"":" & currentCredit & "},"
        Else
            jsonLines = jsonLines & "{""Account"":""" & currentAccount & """,""InventoryItem"":""" & currentInventoryItem & """,""LineDescription"":""" & currentLineDescription & """,""Qty"":" & currentQty & ",""Debit"":" & currentDebit & "},"
        End If
    Next rowNum
    
    ' Remove the trailing comma from the last line
    If Right(jsonLines, 1) = "," Then
        jsonLines = Left(jsonLines, Len(jsonLines) - 1)
    End If
    
    ' Close the JSON payload
    payloadString = payloadString & jsonLines & "]}"

    ' Define the URL for the API endpoint
    url = "https://*************.manager.io/api/************************/5ea52bc4-90ae-4e4a-aec4-ef1224b279ad.json"

    ' Set up the HTTP request
    Set options = CreateObject("MSXML2.XMLHTTP")
    options.Open "POST", url, False
    options.setRequestHeader "Authorization", "Basic " & EncodeBase64("administrator:********")
    options.setRequestHeader "Content-Type", "application/json"
    
    ' Send the payload
    options.send payloadString
    
    ' Get the response
    responseText = options.responseText
    
    ' Check the response status
    If options.Status = 200 Then
        Debug.Print "Data berhasil dikirim: " & payloadString
        Debug.Print "Response: " & responseText
    Else
        Debug.Print "Gagal mengirim data. Status code: " & options.Status
        Debug.Print "Response: " & responseText
    End If
End Sub

' Function to encode a string to Base64
Public Function EncodeBase64(sText As String) As String
    Dim arrData() As Byte
    arrData = StrConv(sText, vbFromUnicode)
    Dim objXML As Object
    Set objXML = CreateObject("MSXML2.DOMDocument.6.0")
    Dim objNode As Object
    Set objNode = objXML.createElement("b64")
    objNode.DataType = "bin.base64"
    objNode.nodeTypedValue = arrData
    EncodeBase64 = objNode.Text
End Function

[Patch]

@martincrowe

almost 5000 in one journal entry

You are probably better to split that across 10 or more journal entries