4 Replies Latest reply on Nov 25, 2011 12:16 PM by JonHodge

    Adding the values from one field that has many records -Please help!!

    JonHodge

      Title

      Adding the values from one field that has many records -Please help!!

      Post

      Hi,

       

      Newbie here!! I would really appreciate your help.

       

      I have created a very simple database for an old peoples home.

      They have their staff that they want to keep track of the amount of hours holiday they take.

      I created a portal within the staff database on a new tab called HOLIDAY!

      There are only three fields , DATE FROM, DATE TO and NUMBER OF HOURS LEAVE

      As a member of staff asks for leave they put in the dates and the admin input how many hours they have taken.

      I have created out of the portal on the same tab a field TOTAL HOURS TAKEN

      I would like a total from NUMBER OF HOURS LEAVE but when I use  sum it only inputs the first record and not a sum of them all!

      Is there a simple way to do this please? I did look at changing TOTAL HOURS TAKEN to a summary field but NUMBER OF HOURS LEAVE was greyed out.

      I would appreciate any help.

      Thanks,

      Jon

        • 1. Re: Adding the values from one field that has many records -Please help!!
          philmodjunk

          What data type is Number of Hours Leave? It should be of type number, not text. this is probably why it is greyed out.

          There are two ways to compute the total of values shown in an unfiltered portal:

          In the parent table, define a calculation field as:

          Sum ( PortalTableOccurrence::Number of Hours Leave )

          In the portal table, define a summary field that computes the total of Number of Hours Leave. (I'd guess Number of Hours Leave is text and that would explain it being "greyed out".)

          The calculation field with Sum will update more smoothly when totaling a value in an unfiltered portal where the users edits fields that affect the total being computed.

          The summary field will work with filtered portals and can compute totals faster in some circumstances when large numbers of records are being totaled. A script trigger can run a script to refresh the window when you use the summary field and need the total to update each time you edit a field that's part of the total.

          • 2. Re: Adding the values from one field that has many records -Please help!!
            JonHodge

            Thanks for that, You were right... it was text!! doh!!

            Now for the silly question....in your calculation:

            Sum(PortalTableOccurence::Number of hours leave)

             

            Where do you get the information PortalTableOccurence?

             

            Do I just put a number here or can it automatically just count the amount of occurences there are?

             

            Thanks again,

             

            Jon

            • 3. Re: Adding the values from one field that has many records -Please help!!
              philmodjunk

              Where do you get the information PortalTableOccurence

              I try to be very specific with my examples. Table occurrences are the "boxes" found in Manage | Database | Relationships.

              In portal setup, there's a drop down titled "show related records from". You'll see the same text listed in the bottom left corner of the portal when in layout mode. This text matches the name of one of the table occurrences in your database. You need to use that name, whatever it is, in place of "portal table occurrence". The key here is to make sure to specify the same occurrence as your portal to make sure that you are instructing FileMaker to use the same relationship.

              If interested in learning more about table occurrences and how they control the function of your database, see this tutorial:  Tutorial: What are Table Occurrences?

              • 4. Re: Adding the values from one field that has many records -Please help!!
                JonHodge

                Brain dead I'm afraid!!! Thanks for the explaination, it worked perfectly.

                Thanks for your help,

                 

                Jon