4 Replies Latest reply on Nov 13, 2014 4:01 PM by philmodjunk

    Crazy with portals

    MaiteChico

      Title

      Crazy with portals

      Post

      HI

      First of all, let me inform you I'm NEW to Filemaker and everything I've learned so far has bee reading manuals here and there, so I'm lost in quite a few things.

       

      I'm trying to make a sumup field based on the registry of a portal. I'll explain:

       

      I have two tables: invoices1 and invoices_details2, and in this last one mentioned I have a field based on a drop-down that includes the name of a company . Example

       

      invoices_details2 table:

       

                                                                                                                                                                                                                                                                                                                                                                                   
      INVOICEARTICLEQTYPRICESUBTOTALCOMPANY
      3Books15101501
      3pen151152

       

      Well the thing is that in the table invoices1 I have a field for the subtotal of each company, that is subtotal_1 and subtotal_2

      I want to sum up the total belongign to each company that appears in the portal, but it doesnt seem to work.

      I wrote down this on the calculated field of, for example the company 1  :

      If (invoices_details2::Company="1" ; Sum ( invoices_details2::subtotal );0)

       

      but it sums ups everything!,  The total it gives me is 165€ and it should be 150€

       

      What am I missing?

       

      Thanks in advance

        • 1. Re: Crazy with portals
          philmodjunk

          What you want is a "sum if" calculation but you have created an "If sum" calculation. The reference to Invoices_details2::Company references only the first related record. If that value is 1, all related records are summed. If it is not, you get zero. It is doing exactly what you set it up to do, not what you want it to do.

          There are a number of ways to selectively sum related records:

          a) you can use additional table occurrences set up a series of relationships where each relationship matches only to the values you want to sum.

          b) you can set up a series of one row filtered portals where each portal filters for just one company. A summary field defined in your InvoiceDetails table can compute the needed total and it will only show the total of the records that pass the filter in this context.

          c) Execute SQL can be used to list each company and a sub total all in one field. FMP 12 Tip: Summary Recaps (Portal Subtotals)

          d) It may be possible to set up a portal for this if you have a table of companies with one record for each company.

          I don't recommend that you set up a single field or one row portals for your company subtotals here. Those approaches require that you set this up in advance and if you end up with more companies listed in your portal than you have fields or one row portals to compute the sub totals, you have to alter the design of your database in order to get the needed sub totals. I would use either c) or d) to do this as they more readily adjust to different numbers of vendors listed in your portal.

          • 2. Re: Crazy with portals
            MaiteChico

            Thank you very much for your answer. I will certaintly take a look at what you've written and to the sql too!

            • 3. Re: Crazy with portals
              MaiteChico

              I have been thinking about this, and first of all I have to admit I'm far too ignorant on filemaker

               

              I created a table of companies and matched the record of invoice_details2 with that table but my question is, how do I now sum up each company in Invoices1? dont I have to write an "if" again?. I may not have made the relations properly

              The answer given in b), means I have to create as many portals as filters right? well if so that is no logical, so I stick to answer d) but I'm not sure how to solve it....

              • 4. Re: Crazy with portals
                philmodjunk

                At this point, I don't know what your relationships are nor exactly what you want to appear in your report. The typical relationships in an invoicing database would be set up like this:

                Companies----<Invoices----<INvoiceDetails>----Products

                Companies::__pkCompanyID = Invoices::_fkCompanyID
                Invoices::__pkInvoiceID = InvoiceDetails::_fkInvoiceID
                Products::__pkProductiD = InvoiceDetails::_fkProductID

                Each record in companies records a record for a different company. Each Record in Invoices records a different sales transaction to a different company and a portal to Invoice Details records the "line item" sales of different items that make up a given sales transaction.

                With this setup a Creating Filemaker Pro summary reports--Tutorial created on a layout based on the InvoiceDetails table can be used to compute and show the totals for each product sold, grouped by Company.

                If the company is NOT the customer, but the vendor--the company supplying you with the products you want to sell, the same report method works, but your relationships will be different.

                For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained