Directly updating stock item costs and prices in Acumatica

Hi Community. I am writing an Excel VBA macro to chug through invoices and price books to update vendor costs and retail prices for stock items in Acumatica via the REST API. If it finds an item on an invoice that is not currently in Acumatica, it can create it. It sets the vendor costs and retail prices by calling VendorPricesInquiry and SalesPricesInquiry respectively (a sample of the former is given below). All is working fine. If the item already exists and I want to update the vendor cost, say, from an existing vendor, I kind of run into a problem, since that item already has a vendor cost for that vendor. I have found a workaround by deleting the old cost and inserting a new cost. Not pretty, but it works. How exactly would I just do an update? Also, I’m pretty new to REST API, so I haven’t been able to get a date to work (every time I try to include a date, my query fails). I want to set the EffectiveDate to a specific date, when I update an item. How do I do that? Is there a better way (using REST API) to automatically update costs and prices, rather than using VendorPricesInquiry and SalesPricesInquiry? Thanks in advance for any help.

Here’s my test code:

Sub ChangeCostTest()
Dim Cookie1 As String, Cookie2 As String
Dim Headers(4, 2) As String, Body As String, ResponseHeaders As String, ResponseText As String
Dim row As Integer

If LogoutAcumatica() <> 0 Then
Debug.Print "Logout failed!"
End If

' Login to Acumatica
If LoginAcumatica(Cookie1, Cookie2) <> 0 Then
Debug.Print "Login failed!"
Exit Sub
End If

Headers(1, 1) = "Accept"
Headers(1, 2) = "application/json"
Headers(2, 1) = "Content-type"
Headers(2, 2) = "application/json"
Headers(3, 1) = "Cookie"
Headers(3, 2) = "ASP.NET_SessionId=" & Cookie1
Headers(4, 1) = "Cookie"
Headers(4, 2) = ".ASPXAUTH=" & Cookie2

