Solved

Object must be of type Int32 Error when filtering on a Generic Inquiry where the column uses the COUNT aggregation function

  • 12 October 2023
  • 12 replies
  • 123 views
chris49
Rank
Userlevel 4
Badge

Good day 🙂,

I’m hoping someone is able to help point me in the right direction to fix the following error. I’ve put togehter a GI that shows users how many times a particular product has been short delivered in the last 2 weeks. 

I’ve managed to put the GI together, and it shows the correct information. However, when you try and apply a filter to the columns that shows the aggregated information, I get the “Object must be of type Int32” error. 

From past experiences, this means that the data is formatted in the incorrect data type. I have thus tried changing the data type to CInt/CLong/CShort, but still getting the same error. I’ve also tried to use the Schema Field setting to e.g. POLine.LineNbr - that also didn’t help. 

Initially I had the aggregated field set to OrderQty, and then doing a count on that. I thought that this might be causing the issue, and have since changed it to the OrderNbr column (which seems to be a more logical ‘countable’ column), but the same error occurs. 

Can anyone spot any obvious issues in the GI or perhaps know of a way to fix this error?

PS. The column in question here is the “Times Short Delivered” column. The “Times Fully Delivered” coulmn displays similar information, and is also giving the same error, but I think if we’re able to solve the one, the other one’s solution would be the same. 


Your assistance is highly appreciated. 
 

 

icon

Best answer by chris49 13 October 2023, 14:29

View original
Everything is possible, if you try hard enough.

12 replies

palbores
Rank
Userlevel 3
Badge

Hi @chris49, you use the CInt() function, that will convert the field value to integer (number) but the OrderNbr is an alphanumeric field like SO00001. Simply leave the COUNT in the aggregate function and remove the CInt() from the data field column if you're only going to COUNT that field.

 

Hope this help.

Using CInt() in a alphanumeric field
Count the field

 

~ Phil
chris49
Rank
Userlevel 4
Badge

Hi @palbores ,

I have tried this option, but it still fails with the same error.

 

Everything is possible, if you try hard enough.
palbores
Rank
Userlevel 3
Badge

@chris49  Can you provide your GI here?

~ Phil
chris49
Rank
Userlevel 4
Badge

@palbores , sure thing. Please find attached

 

1 Attachment
Everything is possible, if you try hard enough.
palbores
Rank
Userlevel 3
Badge

@chris49, have you tried to refresh your GI? Your GI seems fine to my local instance. 

 

~ Phil
chris49
Rank
Userlevel 4
Badge

@palbores , what happens when you try and apply the following filter to “Times Short Delivered” > 0?

Everything is possible, if you try hard enough.
palbores
Rank
Userlevel 3
Badge

@chris49 no error showing.

 

~ Phil
chris49
Rank
Userlevel 4
Badge

That is very strange indeed. Ok, thank you very much @palbores .

I will dig around a little more on my end to try and determine what else could be causing this. I do see that the Attribute columns aren’t present in your GI, but I highly doubt they could be causing the issue. 

Everything is possible, if you try hard enough.
palbores
Rank
Userlevel 3
Badge

That is very strange indeed. Ok, thank you very much @palbores .

I will dig around a little more on my end to try and determine what else could be causing this. I do see that the Attribute columns aren’t present in your GI, but I highly doubt they could be causing the issue. 

@chris49  Yes, I removed it because I don’t have that attribute as well as the UsrWebBackorder field. You can try to uncheck the Active checkbox at the first column under Results Grid to find the column that cause the error.

 

~ Phil
chris49
Rank
Userlevel 4
Badge

@palbores , I will definitely give that a try and let you know what the outcome is. I’m just tied down in some other work at the moment, so will circle back to this in a couple of hours. 

Thank you very much for your help in the meantime though 🙏🏻🙌🏻

Everything is possible, if you try hard enough.
chris49
Rank
Userlevel 4
Badge

Hi @palbores ,

So I checked by removing the 2 columns, and it’s still giving the same error. I think you might not be getting the error, as you might not have Purchase Orders that meet the criteria, and so it’s all just empty results, and so doesn’t break. 

I’m going to keep scratching around, but if I don’t come right, I might need to change it from a COUNT to a SUM, and just change the wording of the columns. 

Everything is possible, if you try hard enough.
chris49
Rank
Userlevel 4
Badge

So I’ve managed to implement a work around for this particular issue. 

I ended up adding 2 additional columns to the GI and used the SUM function instead of the COUNT function. This allowed me to implement the required filters on these new columns, whilst still displaying the original data I wanted to display. I’ve hidden the new columns just for cleanliness of the report. 

 

Everything is possible, if you try hard enough.

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