The Acumatica connector also allows you to lookup/translate values in any Acumatica instance from any VB expression.

The connector has two functions: AcumaticaLookup & AcumaticaInquiryLookup

AcumaticaLookup

The AcumaticaLookup function is very similar to the native Lookup function in IMan.

This allows you to query any Object exposed to the webservices endpoint.

Syntax

AcumaticaLookup(systemid, entity, filter, returnPath, mustreturnvalue)

Arguments

• systemid
  • The system id value of the system connector to be used for the lookup.
• entity
  • The name of the entity to return the field from. E.g. SalesOrder, PurchaseOrder, CustomerClass.
• filter
  • The where clause of the query, see the Filter Record section for syntax.
• returnPath
  • The path of the field to return. Typically this is the field/value E.g. OrderNbr/value
• mustreturnvalue
  • True or False to specify the Lookup query should return one record.
  • True
    • An error will be raised if the lookup fails to return a record.
  • False
    • The function will return an empty string if the lookup fails to return a record.

Example

To return the OrderNbr from the SalesOrder object where ExternalRef is equal to the value of the 'id' field.

AcumaticaLookup("ACUMATICA", "SalesOrder", "ExternalRef eq '" & %id & "'", "OrderNbr/value", False)

AcumaticaInquiryLookup

The AcumaticaInquiryLookup can query any Generic Inquiry exposed to a Webservice endpoint.

The function makes use of two combinative sets of filter fields:

• Inquiry
  • Passes the names and corresponding values to the Paremeters of the Generic Inquiry.
• Response / Return
  • A filter placed on the returned records.

Syntax

AcumaticaInquiryLookup(systemid, expand, inquiryParameterFields, inquiryValues, returnFilter, returnField, mustReturn)

Arguments

• systemid
  • The system id value of the system connector to be used for the lookup.
• expand
  • The property in the response of the Generic Inquiry.
• inquiryParameterFields
  • One or more fields to pass to the Parameters of the Generic Inquiry.
  • Enclose multiple fields in an Array object e.g. Array("CustomerID", "ExternalRef")
  • Pass an empty string "" for Generic Inquiries without any conditions (not recommended).
• inquiryValues
  • The corresponding values to the inquiryParameterFields argument.
  • Enclose multiple fields in an Array object e.g. Array(%customerId, %orderId)
  • Pass an empty string "" for Generic Inquiries without any conditions.
• returnFilter
  • The filter applied to the response of the Generic Inquiry. The filter uses the same Filter Record syntax as the AcumaticaInquiry and Reader.
• returnField
  • The path of the field to return. Typically this is the field/value E.g. OrderNbr/value
• mustreturnvalue
  • True or False to specify the Lookup query should return one record.
  • True
    • An error will be raised if the lookup fails to return a record.
  • False
    • The function will return an empty string if the lookup fails to return a record.

Example

AcumaticaInquiryLookup("ACUMATICA", "StorageDetailsByLocationInquiry", "WarehouseID", %Warehouse, "InventoryID eq'" & %itemCode & "'", "SiteID", False)

Arguments

• ACUMATICA
  • Will query the Acumatica instance defined by the system connector with id ACUMATICA.
• StorageDetailsByLocationInquiry
  • Queries the StorageDetailsByLocationInquiry Generic Inquiry.
• WarehouseID
  • The WarehouseID parameter on the Generic Inquiry.
• %Warehouse
  • The corresponding value to the WarehouseID parameter.
  • The example uses a field reference %Warehouse.
• "InventoryID eq'" & %itemCode & "'"
  • The filter applied to the Generic Inquiry response.
  • Will filter the response to just those record(s) where the InventoryID is equal to the %itemCode field reference.
  • Combined with the Generic Inquiry inquiryParameterFields and inquiryValues parameters will logically limit the returned value to a record where the Warehouse = %Warehouse and InventoryID = %itemCode
• SiteID
  • Return the SiteID field.
• False
  • If there is no matching record then return an empty string ("") vs. throwing an error.