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"" : [{""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
Best answer by FredL
View original
