Skip to main content
Solved

REST API $filter on VendorCustomer not working without substringof

B
Freshman I

We have extended the StockItems endpoint from 23.200.001

to include the following fields

It works great when filtering on AlternateID or InventoryID but when we try to filter on VendorCustomer we get a nil object unless we use substringof.

Here’s an example:

GET Request: ~/23.200.001/PartNumberXRef/?$filter=InventoryID eq '104175' and substringof('00250'%2CVendorCustomer)

Returns: [{"id":"655f9396-3b5a-4796-8031-f18d8c447610","rowNumber":1,"note":null,"AlternateID":{"value":"3639541"},"InventoryID":{"value":"104175"},"VendorCustomer":{"value":"000000000250"},"custom":{}}]

If we try this request, we get a nil object.

GET Request: ~/23.200.001/PartNumberXRef/?$filter=InventoryID eq '104175' and VendorCustomer eq '000000000250'

Returns: []

Any thoughts as to why the equals query is not working or what we’re doing wrong?

Thank you in advance.

Best answer by andriitkachenko

Hi @bryckman04 

I didn't try to reproduce it on my instance, so I’m making assumptions here. But I’ve seen similar issues before in different scenarios, which made me think that it’s the same thing happening.

What I think is happening is that API filtering doesn’t work with trimmed values. So when you check VendorCustomer eq '000000000250' you ask Acumatica for Cross-Reference records with Supplier/Customer field value EXACTLY equal to '000000000250'.

If you take a look at the Customers screen, when you define Customer ID, it shows you empty spaces:

So if you expect it to be “TEST”, in reality, API will see it as “TEST    “. For the eq parameter, “TEST” and “TEST    “ aren’t the same, so they will be filtered out. substringof command searches if VendorCustomer field contains '00250', which is the same as Does the “TEST    “ contain “TEST”? - the answer is yes.

 

Technicalities

This is a more technical explanation of the idea. If you don’t work with the Acumatica code and customizations - skip this part.

The grid in the Cross-Reference tab of the Stock Items screen is filled with INItemXRef records, Supplier/Customer field is underneath called a BAccountID. It’s an integer linking the INItemXRef to the BAccount record. For user convenience, Acumatica shows user-friendly AcctCD instead, which represents the human-readable unique name of the BAccount.

BAccount is a joined table for Suppliers/Customers/Organizations etc.

AcctCD is decorated with PXDimensionSelector, which allows the user to configure the maximum size. Fields decorated by this attribute tend to have trailing space filling it until the character count matches the size configured for it at the CS202000 Segmented Keys screen.

View original
Laura02
1 person likes this
  • Laura02
    Laura02
Did this topic help you find an answer to your question?

10 replies

V
Acumatica Moderator
Forum|alt.badge.img

Can you try using custom functions from the help article below?

help article

 

So you filter would be 

$filter=cf.String(f=’<View name>.VendorCustomer’) eq ’000000000250’

 

Hope this helps!

Chris Hackett
B
2 people like this
  • Chris Hackett
    Chris Hackett
  • B
    bryckman04
B
Freshman I
  • bryckman04
  • Author
  • Freshman I
  • 7 replies
  • August 1, 2024

That doesn’t seem to work.  I’m not sure what you use as the <View name> so I tried this:

~/23.200.001/PartNumberXRef/?$filter=InventoryID eq '104175' and cf.String(f='Cross-Reference.VendorCustomer') eq '000000000250'

and got this:

"exceptionMessage":"Term 'Cross-Reference.VendorCustomer' is not valid in a $select or $expand expression."

Thank you.

B
Freshman I
  • bryckman04
  • Author
  • Freshman I
  • 7 replies
  • August 1, 2024

We tried this and got the same nil object returned.

/23.200.001/PartNumberXRef/?$filter=InventoryID eq '104175' and cf.String(f='PartNumberXRef.VendorCustomer') eq '000000000250'

V
Acumatica Moderator
Forum|alt.badge.img

The view_name can be found from the corresponding screen

help article

By inspecting the element (Alt+Ctrl+click on the element), a dialog box opens up and the details can be found

For example: here the view name for the customer id field is Document

 

 

 

 

 

Chris Hackett
B
2 people like this
  • Chris Hackett
    Chris Hackett
  • B
    bryckman04
andriitkachenko
Jr Varsity I
Forum|alt.badge.img+6

Hi @bryckman04 

I didn't try to reproduce it on my instance, so I’m making assumptions here. But I’ve seen similar issues before in different scenarios, which made me think that it’s the same thing happening.

