One to many link [RESOLVED]
by avrilhowes » Thu Jan 26, 2012 10:39 pm
I am trying to create a materials planning sheet, which lists BOM's.
I want to use the Item table as the primary table, linked to the BOM components table. The Item table has 2 filters:
- Bill of Materials = TRUE
- Reorder quantity >0
I link the component table, and select the fields Line no, No., Description, Quantity per
When I run the report, it only returns the first line of the BOM. How can I get Jet to give me all the BOM components?
avrilhowes
Posts: 6
Joined: Mon Sep 12, 2011 6:07 am
Top
Re: One to many link
by HPDeskJet » Fri Jan 27, 2012 2:53 pm
Whenever linking a header/summary and detail table together, the NL(Table) will include up to one row for each record in the primary table.
Thus, if you want to see detail information, you must add the detail table(s) first and the header/summary table (in this case the Item table) afterward.
In this way, you can get more than one line per Item.
-HP
New Question:
I share a similar problem as the earlier posting above. In building a sales commission report, I am trying to select all the Sales Invoice Line table transactions for documents that are filtered in part based on certain criteria in the Cust. Ledger Entry table including the Closed At Date field. If I add an Inclusive Link function linking the two tables through the Jet Report table function for the Sales Invoice Line table, then I get no records. I need the filtered Cust. Ledger Entry table Document No. field to provide the selected Sales Invoice Line document No. in the report.
So in reading the above advice to first "add the detail table" (which works fine), it's the "afterwards" part of adding the summary table that's not working. Could someone please provide some clarification?
Date
Votes
2 comments
-
Jet Reports Historic Posts Since there is no default link between the 'Sales Invoice Line' and 'Cust. Ledger Entry' tables, you would need to be sure to define one.
This might resolve your issue.
-HP -
Jet Reports Historic Posts I was able to get the report to work. It was a sort of "give a monkey a spreadsheet and eventually…" moment. At first it didn't work and then it did. So I can't say for sure why that happened.
But my method, in general, is to create two separate Jet Table formula reports using the Table Builder. One is a simple Jet Table report where only the primary detail records are selected; which is the many. In this case, the Sales Invoice Line table transactions. I picked a period going back one year to make sure that I didn't miss any invoice lines. It also included any data filtering options that are needed for that table. I refresh the report once to make sure that it's getting the right data. This simple report does not link to any other tables.
Next I created a second complex Jet table report including all tables (4 in my case) with any necessary links and filtering. The same table as the simple JET Report is the first table in this report too. I added all the fields and linked fields from this report too. This second report will not work. But it provides all the table formula, linking NL formulas and NL field formulas which can be used as a template.
From the complex report I then copy to the simple report any added the Link formulas, filtering options and any added fields or linked fields from other tables. This will require adding some extra rows to the simple report to make room for the Jet Link formula and any added filters. Next you check the Jet Table formula and adjust any cell reference to make sure that they point to right cells. You will also have to add Jet InclusiveLink's references to the simple report Jet Table formula. You can find those references in the complex Jet Report Table formula. If you get Jet Table formula errors, then run the Function Wizard and Evaluate Formula. It generally gives you a good idea of where to look if there's a problem.
Next I am going to try to get the AR Detailed Aging Report to start including all the Aged AR transactions. But that's another topic.
Please sign in to leave a comment.