5 Replies Latest reply on Feb 17, 2012 5:18 AM by winstart

    Filtered total of portal - sum?

    paolobkk

      Title

      Filtered total of portal - sum?

      Post

      Hi Everyone,

      I've set up a portal from a related table in my layout.

      The Portal shows a list of records with a "status" field and this status can be "Incoming" or "Arrived", and of course another field with the quantity (QTY) number of items incoming or arrived.

      I want to add a field to show the sum (or total) of QTY with INCOMING on the "status" field.

      I figured that I may need a calculation field but I don't find the way to "filter" the records to sum to have only how many pieces are "incoming.

      Any suggestion?

        • 1. Re: Filtered total of portal - sum?
          philmodjunk

          This one threw me also until I got a tip from a FileMaker newsletter.

          1. Define a summary field in your portal table to summarize the Qty field.
          2. Place a second, one row portal with the same filter expression as your original table on your layout.
          3. Place the new summary field in this portal--which can be made invisible with transparent or background matching settings for the portal's fill and line properties.
          4. The summary field will compute a total that reflects the current filter where a Sum function will compute the total at the data level and thus bypass the filter.
          • 2. Re: Filtered total of portal - sum?
            paolobkk

            I didn't consider to use a filtered portal to place the Sum field. 

            It works great!

            There is one little problem though, it seems I actually can't use this sum in a calculation.

            In the table and layout there are other 2 fields: "stock1" and "stock2"

            The "incoming" sum is the number of items being moved from "stock1" to "stock2" and I would like to have a field to make a grand total of the 3 fields, in order to have an inventory.

            There is any way I can do that? or I should consider to do all the calculations on scripting?

            Thanks a lot!

            • 3. Re: Filtered total of portal - sum?
              philmodjunk

              I see the problem, since your calculations take place on the data level, you once again bypass the filter.

              Can you give an example of the type of portal filtering you are doing? I think you'll need to replicate that logic in a relationship so that this takes place at the data level. This might be very simple to set up or complicated.

              • 4. Re: Filtered total of portal - sum?
                blewvelvet

                This does work great....but I need the summary to NOT include the expenses from a previous years.

                Each expense has a field called "PROJECT YEAR" So when we do 1099s at the end of the year..the Portal filter will only show current year expenses for "2011". I already have portal filtering removing expeneses by this filter

                TABLE CREW::FIND YEAR  =  TABLE EXPENSES CREW::PROJECT YEAR

                Works great....but I can't get the sum field that's in the TABLE EXPENSES CREW::SUMMARY to remove past year's expenses.

                It adds ALL years together.

                any ideas?

                I would think I should be able to create a calculation FIELD in either Tables...based on what's selected on the CREW TABLE::FIND YEAR.

                Not sure how to tackle the formulas.

                • 5. Re: Filtered total of portal - sum?
                  winstart

                  when i enter a singel line portal with the filtering, the sum field still uses all the values. What i would want is a summarize field of value's which are in the portal