Skip to main content

I am trying to write an Excel VBA macro to change Inventory IDs.  On a stock item’s page, there is a Change ID option, where I can do it manually.  I’d like to do something like this:

 

Sub TestChangeInvID(ByRef Cookie1 As String, ByRef Cookie2 As String)
  Dim Headers(4, 2) As String, Body As String, ResponseHeaders As String, ResponseText As String
 

  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


  Body = "{""entity"" : {""InventoryID"" : {""value"":""MYITEM""}},""parameters"" : {""InventoryID"" : {""value"":""MYITEM1""}}}"
 
  If SendData("POST", "https://alaskansales.acumatica.com/entity/Default/22.200.001/StockItem/ChangeID", Headers, Body, ResponseHeaders, ResponseText) = 204 Then
    Debug.Print "Success!"
  End If
End Sub
 

It doesn’t work, I am guessing because I don’t see a ChangeID action when I view my web service endpoints.  But I’d like to do something like the above, changing MYITEM to MYITEM1.

 

I have a not-so-great workaround by copying the stock item to a new Inventory ID and then deleting the old Inventory ID, but this really isn’t good, since it doesn’t transfer inventory counts and so on.

 

' Inventory IDs to delete are in column A, beginning at row 2
Sub DeleteStockItemsFromAcuamtica()
  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

  ' 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

  Body = ""
    
  row = 2
  
  Do Until StrComp(ActiveSheet.Cells(row, "A").Value, "") = 0
    If SendData("DELETE", "https://alaskansales.acumatica.com/entity/Default/22.200.001/StockItem/" & ActiveSheet.Cells(row, "A").Value, Headers, Body, ResponseHeaders, ResponseText) = 204 Then
      Debug.Print "Successfully deleted item " & ActiveSheet.Cells(row, "A").Value
    End If
    
    row = row + 1
  Loop

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

