1 Reply Latest reply on Mar 7, 2011 11:14 AM by philmodjunk

    Reports that uses multiple tables and portals



      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

      TrophyBreakdown Table

      BreakdownID, TrophyID, PartID, PartDescription, PartSupplier, PartQty

      InvoiceDetails Table

      InvoiceDetailID, InvoiceNumber, TrophyCode, TrophyDescription, TrophyPrice, TrophyQty, BreakdownID.

      Invoice Table

      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.

        • 1. Re: Reports that uses multiple tables and portals

          Good idea mentioning FM6. The fact that you are using such an old version of FileMaker can limit some options available in the later versions.

          You have this structure between your files(tables):


          That shows one to many at each relationship and that makes the following approach work:

          For reports that list records from all three tables, create them in the TrophyBreakdown file. If you define a relationship in TrophyBreakdown that matches back to InvoiceDetail and a relatinship in InvoiceDetail that matches back to Invoices, you can list the tropy parts fields in the body of your report layout, but include fields from the other two related files (tables) in either the header or a subsummary part. Use sub summary parts to strategically group your trophy part records listed in the body.