13 Replies Latest reply on Mar 28, 2013 9:45 AM by philmodjunk

    Next Step for Conditional Value LIst

    Tusquittee

      Title

      Next Step for Conditional Value LIst

      Post

            

      After MUCH help from PhilModJunk I hope I’ve figured out how to deal with conditional value lists.  I have Academic Years, Sessions, and Classes.  Choosing a specific year only shows sessions for that year.  Choosing a specific session only shows classes for that session.

            

      I have a Registration Detail Layout from the Registration Table.  

            

      Selecting the Registration::Academic Year, lists ALL years via value list from the Academic Year Table.

            

      Selecting Registration::Session, lists the appropriate sessions via a value list from the Registrations to Registrations to Classes for Sessions Table but only shows related records from the Registrations to Registrations for Portal Table.

            

      Finally, within the portal, selecting Reg Data::Class Name gives me specific classes for that session in that particular year.  The value list selects from the Registration to Registration to Classes for Classes in Portal Table but only shows related records from the Registrations to Registrations for Portal Table.

            

      I’m having difficulties with the next step.  After selecting the appropriate class within the portal, I would like to have the fields for Instructor Name, Start Date, End Date, Room, etc... also display within the portal for that specific class.  The problem is, although I know it’s selecting a specific class, I can’t figure out the relationship between the other information I have recorded about that specific class (that’s listed in the specific Class Detail layout) and the value list I just chose from to make the conditional class list work.  I can’t get the correct instructors to show up.  I can get instructors for only the specified session.. I just can’t get the specific instructor... Should I do another conditional value list for the additional data I want to appear? Am I going about this in the right way? Is there another way to select specific classes and generate a transcript type summary?

            

      As an end result I’d like for the user to be able to select the year, session, and class.  Selecting the specific class would bring up information about that specific class.  Then user could go to 2nd line of portal and choose additional classes. (A single registration can only consist of classes within the same year and session.) Additionally, I’m trying to get the line item in the portal to act as a ledger of sorts... subtracting seats from that specific class but that’s probably an entirely different post.

            

           Thanks in advance...

            

            

      Registration_Detail_Layout.jpg

        • 1. Re: Next Step for Conditional Value LIst
          Tusquittee
          /files/0ad5e94ef1/relationship_graph.jpg 1043x490
          • 2. Re: Next Step for Conditional Value LIst
            philmodjunk
                 

            the other information I have recorded about that specific class (that’s listed in the specific Class Detail layout)

            On what table is that layout based where you record the instructor name, etc. for a given class?

                 Since your portal is based on Reg Data, there needs to be a table occurrence of the correct table, "Class"? linked by Class ID Match Field to Reg Data, then you can add the Instructor name and other fields from this newly added Tutorial: What are Table Occurrences? to your portal row to display this data.

            • 3. Re: Next Step for Conditional Value LIst
              Tusquittee

                   Thanks Phil.  The table with the additional information is indeed the Class Table.  I created another table occurence, Class2, and now have the Class ID Match Field linked with Class ID Match Field in Reg Data. This still doesn't work.

                   Just to double check...Shouldn't my Instructor Name Field be a value list with values coming from Class2 and only showing related from Reg Data?

                   Once I choose the class within the portal... I'd like for the correct instructors for that class to show up, as the only option, in the Reg Data::Instructor Name field.  

              • 4. Re: Next Step for Conditional Value LIst
                philmodjunk

                     If there is only one instructor for a specified class, there is no need for a conditional value list as you are not selecting the instructor, you are selecting the class, then the instructor for that class should appear automatically as should any othe fields from the class table.

                     I suspect that you need to set up the value list for selecting the class in the Reg Data portal differently. It should enter the class ID into the class ID match field. If that were happening here, what I am suggesting would work.

                • 5. Re: Next Step for Conditional Value LIst
                  Tusquittee

                       And to think I thought I had conditional value lists figured out! Smileywink...

                       You were absolutely correct.. I should've been using the Class ID Match Field... Got that fixed... Now, I'm off to figure out how to use that line item as a ledger to keep track of how many students are registered for that class...Along with dealing with wait counts, and overbooking.  Are there any helpful articles or example files out there for this problem? 

                       Thanks again Phil...

                  • 6. Re: Next Step for Conditional Value LIst
                    philmodjunk

                         On a layout based on Reg Data, you can use a List or Table view to list your registration records. You can perform a find or Use Go To Related Records to pull up a specific set of records such as all records for a given class.

                         You can also sort the records by class and look at the registration data from multiple classes in the same report. Sub Summary layout parts, if used in List View with the correct sort order, can serve as "Sub heads" in such a report with summary fields providing sub totals such as the total number enrolled in a given class.

                    • 7. Re: Next Step for Conditional Value LIst
                      Tusquittee

                           Well Phil, I spoke too soon.  I promise it was working.  I changed the Class Field to a Class ID Field and clicking on the class field in the portal and it brought up the instructor automatically.  Woohoo! Except I needed to add some additional fields so I deleted the portal and started a new one....and now, it's not working...So, here's what I've got.

                           At top of page there are two fields

                           Registrations::Academic Year Name (works fine selects all years from Classes::Academic Year Name)

                           Registrations::Session Name (works fine, selects all sessions from Reg.2Reg2Class4Sessions in Portal but only shows related records from Reg2Reg4Portal)

                           Then in the portal.....from Reg Data

                           Reg Data::Class ID (shows Class ID values from Reg 2 Reg 2 Classes 4 Classes in Portal only shows related records from Reg2Reg4Portal)

                           Reg Data::Instructor Name

                            

                           What am I still missing Phil?

                            

                      • 8. Re: Next Step for Conditional Value LIst
                        philmodjunk

                             Instructor name should not come from Reg Data. You should remove that field from your table and you should put the Instructor name from the Class Table occurrence that you linked to Reg Data.

                             The only reason you would have Instructor name defined in Reg Data is if you need that name to remain unchanged even if you go to the Class table and edit the instructor name--I don't think you want that here.

                        • 9. Re: Next Step for Conditional Value LIst
                          Tusquittee

                               Thanks.. That worked like a charm and now I'm working on the totals for each class..So far so good.

                               I appreciate all the help and don't worry... I'll need more soon.  Thanks for all you do to help us out...

                          • 10. Re: Next Step for Conditional Value LIst
                            philmodjunk
                                 

                                      I appreciate all the help and don't worry... I'll need more soon.

                                 Oh and that's such a load off my mind!!!! surprise

                            • 11. Re: Next Step for Conditional Value LIst
                              Tusquittee

                                    

                                   Turns out I probably shouldn't be left alone with filemaker! 

                                    

                              The conditional value list fields on the Registration Detail Layout are working great.  Choose the year, choose the session, then choose the class.  Works like a charm....

                                    

                              Then go to the Class Detail.  I have a portal there from Reg Data and I’ve set it to filter so that only the students for that Class show up.  I used the following calculation.

                                    

                              Reg Data::CLASS ID MATCH FIELD = Classes::CLASS ID MATCH FIELD

                                    

                              And again, this works beautifully.  All of my classes have the correct students in them...

                                    

                              I would like to now go to the Session Detail layout from the Sessions Table and have ALL of the students enrolled in that particular session show up.  So.. a portal from Reg. Data and this time I tell it to filter so that Registrations::Session Name = Sessions::Session Name.  (I use the Registrations table instead of Reg Data table because this is where the original selection comes from on the Registrations layout)

                                    

                              This DOES work... but, it lists students twice.. In other words, If a student is taking more than one class in a session, they will be listed twice in that filtered result.

                                    

                              I run into the exact same difficulty at the Academic Year Table layout.. Create the portal from Reg Data.. ask to filter the results.. and it does indeed list ALL students enrolled for classes in that year.. BUT, it lists them several times.  

                                    

                              Is there a different calculation I should be using? It makes sense that it's returning the result twice... but, How do I tell it to only include students once for each time they meet that criteria?

                              • 12. Re: Next Step for Conditional Value LIst
                                Tusquittee
                                /files/98f8658134/Relationship_Graph.jpg 1060x495
                                • 13. Re: Next Step for Conditional Value LIst
                                  philmodjunk

                                       I am assuming that you are using the portal to display data and that you are not editing fields inside the portal. If you are, you have a much more serious issue.

                                       To list records in a portal only once, your relationship match or your filter expression would need to only match to one of the multiple records for a given student. Obviously, your filter and relationships do not do that.

                                       Have you considered NOT using a portal to display this data? A summary report grouped by student could easily hide the duplicates by using a sub summary layout part with the body layout part removed. This is also a much better option for printing or saving as PDF than using a portal.

                                       Using a portal, you might set up a relationship to a table occurrence of Students linked to occurrences of any other tables that hold data that you need to see in the portal. Since the student only appears once in your portal, the duplicate entries won't appear.

                                       Finally, FileMaker 12 developers can set up a large format calculation field with an ExecuteSQL function call that produces a table like display of the data similar to a portal. In that SQL code, SELECT DISTINCT could be used to drop out duplicates.