0

filter based on relationship between fields values (field 1 value higher than field 2 value)

Gideon Mandel

March 23, 2020 08:17

None

Follow

Hi,

In the customer header I have the following numeric fields:

Balance

credit limit

I would like to filter all customers where the balance is lower or higher than the credit limit value.

 

is that possible?

Gideon Mandel

• Facebook
• Twitter
• LinkedIn

3 comments

• 0
  
  Bryan Robinson May 06, 2020 12:43

  Assuming you are using NAV or BC as your datasource.

  Balance greater than Credit Limit =NL("Rows","Customer",,"=NF(,""Balance ($)"")","="">""&NF(,""Credit Limit ($)"")")

  Credit Limit greater than Balance =NL("Rows","Customer",,"=NF(,""Balance ($)"")","=""<""&NF(,""Credit Limit ($)"")")

• 0
  
  Gideon Mandel May 08, 2020 09:02

  Thank you Bryan. this will be very helpful.

   

  is it possible to apply such check to flowing filters? so compare credit limit to remaining balance per date (via date filter)?

   

  and also compare field value with excel formula? for example filter all invoices with due date which is further/higher than "Today()"?

  Edited by Gideon Mandel May 08, 2020 09:04
• 0
  
  Bryan Robinson May 08, 2020 20:11

  In theory, yes, those should both be possible however using a function like Today() is a volatile function and can cause reports to slow significantly.  The larger the report, the bigger the impact.

  Best to wrap an TODAY function in an NP(Eval) in a separate cell and reference that in the Jet function.