4 Replies Latest reply on Sep 16, 2013 12:11 PM by philmodjunk

    Monthly payments and monthly tuition solution

    rvarghes

      Title

      Monthly payments and monthly tuition solution

      Post


           Hello All,

           I have put together a database for tracking monthly tuition (which changes) and payments received (sometime more than one).  Two questions: 

           A) If enrollment is set-up with M, T, W, TH, F as text field and I've use the following calculation to populate the EnrolledDays calculation field [If (Monday=Enrolled;1;0) + If (Tuesday=Enrolled;1;0) + If (Wednesday=Enrolled;1;0)+If (Thursday=Enrolled;1;0)+If (Friday=Enrolled;1;0)]  Would it be best to make duplicates of these fields for each month so I can have a record before October's changes are made?  Is there a better way to do this?

           B) Tuition is calculated based on the above EnrolledDays calculation multiplied by the daily rate and payments are received during the month to be applied against the tuition.  I have made an invoice table that holds monthly invoices and separate payments table related to the invoices and Family accounts via kf_familyID field.  I would like any suggestions on how best to sum payments related to each family, enter it into the September_payments field in the invoices table, then calculate the October tuition without having the September tuition change (not sure that it will change but worried that it may - September tuition is a calculated field)

            

      Invoice.jpg

        • 1. Re: Monthly payments and monthly tuition solution
          rvarghes

               Relationship in my database are set-up as follows if this helps.

          • 2. Re: Monthly payments and monthly tuition solution
            philmodjunk

                 A) If the data changes with each month, then the best approach is to have a table of related records where you create a new record for each month's enrollment. Your calculations for the next month's tuition can then be based on the most recently created related record (A date field in each table can be part of the "match" fields defined in the relationship.)

                 B) You need to deal with A) before you tackle this issue, but a full up payment tracking system has to handle:

                   
            1.           Partial payments that do not completely pay off a given bill. (Sometimes this is due to a mistake on the part of the client.)
            2.      
            3.           Payments that pay off more than one bill.
            4.      
            5.           Payments that exceed the amount owed. (This could be a client error or they could be "paying in advance".)

                  

                 This describes a many to many relationship between payments and bills. As a given payment may need to be linked to more than one bill and a given bill may need to be linked to more than one payment.

                 Start with these relationships:

                 Payments-----<Payment_Bill>-----Invoices

                 Payments::__pkPaymentID = Payment_Invoice::_fkPaymentID
                 Invoices::__pkInvoiceD = Payment_Invoice::_fkInvoiceD

                 You can place a portal to Payment_Invoice on the Payments layout to list and select an Invoice record for each given Payments record. Fields from Invoices can be included in the Portal to show additional info about each selected Invoice record and the _fkInvoiceD field can be set up with a value list for selecting Invoice records by their ID field. And an Amount field in Payment_Invoice can record that portion of the total payment received that applies to a specific Invoice record.

            • 3. Re: Monthly payments and monthly tuition solution
              rvarghes

                   Thanks for the response Phil.

                   I'm unclear if you mean create a new table (EnrollmentRecords) relate it to the Family_Student table via kf_studentID.  If this is correct, I need to add a autocreation date field to the new table.  How do I include this in the relationship information so that I only find the enrollment record for October and not the record for September when I do my calculations for tuition?  Still new at this.

                   Robin.

              • 4. Re: Monthly payments and monthly tuition solution
                philmodjunk

                     A date field can certainly be set to auto-enter the creation date or a script can create the record and assign a date at the same time. I would use a date for the first day of the specified month for this and a script may be a better option if it's possible that you might create this record both before or after the current date is from the new month.

                     Presumably each invoice is specific to a particular month and year so a date field for the first day of the specified month in invoices can match to the same type of date field in this related table. This then would use two pairs of match fields, one that matches by student or family ID and one that matches by this date.

                     And here is a useful calculation for computing the date for the first day of the month for a date entered into a date field:

                     Datefield - Day ( DateField ) + 1 //be sure to select Date as the result type