Solved

Can we filter/Sorting GI lines by highlighted color

  • 10 December 2020
  • 4 replies
  • 383 views
R
Rank
Userlevel 6
Badge +5

Hello， I wrote a GI for our company to hightlight the lines using the below formula：

 

=IIF([ARTran.Qty]>[INSiteStatus.QtyOnHand],'orange40','default')

 

It works OK. However, you see, we are having 36 pages,

And if the management team would like to view the on hand qty below period usage qty lines,

he have to click 35 times to view all pages.

Is there a way to filter out the lines by the highlight colors?

 

I am thinking out add a new column with value : 

[ARTran.Qty]-[INSiteStatus.QtyOnHand]

However [ARTran.Qty] should by SUM

while [INSiteStatus.QtyOnHand] is by AVG

This column won’t get the expected result.

Any suggestions?

icon

Best answer by porlov 13 December 2020, 00:33

View original

4 replies

R
Rank
Userlevel 6
Badge +5

To who might also interested in this question:
I did not find a way to filter out in GI directly, but I do find a nearly workaround

That is using side panel and report designer to filter the data.

1， you can add a side panel that navigate to a report screen
2， you make the report line visible only when
=Sum( [ARTran.Qty] )> [INSiteStatus.QtyOnHand]

which it looks like

 

But for the workaround, I still have 2 small questions, that I shall raise another questions to seek for help

porlov
Rank
Userlevel 1

Hey @ray20 

Why don’t you make another column near with formula something like:

=IIF([ARTran.Qty]>[INSiteStatus.QtyOnHand],1,0)

and let your users filter/sort by it? 

And if you still have to use grouping in your GI then I don’t see a problem with using =IIF(SUM([ARTran.Qty])>AVG([INSiteStatus.QtyOnHand]),1,0).

Am I missing something? 

Quality is not an act but a habit.
R
Rank
Userlevel 6
Badge +5

Hey @ray20 

Why don’t you make another column near with formula something like:

=IIF([ARTran.Qty]>[INSiteStatus.QtyOnHand],1,0)

and let your users filter/sort by it? 

And if you still have to use grouping in your GI then I don’t see a problem with using =IIF(SUM([ARTran.Qty])>AVG([INSiteStatus.QtyOnHand]),1,0).

Am I missing something? 

@porlov Thank you. It really works. Fantastic.

Previously, I remeber I’ve tried a formual like 
(SUM([ARTran.Qty])-AVG([INSiteStatus.QtyOnHand])

in a new column. and it did not work.
But your formula is really working. Thanks again.

Robert Sternberg
Rank
Userlevel 7
Badge +8

Just ran into this post.  I am going to do something similar in a GI and to continue with the example above I will use something like…

Column Name: Filter by Color

=IIF([ARTran.Qty]>[INSiteStatus.QtyOnHand],Orange,’’)

Then I can continue to build on the conditional to allow filtering by other colors. 

I am also looking to color the column with the same function in the style column

=IIF([ARTran.Qty]>[INSiteStatus.QtyOnHand],Orange40,’’)
Robert Sternberg, Crestwood Associates -- ** Summit 2024 - Visit Us at Booth #600 ** -- If a community member helped you, please *Like* their post/reply. If they provided an answer to your question, please mark it as *Best Answer*.

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