' Delete old cost
Body = "{" & vbNewLine & _
"""InventoryID"" : {""value"" : ""AK18290""}," & vbNewLine & _
"""Vendor"" : {""value"" : ""V000000193""}," & vbNewLine & _
"""VendorPriceDetails"" : p{""delete"": true}]" & vbNewLine & _
"}"

status = SendData("PUT", "https://alaskansales.acumatica.com/entity/Default/22.200.001/VendorPricesInquiry?$expand=VendorPriceDetails", Headers, Body, ResponseHeaders, ResponseText)

If status = 200 Then
Debug.Print "Cost in VendorPriceWorksheet deleted successfully!"

'Application.Wait (Now() + TimeValue("00:00:01"))

' Set new cost
Body = "{" & vbNewLine & _
"""InventoryID"" : {""value"" : ""AK18290""}," & vbNewLine & _
"""VendorPriceDetails"" : :{""InventoryID"" : {""value"": ""AK18290""},""Vendor"" : {""value"": ""V000000193""},""UOM"" : {""value"": ""EA""},""Price"" : {""value"": 123.4500}}]" & vbNewLine & _
"}"

status = SendData("PUT", "https://alaskansales.acumatica.com/entity/Default/22.200.001/VendorPricesInquiry?$expand=VendorPriceDetails", Headers, Body, ResponseHeaders, ResponseText)

If status = 200 Then
Debug.Print "Cost in VendorPriceWorksheet updated successfully!"
Else
Debug.Print "Failed to update cost in VendorPriceWorksheet!"
End If
Else
Debug.Print "Failed to delete cost in VendorPriceWorksheet!"
End If

' Logout of Acumatica
If LogoutAcumatica() <> 0 Then
Debug.Print "Logout failed!"
End If
End Sub

Page 1 / 1

By the way, here is a sample of some code I stuck in earlier to set the EffectiveDate, which didn’t work

"""EffectiveDate"" : {""value"":""" & Format(Date, "yyyy-mm-dd") & "T00%3A%00%3A%00%2B%00%3A%00""}," & vbNewLine & _

It would have went in the Body that sets the new price. What is wrong with it?

Nevermind, I figured this date part out. I’m just dumb. I didn’t need to encode anything, so this worked fine:

"""EffectiveDate"" : {""value"":""" & Format(Date, "yyyy-mm-dd") & "T00:00:00+00:00""}," & vbNewLine & _

But going back to the original question, is there a better way of directly updating costs and prices in Acumatica using REST API than what my sample code does above? There must be a way to update the costs without having to delete the old one and add a new one.

What is the end result of the JSon? It should look something like this:

"EffectiveDate": { "value": "2023-09-30T00:00:00+00:00" }

or just this:

"EffectiveDate": { "value": "2023-09-30" }

Is it possible that your value is not being converted from your hexadecimal coding as you’re expecting?

For those interested in how to do this (updating/inserting UPCs, vendor codes, sales prices, vendor costs), this is what I came up with (not the most efficient, but it works):

Sub ChangeVendorCodeAndUPC(ByRef Cookie1 As String, ByRef Cookie2 As String, InventoryID As String, VendorID As String, VendorCode As String, upc As String, UOM As String)
Dim Headers(4, 2) As String, Body As String, ResponseHeaders As String, ResponseText As String, status As Integer, ItemInfo As String
Dim ID As String, pos As Long

' Get information so we can see if cross-references already exist
status = SendData("GET", "https://alaskansales.acumatica.com/entity/Default/22.200.001/StockItem?$expand=CrossReferences&$select=CrossReferences/AlternateType,CrossReferences/AlternateID,CrossReferences/VendorOrCustomer,CrossReferences/UOM&$filter=InventoryID eq '" & InventoryID & "'", Headers, Body, ResponseHeaders, ItemInfo)

' Check if vendor code exists. If so, update it.
If (StrComp(VendorID, "") <> 0) And (InStr(ItemInfo, """AlternateType"":{""value"":""Vendor Part Number""},""UOM"":{""value"":""" & UOM & """},""VendorOrCustomer"":{""value"":""" & VendorID & """}") <> 0) Then
' Parse out ID
pos = InStr(ItemInfo, """AlternateType"":{""value"":""Vendor Part Number""},""UOM"":{""value"":""" & UOM & """},""VendorOrCustomer"":{""value"":""" & VendorID & """}")
pos = InStrRev(ItemInfo, "{""id"":""", pos)
ID = Mid(ItemInfo, pos + Len("{""id"":"""), InStr(pos + Len("{""id"":"""), ItemInfo, """,") - pos - Len("{""id"":"""))

'Debug.Print "ID = " & ID

Body = "{""InventoryID"" : {""value"" : """ & InventoryID & """},""CrossReferences"" : ;{""id"": """ & ID & """,""delete"": true}]}"

status = SendData("PUT", "https://alaskansales.acumatica.com/entity/Default/22.200.001/StockItem?$expand=CrossReferences&$select=CrossReferences/AlternateType,CrossReferences/AlternateID,CrossReferences/VendorOrCustomer&$filter=InventoryID eq '" & InventoryID & "'", Headers, Body, ResponseHeaders, ResponseText)

'Body = "{""InventoryID"" : {""value"" : """ & InventoryID & """},""CrossReferences"" : "{""id"": """ & ID & """,""AlternateID"": {""value"":""" & VendorCode & """}}]}"

If status = 200 Then
Debug.Print "Vendor ID deleted successfully!"
Else
Debug.Print "Failed to delete vendor ID!"
End If
End If

' Check if UPC exists
If (StrComp(upc, "") <> 0) And (InStr(ItemInfo, """AlternateType"":{""value"":""Barcode""},""UOM"":{""value"":""" & UOM & """}") <> 0) Then
' Parse out ID
pos = InStr(ItemInfo, """AlternateType"":{""value"":""Barcode""},""UOM"":{""value"":""" & UOM & """}")
pos = InStrRev(ItemInfo, "{""id"":""", pos)
ID = Mid(ItemInfo, pos + Len("{""id"":"""), InStr(pos + Len("{""id"":"""), ItemInfo, """,") - pos - Len("{""id"":"""))

'Debug.Print "ID = " & ID

' Update old UPC
Body = "{""InventoryID"" : {""value"" : """ & InventoryID & """}," & vbNewLine & _
"""CrossReferences"" : "{""id"": """ & ID & """,""delete"": true}]}"

status = SendData("PUT", "https://alaskansales.acumatica.com/entity/Default/22.200.001/StockItem?$expand=CrossReferences&$select=CrossReferences/AlternateType,CrossReferences/AlternateID,CrossReferences/VendorOrCustomer&$filter=InventoryID eq '" & InventoryID & "'", Headers, Body, ResponseHeaders, ResponseText)

'"""CrossReferences"" : o{""id"": """ & ID & """,""AlternateID"": {""value"":""" & UPC & """}}]}"

