8 Replies Latest reply on Nov 11, 2010 10:49 AM by philmodjunk

    Portal Calculations

    dg3321

      Title

      Portal Calculations

      Post

      I have a portal which lists expenses on a project.  On this same layout i want to sum each type of expense listed in the portal.  For example if i have three 'tax' expenses, i want the tax field to sum all three tax amounts.  If i have five water expenses, i want the water field to sum all of the water amounts.  Etc, etc.

      I'm assuming i need to write an if statement within these fields to get the itemized expense type totals, however i've been unable to write the correct statement.

      Is that the correct approach?  If not, what would be an easier way to doing this?

        • 1. Re: Portal Calculations
          dg3321

          Or is there a way to create a summary field in a calculation, specifying the expense type (in this situation)

          • 2. Re: Portal Calculations
            philmodjunk

            It might help to describe your data and portal in more detail.

            You could define a calculation field for each expense category, defining them in your portal's table:

            Your cTaxAmount field might look like this:

            If (Description = "tax" ; Amount ; "" )

            Then you can either define a summary field in the portal table that totals this calculation field or you can define a calculation field in the parent record that uses the sum function to compute the total.

            Sum ( PortalTable::cTaxAmount )

            You might be better off not using a portal, but instead creating a summary report based on the portal's table so that you can group your entries by Description. In that case, you don't need any calculation fields and a single summary field can compute the subtotals for each different kind of expense. This really makes life simpler when you consider all the extra fields you will need to add each time you descover that you need to add yet another expense category to your system--none of which will be needed for your summary report.

            If you haven't tried summary reports and want to see how to set one up, see this tutorial: 

            Creating Filemaker Pro summary reports--Tutorial

            • 3. Re: Portal Calculations
              dg3321

              The data in the portal is an itemized list of all the expenses incurred in this example, for a commercial property.  So I have a number of expense categories (water, insurance,roof, plumbing etc, etc).  As these expenses are incurred, they are added to the portal for the current month.  On the other side of the screen i have a basic balance sheet showing income and expenses.  For the expenses, i have a field for each expense type.  I would like these fields to sum the totals for each expense type, i.e. Water field will have a sum of all the water expenses; roof field will have a sum of all the roof repair expenses, and so on.

              • 4. Re: Portal Calculations
                philmodjunk

                That's the approach I described first.

                • 5. Re: Portal Calculations
                  dg3321

                  That worked, thanks!  How do i get it so that the summed totals update automatically?  As it stands right now, the totals do not update until i click out of the portal.  I'd like it to update as i enter in each expense in the portal.

                  Thanks again for your help!

                  • 6. Re: Portal Calculations
                    philmodjunk

                    You might tray experimenting with a script that refreshes the window with the On ObjectSave trigger set on the expense field.

                    • 7. Re: Portal Calculations
                      dg3321

                      Ok, i'll try that.  i actually had this problem with a different portal and it was fixed by making the field 'unstored'.  However that was simply taking the sum of all expenses, not specific expense categories as in this situation.

                      • 8. Re: Portal Calculations
                        philmodjunk

                        Since these calculations refer to data in a related table, they should already be unstored. (They can't be anything else.)