4 Replies Latest reply on Feb 26, 2015 6:56 AM by philmodjunk

    Calculating two calculated fields in a portal

    ogodt

      Title

      Calculating two calculated fields in a portal

      Post

      I'm going nuts crying

      i have been making this: http://filemakerhacks.com/2012/09/13/aggregates-in-filtered-portals/ and it's
      working perfect, i have made it with 2 tables and have one with "total sales per year" per costumer and one with "total claim amout per year" per costumer.
      now i want to have a field that shows the claim percent - per year/costumer and that would normally be:

      ("total claim amout per year" per costumer) / ("total sales per year" per costumer) * 100

      But i have tried everything and googled for days witout any solution, i hope there is someone WHO can help.

      \Ole

       

        • 1. Re: Calculating two calculated fields in a portal
          philmodjunk

          You have encountered a limitation of this method. The resulting summary values shown in your filtered portal are pretty much "read only". If you try to set up a calculation to refer to the values shown in your portal, the calculation knows nothing of the portal with it's filter and accesses all the related records instead of just those that pass the filter.

          You'll need to consider other methods for getting your totals that do not use a portal filter to get those totals. One option is to use ExecuteSQL with A WHERE clause that produces a match to the same records as your filtered portal. Another is to replace the portal filter expression with an added match field in the relationship that achieves the same result. (With some portal filter expressions, this is not possible.)

          And in some cases, you might toss out the idea of using a portal at all and set up a summary report to produce these results instead.

          If you want to pursue any of these options, you'll need to describe your current relationship and portal filter.

          • 2. Re: Calculating two calculated fields in a portal
            ogodt

            Hi Phil,

            I will try my best to explain our problem:

            Okay, i have the the following Tabels:

            Contact: with company, name, address, date, filter_date, Number (Number is unique costumer ID)
            Reklamation (Claims): with costumer, amount, claims-ID, Date, Year, SUM_filtered_sales_amount_SQL with the following code:

            Let (
            [ $query = "SELECT SUM ( Beløb) FROM (Reklamation) WHERE year = ? AND Kunde = ? "
            ; $result = ExecuteSQL( $query ; ""; "" ; Contacts::filter_year ; Kunde )
            ] ; $result
            )
            /*
            ExecuteSQL ( sqlQuery ; fieldSeparator ; rowSeparator { ; arguments... } )
            ExecuteSQL ( "SELECT SUM(Beløb) FROM "Reklamation" WHERE Kunde= ? AND year= ?"  ; "" ; "" ; Kunde; Contacts::filter_year)
            */
            Sales invoice Line: with a lot of text/number fields, costumer number is the unique ID.

            Relationship is:

            Contacts::Firma = Reklamation::Kunde (“Firma” is company and the same is “kunde”)Contacts::Nummer = Sales invoice Line::Kundenr (“Nummer” is customerID ant the same is “Kundenr”)

            The Portal filer is: “Sales Invoice Line::year = 2014” for sales and we have with filter date sales like: “Sales Invoice Line::year_month = Contacts::filter_date”

            It actually works but if I want to calculate the claims percent it fails for me.

            We have 900 contacts in the DB and when I for example choose the company “Filemaker” I can choose a button called “Sales” which goes to a new layout were I want all the sales information on that company, we would like to filter or see a year/month a time.

            The next problem we have is to see summarized product groups per costumer.

            I hope this gives you an idea of what our task is J

            Kind regards,
            Ole

            • 3. Re: Calculating two calculated fields in a portal
              ogodt

              and a image of the portal

              • 4. Re: Calculating two calculated fields in a portal
                philmodjunk

                Am I correct that Your layout is based on Contacts and your portal refers to Sales Invoice Line?

                If so, you can replace your portal filter with an unfiltered portal using this relationship:

                Contacts::Firma = Sales Invoice Line::Kundenr AND
                Contacts::SelectedYear = Sales Invoice Line::Year

                Put 2014 into Selected Year or define it as a calculation that always evaluates as 2014 and you get the same effect as the portal filter, but now your calculations should work.

                You may also want to consider this setup:

                Contacts-----<Years----<Sales Invoice Line

                Contacts::anyField X Years::anyField

                Years::GlobalFirma = Sales Invoice Line::Kundenr AND
                Years::Year = Sales Invoice Line::Year

                Years would be a table of years, on record (but only one) for each year.

                You can then use a portal to Years in place of the individual portals on your layout if you set up the OnRecordLoad trigger to set the global field, GlobalFirma to the value of Contacts::Firma.