10 Replies Latest reply on Jan 29, 2013 10:52 AM by philmodjunk

    Help with Portal for a Registration Database

    Tusquittee

      Title

      Help with Portal for a Registration Database

      Post

            

      Mac OS X

      Filemaker Pro 12.. Newbie

      I'm creating a continuing ed database.  Academic Years have Sessions.  Sessions have Classes.  Those specific classes are from a body of courses that all students must eventually take.  Students have specific registrations that include these classes as well as invoicing information.  Registrations and Classes are joined by a Registration Data table that allows me to match the specific class ID with a specific registration ID.  

      I have a registration detail layout from the Registration Tbl which includes a portal with line items from the registration data.  Currently, the portal is setup so that Class Name is a drop down item that shows ALL Classes. I created a value list from field Classes::Class Name.  Understandably, that shows every single class, from every session, from every academic year.

      I would like for that portal to only show classes from a particular academic year.  However, from time to time the registrar will need to register students not in the current academic year.  I’m not certain that using a portal is the best way to register students, but is there a way to filter the results of that drop down list?

      Finally... It seems to me there should be a way to create a window that allows the user to select a specific academic year.  This would bring up session items.  User could select a session, which would bring up available classes... and then I imagine they would like to select multiple classes and then all of this information fill into multiple line items on that portal. Is this setup possible?

      Thanks in advance for any help or suggestions you might offer..

      screenshot.jpg

        • 1. Re: Help with Portal for a Registration Database
          philmodjunk

               What you are describing is called a conditional value list. You can set up a value list to only list values from the current academic year, and you can also set up a value list that lists values for a year selected in a second field. (Select the year, then see the classes for that year..).

               Forum Tutorial: Custom Value List?

               Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list

               Demo File: https://www.dropbox.com/s/j6qf0z9fnem3uxd/ConditionalValueListDemo.fp7

               Hierarchical Conditional Value lists: Conditional Value List Question

               Feel free to post follow up questions here if those links aren't enough to get your value lists working correctly.

          • 2. Re: Help with Portal for a Registration Database
            Tusquittee

                 Thank you for those resources but I'm still having difficulties.  I now have three occurances of the Classes Tbl... 

                 Registration::Ac. Yr.Name =Classes::Ac. Yr. Name

                 Registration::Session Name=Classes2::Session Name

                 Registration::Class Name=Classes3::Class Name...

                  

                 I continue to run into this error when I create my value lists...."this value list will not work because the field "Academic Year Name" cannot be indexed."...

                 The Academic Year Name is indexed because I have it set up as a calculation... that references a global field...$$Current_Academic Year_ID

                  

                 Perhaps I have the relationship set up wrong between the Academic Years, Sessions, and Classes? 

            • 3. Re: Help with Portal for a Registration Database
              philmodjunk

                   Is Ac. Yr. Name the exact same field as "Academic Year Name"? Details are critical here.

                   Unstored calculation fields cannot be indexed and this is why this doesn't work. Ac. Yr. Name in which table is defined as this unstored calculation field?

                   Why is it so defined?

                   This also affects relationships. A match field on the "many" side of a relationship must be an indexed field or the relationship will not work.

              • 4. Re: Help with Portal for a Registration Database
                Tusquittee

                     Yes..sorry, "Academic Year Name" is what I meant... that's the same throughout...

                     Looks like I need to back up then and get help with my relationships between the Academic Year, Sessions, and Classes Tables.

                     They aren't just lists of all possible sessions or all possible classes... Instead each session is linked to a specific year and each class is linked to a specific session.... 

                     Along with other fields... the Academic Year Table has these two fields defined as...

                     ACADEMIC YEAR ID MATCH FIELD= Number... Indexed, Auto-enter Serial

                     Academic Year Name=Text....Indexed, Auto-enter Calculation replaces existing value          

                                                                                              If ( IsEmpty ( Self ) ; "New Academic Year" ; Self )

                      

                Along with other fields...the Sessions Table has these four fields defined as...

                    ACADEMIC YEAR ID MATCH FIELD= Number Indexed, Auto-enter, Calculation replaces existing value

                                                                                                $$CURRENT_ACADEMIC_YEAR_ID

                          Academic Year Name=Calculation.....Unstored, evaluated from Sessions Table,

                                                                                =If ( IsEmpty ( ACADEMIC YEAR ID MATCH FIELD ) ; "No Academic Year" ; Academic Year::Academic Year Name )

                          SESSION ID MATCH FIELD= Number... Indexed, Auto-enter Serial

                          Session Name=Text...Indexed, Auto-enter Calculation replaces existing value

                                                                                                       If ( IsEmpty ( Self ) ; "New Session" ; Self )

                      

                     Along with other fields...the Classes Table has these six fields defined as...

                          ACADEMIC YEAR ID MATCH FIELD=Number Indexed, Auto-enter, Calculation replaces existing value

                                                                                               $$CURRENT_ACADEMIC_YEAR_ID

                          Academic Year Name=Calculation, Unstored, evaluated from Classes Table,

                                                                               =If ( IsEmpty ( ACADEMIC YEAR ID MATCH FIELD ) ; "No Academic Year" ; Academic Year::Academic Year Name )

                          SESSION ID MATCH FIELD=Number, Indexed, Auto-enter Calculation replaces existing value

                                                                                                           $$CURRENT_SESSION_ID

                          Session Name=Calculation, Unstored, Evaluated from Classes Table, 

                                                                                 If ( IsEmpty ( SESSION ID MATCH FIELD ) ; "No Session" ; Sessions::Session Name )

                          CLASS ID MATCH FIELD=Number...Indexed, Auto-enter Serial

                          Class Name=Text, Indexed, Auto-enter Calculation replaces existing value, =If ( IsEmpty ( Self ) ; "New Class" ; Self )

                      

                     I have a layout called Academic Year Details.  On this layout I have a button that adds a Session to that specific year.  Clicking the button opens the Session Detail layout where I enter information about each Session and have another button that adds a Class to that specific Session. Clicking the button to add a Class takes me to the Class Detail layout where I can enter information about that class...

                      

                     I set things up this way because I was following, or trying to follow, the example in one of the filemaker sample files.. I think perhaps invoices or event management?... 

                      

                      

                • 5. Re: Help with Portal for a Registration Database
                  Tusquittee
                  /files/8f448090f6/Class_Detail_Layout_with_edit_picture.jpg 1274x551
                  • 6. Re: Help with Portal for a Registration Database
                    philmodjunk

                    Academic Year Table has these two fields defined as...

                         ACADEMIC YEAR ID MATCH FIELD= Number... Indexed, Auto-enter Serial

                    The value from this field is what you should be using instead of academic Year Name to link records by Academic Year. This will be an indexed field so it will not have the issues you'll encounter trying to use a calculation field that matches by a calculation field that returns the Academic Year Name from this table and thus is an unstored calculation.

                    Unstored calcualtions cannot be used as a match field on the "many" side of a relationship nor can they be a sorted field in a value list.

                    • 7. Re: Help with Portal for a Registration Database
                      Tusquittee

                           Thanks again for your help... I think I've finally got the Academic Year, Sessions, and Classes related properly... I took out the calculations and now have the Academic Year table related to the Registration Data table through the ACADEMIC YEAR ID MATCH FIELD....

                           but it seems I'm back to the original question...

                           One of the examples you referenced had two tables, products and assets, and had category, manufacture, product ines, and model fields in each table... As I have more than one product...(I'm thinking that my specific classes are like the products)... I'm unsure which table to create multiple table occurances of but I think it should be the classes table.

                           So I've created the following table occurances and relationships.

                           Classes::Class Name=Registration Data::Class Name

                           Classes by Session::Session Name=Registration Data::Session Name

                           Classes by Year:: Academic Year Name=Registration Data::Academic Year Name

                           but the fields I set up don't work... I feel like I've tried every possible configuration and I'm stuck...

                      • 8. Re: Help with Portal for a Registration Database
                        philmodjunk

                             Isn't Academic Year Name an unstored field in Classes and Registration Data?

                             If so, that last relationship won't work.

                             And shouldn't the last two relationships match by Class AND by session (2nd relationship) or year (3rd Relationship)?

                        • 9. Re: Help with Portal for a Registration Database
                          Tusquittee

                               Thanks for your patience..Some days I wish there was a "I'm too dumb to use filemaker" button....smiley

                               1.  I changed the relationships as you suggested... so the first stayed the same, Classes::Class Name=Registration Data::Class Name,

                          then for table occurance two I have Classes 2::Class Name=Registration Data::Class Name AND Classes 2::Session Name=Registration Data::Session Name,

                          then for table occurance three I have Classes 3::Class Name=Registration Data::Class Name AND Classes 3::Academic Year Name=Registration Data::Academic Year Name

                          2.  I changed Classes::Academic Year Name to text, indexed, Auto-enter, Calculation replaces existing value with the calculation being If ( IsEmpty ( ACADEMIC YEAR ID MATCH FIELD ) ; "No Academic Year" ; Academic Year::Academic Year Name )

                               I'm still experiencing difficulties...

                                

                          • 10. Re: Help with Portal for a Registration Database
                            philmodjunk

                            2.  I changed Classes::Academic Year Name to text, indexed, Auto-enter, Calculation replaces existing value with the calculation being If ( IsEmpty ( ACADEMIC YEAR ID MATCH FIELD ) ; "No Academic Year" ; Academic Year::Academic Year Name )

                                 Auto-entered calculations that refer to data in other tables or records do not automatically update if data in that other record changes. I suggest using the ID number of the academic year, not the name field in your relationships. You may need to use a script or Replace Field Contents to update an ID field in your related tables before you can use this value in relationships.

                                 

                                      I'm still experiencing difficulties...

                                 Can't realy help you without a description of those difficulties.