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
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.
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.
@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).
@andriitkachenko We tried eq '000000000250 ‘ and got back t].
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.
@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.
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)
