3 Replies Latest reply on Oct 14, 2009 9:06 AM by philmodjunk

    Help With Table set-up

    nycnewbie

      Title

      Help With Table set-up

      Post

      Hey all! I use Filemaker Pro 10 on a Mac desktop. I am working on a database for a small school keeping track of student info and enrollement. Please check out how I have set up my database....it looks like it makes sense but I'm not able to share info except between STUDENTS, ENROLLEMENT, & CLASSES - as you can see i've connected INOVICE to ENROLLEMENT. I'm having trouble wrapping my head around how to get this to work. I need to be able to take payments for students and customize payment plans.

       

       

      STUDENTS
      students ID

      ENROLLEMENT
      classes ID
      students ID
      Invoice ID

      CLASSES
      classes id

      INVOICE
      Invoice ID

      JOIN TABLE
      Payments ID
      Invoice ID

      PAYMENTS
      Payments ID









        • 1. Re: Help With Table set-up
          philmodjunk
            

          It looks reasonable if you need a single payment to be able to pay for more than one invoice such as a parent paying for the enrollment of more than one child with a single check.

           

          Describe where/how you get stuck setting this up.

          • 2. Re: Help With Table set-up
            nycnewbie
              

            Well I have a portal set up in Classes so that I never go into Enrollement I just go to the class where the enrollement portal is and using the STUDENT ID to find the student they are automatically enrolled into a class. That works perfectly....and I have the portal in the student file so I can see what classes the student is enrolled in.

             

            I take checks where students make partial payments and break the check up between classes. In addition depending on their enrollement they have different payment plans and different due dates. So I'd like to go to the invoice table and bring up one student to see all of the classes they are in and what they owe and how much they've paid.  Then I'd like to have a portal to their student file so I can make changes directly from there.  When I go into INVOICE table I cant bring up info with STUDENTS ID..... infact I can't pull up any related info.

             

            (Thanks for your quick response)

            • 3. Re: Help With Table set-up
              philmodjunk
                

              The problem you encounter when creating a new invoice is that you have yet established the necessary link to the specific list of enrollment records that comprise the bill you are invoicing with this record.

               

              I picture this as an invoice where the student is the customer and the enrollment records listed on the invoice are the line items whose individual costs add up to the total billed on the invoice.

               

              Delete the link between enrollments and invoices.

              Add a student ID field to Invoices.

              Link Invoices to Students by student ID.

              Now, when you enter or select a student ID in invoices a portal can show all enrollment records for that student.

               

              Now you need to select enrollment records for the given student that you need to link to this invoice record.

              Link Invoices to enrollments by Invoice ID, getting a new table Occurrence, InvoicedEnrollments.

              Now, manually or in a script, you can assign enrollment records to the current invoice. Manually, you'd display the enrollment records in a Portal to Enrollments and enter the invoice number record by record. (Or script it so that when you click a button on each row, the current invoice record's Invoice ID is assigned to this record.) In a script, you could use the relationship to enrollments and Go To Related Records to find all enrollment records and then assign any uninvoiced (invoiceID field is empty) records to the current invoice record.

               

              If you use Go To Related Records, check out this thread for a complete description of all it's features and issues:

              The Complete Go To Related Record