Solved

Count Color in Generic Inquery

  • 3 August 2023
  • 3 replies
  • 73 views
M
Rank
Userlevel 1

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 ? 

icon

Best answer by sweta68 17 August 2023, 08:14

View original

3 replies

dcomerford
Rank
Userlevel 7
Badge +12

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.

Dermot Comerford
BenjaminCrisman
Rank
Userlevel 7
Badge +4

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. 

S
Rank
Userlevel 7
Badge +10

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