9 Replies Latest reply on Dec 17, 2010 10:30 AM by pdoak_1

    Trouble with summary fields



      Trouble with summary fields


      Background Info: I am using the following

      1. Filemaker pro Advanced 11
      2. Mac OS X 10.6.5
      3. Single user mode
      4. I have been using Filemaker Pro for a couple of months

      I have one table which lists client id's and client accounts numbers.  One client can have multiple accounts.  In another table, I have the client account nos, the balance on the account listed by date.

      I have used the following join relationship:

      Table1:RefDate = Table2:Date and

      Table1:client account = Table2:client account.

      However, I cannot seem to produce a layout which shows the client accounts and their corresponding balance for the chosen RefDate.  I can list the accounts but the balance field is blank.  In addition, I want to be able to show the total of all client balances for that day as a sub-total.  

      I am sure that this must be simple but I just can't seem to get it to work.

        • 1. Re: Trouble with summary fields

          We need to know more.

          Is your layout based on table 1 or table 2?

          How have you set up your fields for deposits, withdrawals and the balance of the two?

          In table two do you have multiple entries for given account on the same date or just one?

          Do you want to see all the entries or just a single row for each account?

          • 2. Re: Trouble with summary fields

            The layout is based on Table 1.

            Table 2 just shows the balance of each account

            In Table 2, I have just have one entry per account per day.

            I want to see the following:

            Client ID 1

            Account 1 £xxxx

            Account 2 £xxxx

            Client ID 2

            Account 3 £xxxx

            Account 4 £xxxx

            Summary £Total of all balances for that day

            • 3. Re: Trouble with summary fields
              1. Base your layout on Table 2 instead of Table 1.
              2. Put the needed client fields from Table 1 in a Sub Summary part "when sorted by Client ID" that "prints above" the body.
              3. Put the account fields in the body.
              4. Define summary fields for any grand totals you want and put them in a Trailing Grand Summary part.
              5. To add layout parts and/or change their settings, select Part Setup... from the Layouts menu while in Layout mode.
              6. You can control what data is included in this report by performing finds. After finding the records you want, you must then sort your records at least by Client ID in order for the Sub Summary part to be visible. And the layout should be set to list view, not form or table view.
              • 4. Re: Trouble with summary fields

                Thank you - that worked.

                Please can you explain why working from Table 1 does not work?

                • 5. Re: Trouble with summary fields

                  Aslo,  I am trying to write a script so that the table will automatically perform the find based upon a global date variable.  I have written the following script and set it to run "OnLayoutKetstroke" and "OnViewChange" but it does not seem to do anything.  The script runs with the scriptparameter set to the global date variable which is in another table.

                  Find Rec Layout

                  Enter Find Mode [ ] [ Pause ]

                  Set Field [ Y_ACCOUNTSTATUS 3::DateFM; Get(ScriptParameter) ]

                  Perform Find [ ]

                  • 6. Re: Trouble with summary fields

                    Why the Pause in Enter Find Mode?

                    Does your report layout refer to Y_ACCOUNTSTATUS 3 in Show Records from in Layout setup... ?

                    Please can you explain why working from Table 1 does not work?

                    It may work--depends on the structure of table 1. I knew it would work from table 2 and so instructed you to use it. If you can list one record in Table 1 for each account, then you can also set this up with Table 1. You can either add the summary field from table 2 or define a calculation field in Table 1 that uses the Sum function to compute the total value of the matching related records in Table 2. The grand summary totals, however, will need to be a summary field that totals your calculation fields that use Sum or which use a different relationship matching by date only to get the grand totals.

                    • 7. Re: Trouble with summary fields

                      I have removed the Pause in Enter Find Mode but that hasn't made any difference.

                      The report layout is from y_accountstatus 3.

                      Please can you explain why working from Table 1 does not work? Thanks for the explanation

                      • 8. Re: Trouble with summary fields

                        I didn't think the pause was necessarily wrong, just needed to know why you had it in there to begin with. Wink

                        Hmmm, immediately after running the script, select modify last find from the records menu and check the DateFm field to see what criteria was entered. If this criteria looks correct let me see an example of the criteria you are entering. If it's not what you expected, go back to the place where the parameter is specified and see if that expression needs modification. (And you can post that expression here if you can't see what's wrong with it.)

                        • 9. Re: Trouble with summary fields

                          I solved.  I set a script trigger on the global date field and it now works as desired.  Many thanks