Solved

Trying to find items in Acumatica by vendor and vendor code/alternate ID or barcode/UPC using REST API

  • 9 October 2023
  • 3 replies
  • 112 views
F
Rank
Userlevel 2

I’m writing a script to automatically process electronic invoices that come in through email and perform several actions after parsing up the invoices and extracting information from them.  I’m kind of new to REST API (I am very familiar with SQL) and so I was wondering how I could write a query that would return the InventoryID of the item based off 1) UPC (barcode) or 2) vendor and vendor code/alternate ID, which are things that are typically included on invoices.  My code is below:

 

Private Sub test()
  Dim ResponseText As String
  
   If RESTQuery("Default/22.200.001/StockItem?$expand=CrossReferences&$select=InventoryID&$filter=CrossReferences/VendorOrCustomer%20eq%20'V000000247'%20and%20CrossReferences/AlternateID%20eq%20'8836'", ResponseText) <> 0 Then
    Debug.Print "Query failed!"
  Else
    Debug.Print "Query successful!"
  End If
End Sub
 

The first part of the error message that is returned is:

 

{"message":"An error has occurred.","exceptionMessage":"The parent value for a property access of a property 'VendorOrCustomer' is not a single value. Property access can only be applied to a single value.","exceptionType":"Microsoft.Data.OData.ODataException","stackTrace":"  …

 

I am trying to write something to the effect of SELECT InventoryID FROM StockItem WHERE (VendorOrCustomer = 'V000000247') AND (AlternateID = '8836')

 

Thanks in advance for any help with this!

icon

Best answer by Django 9 October 2023, 22:36

View original

3 replies

D
Rank
Userlevel 7
Badge +5

I believe that the error is ultimately telling you that you can’t filter for Stock Item records based on the detail entities.  From a SQL query perspective you’re asking for

select * from InventoryItem

inner join INItemXRef on…

where INItemXRef.AlternateID = ‘xyzxzy’

The StockItem endpoint doesn’t let you do that without selecting a single InventoryItem record, first.

Similarly you can’t ask for all SalesOrder endpoint records that have a specific InventoryID value in the details.

It’s inefficient and too much of a performance hit.

What you can do, however, is to create a Generic Inquiry on the INItemXRef table (and maybe join in the InventoryItem table as required) and then expose that GI as it’s own end point. Then you’re querying the table you want to hit directly and, from there, pull the other values you need.

https://community.acumatica.com/reports%2Dand%2Dgeneric%2Dinquires%2D115/generic%2Dinquiry%2Dhow%2Dto%2Dfilter%2Dan%2Dgi%2Dwhen%2Dgi%2Dis%2Dexposed%2Dvia%2Dweb%2Dservice%2Dendpoint%2D5701

 

F
Rank
Userlevel 2

Hi community!

 

I’m revisiting this question because I found a nice solution that might be useful to others.  I still don’t know a whole lot about web service endpoint extensions (WSE), but I had to create one this morning to solve another problem (thanks @RohitRattan88 for helping me solve that other problem!)

 

See How to change a stock item's Inventory ID using REST API | Community (acumatica.com) for how I went about creating a WSE.

 

I went to StockItems and created a new field called UPC and mapped it to the AlternateID field in CrossReferences (see picture below).  I was then able to query by UPC using this code:

 

 

Sub QueryByUPCExample(ByRef Cookie1 As String, ByRef Cookie2 As String, UPC 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

  Body = ""
 
  If SendData("GET", "https://alaskansales.acumatica.com/entity/DefaultExt/23.200.001/StockItem?$filter=UPC eq '" & UPC & "'", Headers, Body, ResponseHeaders, ResponseText) = 200 Then
    Debug.Print "Success!"
  End If
End Sub
 

 

 

 

Chris Hackett
Rank
Userlevel 7
Badge

Thank you for sharing your solution with the community @FredL!

Chris ~ Let us know how we're doing: https://forms.office.com/r/xmJUdZ1QE5 - A member helped you, please *Like* their post. A member provided an answer to your question, please mark it as *Best Answer*.

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