2 Replies Latest reply on Dec 10, 2010 1:28 PM by RandallNewman

    Calculation Field

    RandallNewman

      Title

      Calculation Field

      Post

      Hi, I am pretty new to Filemaker Pro and need some help. I am trying to implement a database to help keep track of expenses associated with academic conferences, lectures, or other departmental events. To that end, I have created a file with a unique record for each event and with a comprensive list of categories of expenses.

      My difficulty is that different accounts may be used to pay for different expenses in the same event. I would like to create calculation fields for each account that total the expense amounts for that account in that one conference, lecture, or event. I have been unable to figure out the right calculation formula.

      Below is an example of what I have created. Please note that the account fields are simple text fields in which the account number must be entered manually. I am not certain whether specific expenses will always be associated with the same account. The department has multiple accounts and when one account is depleted others may be used to cover the same expense.

      For example,

      Event ID = 10001

      Event = Conference

      Expense                 Amount                      Account

      Honorarium            $500                          43100

      Travel                      $350                         44100

      Dinner                    $100                           43100

      Room                      $25                             44100

      Account 43100    Total

      Account 44100    Total

      Thank you for any help that you can offer.

      R

        • 1. Re: Calculation Field
          philmodjunk

          I think you should set this up as a related table of expense entries with fields to record the description, the amount and the selected account number. A summary report based on this table, but drawing data about the conference from the related conference record can be designed that groups your expenses by account and computes a subtotal for each.

          Conferences::ConferenceID = Expenses::ConferenceID   (Conferences::ConferenceID should be an auto-entered serial number).

          Define these fields in Expenses:

          ConferenceID
          Description (where you enter Honorarium, travel, etc.)
          Amount
          Account
          sTotal (a summary field defined as the Total of Amount )

          You'd place a portal to Expenses on your Conferences Layout and format the Description and Account fields with drop down lists to reduce data-entry errors.

          You can then either use relationships that report a total for each specified account on your conferences layout, or you can place a button on your conferences layout that pulls up this same set of records in a summary report based on your Expenses table that looks like this:

          Conference Name, date, etc. (any fields from the conferences table that you want at the top of your report)

          Account 43100
          Honorarium      500
          Dinner              100
          Room                100

          Total for 43100:  $700

          Account 44100
          Travel               350
          Room                  25

          Total for 44100 $375

          Grand Total $1075

          This portal with summary report based approach will enable you to process very different expense reports without having to redesign your system.

          • 2. Re: Calculation Field
            RandallNewman

            PhilModJunk:

            Thank you for your suggestion for how to structure the database.  I will try to implement the changes and see if any questions arise.

            R