Would someone please give me examples of NF functions? No one does a very good job of explaining "NF in extraordinary layman's terms.
Thanks!
Date
Votes
9 comments
-
CascadiaDataworks It may be helpful to get some formal training on the building reports with Jet Reports. It will help you understand all of the functions, as well as teach you best practices using the product. The training from insightsoftware/Jet Global is excellent and would set you up for success.
That said, the NF is a bit hard to explain because there is quite a bit to cover before you can fully explain it (plus it helps to understand databases a bit). I'm unsure of your level of knowledge so some of below you may already know.
The NF function is a function that will pull back the value of a specific field from a specific record in a given table. If you think of a database like an address book, the record would be one individual person in your address book and a field would be something specific about that person like name or address or phone number.
The NF is always used in conjunction with an NL replicator function, most commonly within a Detail List type report. The NL Rows would return a list of record keys (location to a specific record in the database) and the NF will go to the database record referenced in the NL result and pull back the value of that field.
(A record key would look something like: "Database Source Name","Company Name","Table No.","Primary Key Field No.","Primary Key Value")
Detail list reports are super efficient because the heavy lifting is done by the replicator. The NF functions know exactly where to go, unlike an NL First or NL Sum that has to search the database for the values until it either finds what it is looking for (in the case of First) or finds all the values to add up (in the case of Sum). But not every report can be a detail list report.
If you want a really high level overview of this process, search YouTube for "may 2018 summary list vs detail list"
-
CascadiaDataworks As an example, perhaps using your detailed trial balance.
The NL Rows would pull back each record from the G/L Entry table. This would allow you to pull back specific information about each record.
-
John Ellis Yes, I have viewed the video and see your point.
It works, until I get to the "Amount" columns. There, NF is not letting me place filters such as Department Code or any parameters for that matter. I was disappointed when I got there and could not filter or enter any parameters into NF.
And, using NL in those columns again duplicates the amounts.
If only I could get NF to work for the amounts.
So near, yet so far....
-
John Ellis Hi BobRoss:
I think that I figured this out. For those amount columns, I simply used "If, Then" statements where I said "if the department code = 'whatever', then give me the amount and place it in this column".
Thanks, for your help!
John
-
CascadiaDataworks As a general rule, NF almost never have filters. In fact, the filters available for the NF are FlowFilters that leverage calculations within NAV/BC but that is a whole different topic. The filters you need occur on the Replicator and within the record Key.
If you think about a single transaction in your GL Entry table, that transaction can only apply to one account, one department, one date, and one amount. There is no need for filters on the NF because it is going to the singular record and pulling the data found in that field for that record.
As such, you shouldn't need an IF statement to determine if the transaction has a department or not in order to pull back the amount. I would also highly avoid this as it will significantly slow the report. I have seen instances where a report will go from 10 seconds to 5 minutes by wrapping a Jet function within an Excel function.
The tough part is not being able to really see the report and see what you have and what you are trying to accomplish (I've seen many trial balances). Couldn't hurt to have a consultant review and make suggestions.
-
John Ellis Would you have any thoughts, in lieu of "if, then" statements?
-
CascadiaDataworks Shouldn't be needed at all, but that is where not being able to see the report prevents me from helping more, and a consultant that you can work with live would be a big help.
-
John Ellis Good Morning, BobRoss:
Actually, I just ran another test. The columns that are causing the slowness aren't the ones with "if, then" statements.
The issue is stemming from the columns that are pulling amounts from other companies in Business Central, other than the default company.
Those columns, in fact, are NL columns.
Any thoughts?
John
-
CascadiaDataworks I haven't had access to Jet for about a year but used it everyday for the prior 8 years, so I can really only speak to what it was like a year ago.
In general, BC was slow, especially if it was Cloud. That may have improved, but I don't know.
Pulling from multiple companies was another thing that slowed down reports because, behind the scenes, Jet would need to connect to a new database and pull the schema for each of those queries. You also have to think about how an NL Sum works (or really all NL functions). It has to go to the table, find all the records that are applicable by searching the table and then sum those specific records. Depending on the date range you are filtering for along with how many records are posted in a given time frame, this could take time. NL functions will always take longer than NF functions just due to the nature of what they are doing.
Please sign in to leave a comment.