Hello All
Is it possible to create a report which captures usage to schedule costs and invoice to contract price in Jet reports? We are using Nav 2013 and need a dashboard report for all the jobs (projects)
Cheers
Sandy
Date
Votes
4 comments
-
Harry Lewis Hello Sandy.
Jet Basics can create an Excel table from any Dynamics NAV table (or combination of NAV tables that are related in some way - e.g., the Customer and Cust. Ledger Entry tables).
You can then use Excel to manipulate that Excel table (the most common way, of course, is to use Excel's Pivot capabilities).
Do you know which NAV tables contain the data you are looking for?
If you are using an NAV Card or Form...
usually the table has the same name (in this case, Customer).
If not, you can use NAV's Help feature to help you along. Select a Card or Form you are interested in and then:
- click NAV's pull-down arrow
- click Help
- select About This Page
Some information will be displayed - including the Source Table for that data:
Once you know all the tables/fields you want (and the key fields that relate separate tables to each other), you can use the Jet Basics Table Builder to retrieve that data into Excel and then use Pivot Tables, etc. to display it how you want.
I hope that gets you pointed in the right direction.
-
Iamsandygawankar Many thanks Harry, I will give it a go. How do you find the key field that links 2 tables. Is there a way to find the linked key to avoid duplication.
Regards
Sandy -
Harry Lewis Hi Sandy -
In many cases with standard NAV tables, the Table Builder automatically knows how tables are related:
When that is *not* the case, you'll need to find out for yourself. I did a quick Internet search and found this information for finding if a field is related elsewhere:
----------
Foreign Keys and the Jet Sample Report NAV036 Data Dictionary
Unlike in the sample Northwind Database, NAV doesn’t maintain foreign key integrity at the database level. So, you don’t have any of those nice silver key emojis to help you figure out relations.
The good news is that, in general, foreign keys in NAV are fairly straightforward.
For example, if you were reporting on Sales Order and wanted to know where the customer data was found, you could simply go to the Customer page using “Advanced”:
And once you’re on the Customer page, you can use About This Page again.
About This Page tells you that the sell-to customer on the sales order came from the Customer table:
-------
that information came from here: https://reportsyouneed.com/understanding-the-nav-database/
Unplanned by me, that article also mentions the Jet NAV Data Dictionary report. This pre-built report is available for Jet Basics. I don't *think* it provides any additional information beyond what the Table Builder already knows, but it can be a good resource.
Here's how to access the pre-built reports:
- Using your web browser, navigate to the Jet Resources Library
- Select Sample Report from the pull down and then click the area for Dynamics NAV
- On the next screen, enter your information and then click ACCESS RESOURCE
- When the next screen is displayed, click on the button
This will download a ZIP file containing the pre-built sample reports.
Where to find that file:
If you need help extracting the files from the ZIP, this Microsoft article is specifically for you.
Once you have all the files, look for NAV036 Data Dictionary:
Other than those resources, here's what I do...
I create a temporary report. On one sheet, I use the NL(Table) function to list out all of Table #1. On the next sheet, I use the NL(Table) function to list out Table #2. Then I examine the results to see if I can find how the two tables might be related (i.e., is there some field in Table #2 that links to a unique field in Table #1). It's a brute-force method, but I'm an old-school kind of guy.
I hope that helps.
-
Iamsandygawankar Thank you so much for the detailed guidance. It surely helps.
Please sign in to leave a comment.