Skip to main content

Hello, 

I’m looking for a way to count the color with the Generic Inquery, for example here, I want to count all the green case per line. Line 1 : 2 , Line 2 : 3 …

 

 

This is the query for the green and orange color : 

I try a COUNT() but it didn’t work. 

 

Do you have an idea ? 

I dont imagine you can count the colours you should be able to use a similair IIF statement in the Data field on that line and set the value to 1 or 0 and then Sum these to work out how many greens etc.

Hi @MargauxCella To add to what @dcomerford said, this may not be possible in the way it looks like you’re organizing the data, left to right. There may need to be something like what was mentioned and get a resulting number amount, then you can add a Total Aggregate Function which will show at the bottom, or maybe a pivot filter?

Otherwise it could be that this intention is better executed in a report via Report Designer. 

Hi @MargauxCella ,

In your Generic Inquiry, create a new formula field that will be used to determine whether the color matches the criteria (e.g., green). The formula should evaluate to 1 if the color matches and 0 otherwise.

  • Expression: IIf((ColorField] = 'Green', 1, 0)
  • Data Type: Integer

Group the inquiry results by the fields that uniquely identify each line (e.g., Line ID).
Utilize the SUM() function on the formula field within each group. This aggregates the formula field values, effectively counting the occurrences of the color per line.

Regards,

Sweta

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