4 Replies Latest reply on Jul 23, 2015 1:38 PM by SteveMartino

    Expenses by month

    TimCranwill

      Title

      Expenses by month

      Post

      I have a client base that have expenses attached to them via relationship. I have a global date range picker in order to see expense within the range selected that work well with other layouts. 

      What I'm trying to do now is build a new list layout of clients with all months listed Jan-Dec and the expense totals for each month all on one layout. Also in the header I need to have a year picker, so we can look at the results by year. 

      Manage_Database_for_%E2%80%9CTeskey%E2%80%9D_FileMaker_Pro_Advanced%2C_Today_at_12.03.22_PM.png

        • 1. Re: Expenses by month
          SteveMartino

          You just need a new sub summary report with some new fields.

          a)  cExpenseMonth-calculation field=MonthName(ExpenseDateField)

          b)  sCountExpenses-summary field=Count(PrimaryKeyFromYourTable)

          c)  gYear-global text field.  You can either tie this into a value list of years, and let the user pick from a drop down, or let the user type in a year(I'd rather pick from dropdown)

          On the new layout, based on clientsExpenses

          1. create a sub summary part sorted by cExpenseMonth (above).  Put the cExpenseMonth field and the sCountExpenses (totals by month) field in this part (usually as merge fields)

          2.  In the body part, put the clients name and any other detailed info

          3.  In the footer, put another instance of sCountExpenses (totals for the year)

          4. In the header put gYear in the header, dropdown.  Attach an OnObjectExit script trigger with the following script parameter:

          Table::ExpenseDateField //you'll have to pick this from your database

          and the following script:

          Set Error Capture [On]

          Enter Find Mode [ ] //uncheck pause

          Set Field [Table::ExpenseDateField; Get(ScriptParameter)]

          Perform Find [ ] //make sure this is cleared out

          Sort Records [ ] //sort by cExpenseMonth (based on value list-create a value list with the Month Names in chronological order), then sort by client name, and any other ways you want to sort

          Go to Record/Request/Page [first]

          • 2. Re: Expenses by month
            TimCranwill

            Thanks Steve I will definitely try this and get back to you.

            • 3. Re: Expenses by month
              philmodjunk

              For cExpensesMonth, I would define it as:

              ExpenseDateField - Day (ExpenseDateField) + 1   //calculates the date of the first day of the same month, year

              and select "date" as the result type. When I sort on this value I'll get January, not April as the first group of expenses and I can put this field field in my sub summary part, but formatted to only display the month name to show the name of the month for each year. This also allows you to use a report that might not start with January and end with December and you'll still get your groups of records in the correct chronological order.

              • 4. Re: Expenses by month
                SteveMartino

                I was wondering why that happened, hence making the value list to sort by...thanks Phil....