If status = 200 Then
Debug.Print "UPC deleted successfully!"
Else
Debug.Print "Failed to delete UPC!"
End If
End If

If (StrComp(VendorID, "") <> 0) Then ' No, so insert
' Set new vendor code
Body = "{" & vbNewLine & _
"""InventoryID"" : {""value"" : """ & InventoryID & """}," & vbNewLine & _
"""CrossReferences"" : _{" & vbNewLine & """AlternateType"" : {""value"" : ""Vendor Part Number""}," & vbNewLine & _
"""AlternateID"" : {""value"" : """ & VendorCode & """}," & vbNewLine & _
"""VendorOrCustomer"" : {""value"":""" & VendorID & """}," & vbNewLine & _
"""UOM"" : {""value"": """ & UOM & """}" & vbNewLine & "}]" & vbNewLine & _
"}"

Sub ChangeCost(ByRef Cookie1 As String, ByRef Cookie2 As String, InventoryID As String, VendorID As String, Price As String, UOM As String)
Dim Headers(4, 2) As String, Body As String, ResponseHeaders As String, ResponseText As String
Dim pos As Long, ID As String

' Get information we can determine if we need to delete a record
Body = "{""InventoryID"" : {""value"" : """ & InventoryID & """}}"

status = SendData("PUT", "https://alaskansales.acumatica.com/entity/Default/22.200.001/VendorPricesInquiry?$expand=VendorPriceDetails", Headers, Body, ResponseHeaders, ResponseText)

' Delete record if it exists
If InStr(ResponseText, """UOM"":{""value"":""" & UOM & """}") <> 0 Then
' Parse out ID
pos = InStr(ResponseText, """UOM"":{""value"":""" & UOM & """}")
pos = InStrRev(ResponseText, "{""id"":""", pos)
ID = Mid(ResponseText, pos + Len("{""id"":"""), InStr(pos + Len("{""id"":"""), ResponseText, """,") - pos - Len("{""id"":"""))

'Debug.Print "ID = " & ID

' Delete old cost
Body = "{" & vbNewLine & _
"""InventoryID"" : {""value"" : """ & InventoryID & """}," & vbNewLine & _
"""VendorPriceDetails"" : "{""id"": """ & ID & """,""delete"": true}]}"

status = SendData("PUT", "https://alaskansales.acumatica.com/entity/Default/22.200.001/VendorPricesInquiry?$expand=VendorPriceDetails", Headers, Body, ResponseHeaders, ResponseText)

If status = 200 Then
Debug.Print "Cost in VendorPriceWorksheet deleted successfully!"
Else
Debug.Print "Failed to delete cost in VendorPriceWorksheet!"
End If
End If

' Set new cost
Body = "{" & vbNewLine & _
"""InventoryID"" : {""value"" : """ & InventoryID & """}," & vbNewLine & _
"""VendorPriceDetails"" : o{""InventoryID"" : {""value"": """ & InventoryID & """},""Vendor"" : {""value"": """ & VendorID & """},""UOM"" : {""value"": """ & UOM & """},""Price"" : {""value"": " & Price & "},""EffectiveDate"" : {""value"":""" & Format(Date, "yyyy-mm-dd") & "T00:00:00+00:00""}}]" & vbNewLine & _
"}"

status = SendData("PUT", "https://alaskansales.acumatica.com/entity/Default/22.200.001/VendorPricesInquiry?$expand=VendorPriceDetails", Headers, Body, ResponseHeaders, ResponseText)

If status = 200 Then
Debug.Print "Cost in VendorPriceWorksheet updated successfully!"
Else
Debug.Print "Failed to update cost in VendorPriceWorksheet!"
End If
End Sub

Sub ChangePrice(ByRef Cookie1 As String, ByRef Cookie2 As String, InventoryID As String, Price As String, UOM As String)
Dim Headers(4, 2) As String, Body As String, ResponseHeaders As String, ResponseText As String
Dim pos As Long, ID As String

' Get information we can determine if we need to delete a record
Body = "{""InventoryID"" : {""value"" : """ & InventoryID & """}}"

status = SendData("PUT", "https://alaskansales.acumatica.com/entity/Default/22.200.001/SalesPricesInquiry?$expand=SalesPriceDetails", Headers, Body, ResponseHeaders, ResponseText)

' Delete record if it exists
If InStr(ResponseText, """UOM"":{""value"":""" & UOM & """}") <> 0 Then
' Parse out ID
pos = InStr(ResponseText, """UOM"":{""value"":""" & UOM & """}")
pos = InStrRev(ResponseText, "{""id"":""", pos)
ID = Mid(ResponseText, pos + Len("{""id"":"""), InStr(pos + Len("{""id"":"""), ResponseText, """,") - pos - Len("{""id"":"""))

'Debug.Print "ID = " & ID

' Delete old price
Body = "{""InventoryID"" : {""value"" : """ & InventoryID & """}," & vbNewLine & _
"""SalesPriceDetails"" : {""id"": """ & ID & """,""delete"": true}]}"

