5 Replies Latest reply on Mar 6, 2013 12:16 PM by philmodjunk

    Adding automatic invoices for repeat orders.



      Adding automatic invoices for repeat orders.


       Hi I am working on a database to help keep track of classes and fees at my sisters local dance school. I am new to filemaker and am having some trouble. I have started with the Invoices Starter solution as this seems to be the closest to my needs from the starter solutions (open to suggestions if anyone has a better solution). I am substituting customers with the students for the school. The products are in this case the lessons offered. I would like to create monthly invoices for each student listing the classes they take and the cost. I have been able to do this manually, create new invoice and add the lessons that apply to the student. This is would be too time consuming given the number of students and classes. I would like to find a way to list the lessons each student does and have an invoice created on the 1st of each month for all these classes. Any help that can be provided would be very much appreciated. Thanks

        • 1. Re: Adding automatic invoices for repeat orders.

               A short term solution might be to use a script to duplicate the invoice from the preceding month. You can duplicate the invoices, and then edit the resulting invoices, if necessary to reflect any changes since the preceding month. That requires duplicating records in two tables: Invoices and INnvoice Data (Called Line Items if you are using FileMaker 11). See the script near the end of this thread for an example script that does this: Duplicating Bill Of Materials (duplicating portal line items)

               A long term solution might be to use a different solution of your own design that allows you to manage student enrollment and attendance (if relevant) in a many to many relationship. A script can be run each month to generate the needed monthly invoices for billing your customers.

          • 2. Re: Adding automatic invoices for repeat orders.

                 Thank you for the suggestions. I feel that the best approach would be to create a new solution as you say however I fear this is beyond my abilities at present could you suggest a book, or other method of me learning how to design an advanced solution like this. 


            • 3. Re: Adding automatic invoices for repeat orders.

                   I can't really make a recommendation for you. There are many different resources available--even you tube videos as well as a number of book titles. Which will work best for you will be determined by your own aptitude and prior experience with computers and relational databases.

              • 4. Re: Adding automatic invoices for repeat orders.
                 Thank You for taking the time to help me out. I have begun working on my solution for the school I have used the invoicing solution provided as a starting point as this achieves a good portion of what I need it to. I Have the database set up as follows. Customers- these are the children who attend the dance school Products - These are the classes that can be taken Invoices- Used to create invoices for the customers on a monthly basis listing there classes and the cost. This works well to create manual invoices for the customers each month but I am looking to automate this. As you stated above what I would like to do is somehow store the list of classes attended by each student and have the invoice automatically created on 1st of the month which includes all of the classes in the list for that student. Visually what I would like is a list of classes on each students record which could be checked if the student takes the class. I do not know how to set this up so that the invoices can draw information from this list. I have been searching online for tutorials to accomplish this but can't seem to find anything like this. Any help would be greatly appreciated. Thanks
                • 5. Re: Adding automatic invoices for repeat orders.

                       There are a number of threads here that discuss enrolling students into classes. Typically, a given student can enroll in more than one class and of course a class lists many students. This makes for a classic "many to many" relationship. That may sound intimidating, but the relationship between invoices and products in your database is also a many to many relationship.

                       But there are some key details that can further complicate this depending on how you operate your business:

                  Does attendance affect billing?

                       If a student is sick for a week and misses class, do they still get billed for the class? If so, you'll need to track student attendance as well as student enrollment.

                  Is a given class offered at more than one date and time?

                       Example: Beginning Ballet might be offered to one group of students on Monday and another group might be enrolled in the same class on Tuesday. While you could log the Tuesday and Monday sessions of this class as separate products, it is more efficient to add a related Sessions table that lists each available session of each available class.

                       Head hurting yet? wink