1 Reply Latest reply on Apr 12, 2014 9:38 AM by philmodjunk

    New to FileMaker! Need help summarizing fields!



      New to FileMaker! Need help summarizing fields!


           Hi Guys,

           I am a complete newbie to FileMaker, and gotta say...so far I am LOVING it!

           I'm trying to build a spending tracker in FileMaker right now. So far, I have (1) tables for the two credit cards that I have. Essentially, each table has the following fields:

      •           Transaction Date
      •           Description
      •           Payment Method
      •           Credit Card Name
      •           Transaction Amount
      •           Total Spent

           What I'd like to do is create a "tracking" page/layout where there is a Drop-Down field, in which you can select your Credit Card Name, then based on that Credit Card Name, the next field to the right is a date range where you can type in "4/1/14...4/30/14" for example, and then (sorry this is a long sentence) the next field to the right is a Running Total of the amount spent for that Credit Card Name.

           Hopefully this makes sense! Thanks FIleMaker community!



        • 1. Re: New to FileMaker! Need help summarizing fields!

               This should be one table, not two for your two credit card transactions as it will make your reporting much simpler to do. Note that your Credit Card Name field can be used to group your transactions by the two different cards. I would also add additional fields so that you can log payments against your credit cards and get the balance due.

               What you describe is not actually a running total, it's just the total for that card for that date range.

               If you set up these fields: SelectedCreditCardName, StartDate, EndDate

               Then this relationship would work, provided that you use one table for both credit card transactions:

               LayoutTableOccurrence::SelectedCredCardName = Transactions::CreditCardName AND
               LayoutTableOccurrence::StartDate < Transactions::TransactionDate AND
               LayoutTableOccurrence::EndDate > Transactions::TransactionDate

               Then, if Total Spent is a summary field computing the total of Transaction Amount, you can place Transactions::Total Spent on this layout and get the total for the specified credit card over the specified range.

               LayoutTableOccurrence is the table occurrence (box from Manage | Database | Relationship) that you select in the "show records from" drop down for your layout. Transactions would be a different table occurrence linked to LayoutTableOccurrence in Manage | Database | Relationships with the match fields that I have just shown. (Drag from one table occurrence to the other to start the relationship, then double click the relationship line that this has created to open a dialog where you can add more math field pairs and specify the inequality operators.

               LayoutTableOccurrence and Transactions could beTutorial: What are Table Occurrences? and the fields specified as match fields for LayoutTableOccurences could be fields with global storage specified.