4 Replies Latest reply on Oct 9, 2012 11:11 AM by philmodjunk

    I have two tables 1) A " Youth " table where there is one youth for each record and another table 2)...

    Stevehou

      Post

           I have two tables 1) A "Youth" table where there is one youth for each record and another table 2) "Event Youth List" (EYL) where each youth may show up many times as scheduled for an event. The two tables are related by the Youth Number.

           (With the guidance of Phil - Thank YOU!) I have a count field in EYL (sCount) which (with a filter) counts how many times the youth "Did not attend" an event.  The sCount field from EYL is placed in a portal on the Youth record so I only get the count for that one related record - that one youth.. 

      What it does:  If a Youth "Did not attend" an event, his number of failures (sCount) shows in the portal placed on the Youth record.

      Problem:  I need the number of failure results (sCount) to also  show on the EYL table for that youth.  The people who input attendance data only work in the EYL table.

        • 1. Re: I have two tables 1) A " Youth " table where there is one youth for each record and another table 2)...
          schamblee

               Here is a sample app.  Maybe this will help.   My sample has three tables. It has an event table, a youth table,  and a sign up table. 

          https://www.sugarsync.com/pf/D9559058_790_730173158

          • 2. Re: I have two tables 1) A " Youth " table where there is one youth for each record and another table 2)...
            philmodjunk

                 You can set up a relationship by YouthID to a second occurrence of this table. Then sCount from this new table occurrence will report the same total. (And no portal will be needed in this case.)

            • 3. Re: I have two tables 1) A " Youth " table where there is one youth for each record and another table 2)...
              Stevehou

                   Awsome, I always try to figure it out myself  - otherwise I don't learn - but sometimes I need to reach out and I am absolutely amazed at the outstanding, spectacular, amazing and high level of help I have ALWAYS gotten from this forum!  (S Chamble, I am not confident i get the mechanism of action with your sample but it worked and I am grateful to both you and Phil).

                   As I am trying to process why it works I am thinking that the Portal I created works because the "Count" function only counts related records versus all records (something that would not be intuitive - I guess you just gotta know that).

                   I'm guessing that the S Chamble relating a table back to itself does the same thing, the related table only counts related records and therefore can be reported back to the primary table and into the record it relates to.

                   You are so helpful, I don't expect a response but if my theory is way off I'd appreciate a quick "You are way off" response, otherwise I'll assume my lay person perception is "close".

                   Thank you Thank you Thank you!!!!

              • 4. Re: I have two tables 1) A " Youth " table where there is one youth for each record and another table 2)...
                philmodjunk

                     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.