In the 2019R1 update, Acumatica introduced color GI screens in Acumatica as a way to format generic inquiries. These reporting and data querying screens are one of the most powerful tools onboard, and a great way to style reports in Acumatica. They are the primary lists that show up on nearly every data entry screen. They drive the widgets and dashboards that let us focus on working today’s work today and by adding color to them we can further highlight what is important to address right now.
In the 2019R1 update, Acumatica introduced color GI screens in Acumatica as a way to format generic inquiries. These reporting and data querying screens are one of the most powerful tools onboard, and a great way to style reports in Acumatica. They are the primary lists that show up on nearly every data entry screen. They drive the widgets and dashboards that let us focus on working today’s work today and by adding color to them we can further highlight what is important to address right now.
In this post we will expound on Cristi’s article 2019 Colors on GI which showed the basics of adding color.
We can control color by column or by row. This is done via the Row Style field or in the Style column of the results grid tab.
The value of the style, needs to be one of the following:
‘default’, ‘bold’, ‘italic’ – Uses normal coloring, a bold font or an italic font
‘good’, ‘neutral’, ‘bad’ – Uses a green background with green font, khaki background with brown font or pink background with red font
‘red’, ‘green’, ‘orange’, ‘yellow’, ‘purple’, ‘blue’ – changes the background color with a white font.
In addition, the colors can be combined with 0, 20, 40, and 60. Where 0 is just the font color changed, and the other numbers getting increasingly more saturated with the standard black font.
Thus writing a formula like =Iif([APInvoice.Status]='C' Or [APInvoice.Status]='V', 'good' ,'bad') will result in a pale green back ground with green font
if the AP Bill is Closed or Voided; and will result in a pink background with a red font
if the AP bill in in any other status.
This conveys a general sense of the situation, but I think we can do better. I want to take the non-closed Bills and identify which ones are Past Due. That is what our Good vs. Bad color scheme above is trying to show us. This table
To do this I am going to use the equation below to handle the calculation of how past due a bill is.
=DateDiff( 'd', [APInvoice.DueDate] , Today()) - DateDiff( 'd', [APInvoice.DueDate] , Today()) % 10
At first glance this might look intimidating but let’s break it down.
DateDiff() is an equation that takes 3 parts and returns a number that represents the time that has passed between the two other parts. The first part is the time interval we want the number returned as. ‘y’ for years, ‘m’ for months, ‘d’ for days, ‘h’ for hours, ‘n’ for minutes, ‘s’ for seconds, ‘w’ for weeks and ‘q’ for quarters. Therefor are resultant function returns the number days late the Bill is.
The second DateDiff() equation has the % 10 appended to the end. It is calculating the number of days late again and then dividing it by 10, the % or Mod symbol gives us the remainder from the division. For example 92 days late % 10 would return 2 days.
Subtracting these two numbers from each other creates 10 day buckets. Where 9 days would be in the 0 bucket and 11 days would be in the 10 day bucket.
This gets us 80% to our goal but we still need to map our buckets to the styles. To do this we are going to use the switch function. The switch function allows us to check for a value and then replace that value with our own if the check was true. For example, if our buckets calculation would be =0 then we will set the style to ‘yellow20’. See the orange text below for other examples. Each row is its own case that could be true.
Keen eyed folks may have noticed the Cint() function in the expression above. This converts the results of the DateDiff to an integer and deals with an error only present in the Style fields in Acumatica. This conversion is not necessary otherwise.
We are 90% we need to add our first equation =IIf([APInvoice.Status]='C' Or [APInvoice.Status]='V','good', back in to deal with our closed and voided orders. The end equation looks like this
When pasted into our row style field. The end result is the colorful GI below that quickly shows the worsening state of overdue Bills for the vendor below.
We could have just as easily have done this for the closed items, transforming our GI from looking at the open records, to looking at the historical payment status of the vendor. If we change Today() to [APInvoice.ClosedDate] the results for the closed orders show which vendors pay on time vs which are historically late. We can even bucketize the negative numbers to show whom are paying too early.
Acumatica's introduction of color GI screens in the 2019R1 update has revolutionized the way generic inquiries are formatted. This powerful feature within Acumatica's cloud ERP system, along with the flexibility of the Acumatica report designer, allows users to create custom reports and leverage real-time data. With its extensive formatting options and robust reporting tool, Acumatica offers a comprehensive solution for businesses to enhance their business intelligence through visually appealing reports and customizable dashboards. By utilizing the Acumatica reporting capabilities, businesses can gain valuable insights and make informed decisions based on the accurate and up-to-date information at their disposal.
