5 Replies Latest reply on Mar 14, 2013 8:53 AM by philmodjunk

    Back to Basics - set pointer on file, read, write

    hoffmanjan@umkc.edu

      Title

      Back to Basics - set pointer on file, read, write

      Post

           Hi,

           I'm new to Advanced FM Pro 11.  I need to know the basics.  I understand about "find records" but what if I only need the data as input to another table based on a condition I set up in a script?

           1) To set a pointer to read equal to a value, must I always perform a find on that table using the value? I do not want to display the table data? 

           2) To read the first record , do I use the Go to Record/Request/Page[First]? And then where do I need to place the Go to Record/Request/Page[Next]?

           3) "Do Until" and "Do While" are accomplished how? 

           4) When using a Loop when does the read happen?  At the top or bottom of the loop?

           PURPOSE:

           I'm creating a record in a table only when one does not exist.  When creating the new record I use a set of data from a different table.  I do have a relationship between the 2 tables.  It is a 1 to Many relationship. I'm not displaying the table for the Many.  I am filling an array (repetitions field).

           Should I use the Go to Related Record?

           Thank you,

           Janh

            

        • 1. Re: Back to Basics - set pointer on file, read, write
          philmodjunk

               While performing a find and using Go To record... is an option. It's not the only option. A relationship can be used to access a specific record or group of records.

               You'll need to provide a more detailed example of what you are trying to accomplish before I can make a sufficiently detailed response.

               One comment:

               

                    I am filling an array (repetitions field).

               If that means you are filling in the repetitions of a repeating field, then it appears that you really have gone back in time. Repeating fields are what we used when FileMaker was a flat file system (no related tables) and needed to work with a set of similar data all associated with a single record in our file. In a relational database such as FileMaker, you almost always are better off NOT using a repeating field. A related table is a much more flexible way to work with such a set of data.

          • 2. Re: Back to Basics - set pointer on file, read, write
            hoffmanjan@umkc.edu

                 Hi,

                 Good to know.  I'm using Advanced FileMaker Pro 11.  I'm new to FileMaker.  I did not know that arrays should not be used in FileMaker.   

                 I have a Student Advising Plan table which lists the required courses, credit hours, grade, etc..  When a Student Advising Plan is created (for several different educational programs) by the advisor, I pull in from another related table the required couses listed by program.  My relationship is as follows:

            Student Database as the main, Educational Plan is - related by Student#, Program Code.  Next Student Advising Plan table is related to the Educational Plan by Student#, Program Code.  

                 When a Student Advising Plan record is created, I use the related Student Course List table,  which uses the Program Code for the relationship.  Once the newStudent Advising Plan record is created for a student, the values should not be changed.  But the educational program will indeed change with time and is maintained through another form.  

                 My array (repetition field) is set up in the Student Advising Plan because there are up to 20 courses (same size) listed dependant on the Program Code for a student.  

                 Is there another way to create an array?  Or are arrays too costly in resource?

                 Thank you,

                 JanH 

                  

            • 3. Re: Back to Basics - set pointer on file, read, write
              philmodjunk

                   It's not a case that arrays should not be used. But it is the case that most of the time, there's a better alternative, and this is clearly one of those cases.

                   From your text, this is what I am understanding to be your relationships:

                   Student Database ---?Educational Plan?----?Student Advising Plan>----Student Course List

                   Student Database::Student# = Educational Plan::Student# AND
                   Student Database::Program Code = Educational Plan::Program Code

                   Student Advising Plan::Student# = Educational Plan::Student# AND
                   Student Advising Plan::Program Code = Educational Plan::Program Code

                   Student Advising Plan::Program Code = Student Course List::Program Code

                   I have put ? instead of > or < in places where it is not clear whether you have a one to one, one to many or many to one relationship.

                   For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

                   It's not clear to me how an educational plan differs from a student advising plan. For this post, I will focus on the Student Advising plan and it's relationship to students and the Student Course list. I will pretend that the Educational Plan does not exist for now. I know you have a need for it, but am pretty sure that you need a different relationship for it than what you have currently.

                   Linking students to Courses is a many to many relationship. A student record can be linked to more than one Course in the course list and a given course in the course list can be linked to any number of different student records. This relationship can be implemented in Relational Databases such as FileMaker by using a join table and your Student Advising plan can serve as that table:

                   Student Database-----<Student Advising Plan>-----Student Course List

                   Student::__pkStudentID = Student Advising Plan::_fkStudentID
                   Student Course List::__pkProgramID = Student Advising Plan::_fkProgramID

                   The key to listing multiple courses in the student advisiing plan for one student is to create multiple records in the student advising plan table all with the same student ID, but each with a different course ID. This is frequently implemented by putting a portal to Student Advising Plan on your Student Database layout and including any needed fields from Student Course List inside the portal row. A drop down list formatted _fkCourseID field can be used to select courses from the Student Course List. It's also possible to create a script that takes information about the student such as a declared major or minor whiich then creates a standard set of records in the Student Advising Plan table linked to records in the Courses table to save you the time of selecting such courses one at at time.

                   And if it is necessary to create and keep multiple advising plans for a given student, this can be done by creating multiple groups of advising plan records. Either a portal filter or an additional match field in the relationship can be used so that only one such group of records appears in the portal at a time.

                   You may find this demo file on many to many relationships helpful: https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7

              • 4. Re: Back to Basics - set pointer on file, read, write
                hoffmanjan@umkc.edu

                     Thank you for your help.  No, I do not have a many to many relationship anywhere in my database.  

                     I do not like your portal display, behavior, storage.  I've spent over 5 years designing in BigMachines software and FileMaker Pro can do better (Python, Perl, Java, html).  I'm sure it is in your future design map to allow arrays of some kind to display in a better format.

                     Also, conditional hiding of a display field should also be a goal for FileMaker Pro's future versions.

                     Again, thank you for your time.  I am a one man shop and do not have in-house examples nor a reference text other than help.

                     JanH 

                      

                • 5. Re: Back to Basics - set pointer on file, read, write
                  philmodjunk
                       

                            Thank you for your help.  No, I do not have a many to many relationship anywhere in my database.

                       Yes you do. You are associating many courses with many students. The join table is the standard method for implementing such a relationship in any relational database.

                       

                             I'm sure it is in your future design map to allow arrays of some kind to display in a better format.

                       Since I am just a fellow user of this product, that is not the case as it is not MY future...

                       

                            I do not like your portal display, behavior, storage.

                       Portals are the simplest way to work with a set of many related records from the context of the parent record. It is pretty standard as other DB applications such as MS Access use similar interface constructs for the same purpose. They are not, however, the only way for working with a set of related records. But when you say "you do not like them/FileMaker can do better" without saying what specifically it is that you don't like or how you'd like it to do better, I can't really suggest alternatives that might answer your objections here.

                       

                             I am a one man shop and do not have in-house examples nor a reference text other than help.

                       You are of course totalyl free to make your own choices, but there are many reference materials avialable, even youTube videos that you can watch to broaden your understanding of database design and how to use FileMaker Pro.

                       BTW, "one man shops" tend to b be the rule rather than the exception when it comes to FileMaker Pro database development as far as I know. I'm sitting in my "one man shop" as I type this message out. wink