0

Algebraic functions in Jet Formulas

Tom Hillegas

February 01, 2018 16:54

None

Follow

I am using Dynamics NAV and Jet reports for a Manufacturing and distribution company. our warehouse is looking for a report that will assist them in refilling Bins before they run empty. they way the rank bins for this purpose is by using the following formula:

(Quantity/Min. Qty.)*5

Quantity and Min. Qty. are database fields, so I am able to extract them with NL/NF functions, and I can use excel to create the algebra formula, but I am being asked to sort by the calculated field. I reviewed the articles on specifying a sort column by nesting the function in the Rows Replicator, but I cannot figure out how to nest the algebra functions. I have tried using NP eval and NP Formula, but I keep getting errors either in the formula itself, or the result.

I am using a NL data dump as my Rows replicator:

=NL("Rows","Bin Content",,)

and NF to display the columns, and excel to calculate DIB as described above:

 

anyone have ideas on performing the algebra inside a Jet function so I can use it as a sort filter in my NL data dump?

• Facebook
• Twitter
• LinkedIn

2 comments

• 0
  
  Harry Lewis February 01, 2018 18:41

  You sure can, Tom.

  Let me put together an example using the sample Cronus database.

  Here I have a basic report with a data dump, some NF functions, and a calculation based on those NF functions:

  

  Next, I'm going to re-calculate the "Extended" value without using cell references:

  

  Now, I'm going to apply the same techniques as used when performing a Sort by Sum.

  I'll modify my new formula (by removing the cell references and putting the whole thing within quote marks - being sure to double all the quote marks that are already there) and use it as a filter in my NL("Rows") function:

  =NL("Rows","some table",,"-=NF(,""15 Some Num"")*NF(,""22 Another Num"")*5","*")

  Note that I used a minus sign (-) at the beginning of that so that it will perform a descending sort.  If I wanted an ascending sort, I use a plus sign (+).

  When I run the report:

  

  My output is sorted by the calculated value.

  I hope that helps get you pointed in the right direction.

   

   

  Edited by Harry Lewis February 01, 2018 23:30
• 0
  
  WantsToLearn February 07, 2018 22:17

  Hello Harry,

  Thank you for taking the step by step approach. Very helpful and much appreciated.

  In Tom's original post, he was doing a division. Can you check for that also within Jet?