3 Replies Latest reply on May 2, 2014 8:14 AM by philmodjunk

    Creating Found Sets for a portal

    CaitlindeLisser

      Title

      Creating Found Sets for a portal

      Post

           Hi all, 

           Whenever I try to go into browse mode for my portal and sort according to various fields, it only orders by those fields, and does not omit unrelated records. How do I get my portal to create found sets based on the fields searches I put in? 

            

           For example, I am trying to get revenue for the month of June, but it is hard as it is simply reordering the records so that month goes first, not omitting the results not related to June. 

            

           Thanks! 

        • 1. Re: Creating Found Sets for a portal
          philmodjunk

               Sorting records and performing a find are two different operations.

               Portals do not have found sets, that's a function of a layout, not a portal.

               To show different sets of records in a portal, you either manipulate the values in match fields to match to different sets of records or you manipulate a value used in a portal filter.

          • 2. Re: Creating Found Sets for a portal
            CaitlindeLisser

                 Thanks -- changed so I was using a layout instead. Any idea how to create a formula in which I can get the decay rate on a summary? i.e. the summary of revenue from month 1 --> the revenue of month 2? I want to see what the drop in revenue was. 

            • 3. Re: Creating Found Sets for a portal
              philmodjunk

                   Much will depend on how the data is set up in your table, presumably you have multiple records for each month. But a self join relationship can be set up to match to the previous month's set of records in order to subtract the tsubtotals.

                   Say each record has a date field named TransactionDate.

                   cMonth could be a calculation field with date as its result type defined as:

                   TransactionDate - day (TransactionDate ) + 1

                   cMonthPrev could be defined as:

                   Date ( Month ( TransactionDate ) - 1 ; 1 ; year ( TransactionDate ) )

                   Then this self join relationship:

                   YourTable::cMonthPrev = YourTable 2::cMonth

                   will matc a record in YourTable to all records dated from the previous month in YourTable 2.

                   And

                   YourTable::cMonth = YourTable 3::cMonth

                   would match to all records of the same month.

                   Sum ( YourTable 3::amtField ) - Sum ( YourTable 2::amtField )

                   would then compute the difference you described.

                   YourTable, YourTable 2, and YourTable 3 would all be different Tutorial: What are Table Occurrences?.

                   PS. by adding an additional pair of match fields to both of these relationships, you can refine the matching to a subset of the records for a given month such as all records with the same accountID or ProductID from the previous or same month.

                   ExecuteSQL in FileMaker 12 or newer could also compute these totals without the need to add more relationships and table occurrences to your relationship graph in Manage | Database.