1 Reply Latest reply on Nov 20, 2013 7:01 AM by philmodjunk

    Sum

    MarkJohnson1069

      Title

      Sum

      Post


           I have a relation ship between my customer file & invoice file. On the customer file, I created a field 'MTH-01' that I need the total transactions for month-1 for the current year (field gCurrentYear set as a global field), using the sale date.

            

           Thanks

        • 1. Re: Sum
          philmodjunk

               I would think a date field that returns the date for the first of the month for all dates in the same month would be easier to set up:

               InvoiceDate - Day ( InovoiceDate ) + 1

               with date specified as the result type.

               You can then use:

               Get ( CurrentDate ) - Day ( Get (CurrentDate ) ) + 1

               As an autoenter -calculation on a global date field. That way, you can select or enter any date and it automatically converts to a value matching the calculation field's value for all Invoices of that month. And you also have a single field that specifies both month and year.

               Whether you use that method or "MTH-01", you'd duplicate your Invoices table occurrence in Manage | Database | Relationships and set up this relationship:

               Customer::__pkCustomerID = Invoices|Month::_fkCustomer AND
               Customer::gMonth = Invoices|Month::cMonth AND
               Customer::gCurrentYear = Invoices|Month::cYear   (Don't need this last pair of match fields if you use my suggestedcalculation.

               With your method, you'll need a calculation for cMonth similar to this:

               "MTH-" & Right ( "0" & Month ( InvoiceDate ) ; 2 )

               and cYear: Year ( InvoiceDate )

               Either way, Sum ( Invoices|Month::InvoiceTotal )

               will return a total for the selected month.

               If you want this for every month of the year at the same time, there are other options that you can explore.

               Some are described in this thread: Sum_Calculation based on condition

               And if you have FileMaker 12, you can use this method: FMP 12 Tip: Summary Recaps (Portal Subtotals)