Solved

Filtering Sales Order with substringof on OrderNbr field

  • 10 January 2022
  • 4 replies
  • 287 views

We are using C# with Swagger definition to access a Web Service Endpoint

We are attempting to perform a filter on the SalesOrderClient GetListAsync and would like to perform a substringof filter on the OrderNbr field to find any orders that contain the value that has been specified.

 

SalesOrderClient soClient = new SalesOrderClient(httpClient);
soClient.BaseUrl = "http://localhost/Acumatica_DEV/entity/Test/20.200.001";

select = "OrderNbr, OrderType, Status, CustomerID, Details/LineNbr, Details/InventoryID, Details/OpenQty";
if (!string.IsNullOrWhiteSpace(e.RequestData.OrderNumber))
    filter = "substringof(OrderNbr.Value, '" + e.RequestData.OrderNumber + "')";
else
    filter = "OrderType eq 'SO' and Status eq 'Open'";

IEnumerable<SalesOrder> salesOrders = await soClient.GetListAsync(select, filter, "details", "", null, null);
foreach (SalesOrder salesOrder in salesOrders)
{
    // Process Results
}

 

When we execute the search the error returned is

Unable to cast object of type 'System.String' to type 'PX.Api.ContractBased.Models.EntitySearchField'.

 

Is there a way to apply the substring, startswith or endswith filters here?

Thanks

Chris

 

icon

Best answer by Dmitrii Naumov 10 January 2022, 19:17

View original

4 replies

Substringof function has the reversed order of parameters. 

The first parameter must be constant value and the second parameter must be a field name.

Example

{{sitename}}/entity/Default/21.200.001/JournalTransaction?$filter=contains(BatchNbr,'GL')

So in your case you need:

  filter = $"substringof('{e.RequestData.OrderNumber}', OrderNbr)";

 

Thank you that fixed it.

Userlevel 7
Badge +5

Substringof function has the reversed order of parameters. 

The first parameter must be constant value and the second parameter must be a field name.

Example

{{sitename}}/entity/Default/21.200.001/JournalTransaction?$filter=contains(BatchNbr,'GL')

So in your case you need:

  filter = $"substringof('{e.RequestData.OrderNumber}', OrderNbr)";

 

I tried that first and got the same error.

Userlevel 3
Badge +1

Have you tried replacing

‘substringof(OrderNbr.Value,’

with ‘substringof(OrderNbr,’ ?

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