What I think is happening is that API filtering doesn’t work with trimmed values. So when you check VendorCustomer eq '000000000250' you ask Acumatica for Cross-Reference records with Supplier/Customer field value EXACTLY equal to '000000000250'.

If you take a look at the Customers screen, when you define Customer ID, it shows you empty spaces:

So if you expect it to be “TEST”, in reality, API will see it as “TEST    “. For the eq parameter, “TEST” and “TEST    “ aren’t the same, so they will be filtered out. substringof command searches if VendorCustomer field contains '00250', which is the same as Does the “TEST    “ contain “TEST”? - the answer is yes.

 

Technicalities

This is a more technical explanation of the idea. If you don’t work with the Acumatica code and customizations - skip this part.

The grid in the Cross-Reference tab of the Stock Items screen is filled with INItemXRef records, Supplier/Customer field is underneath called a BAccountID. It’s an integer linking the INItemXRef to the BAccount record. For user convenience, Acumatica shows user-friendly AcctCD instead, which represents the human-readable unique name of the BAccount.

BAccount is a joined table for Suppliers/Customers/Organizations etc.

AcctCD is decorated with PXDimensionSelector, which allows the user to configure the maximum size. Fields decorated by this attribute tend to have trailing space filling it until the character count matches the size configured for it at the CS202000 Segmented Keys screen.

Andrii Tkachenko | Sprinterra | https://www.sprinterra.com/ | If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
D
Chris Hackett
B
3 people like this
  • D
    Django
  • Chris Hackett
    Chris Hackett
  • B
    bryckman04
B
Freshman I
  • bryckman04
  • Author
  • Freshman I
  • 7 replies
  • August 2, 2024

Thank you both for your replies.

@andriitkachenko We noticed the underscores you pointed out and thought it might be something like that.  We looked in the Segmented Keys and see that the CustomerID has a length of 15 and a Max length of 30.  In our case, the trimmed VendorCustomer is ‘000000000250’ which is 12 characters long.  We tried adding 3 trailing spaces and still got the same nil object back.  We tried adding 3 trailing underscores with the same result.  Should we be using some other character to fill out the remaining characters?

Thank you again. 

andriitkachenko
1 person likes this
  • andriitkachenko
    andriitkachenko
andriitkachenko
Jr Varsity I
Forum|alt.badge.img+6

​
@bryckman04 it’s not underscores, it’s spaces. They look like underscores because everything is underlined (I’ve underlined it in my response as well just to highlight that that space there is for a reason).

You can try this one - eq '000000000250   '.

You can also try trim(VendorCustomer) eq '000000000250', but I’m not sure Acumatica OData Engine supports that.

I’d say - if it won't work with variants above - don’t bother and do substringof('000000000250'%2CVendorCustomer) (note that I’ve placed full value there, not just '00250' you set in the original example - to decrease the chance of wrong records being fetched). 

​

Andrii Tkachenko | Sprinterra | https://www.sprinterra.com/ | If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
B
1 person likes this
  • B
    bryckman04
B
Freshman I
  • bryckman04
  • Author
  • Freshman I
  • 7 replies
  • August 2, 2024

@andriitkachenko We tried  eq '000000000250   ‘ and got back [].

We also tried  trim(VendorCustomer) eq '000000000250' and received the following:

"exceptionMessage":"Unsupported function trim"

So it looks like the Acumatica Data Engine doesn’t support trim.

We’ll continue to use the substringof, using the entire VendorCustomer number as you suggested.

Thank you again for all of your help.

 

andriitkachenko
1 person likes this
  • andriitkachenko
    andriitkachenko
andriitkachenko
Jr Varsity I
Forum|alt.badge.img+6

@bryckman04 well, that’s unfortunate.

I’d retry one with trailing spaces - if you copied it from your query, it seems to me that the ending single quotation mark isn’t correct - it’s a different symbol from one at the start. That might also be an issue (not that I bet on it, but worth trying)

Too bad trim isn’t supported. At least you have a different solution, that’s something.

Andrii Tkachenko | Sprinterra | https://www.sprinterra.com/ | If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
Chris Hackett
B
2 people like this
  • Chris Hackett
    Chris Hackett
  • B
    bryckman04
B
Freshman I
  • bryckman04
  • Author
  • Freshman I
  • 7 replies
  • August 2, 2024

That’s weird because I copied it from your post.  I can see that it looks different though.

We retried it just to be sure with eq '000000000250   ' (Copied from query)

We got the same nil object response.  

Worth a shot and we have something that works.

Thanks.

andriitkachenko
1 person likes this
  • andriitkachenko
    andriitkachenko

Reply


Most helpful members this week

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie 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