3 Replies Latest reply on Oct 7, 2011 10:34 AM by philmodjunk

    Portal question

    dg3321

      Title

      Portal question

      Post

      I have a portal which basically shows credits and expenses as well as a running balance, with a category noted for each credit/expense.

      Separate from the portal i have fields with which i would like to have a running total of each category, per month.

      For example if X is a category for an expense, how do i write the calulation in the 'X Total' field whereby I basically want it to total all expenses that fall under the 'category x' in the month of january 2011.  Would that be an if statement?  If so, how should that be written?

      Thank you!

        • 1. Re: Portal question
          philmodjunk

          If you are using FileMaker 11, you can set up each total in a one row filtered portal where the portal filter expression specifies the category. A summary field defined in the portal table will then correctly compute and display the total.

          You may need to add a triggered script such as:

          Commit Record
          Refresh Window [Flush cached Join results]

          To get the totals to update whenever a field on the layout affecting such totals is edited.

          • 2. Re: Portal question
            dg3321

            Ok, thanks.  And if i did this outside of the portal w/ a basic field, how would i write that calculation?

            I know this isn't how to write it, but basically i want to say "total all withdrawals under category X in the month of january".

            • 3. Re: Portal question
              philmodjunk

              That option requires considerable more design work. You'd need category specific relationships for each such calculation field to use so that Sum ( RelatedField ; cBal) will return the correct value. You can also use the summary field from the portal's table, but select it from the occurrence set up for each category based relationship.

              This added complexity, which is also very inflexible, is why I've suggested the filtered portal approach.

              A typical category specific relationship would look like this:

              MainTable::PrimaryKey = PortalTableEnt::ForeignKey AND
              MainTable::constEntertainment = PortalTableEnt::Category

              constEntertainment is a calculation field with "Entertainment" as it's expression so that all records in MainTable have the same value in it.

              You'd need such a const field and added occurrence of the portal's table for each category.