7 Replies Latest reply on Dec 3, 2011 7:56 AM by AndrewDunn6760

    Relationship help

    AndrewDunn6760

      Title

      Relationship help

      Post

       Hi, looking for some assistance on working out relationships.  I run a dance school and I want to be able to manage class enrolment and actual weekly class attendance.  I currently have a 'class list' table that lists the classes we have over the week but without including a date field and then on my customers layout I have a checkbox field called enrolment, and I pull the class name values from that table. I have then placed a portal on the class list layout to show me everyone who is enrolled onto that class.  Firstly, I understand to get a report on who is enrolled to my classes, I need to be creating my report via the customers table however how can I filter the report so that I can run the report from the class list layout to show all customers enrolled to the current class (current record).  Secondly, I would like to track weekly attendance as well. I have created a third table for weekly attendance and created a class name field which uses the class list::class name value list again to choose the class and a date field. I would then like to create a portal below and be able to add a contact from a drop-down list similar to how the gift feature works by only letting you select customers registered to the event in the event management starter solution.  Once this is setup, I will also want to create a looping script so that my user can select a class on the attendance drop-down and run the script to add all customers who are enrolled and then mark a field for attended.  I know it is a lot which is why I would appreciate any help to ensure I get the Table relationships and TO's correct as I am not having much luck figuring it out myself! Regards Andrew

        • 1. Re: Relationship help
          AndrewDunn6760
           Ps sorry about the block of text as the line spaces were not copied correctly!
          • 2. Re: Relationship help
            philmodjunk

            I suggest breaking this down into parts and getting each part working before moving on to the next.

            First part is to get a better set of tables and relationships so that you have a better foundation for the additional features you want to add.

            You have these tables and relationships, if I read your post correctly:

            Attendance>-----ClassList>-----<Customers             (----< means one to many)

            Attendance::ClassName = ClassList::ClassName
            Customers::EnrollmentCheckBoxField = ClassList::ClassName

            First problem is that Many to Many relationship between ClassList and Customers. While your checkbox formatted enrollment field does work to produce the desired relationship, this approach can be very difficult to work with as Classes offered change and when you start to produce some of the reports that you want. The following change is much easier to work with when you have a many to many relationship:

            ClassList----<Enrollment>------Customers

            ClassList::ClassID = Enrollment::ClassID
            Customers::CustomerID = Enrollment::CustomerID

            ClassList::ClassID and Customers::CustomerID should be defined as auto-entered serial numbers. The matching fields in Enrollment should be number fields.

            With this setup, a portal to Enrollment on your Customers layout can list all classes in which that customer is enrolled and can be used to change their enrollment. A portal to Enrollment on your ClassList table can be used in similar fashion to list all customers enrolled in that class and you can use it to enroll additional customers as well. You can also use layouts based on the enrollment table to list either all classes for which a given customer is enrolled or to list all customers enrolled in a given class.

            Note also that I am using ID numbers instead of names to link these records. Names are not unique and maybe changed after related records. Either situation causes severe problems for your database as changing a class or customer name will break any links to related records that are based on this name. Using serial numbers avoids that issue.

            Here's a demo file that matches up "Contracts" to "companies" in this same many to many relationship. You can examine it to see how this can be implemented in your database:  http://www.4shared.com/file/PLhjErzu/Contracts_to_Companies.html

            PS. What browser and Operating System were you using when your post got mushed into a single paragraph? I've seen several of these recently and want to pass this info on to Modman--the forum moderator so that he can investigate and complain to the Right Now Programmers if appropriate.

            • 3. Re: Relationship help
              AndrewDunn6760

              Thank you for the assistance I have set up the relationships as shown and now I can handle class registration. 

              In order to manage weekly attendance, I have created a join table between customers and class list which contains the fields Class List Id, Customer ID and class date so i can mark attendance from either a second portal on the class list table or from a portal on the customers table. Can you assist in how the relationship will work with the above relationship in place, as of course I cannot relate the same class list table that is on the layout?

              and the block text issue was because I pre-wrote the message using the notes app on the iphone and it didn't post across properly and i missed it before I pressed post.

              • 4. Re: Relationship help
                philmodjunk

                I thought it is was an iOS issue as that matches other posts, but it's nice to be sure. For future reference, you can click the edit link to modify a post just after you post it. My posts would be much more confusing/hard to read if I couldn't do that with my posts! Wink

                You'll need on table for managing enrollment and an additional table for logging attendance as you'll need at least a new record for each attending customer on each class meeting date.

                I'm assuming that you have FileMaker 11. If you do not, we'll have to modify the following to work with older versions:

                Define attendance with these fields:

                ClassID
                CustomerID
                ClassDate
                AttendanceStatus (optional)

                They'll all be number fields excpet ClassDate, which will be a date field.

                The relationship looks similar:

                ClassList::ClassID = Attendance::ClassID
                Customers::CustomerID = Attendance::CustomerID

                The difference here, is that you'll create a new record with a class meeting date for each attending customer for each date that they attend the class. You can either do that, or create a new record for every enrolled customer for every meeting date and use the AttendanceStatus field (formatted as a single value check box field) to mark students either present or absent. Either approach enables you to track customer attendance to your class sessions.

                You can use a script to create attendance records for every enrolled customer for every session, or for every enrolled customer for the current session, or you can just manually add records to a portal to Attendance to log each customer present for the current session. The method to use depends on your preferences and the size of each class. (The larger the class, the more useful it is to generate the attendance records by script in advance.)

                You can set up a FilteredPortal (requires FileMaker 11 with the relationship I've described here) to list attendance records for just the current date so that you can record attendance and the ClassDate field can auto-enter the current date or a date specified in a global field to log the date that the class session was held.

                • 5. Re: Relationship help
                  AndrewDunn6760

                  Hi, 

                  I have uploaded my relationship diagram below and tried to make it as clear as I could.

                   

                  Every time I am on the customers layout I get a 'related records could nto be created' message despite having the box for create new record ticked on the side of the enrollment table on all relationships and on the side of the attendance tables for all relationships?

                   

                  Regards

                   

                  Andrew

                  • 6. Re: Relationship help
                    philmodjunk

                    Presumably, you have a portal to Class Enrollment on your Customers layout.

                    Contact ID should be an auto-entered serial number and the CustomerID field in Class Enrollment should be a number field without any auto-enter settings.

                    Then, with "allow creation..." enabled for Class Enrollment in the customer to class enrollment relationship, you should be able to create new records in teh Class Enrollment layout.

                    If you are tying to put a portal to class attendance here, don't.

                    Put it on a layout that specifies Customers class attendance in Layout Setup | Show Records from--assuming that this is an occurrence of the Customers table.

                    • 7. Re: Relationship help
                      AndrewDunn6760

                      Hi

                      Thank you for your assistance, the information was very helpful and I have now got my relationships working correctly for both Enrollment and Attendance. Can you help me a little further. I am looking to be able to view a layout with all the customers who attended a meeting date and then add a new customer record to the same class and meet date.

                      Kind regards

                      Andrew