3 Replies Latest reply on Jan 26, 2010 9:39 AM by hil400

    help creating DB for school lunch program



      help creating DB for school lunch program


      I haven't used Filemaker in 6 years and at a loss. Using FMP ver 10 on a mac.  The school I work in wants a DB created to track lunch provided to students. The categories for students are: Free, Reduced ($0.25), and full pay ($1.50). Students are tracked by number category: A=free 1= free 2=reduced 3= full pay 4=full pay and 5=full pay. There are 480 students. They want to know: How many free, reduced, and full pay lunches are served DAILY, and invoices printed WEEKLY delivered to each student (just name and class.. no mailing address needed) detailing what each family owes for lunch. This again is a weekly printout. Of course once payment is made, it's reflected in the database.


      I've created these fields for each of the 480 students and have the data already entered from an excel spreadsheet. :


      Student Name


      Meal code (see above)

      Calendar (date field with a calendar popup for easy date entry

      Price code ( a calculated field that reflect the meal code:    If ( Meal Code = 3; 1.5 ;  If ( Meal Code =2 ; .25 ; If ( Meal Code=4 ;  1.5 ;  If ( Meal Code=5 ; 1.5 ; If ( Meal Code=1 ; 0 ; If ( Meal Code="A" ; 0 ; If ( Meal Code=" " ; 0 ; If (Meal Code=1 ; 0 ; 0 ))))))))


      (there are 2 students without meal codes so I entered " " they are free so it produced $0.00


      That's as far as I got. ANY HELP greatly appreciated.


      Thank you

        • 1. Re: help creating DB for school lunch program

          You can certainly use the table and fields you describe to do what you want, but there's a drawback to it that you can avoid with a small design change:


          What happens when you have to change the amount charged for a reduced or full price student? With the current set up, a person with developer skills and full database access has to locate your field definition and change the amounts in the expression.


          A better approach:

          Define a second table: Prices

          Meal code (text)

          Amount (number)


          Define a relationship to link your two tables:

          YourTable::Meal code = Prices::Meal code


          In your original table, replace the calculated field with a new field, AmountCharged.

          In field options for this field, select the Looked up value and set it to copy the amount from the Prices table. In your original table, I'd also make sure that Meal code is of type text as A is one of your meal codes.


          Now, when you enter a meal code, the current amount in the matching Prices record is copied into your table. If the amount charged changes, you simply use a layout that displays the records in Prices to enter the price change and the new prices will appear in each new record you create in your original table.


          More design change:


          I'd split the fields you've listed into two tables:


          StudentID (serial number field)

          Student Name


          Meal Code




          Weekof (date)

          Meal Code (text)

          Date (auto entered creation date)

          Paid (text, use if students can pay meal by meal)


          I'd link these two tables by StudentID. (I wouldn't use names here, even children's names can be subject to change and this would cause problems for your databse if you use the name field.)


          Since you want a weekly invoice, I'd also define an Invoices table:


          Weekof (Date)

          Paid (text, use to show that invoice has been paid.)


          A portal linked to Meals by Student ID and Weekof can be set with 5 rows to display the weekly meals invoice for a given student.

          • 2. Re: help creating DB for school lunch program

            You really should have a students table, a tranactions table, and a price table where you can lookup the prices from as prices can change.


            For each transaction, the meal code could be looked up from the students table. Then setting this meal code should lookup the prices feom the price table.


            In the end, the transaction should look something like the following:


            fkStudentID     Date               MealCode             Price

            3                   11/24/2009         2                      .25




            • 3. Re: help creating DB for school lunch program
                 Thanks so much .......and so sorry to be late in the thanking..... been away with family issues...... I'll try your suggestions and get back to you.