8 Replies Latest reply on Apr 4, 2013 9:31 AM by philmodjunk

    Possible Solution



      Possible Solution



           I am new to this File maker, so i don't know that if can achieve this function in File maker or not. If yes then how?

           Here is what i want to do.


           I am trying to make School Database in regard with Fee payment. My Goal was to make a database which can store all students records with there proper classes. and maintain a record of Fee.

           first there Fee the for each student is Different. Hence each students fee must be stored differently. Second the each student must pay 12 month Fee. For that, i want to issue a Fee voucher that can be paid in the Bank. with there monthly fee on it. and if he has not paid the previous months Fees. then it should also be added in current months voucher.

           Voucher numbers should be unique. and also stored in Database. So i can make a list of Voucher Numbers issue to which student. of which month.

           I am attaching a File. which i have made so far.

           there will be 2 layouts. in it. Main(Which will allow to enter new students data. there monthly Fee and Also enter Voucher number and month for each student which have paid the Fee.)

           second is Voucher. in this layout i want to make a Filter and Print the vouchers class wise and Gender.


           username: admin


        • 1. Re: Possible Solution

               How many records in Fee might there be for a given record in student? Just one or could there be multiple records such as one for each year, one for each class or one for each semester?

               If more than one fee record for a given student, there's no field yet added to Fee to distimguish one such record from another.

               What is the purpose for the Fee Detail table? Will there be more than one Fee Detail record for a given Fee record?

          • 2. Re: Possible Solution

                 There will be only 1 record per student record in Fee. As for Fee detail. It will hold the record for received Fee. It will hold data like what voucher number and which month was paid at what amount.

                 I want use that data to print defaulters list(student who has not paid fee of previous months).

            • 3. Re: Possible Solution

                   There will be only 1 record per student record in Fee.

                   Then one option to simplify your relationship graph is to move the fields from Fee into the student table and then you remove the Fee table from your database.

                   Any of the following relationships might be used:

                   Student-----Fees-----<Voucher Numbers------Fee Detail

                   Student-----<Voucher Numbers-----Fee Detail  (field from Fees now moved to Student)

                   Student -----<Fee Detail (Voucher Number and other Voucher fields moved to Fee Detail)

                   But in any of these cases, what do you plan to do when a student brings in a payment that pays off more than one Voucher to bring their account up to date? Will you create a Fee Detail record for each Voucher splitting up the amount of the single payment over each voucher thus paid?

                   To get a list of all students that are in default, you will need to specify a month (or month/year) with so that you can compute that total that should be paid to compare to the total from Fee Detail finding all recors where the total that should have been paid is less than the total from Fee Detail. The specified month can be computed from the current date or be from a value specified by the user in a field. (Using a field, you can get a list of those students not yet paid for the upcoming month or the current month by selecting a different value in such a field.

                   Next question:

                   What month marks the first month for which a payment must be received?

                   We'll need that detail in order to set up a relationship based calculation that can identify which students are in default. This "first month" can be set up as a field in your database if you need it to be a value that you can change from year to year or even student to student.

              • 4. Re: Possible Solution

                     For calculating the previous months that are not paid. my plan was to add field in Field table. which will contain last paid months (month/year). hence when i will be printing the Vouchers. it will calculate the difference between the current and last paid month and then add that amount accordingly into voucher.

                     As for First month, yeah First month is March.

                     But what i cannot understand is to how to get unique voucher numbers and place them on vouchers? Kindly explain that part in step by step.

                     Because i need it that info stored. So, in future i can print that list class wise, for checking which voucher number was issued to which student.

                • 5. Re: Possible Solution

                       Can't your voucher numbers be auto-entered serial numbers so that each voucher number in your table is unique?

                  • 6. Re: Possible Solution

                         My Voucher Generation Layout is Fee table based. Can u plz give me hint, how to do it?

                    • 7. Re: Possible Solution

                           if am using this script o calculate the elapsed months,


                           "(Year(datetest::date2+ 1) - Year(datetest::date1 + 1))*12 + (Month(datetest::date1 + 1 ) - Month(datetest::date1 + 1)) - If (Day (datetest::date2 + 1) = Day(datetest::date1); 0; If (Day(datetest::date2 +1) < Day (datetest::date1 + 1); 1; 0))"

                           where datetest2 is current date and datetest1 is older date.

                           But i am getting wrong result, like 0 only.

                           am  doing somthing wrong or missing?

                           I am trying to follow this example,


                      • 8. Re: Possible Solution

                                  My Voucher Generation Layout is Fee table based. Can u plz give me hint, how to do it?

                             But you have a Voucher Table that auto-enters ID numbers. Each time you create a new record in that table, you'll get a unique ID number for the voucher.


                                  But i am getting wrong result, like 0 only.

                             After using the FileMaker Advanced's Data viewer to test, analyze and simplify, I came up with:

                             (Year(date2) - Year(date1))*12 + Month(date2 ) - Month(date1 ) - ( Day(date2 ) < Day (date1) )

                             Your original calculation had two main problems: The use of + 1 that served no purpose throughout the calculation and when you substracted months, you used Date1 in both cases.