Sign Up for Training |
Jet Global Company Site
Community
Downloads
Training
Submit a Request
Give Feedback

NF Function Reference


Related Articles...

Overview

=NF (Key,Field,FlowFilterField1,FlowFilter1,...,FlowFilterFieldN,FlowFilterN)

Purpose:  Returns a field based on a record key generated with an NL function.

The NL function works great if we want a single field from a record, but if we want to retrieve more than one field from the same record, retyping all the filters for each field can be tedious.

The NF function provides a fast way get multiple fields from our NL. 

In Dynamics NAV, when an NL function returns a record key, the Flow Filters from the NL function are not used to calculate the return value of the NF function.  Rather, the Flow Field returned by the NF function is calculated based on the Flow filters applied in the NF function.

Parameters

  • Key

    Specifies a record key returned by the NL function (when the NL function's Field parameter is either blank or contains a Field Cache).

  • Field

    Name of the field to return.  The field specified must be present in the Field Cache in the NL function if a Field Cache is being used.

Flow Filters

The following flow filter fields and flow filters are only available in Dynamics NAV.

The name of the first field by which to filter.  For Dynamics NAV users, this can also be an Advanced Dimention.  

The following special values are allowed as FilterField arguments:  

  • FlowFilterField_1

    The name of a flow filter to use in calculating the value to return. Flow filters are only used if a Field is a flow field.

  • FlowFilter_1

    The name of the flow filter to use in calculating the value to return. Flow filters are only used if Field is a flow field.

  • FlowFilterField_N

    Same as Filter_1 but for FlowFilterField_N. Up to 9 pairs of Flow Filter Field and Flow Filter pairs can be included in an NF Function.

  • FlowFilter_N

    Same as Filter_1 but for FlowFilterField_N.


Individual Examples of the NF function

This NF returns the Net Change flow field from the record key in cell A3 and filters it for January 2020.. 

=NF(A3,"Net Change","Date Filter","1/1/2020..1/31/2020")

This NF returns the Name field from the NL record key in cell B3.

=NF(B3,"Name")

This NF returns the City field from the NL record key in cell B3.

=NF(B3,"City")

Expanded Example using NAV Cronus

With NAV, the following report lists Employee numbers along with their First Name, Last Name and State.

intronfn1.png

The employee No. uniquely identifies each employee, so we can make a list those, then use that list as a filter to get the name and status for each employee:

intronfn2.png

The NF function is a shortcut that will return the value of a field in a record.  One or more NF functions can be used with a single NL function that has the NL field parameter left blank.  The report listed above can be created with much less typing by using this method:

intronfn3.png

As we can see, the second version requires much less typing and provides the same end results:

intronfn4.png

The new column does not contain information that we want to see on the report so we can hide this column (see Automatic Hidden Columns and Rows).

If we drag and drop fields from the Jet Browser with Rows and Records selected, Jet Reports will create the appropriate NL and NF functions for us:

browser_ex.png


Expanded Example using SQL AdventureWorks

With AdventureWorks, the following report lists CustomerIDs with their City and Phone.

intronfu1.png

The BusinessEntityID uniquely identifies each customer, so we make a list of BusinessEntityID, then use it as a filter to get the LastName and Phone for each customer.  A sample of the output of this report is listed below.

intronfu2.png

The NF function is a shortcut that will return the value of a field in a record.  One or more NF functions can be used with a single NL function that has the NL field parameter left blank.  The report listed above can be created with much less typing by using this method.  The simpler version of the report is shown below.

intronfu3.png

As we can see, the second version requires much less typing.

Unlike in the Dynamics NAV example listed above, the vAddiitionalContactInfo table in the AdventureWorks database does not include a pre-defined primary key to uniquely identify the records.  Thus, the list of fields in the NL function is enclosed in brackets {} and must include all of the fields we want to use in the NF functions.

The first parameter of the NF function has a cell reference to the NL function, and the second parameter is the name of the field in which we are interested.  Note that this type of NL function returns what is called a record key, which will be discussed later.

A sample of the output of the report is listed below.

intronfu4.png

As we can see, the first column does not contain information that we want to see on the report so we have hidden this column (see Automatic Hidden Columns, Sheets and Rows).

If our table were set up to include a primary key, we could can just leave the Field parameter in the NL function blank and Jet Reports would take care of the rest.  Then, the report would look more like this:

intronfu5.png

If there is no primary key in the table, Jet Reports will give an error message if weleave the Field parameter of the NL blank so we can try leaving the Field blank, then fill in a list of field names if necessary.


Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments

  • Avatar
    j2associates

    It would be helpful to have a few more Flow Field examples as they are less well known.