7 Replies Latest reply on Aug 3, 2017 8:04 AM by dburnham

    Invoices report-grouping by year and month...how?

    dazerella

      Hi,

       

      I am quite new to Filemaker and I am in the process of trying to build a database with a number of different tables which are all related to one main table.

       

      One of the tables needs to show all of the invoices which have been sent (Just a reference number, invoice number and date sent).  It doesn't need to be fancy, or link to any inventory, etc.  But, I want to be able to create a report which sorts the invoices by Year, and then within each year give a sub-heading of the Month.  I have been trying to do this and am stuck....

       

      So far I have managed to sort the invoices with a sub-summary (of MonthSort) with the 'Date Sent' field set within this (customised to show only the month and year).  However it does a new sub-heading for each day within the month, rather than lumping all of 'January', etc. together under one heading.  So three invoice dated on three dates in January result in three headings.

       

      Please can you advise, in basic terms, how to complete this using Filemaker Pro 16?

       

      Any help you can give would be greatly appreciated.

       

      P.S. I'm using Windows if that makes a difference to how Filemaker is laid out.

        • 1. Re: Invoices report-grouping by year and month...how?
          marthazink

          What you'll need to create is a calculation that will clump things together. Like you mentioned, the date groups by that specific date.

           

          Let's pretend your date field is called InvoiceDate. Create a calculation that looks like:

           

          Date ( Month ( InvoiceDate ) ; 1 ; Year ( InvoiceDate ) )

           

          That will force every record to have a date with the 1st of the month. So an invoice for 1/21/17 and 1/31/17 would both show 1/1/17 in the new calculation. Then you can use your subsummary sections to group by your new field.

          1 of 1 people found this helpful
          • 2. Re: Invoices report-grouping by year and month...how?
            techt

            You're on the right track if I'm reading your question correctly. Using a sub-summary, but exclude the body part of the layout. That way you see just the sub-summary items. Another interpretation would be that your sub-summary is still sorting on the date, not the MonthSort field you mentioned. If that's the case, then you'll still get three sub-summaries for the month (in your example). You may need another field, or modify your MonthSort to only include the month and sort on that in the sub-summary. That would group all of January together. If you could post a copy of your db, I'm sure it's a quick change.

            • 3. Re: Invoices report-grouping by year and month...how?
              beverly

              Interesting approach, Martha! I like it. I had been using a

              YYYY_MM

              (as text) auto-enter with some calc to make it...

              Then I got this as a better alternative (thank you, @user19752) as number auto-enter:

              (Year(myDate) * 100 ) + Month(myDate)

              It works for all kinds of "grouping" for reports and is numerically and chronologically correct.

              I suppose the date, as you have it - all on day one, also works internationally and is a searchable date field as well. Very cool! Thank you for sharing.

              Beverly

              1 of 1 people found this helpful
              • 4. Re: Invoices report-grouping by year and month...how?
                marthazink

                Nice! I like that alternative approach of making it numerical. Like you mentioned, the date is nice because it respects the date format and I can still have calculations against it for month and year, if needed. As usual, one problem... many solutions!

                • 5. Re: Invoices report-grouping by year and month...how?
                  philmodjunk

                  You can also get that first of the month date by using:

                   

                  Date - Day ( Date ) + 1

                   

                  Can't say that one is better than the other, but you'll see the above a lot in other parts of this forum.

                  1 of 1 people found this helpful
                  • 6. Re: Invoices report-grouping by year and month...how?
                    dburnham

                    I may have understood the original request to be more like a request for an Aged Trial Balance, in which the invoices are grouped according to the balance owed in each of 4 (or more) columns:  current, 30-60, 60-90, 90+ etc.

                     

                    First you need a balance due field that calculates the total minus any payments applied.

                    Second, you need to have an unstored calculation (See tip below) that indicates the number of days since the invoice date.

                    Third, create 4 fields to store the current balance, 30-60 day balance, 60-90 day balance, and 90+ balance using calculations that make the result zero unless the number of past due days matches the defined range.

                     

                    Then, the 4 columns can be displayed in a report, with summary total fields at the end if that is desired, too.

                     

                    The tip I want to offer is to avoid using unstored calculations because of the time it takes to evaluate a large account receivable file of invoices.  Instead, do this:

                    1. Create a script that runs on the server that recalculates all invoice records once every day at 12:01 AM.  That's the only time in any 24 hour cycle which would change the way the numbers appear in each column
                    2. Create a script trigger that does the same re-calculation on any invoice record that is changed by the entry of a payment or any other change that would alter the balance due.

                     

                    One more thing:  I like Martha's use of "clump".  It's great. 

                    • 7. Re: Invoices report-grouping by year and month...how?
                      dburnham

                      For a number of clients who need to report taxes, I have always defined a field which I call a "billing period" because it's numerical and can easily be sorted.  Be careful to put a leading zero in front of the months that precede October so that you won't get a number like  20175 when what is needed is 201705.

                       

                      This kind of field allows you to "lock" records in the Security/Privilege sets to disallow the changing of any records in billing periods that have been "closed."  Typically, in accounting, it is customary to "close" a month.

                       

                      Also, some companies operate on a 13-month year, rather than 12, because 52/13=4.  In other words, they can measure their month-to-month results more accurately when every month (billing period) has the same number of weeks.   Defining the billing period this way is not much more difficult than using year+month.  You can use weekofyear or weekoffiscalyear instead.