I am trying to build a report that will show me the quantity of base-level components needed for a specific sales order which contains many assemblies that have sub-assemblies. We use a project code as a dimension which is tied to the sales order.
I don't see "sales order number" in the various lists of tables, and every other report I've tried to generate gives me an error, something to the effect of "field name'([Item], [No.]) is qualified with a table name that is not a link table of that function.". I assume I'm building this incorrectly. Can someone point me in the right direction please?
Thanks.
Date
Votes
4 comments
-
Jet Reports Historic Posts Hi Bryan,
I'm guessing you are connecting to a NAV data source so my help is based on that. If it is incorrect, let me know.
If you are looking for open orders, you should start at the Sales Line table to get the item number and document number. In that table, the document number is "Document No." and item number is just "No.". This will list every item on each line of the sales order.
You can then link to the BOM Component table where "No." equals "No." to pull "Quantity per". I'm not sure exactly what you are after because I can't see the report you are trying to build so I'm just guessing.
The problem with using those two tables is that Jet doesn't understand how they are connected so you have to manually tell it the link between them (see screenshot).
Hope that helps but if you need more, it is helpful to upload the report you do have so far. -
Jet Reports Historic Posts Sorry, I should have mentioned the database. Yes, we are working on NAV and the steps you have listed are getting me closer to my end goal.
The report you have outlined doesn't list the base level items, only the assembly numbers. I'll give an example from the CRONUS company to illustrate, as I don't have something on hand to upload in it's place;
I need to assemble 1 pc of 766BC-C "CONTOSO Storage System". I need the report to tell me the components needed for each of the sub-assemblies (1952-W, 1928-W, 1976-W and 1964-W) as well as the 70060 listed among the sub-assemblies.
Essentially, I am trying to combine the pick instruction and various assembly orders created through the order planning onto 1 sheet.
Thanks for your time on this, I appreciate it. -
Jet Reports Historic Posts I would love to be proven wrong, but I don't think this is something you could accomplish with Jet Express since you would need to build some sort of grouping report that shows all the items on a sales order and for each item, show the components but then have a self reference to have a third level of "give me all the components required to build that sub-component of the primary item".
Even using Jet Essentials this may be impossible unless all items have the same "depth" of components. If some have 3 levels of sub components and others only 1, you would need a recursive call which Jet simply cannot do. -
Jet Reports Historic Posts That's too bad. Thanks for your time.
Please sign in to leave a comment.