Reports that uses multiple tables and portals
I am having trouble getting me head around creating a report from my tables that uses portals and look ups from other tables. Here is my structure at the moment. I have 3 main tables. Invoice, InvoiceDetails, TrophyBreakdown
BreakdownID, TrophyID, PartID, PartDescription, PartSupplier, PartQty
InvoiceDetailID, InvoiceNumber, TrophyCode, TrophyDescription, TrophyPrice, TrophyQty, BreakdownID.
InvoiceID, CustomerID etc....
This is my thoughts on how I would like me system to work. I have an Invoice form that I enter details eg Customer Name (this can look up data from the Customer Table). In the form there is a portal linked to InvoiceDetails Table. I can enter a Trophy Code. (This is also taken from a Trophy Table for codes, description and prices etc). So the customer's order can include a number of different trophies from different suppliers.
What I need to do is create a report that will summarise Part quantities from the different Suppliers so I can then send that off to my separate suppliers. Ideally if I could add another customer's order to the same Purchase order, and have the 2 orders summarised and totalled to give me one complete Purchase order, that is the goal. However, i am still trying to get the order system to work with only one customer's order.
I have the portals and forms working like I think they should, however creating the report that groups InvoiceID, groups together PartID and gives a total, as well as groups them into SuppliersID is proving to be a handful. Because I need to have separate Supplier orders, if I could pull a query that says something like, Select all from InvoiceDetails table, Groupby InvoiceID, Groupby SupplierID, Groupby PartID, Subtotal PartQty, or something like that.
Any help with my project would be greatly appreciated.
I am using FM6 by the way.