status = SendData("PUT", "https://alaskansales.acumatica.com/entity/Default/22.200.001/SalesPricesInquiry?$expand=SalesPriceDetails", Headers, Body, ResponseHeaders, ResponseText)

If status = 200 Then
Debug.Print "Price in SalesPriceWorksheet deleted successfully!"
Else
Debug.Print "Failed to delete price in SalesPriceWorksheet!"
End If
End If

' Insert new price
Body = "{" & vbNewLine & _
"""InventoryID"" : {""value"" : """ & InventoryID & """}," & vbNewLine & _
"""SalesPriceDetails"" : k{" & vbNewLine & """Price"" : {""value"" : """ & Price & """}," & vbNewLine & _
"""InventoryID"" : {""value"" : """ & InventoryID & """}," & vbNewLine & _
"""PriceType"" : {""value"" : ""Base""}," & vbNewLine & _
"""EffectiveDate"" : {""value"":""" & Format(Date, "yyyy-mm-dd") & "T00:00:00+00:00""}," & vbNewLine & _
"""Warehouse"" : {""value"" : ""AK""}," & vbNewLine & _
"""UOM"" : {""value"": """ & UOM & """}" & vbNewLine & "}]" & vbNewLine & _
"}"

status = SendData("PUT", "https://alaskansales.acumatica.com/entity/Default/22.200.001/SalesPricesInquiry?$expand=SalesPriceDetails", Headers, Body, ResponseHeaders, ResponseText)

If status = 200 Then
Debug.Print "Price in SalesPriceWorksheet updated successfully!"
Else
Debug.Print "Failed to update price in SalesPriceWorksheet!"
End If
End Sub

Hi @FredL ,

Thanks so much for sharing this! If you’re willing to share, I’d be interested to see how you implemented the LogoutAcumatica and LoginAcumatica functions in VBA.

@lauraj46 Here’s the code (note that you have to pass the cookies into the logout function, which I found out later after I submitted my question):

Function LoginAcumatica(ByRef Cookie1 As String, ByRef Cookie2 As String) As Integer
Dim errorCode As Integer, pos As Long
Dim Headers(2, 2) As String, Body As String, ResponseHeaders As String, ResponseText As String
Dim UserName As String, Password As String

UserName = InputBox("Enter user name:", "USER NAME")
Password = InputBox("Enter password:", "PASSWORD")

errorCode = 0

Headers(1, 1) = "Accept"
Headers(1, 2) = "application/json"
Headers(2, 1) = "Content-type"
Headers(2, 2) = "application/json"

