Solved

Can we filter/Sorting GI lines by highlighted color

  • 10 December 2020
  • 3 replies
  • 99 views
R
Rank
Userlevel 4
Badge

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

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? 

View original

3 replies

R
Rank
Userlevel 4
Badge

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
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 4
Badge

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.

Reply


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 — 2020  Acumatica, Inc. All rights reserved