9 Replies Latest reply on May 27, 2013 3:23 PM by philmodjunk

    Portal Calculation Help

    CraigFiore

      Title

      Portal Calculation Help

      Post

           Hello All,

           I am hoping I can get a little help with a calculation in a portal. Here is my situation:

           I have a portal in a table called "Work Order". This portal is from a table called "Expense Categories" This portal shows the categories of my expenses, such as "Admin Fees, Labor, Material Cost, Profit and so on"  I also have a field called "Expense Total" "Expense Total, as you guessed, totals all the categories.

           Here is my dilemma, I want "Expense Total" to total every category except for the "Profit" category.

           The calculation I have for "Expense Total" is:
           Sum(Expense Log::c Invoice Amount) This actually goes to another table called Expense Log and totals the amount for each category. 

           Is this possible?

           Thank you for your help.

      Screen_Shot_2013-05-21_at_8.36.24_AM.png

        • 1. Re: Portal Calculation Help
          philmodjunk

               This is another "Sum IF" issue. Sum the totals if the related records have (Or in your case do not have) a specified value.

               There are multiple approaches that can be set up to work:

               1) Set up a calcuation field in your portal's table with an If function:

               If ( category ≠ "Profit" ; ExpenseField )

               Then compute the total of this calculation field.

               2) Set up a one row filtered portal where a filter expression filters out the Profit record(s). A summary field that totals the expense amount field can then be defined in the portal's table and placed in this one row portal's portal row.

               Warning: while simple to set up, this method may have screen update issues and is a "Display Only" value. You can't access this total to use in a script or calculation.

               3) Execute SQL with the Sum() function can be used with a WHERE clause that omits the Profit record(s). This requires FileMaker 12 or newer.

          • 2. Re: Portal Calculation Help
            CraigFiore

                 Thank you for responding Phil. 

                 I did forget to mention that the "Expenses Total" Field is not part of the Portal.

                 I did figure out the "Filter portal" Calulation to hide the "Profit" total. But the main reason I am trying to do this is to produce a Chart that will show that wil include the profit. That is why I just want to hide it. 

                 I am using FMP 12.0v4 Advanced. Could you tell me more about the SQL wtih the Sum function or lead me to a place I could learn about it?

                 Thank you.

                  

            • 3. Re: Portal Calculation Help
              philmodjunk

                   Option 1 will be easier to implement than ExecuteSQL unless you are already fairly familiar with SQL queries. The current implementation doesn't give you a lot of feedback to help you resolve syntax errors and the like that can occur within the SQL expression.

                   Theres a helpful widgit that you can dowload from SeedCode, SQL Explorer that you can use to build your first SQL queries. And the SELECT query examples found in the JDBC ODBC Guide you can open from FileMaker Help use the same exact SQL syntax that can be used in this function.

              • 4. Re: Portal Calculation Help
                CraigFiore

                     Thank you for your help but unfortunately this will option 1 will not work for me. In retrospect I should have asked the question different. The main reason I was trying to this was to make a chart. 

                     I am going to repost for help in a different way.

                     Thanks again for your time and help.

                • 5. Re: Portal Calculation Help
                  philmodjunk

                       I see no reason why option 1 will not work for you and it's very simple to implement.

                  • 6. Re: Portal Calculation Help
                    CraigFiore

                         Option 1 did hide the "profit" category but the amount was still included in the "Expense Total"l field. There was another issue which I did not figure out until later. 

                         To calculate the "Profit" I use "Gross Sale"-"Expense Total". So when I included the "Profit" in the Expense portal it created a circular reference issue.

                         So I am trying to figure out another way to caculate "Profit" If I do that I may beable to do it. I hope.

                    • 7. Re: Portal Calculation Help
                      philmodjunk

                           Yes, it would be included in the Expense Total field, which is why you would replace that field with a summary field that totals the new calcualtion field to get a total that omits those values from the total.

                           And then you would also refer to that new summary field in your profit calculation.

                      • 8. Re: Portal Calculation Help
                        CraigFiore

                             Could you give me an example of how to create a summary field that would omit the "profit" category.

                             I tried using a field called  "New Cat Total" GetSummary (sTotal ; Categories) This was able to get the Category totals but every time I tried creating a summary field that totaled "New Cat Total" it wold not calculate correctly. This is what it would do:

                             If i have (3) $100 charges. It would calculate:

                             $100x1

                             $100x2

                             $100x3

                             That would total $900.

                             And I still don't know how to calculate how to omit the "Profit" category.

                             Am I on the right track or completely off base?

                        • 9. Re: Portal Calculation Help
                          philmodjunk

                               The summary field does not omit any data. A calcualtion field with If or Case omits the data and then the summary field totals the calculation field instead of the original field.

                               Please go back and read my original post again where I decribe creating both a calculation field and a summary field to total the calcualtion field.