1 Reply Latest reply on Jan 3, 2013 9:35 PM by philmodjunk

    Calculation formula needed please



      Calculation formula needed please


           I just can't seem to be able to work this one out. I have two tables, the first listing instructors. The second listing their pupils. I want a auto enter field in the instructors table which gives a count of new pupil records created say between 1/12/2012 - 31/12/2012.  I intend to have a field for each month. Each pupil record has a field as an auto enter for creation date. 

           If this is possible, could you please give the formula to enter in the auto enter calcualtion box please. 

        • 1. Re: Calculation formula needed please

               An auto-enter calculation will not update when records in the related pupils table are added/removed/modified.

               If you define a date field with this autoenter calculation, in the pupils table:

               Let ( D = Get ( CurrentDate ) ; D - Day ( D ) + 1 )

               You get a date field that auto-enters the date for the first day of the month in which the record was created.

               A summary report based on the Pupils table can list all pupils for a specified instructor grouped by month with a count for each month. If you just want the count for each month, you can remove the body layout part from that layout and you'll get one row for each month that at least one student joined.

               From the instructor layout, you can set up a matching date field that uses an auto-enter calc to auto-correct to the first day of the month: Self - Day ( Self ) + 1 and you can then use it in your relationship so that by entering a date in this field, you get the total for the specified month.

               There are also ways to get these totals in FileMaker 12 using the ExecuteSQL function.