6 Replies Latest reply on Apr 30, 2015 8:18 PM by danlee

    Can't get fields to add up

    danlee

      Title

      Can't get fields to add up

      Post

      I have calculation field MPQuota with calculation 

      Let ([
      SummaryYrMo = Year(Date_Meal Plan) & Right ("0"&Month (Date_Meal Plan); 2);
      Today= GetAsDate (Get(CurrentHostTimeStamp));
      TodayYrMo = Year (Today) & Right ("0" & Month (Today); 2)];

      Case (TodayYrMo = SummaryYrMo; 1; 0)
      )

      Then a summary field, s_SummMPQuota, has the Total of MPQuota

      I have a calculation field MPQuota% with calculation ( s_SummMPQuota ) / ( Trainers::SummaryActive )

      This gives me the % that I want for each trainer.  But when I try to create a field to total all these percentages I get a blank for the outcome.  I created calculation field TotalAverage MPQuota, Count ( MPQuota% ) / SummaryActive Trainer

      So the category under New Meal Plan should be Averaging that column.

      Screen_Shot_2015-04-27_at_9.17.45_PM.png

        • 1. Re: Can't get fields to add up
          philmodjunk

          That's not how a count function works.

          Count ( field )

          will return 1 if the field is not empty, 0 if it is.

          Count ( RepeatingField )

          will return the number of non-empty repetitions

          Count ( relatedTable::field )

          will return the number of related records where the field is not empty. (both times that I refer to "field", i mean a non-repeating field.)

          Therefore the only possible values for: Count ( MPQuota% )

          are 1 or 0.

          • 2. Re: Can't get fields to add up
            danlee

            Trying to research how to get the total from ( s_SummMPQuota ) / ( Trainers::SummaryActive )  

            So Trainer A = 3% and Trainer B = 5% then total should be 8%

            • 3. Re: Can't get fields to add up
              philmodjunk

              In what table is MPQuota (or is it MPQuota%) defined? Trainers or Clients?

              On what table is your report layout based?

              • 4. Re: Can't get fields to add up
                danlee

                MPQuota and MPQuota% are in table Trainer_Score actually.  There is a relationship from Trainer_Score to Clients.  And the Layout is based off of Trainer table.  Here is the relationship setup...

                • 5. Re: Can't get fields to add up
                  philmodjunk

                  That definitely won't work.

                  I am assuming that for one client there are multiple related records in Trainer_Score?

                  If my analysis is correct, you need a workable context for: s_SummMPQuota so that it reports a total only for either one specific client or all clients for one specific trainer so that your calculation will then work.

                  I would use an occurrence of trainer_score that is linked to Clients|Active so that you only access data for active clients. Then define the calculation in the trainers table so as to get the correct context for the summary fields involved.

                  Here is some general info on context as it applies to summary fields:

                  A "non Running" Summary field produces an aggregate value (a value from more than one field in one record). The value returned is determined by the context in which it is used/displayed:

                  Summary field is referenced on a layout based on the table in which it was defined:

                  A group within a FoundSet

                  If you place the summary field in a subsummary part that specifies the "break" field that grouped the records when the found set was sorted, you get a subtotal--the total for that group.

                  In a calculation, you can use the getSummary function to access the same group based sub total.

                  All the records in a FoundSet

                  If you put that summary field in a layout part other than the sub summary part, you get the total for all the records in the current found set.

                  If you refer to a summary field in a calculation field defined in the same table as the summary field, it will also return a total for the current found set. (Which is why we have the GetSummary function to get sub totals in calculations.)

                  Summary field is referenced on a layout based on a table related to the table in which it was defined:

                  Not in a Filtered Portal

                  If you place the summary field on a layout based on a related table or refer to it in a calculation defined in a related table, the relationship controls the value that is computed. It will be based on all the records in the summary field's table that are related to the current record in this table.

                  Think of it this way, if you put a portal on this layout to the summary field's table, you'd see all the records in this portal that are used to compute the summary field's value in this context.

                  In a Filtered Portal (FileMaker 11 and newer only)

                  If you place that summary field inside a portal with a filter, you no longer get a value based on all the related records. Instead, you see a value based on all related records for which the filter expression evaluates as True.

                  This is a special case use of a summary field that is often implemented by putting a single row copy of a filtered portal below it with the summary field inside so that the user sees a value based on just the records visible in the larger portal.

                  This is a "Display Only" trick as you cannot refer to the value of this field in a calculation and get the same value shown on the layout--you get the result described in "Not in a Filtered Portal" above.

                  Note that this does not just apply to "total" summary fields, Average, Count, Maximum, standard deviation, etc all follow these same rules.

                  • 6. Re: Can't get fields to add up
                    danlee

                    Got it working!  Thank you!!