4 Replies Latest reply on Sep 16, 2015 4:09 PM by mark_b

    Sum of individual cells in Repeating Field across records?

    mark_b

      Hi All,

      I have a solution that tracks course updates for a school.  Each course consists of many classes, which can vary in number depending on the length of the course.  The tasks at the course level are based on the description the student sees on the website.  In the example shown below, the assignment for classes 3, 7 and 10 need to be updated.  However, updating the assignment involves modifying the Word document, the WordPress website and an Excel grade sheet (Detail).  When each of the tasks is completed for a specific class, I would like the box checked for the parent record for that class.

       

      Using the self-join relationship below (The "id" fields are all UUIDs), I have tried "Sum ( Class Detail Status »  SelfJoin::Status[3] )" just to see if I could get the value expected.  As you can see from the screen-shot below, I would expect a value of 2, but I get 7. I don't know how to isolate the summation to just the one repetition across several records.  If I can figure that out, then I could try using the "Sum ( GetRepetition ( Class Detail Status »  SelfJoin::Status; Get ( CalculationRepetitionNumber )))" to be more generic.  Maybe it can't be done, in which case I'll have to go back to the drawing board, but the Repeating Field works nicely on the layout.  I'm doing this on a Windows 10 system with FMPA-14.

       

      Cheers, Mark

      2015-09-16_13-34-36.png2015-09-16_13-23-23.png

      2015-09-16_13-45-10.png

        • 1. Re: Sum of individual cells in Repeating Field across records?
          erolst

          When you create a summary field that aggregates a repeating field, you have the option to aggregate the individual repetitions, and the summary field itself becomes a repeating field.

           

          That being said: are you sure that repeating fields are the best way to store your data?

          • 2. Re: Sum of individual cells in Repeating Field across records?
            mark_b

            Erolst,

            Thanks for your reply.  Since portals can't display records horizontally, I can't think of another way to do this cross-tab layout. There will never be more than 16 classes per course, but there can be any number of detail tasks needed to complete the update. So my thinking is that the variable number of detail tasks would be displayed in a portal with the fixed value of class titles across the top. 

            I could be wrong, but the option to aggregate individual repetitions works only in a summary field which can only summarize fields within the same table.  I want each repetition in the ClassUpdateStatus::Status field to be True if the sum of the corresponding repetition in ClassDetailStatus::Status field is equal to the number of detail records.  So in the screen-print above showing "Class Level Updates", class 3 an 7 would be blank and class 10 would be set (X).

            Cheers, Mark

            • 3. Re: Sum of individual cells in Repeating Field across records?
              erolst

              So you do need a summarized repetition / repeating summary.

               

              In ClassDetailStatus, create ClassDetailStatus::sSumOfStatus with individual repetitions;

               

              Now – if (Big If) I understood correctly, the calculated repetition field ClassUpdateStatus::Status would read:

               

              Let ( [

                i = Get ( CalculationRepetitionNumber ) ;

                relatedSumRepetition = ClassDetailStatus::sSumOfStatus[i] ;

                relatedCount = Count ( ClassDetailStatus::primaryKey )

              ] ;

              relatedSumRepetition = relatedCount

              )


              More explicit than necessary to better bring the point across.


              mark_b wrote:

              Since portals can't display records horizontally, I can't think of another way to do this cross-tab layout.

               

              There are a number of approaches to this challenge of cross-tab reports; repetitions are one of them, but this is for display; you should not compromise a properly normalized data model for display purposes.

               

              Anyway, another approach is a Virtual List; or just search for crosstab filemaker.

              • 4. Re: Sum of individual cells in Repeating Field across records?
                mark_b

                Thanks Erolst.  As usual, you came through again.  This worked.

                Cheers, Mark