11 Replies Latest reply on Aug 30, 2013 2:36 PM by megorama

    Course database help

    megorama

      Title

      Course database help

      Post

           I am relatively new to FM, I am trying to create a course database that has COURSES - FACULTY - LOCATIONS, I succeeded in setting up the database but am stuck. I am trying to have the Course Description layout display only those FACULTY associated with the LOCATION that was selected. I figured out how to have all of the faculty listed but am stuck on how to have it relate to the LOCATION. Attached is my Relationship map. Seems there has to be a simpe answer but I am not finding it.

           FOR EXAMPLE: We have faculty in three locations - Paris, Madrid and London, if I add a course and select Paris, I would like only those faculty that teach in Paris to by in the pop-up menu. 

           Any help is greatly appreciated.

      Screen_Shot_2013-08-15_at_2.41.37_PM.jpg

        • 1. Re: Course database help
          philmodjunk

               You do not have a relationship that links faculty to locations only faculty to courses.

               You can duplicate your location Tutorial: What are Table Occurrences? and link it to your faculty table via a location ID field you add to the faculty table.

               Then you can get a relationship where you see all faculty for a specified location--either in a conditional value list or in a portal. You'll need two more occurrences of the location table. (I am assuming that a given faculty record need only be linked to one location. It get's even more complex if a given member of your faculty teaches in two or more locations.)

               To link a given faculty member ot a specific location:

               Faculty::_fkLocationID = Location|Faculty::__pkLocationID

               to get a list or portal of faculty after selecting a location in Courses::fkLocation:

               Faculty|LocationCourse::_fkLocationID = Location::__pkLocationID

               You'd refer to Faculty|LocationCourse to get a portal or conditional value list of faculty members on your Course layout.

               Faculty|LocationCourse is a second occurrence of the Faculty table and Location|Faculty is a second occurrence of the Location table.

          • 2. Re: Course database help
            megorama

                 Thank you very much for your help - really appreciate it. I am still having problems and any additional assistance would be amazing. Here is a screenshot for what I am working on in the Value List for the "Faculty" field on the Courses layout - as well as my relationship map. What am I doing wrong? 

            • 3. Re: Course database help
              philmodjunk

                   Your layout is based on Courses, so your relationships and value lists have to work from that context.

                   As shown in my previous post,

                   Faculty|LocationCourse::_fkLocationID = Location::__pkLocationID

                   Faculty|LocationCourse should link to LOCATION, not Location|faculty.

                   Then you can set up your value lits to list value for Faculty|LocationCourse, Include only related values, starting from Courses.

                   THe only occurrence you would link to Faculty is Location|Faculty so that you can link each faculty member to their location. This occurrence enables you to display additional info about each location at the time you assign a location to a faculty member.

              • 4. Re: Course database help
                megorama

                     Thank you again, especially with your patience

                     I have succeeded but obviously did something wrong - when I am on the Faculty layout and designate a Location (Faculty::fkLocationID = "LOCATIONfaculty::_pkLocationID" and "LOCATIONfaculty::LocationCity") for a Faculty, then go to the Course layout and choose a Location (Course::fkLocationID = "Location::_pkLocationID" and "Location::LocationCity") - first a number pops up in the Faculty menu, then I click on the menu and see the related Location Faculty, choose a faculty, then go to the Faculty Layout to look at that assigned faculty and their Location is now a number (not the city name), I switch that to the City Name again and back on the Course layout  the faculty name is a number again. I'm guessing this still has to do with my Relationships?  

                • 5. Re: Course database help
                  philmodjunk

                       Your relationships look correct. I sounds like you have LocationCity set up with the value list that enters the LocationID instead of the Faculty::__fkLocationID being set up with this value list. Thus, selecting a location enter the ID number into the LocationCity field instead of the _fkLocationID field.

                  • 6. Re: Course database help
                    megorama

                         Maybe you can help me again. After my success above relating the faculty to the loaction it broke a Portal I had created on may faculty layout. I want a portal to list all of the courses the faculty have assigned to them. It worked before the updates above but is not working now. I had the portal based on the Faculty table with the portal showing related records from the Course table, then the specify field is ::Course. Any advice on what I am doing wrong?

                    • 7. Re: Course database help
                      philmodjunk

                           From what I see, that should work. Specifying "Faculty" in Layout Setup | Show Records from and then selecting "Courses" in Portal Setup | Show Related Records From should result in a portal that lists every course to which the current record in Faculty is linked. That relationship has not changed from the original relationships and should continue to function as it did before.

                      • 8. Re: Course database help
                        megorama

                             OK, just figured something out - back to my Course Catalogue layout, I am finding that when I create a new course and designate a location my list of faculty from that location appear BUT when I choose a faculty for that course it changes ALL of the faculty for ALL of the courses assigned to that location. I have a feeling this is causing my portal problems as well. 

                        • 9. Re: Course database help
                          philmodjunk
                               

                                    BUT when I choose a faculty for that course it changes ALL of the faculty for ALL of the courses assigned to that location.

                               Clearly, something is not correctly set up. IF your layout is based on Courses, you would assign a faculty record to that course by selecting a faculty ID value in the courses::_fkCoursesFaculty field. That would affect just the one courses record and no other.

                          • 10. Re: Course database help
                            megorama

                                 the layout is based on Courses here is a screenshot of how I have it set up so that the faculty pop-up is dictated by the Location - I am unsure where to I went wrong but cearly it deosn't appear I have anyhting like what you describe - courses::_fkCoursesFaculty

                            • 11. Re: Course database help
                              megorama

                                   I think I figured it out - thank you so much!!