' Real site
Body = "{" & vbNewLine & """name"": """ & UserName & """," & vbNewLine & """password"": """ & Password & """," & vbNewLine & """tenant"": ""Alaskan Sales""," & vbNewLine & """branch"": ""101""" & vbNewLine & "}"

' Sign in
If SendData("POST", "https://alaskansales.acumatica.com/entity/auth/login", Headers, Body, ResponseHeaders, ResponseText) <> 204 Then
errorCode = -1
End If

' Parse out cookies
pos = InStr(ResponseHeaders, "ASP.NET_SessionId=")

If pos = 0 Then
errorCode = -2
GoTo ExitFunction
End If

Cookie1 = Mid(ResponseHeaders, pos + Len("ASP.NET_SessionId="), InStr(pos, ResponseHeaders, ";") - pos - Len("ASP.NET_SessionId="))

Debug.Print "ASP.NET_SessionId=" & Cookie1

pos = InStr(ResponseHeaders, ".ASPXAUTH=")

If pos = 0 Then
errorCode = -2
GoTo ExitFunction
End If

Cookie2 = Mid(ResponseHeaders, pos + Len(".ASPXAUTH="), InStr(pos, ResponseHeaders, ";") - pos - Len(".ASPXAUTH="))

Debug.Print ".ASPXAUTH=" & Cookie2

ExitFunction:
Set objHTTP = Nothing

LoginAcumatica = errorCode
End Function

Function LogoutAcumatica(Cookie1 As String, Cookie2 As String) As Integer
Dim errorCode As Integer
Dim Headers(4, 2) As String, ResponseHeaders As String, ResponseText As String

errorCode = 0

Headers(1, 1) = "Accept"
Headers(1, 2) = "application/json"
Headers(2, 1) = "Content-type"
Headers(2, 2) = "application/json"
Headers(3, 1) = "Cookie"
Headers(3, 2) = "ASP.NET_SessionId=" & Cookie1
Headers(4, 1) = "Cookie"
Headers(4, 2) = ".ASPXAUTH=" & Cookie2

' Sign out
If SendData("POST", "https://alaskansales.acumatica.com/entity/auth/logout", Headers, "", ResponseHeaders, ResponseText) <> 204 Then
errorCode = -1
End If

Set objHTTP = Nothing

LogoutAcumatica = errorCode
End Function

Thanks @FredL !

Hi @FredL ,

Thanks so much for sharing! Would you be willing to share the code for the SendData function?

@lauraj46 Here it is:

Function SendData(ByVal Method As String, ByVal Url As String, Headers() As String, ByVal Body As String, ByRef ResponseHeaders As String, ByRef ResponseText As String) As Integer
Dim i As Integer

With CreateObject("MSXML2.ServerXMLHTTP")
.SetOption 2, 13056 ' SXH_SERVER_CERT_IGNORE_ALL_SERVER_ERRORS
.SetTimeouts 10000, 10000, 10000, 300000 ' Set receive timeout to 5 minutes
.Open Method, Url, False

For i = 1 To UBound(Headers)
.SetRequestHeader Headers(i, 1), Headers(i, 2)
Next
.Send (Body)

ResponseHeaders = .GetAllResponseHeaders
ResponseText = .ResponseText
Debug.Print "RESPONSE HEADERS:" & vbNewLine & ResponseHeaders
Debug.Print "RESPONSE TEXT:" & vbNewLine & ResponseText

Debug.Print "STATUS CODE: " & .status
SendData = .status
End With
End Function

I also have a bunch of other code for importing stock item stuff, like image files and so on. Sometimes, the Import Scenarios we have don’t work well, so I wrote my own importer (to create/modify stock items). It works great, but it’s slow.

Thanks @FredL,

I was receiving errors on the Send, and SXH_SERVER_CERT_IGNORE_ALL_SERVER_ERRORS did the trick. Thanks again for sharing !

Laura

Reply

About Acumatica ERP system

Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.

Reply

Sign up

Social Login

Login to the community

Social Login

Scanning file for viruses.

This file cannot be downloaded