' Inventory IDs to copy are in column A, beginning at row 2, and the new inventory IDs are in column B
Sub CopyStockItemsToNewInventoryIDs()
  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

  ' 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

  row = 2
  
  Do Until StrComp(ActiveSheet.Cells(row, "A").Value, "") = 0
    Body = ""
    
    If SendData("GET", "https://alaskansales.acumatica.com/entity/Default/22.200.001/StockItem/" & ActiveSheet.Cells(row, "A").Value & "?$expand=Attributes,Boxes,Categories,CrossReferences,FileURLs,ReplenishmentParameters,SubItems,UOMConversions,VendorDetails,WarehouseDetails", Headers, Body, ResponseHeaders, ResponseText) = 200 Then
      ' If item exists, copy it to new Inventory ID (along with vendor costs and retail prices)
      ResponseText = Replace(ResponseText, """InventoryID"":{""value"":""" & ActiveSheet.Cells(row, "A").Value & """}", """InventoryID"":{""value"":""" & ActiveSheet.Cells(row, "B").Value & """}")
    
      ' Get rid of item-specific stuff, like IDs, links, etc
      Call CleanUpResponseText(ResponseText)
      
      Debug.Print vbNewLine & ResponseText
    
      Body = ResponseText
    
      If SendData("PUT", "https://alaskansales.acumatica.com/entity/Default/22.200.001/StockItem?$expand=Attributes,Boxes,Categories,CrossReferences,FileURLs,ReplenishmentParameters,SubItems,UOMConversions,VendorDetails,WarehouseDetails", Headers, Body, ResponseHeaders, ResponseText) = 200 Then
        Debug.Print "Successfully copied data from item " & ActiveSheet.Cells(row, "A").Value & " to item " & ActiveSheet.Cells(row, "B").Value
      End If
      
      ' Copy vendor cost information
      Body = "{""InventoryID"" : {""value"" : """ & ActiveSheet.Cells(row, "A").Value & """}}"

      If SendData("PUT", "https://alaskansales.acumatica.com/entity/Default/22.200.001/VendorPricesInquiry?$expand=VendorPriceDetails", Headers, Body, ResponseHeaders, ResponseText) = 200 Then
        ResponseText = Replace(ResponseText, """InventoryID"":{""value"":""" & ActiveSheet.Cells(row, "A").Value & """}", """InventoryID"":{""value"":""" & ActiveSheet.Cells(row, "B").Value & """}")

        ' Get rid of item-specific stuff, like IDs, links, etc
        Call CleanUpResponseText(ResponseText)

        Debug.Print vbNewLine & ResponseText
        
        Body = ResponseText
        
        If SendData("PUT", "https://alaskansales.acumatica.com/entity/Default/22.200.001/VendorPricesInquiry?$expand=VendorPriceDetails", Headers, Body, ResponseHeaders, ResponseText) = 200 Then
          Debug.Print "Successfully copied vendor cost data from item " & ActiveSheet.Cells(row, "A").Value & " to item " & ActiveSheet.Cells(row, "B").Value
        End If
        
        ' Copy retail price information
        Body = "{""InventoryID"" : {""value"" : """ & ActiveSheet.Cells(row, "A").Value & """}}"

        If SendData("PUT", "https://alaskansales.acumatica.com/entity/Default/22.200.001/SalesPricesInquiry?$expand=SalesPriceDetails", Headers, Body, ResponseHeaders, ResponseText) = 200 Then
          ResponseText = Replace(ResponseText, """InventoryID"":{""value"":""" & ActiveSheet.Cells(row, "A").Value & """}", """InventoryID"":{""value"":""" & ActiveSheet.Cells(row, "B").Value & """}")

          ' Get rid of item-specific stuff, like IDs, links, etc
          Call CleanUpResponseText(ResponseText)

          Debug.Print vbNewLine & ResponseText
        
          Body = ResponseText
        
          If SendData("PUT", "https://alaskansales.acumatica.com/entity/Default/22.200.001/SalesPricesInquiry?$expand=SalesPriceDetails", Headers, Body, ResponseHeaders, ResponseText) = 200 Then
            Debug.Print "Successfully copied retail price data from item " & ActiveSheet.Cells(row, "A").Value & " to item " & ActiveSheet.Cells(row, "B").Value
          End If
        End If
      End If

    End If
    
    row = row + 1
  Loop

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

Sub CleanUpResponseText(ByRef ResponseText As String)
  Dim pos As Long

  ' Remove IDs from JSON
  Do Until InStr(ResponseText, """id"":""") = 0
    pos = InStr(ResponseText, """id"":""")
      
    ResponseText = Left(ResponseText, pos - 1) & Mid(ResponseText, InStr(pos, ResponseText, """,") + 2)
  Loop
    
  ' Remove RecordIDs from JSON
  Do Until InStr(ResponseText, """RecordID"":") = 0
    pos = InStr(ResponseText, """RecordID"":")
      
    ResponseText = Left(ResponseText, pos - 1) & Mid(ResponseText, InStr(pos, ResponseText, ",") + 1)
  Loop
    
  ' Remove row numbers from JSON
  Do Until InStr(ResponseText, """rowNumber"":") = 0
    pos = InStr(ResponseText, """rowNumber"":")
      
    ResponseText = Left(ResponseText, pos - 1) & Mid(ResponseText, InStr(pos, ResponseText, ",") + 1)
  Loop
    
  ' Remove notes from JSON
  Do Until InStr(ResponseText, """note"":") = 0
    pos = InStr(ResponseText, """note"":")
      
    ResponseText = Left(ResponseText, pos - 1) & Mid(ResponseText, InStr(pos, ResponseText, ",") + 1) ' Could be bug here if note has a comma in it
  Loop
    
  ' Remove RefNoteIDs from JSON
  Do Until InStr(ResponseText, """RefNoteID"":") = 0
    pos = InStr(ResponseText, """RefNoteID"":")
      
    ResponseText = Left(ResponseText, pos - 1) & Mid(ResponseText, InStr(pos, ResponseText, ",") + 1)
  Loop
    
  ' Remove NoteIDs from JSON
  Do Until InStr(ResponseText, """NoteID"":") = 0
    pos = InStr(ResponseText, """NoteID"":")
      
    ResponseText = Left(ResponseText, pos - 1) & Mid(ResponseText, InStr(pos, ResponseText, ",") + 1)
  Loop
    
  ' Remove links from JSON
  Do Until InStr(ResponseText, ",""_links"":{") = 0
    pos = InStr(ResponseText, ",""_links"":{")
      
    ResponseText = Left(ResponseText, pos - 1) & Mid(ResponseText, InStr(pos, ResponseText, "{filename}""}") + Len("{filename}""}"))
  Loop
    
  ' Remove QOHs from JSON (so it doesn't potentially mess up our inventory)
  Do Until InStr(ResponseText, """QtyOnHand"":") = 0
    pos = InStr(ResponseText, """QtyOnHand"":")
      
    ResponseText = Left(ResponseText, pos - 1) & Mid(ResponseText, InStr(pos, ResponseText, ",") + 1)
  Loop
  
  ' Remove last modified date/times
  Do Until InStr(ResponseText, """LastModified"":") = 0
    pos = InStr(ResponseText, """LastModified"":")
      
    ResponseText = Left(ResponseText, pos - 1) & Mid(ResponseText, InStr(pos, ResponseText, ",") + 1)
  Loop
  
  Do Until InStr(ResponseText, """LastModifiedDateTime"":") = 0
    pos = InStr(ResponseText, """LastModifiedDateTime"":")
      
    ResponseText = Left(ResponseText, pos - 1) & Mid(ResponseText, InStr(pos, ResponseText, ",") + 1)
  Loop
  
  ' Remove created date/time (I think Acumatica will supply this)
  Do Until InStr(ResponseText, """CreatedDateTime"":") = 0
    pos = InStr(ResponseText, """CreatedDateTime"":")
      
    ResponseText = Left(ResponseText, pos - 1) & Mid(ResponseText, InStr(pos, ResponseText, ",") + 1)
  Loop
End Sub
 

If it helps, I found this link:

 

https://stackoverflow.com/questions/59383416/how-to-use-acumatica-contract-api-to-change-stock-item-id

 

It appears this person is using a SOAP-based API as opposed to REST-based.  The person wrote:

 

“I'm using a web service endpoint with the ChangeID method added. Under the parameters I added a parameter named key and mapped to CD (which is what the field is called in the dialog, and how it's used in a change project order example), but I'm not really clear on how I associate via the mapped object.”

 

How exactly do I add a ChangeID method to an WSE?  I don’t really understand this person’s code or their final resolution to the problem.  But perhaps someone else will and perhaps they can help me solve this problem using REST and VBA.

 

Any help would be appreciated.

 

 

@FredL 

Have you tried extending your StockItem Entity to add ChangeID action yet:

 

@RohitRattan88 was exactly right. Here’s a little more detail, since I’m not familiar with extending an endpoint.  Hopefully this little extra detail will be helpful to someone else like me coming along and seeing Rohit’s answer.

 

I clicked on Extend Endpoint like Rohit said.  It came up with a box.  I entered what Rohit has above, DefaultExt for the Endpoint Name and 23.200.001 for the Endpoint Version.  Then I went to StockItem->Actions, like he has above in 2 and 3.  While clicked on Actions, I clicked the +INSERT button next to the garbage can.  It came up with something like Rohit has in the picture and I added ChangeID to both fields.  Then I went to the PARAMETERS tab.  Based off that link I posted from StackOverflow, I set Parameter Name to InventoryID (I’m thinking I could have named it whatever I wanted to and then changed the parameters name in the code below, but out of laziness, I set it to InventoryID so I didn’t have to change my code), Mapped Object to Specify New ID, Mapped Field to CD and Parameter Type to StringValue (see picture).

 

Here’s the final code (only thing that changed was the URL to reflect the new web service endpoint):

 

Sub TestChangeInvID(ByRef Cookie1 As String, ByRef Cookie2 As String)
  Dim Headers(4, 2) As String, Body As String, ResponseHeaders As String, ResponseText As String
  Dim pos As Long, status As Integer

  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

  ' Get information we can determine if we need to delete a record
  Body = "{""entity"" : {""InventoryID"" : {""value"":""MYITEM""}},""parameters"" : {""InventoryID"" : {""value"":""MYITEM1""}}}"
  Debug.Print Body
 
  If SendData("POST", "https://alaskansales.acumatica.com/entity/DefaultExt/23.200.001/StockItem/ChangeID", Headers, Body, ResponseHeaders, ResponseText) = 204 Then
    Debug.Print "Success!"
  End If
End Sub
 

 

Reply


Terms & ConditionsCookie settings
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.
© 2008 — 2024  Acumatica, Inc. All rights reserved