8 Replies Latest reply on Sep 23, 2011 7:29 AM by mgores

    Auto Entering ID from one table to another related table



      Auto Entering ID from one table to another related table



      I've got student table where each student has an ID number.  Various evaluations are filled out on each student, and I've set up a different table for each evaluation, and then related the tables based on the student ID.  What I would like to do is when a new eval form is filled out that there is a script that pulls the student ID from the student table and automatically enters it into the new evaluation record.  Each attempt at building a script and using Set field or Get field isn't working.  I know there's got to be an easy way to do this.  Can anyone help?  Thanks!


        • 1. Re: Auto Entering ID from one table to another related table

          New Evaluation button on Student layout would be script similar to this:

          Set Variable [ $studentID ; Students::StudentID ]
          Freeze Window
          Go To Layout [ enrollment form based upon Enrollments ]
          New Record/Request
          Set Field [ Enrollments::StudentID ; $studentID ]
          Go To Field [ whatever field your want to place the cursor ]

          You can also use a portal from Enrollments and place it directly on the Student layout.  Then in your graph, turn on 'allow creation of related' on the Enrollments side (at the bottom).  Then you can just type into any Enrollment field (don't even place the Student ID in the portal).  When you type into any enrollment field to enroll the student, the StudentID is automatically populated into the Enrollments::StudentID field.  It's cool.

          • 2. Re: Auto Entering ID from one table to another related table

            Thanks so much LaRetta - That worked great!


            If I could do the portal, I would, but there are too many fields that have to be filled out on the evaluation (sometimes 60+)


            Here's the next dilema:  The same evaluation is done each term for three terms.  I've got a "Term" field that automatically enters based on the button and the script attached to it.  So, it fills in a 2, 3, or 4 depending on which button/script is run, and I've put that "Term" field from the evalution table/layout onto the Student Summary page (which is the Student table).  On that page, the field shows a "2" when I've created an evaluation record for Term 2.  However, the 3 and 4 don't show up when I create those evaluation forms.  So, I thought, okay - I need three separate fields (Term 2, Term 3, Term 4)  They are there on the evaluation table/layout....AND, get this...I created an eval record for Term 2 (it auto enters a "2" for that field).  Then, I created a new evaluation record for Term 3 (fills in a "3").  The script worked great, but on the first record I created (for Term 2) it filled in a "3" for the Term 3 field (so both Term 2 and Term 3 had data) as well as creating a new record with just Term 3 entered.

            When I went back to the Student Summary layout, only the Term 2 field showed any data, - not anything for Term 3.

            Other than each evaluation form showing the same student ID, I've made sure that the rest of the fields have unique data from each other.  Am I just confusing FMP, or am I the confused one?


            • 3. Re: Auto Entering ID from one table to another related table

              I think that issue is coming from the fact that the Term field is in the student table, I would not think that you would want a new student record created for each evaluation, just a new evaluation record.

              • 4. Re: Auto Entering ID from one table to another related table

                "The same evaluation is done each term for three terms."

                One student can have many evaluations.
                Each evaluation record represents a Term of that evaluation.  So you have:

                StudentID 1 Eval 1 Term 1
                StudentID 1 Eval 1 Term 2
                StudentID 2 Eval 2 Term 3 ... etc?

                And you should have them related as:  Students::StudentID = Evaluations::StudentID

                Everything in FileMaker is POV-oriented (point-of-view).  Your point of view is dependent upon where you are standing when you evaluate your surroundings.  Where you are standing is depending upon the table occurrence name assigned to the layout (see Layouts > Layout Setup > 'Show records from'

                So from PV of layout based upon Students, FileMaker can only see the * first Evaluation for that Student.  That is why you need a portal, to view the many side of the evaluations.  Insert > Portal (based upon evaluations). 

                * depending upon sort order specified by relationship or if unspecified, natural sort order of ascending on creation date/time.

                I'm not sure what is going on with the data within the Term field.  As Mark suggests, the Term field shouldn't be in the Student table unless you are trying to use it to filter the evaluations?  :^)

                • 5. Re: Auto Entering ID from one table to another related table

                  Maybe something like a 1 row portal showing all those fields and having copies of that portal on Tabs for each term.  You could set the portal one the first tab to show record 1, the second tab to show record 2 (being the second term), etc.  Then add a calculation field to the student table to count records in the Eval table to show whether there are 1, 2, 3 or 4 evaluation records for that student.

                  • 6. Re: Auto Entering ID from one table to another related table

                    Hi -


                    Thanks Mark and LaRetta.  I've had the relationships set up exactly how LaRetta explained, and the only reason why I have the Term field (from the Eval table) showing on the Student Summary layout (Student Table) is for a visual for the evaluators and cannot be entered or edited on the Student Summary layout.  The evaluators have a habit of entering a year's worth of data at a time, and sometimes the visual of "Evaluation 1:  Term 1 - completed, Term 2 (blank), Term 3 - completed"  sparks the "Oh - I didn't get the Term 2 evaluation entered.  what did I do with that?"  The Term field gets filled in when the record is created, so I was hoping that when the evaluators or data manager (me) looks at a Student's Summary page, all the "Term" fields are filled in at the end of the school  year.  If it's not all filled in, the evaluators know what they still need to do.

                    Each student has 5 different evaluation forms (so set up as 5 different tables, each related through Student ID).  Depending on the Evaluation, there could be multiple records of that evaluation because sometimes they are evaluated each term.

                    Evals 1 & 2 are given 3 times (three records of each in each table)

                    Eval 3 is given twice

                    Evals 4 & 5 are given once.

                    So if we've got 10 students, there would be 10 records in the Student Table.  30 records in each Eval 1 and Eval 2 tables, 20 records in Eval 3 table, and 10 records in Eval 4 & 5 tables.

                    I'm pretty sure the relationships are set up correctly.


                    Mark - I've thought about a 1 row portal.  That's going to be my next attempt.   :~)


                    Thanks for all of your input!  It is truly very helpful and appreciated.



                    • 7. Re: Auto Entering ID from one table to another related table

                      Having each Evaluation in a different table will make things more difficult for you.   You are already hitting your first issue where some records are in one table and some in another.  All evaluations should be in the same table which contains the StudentID with a field TermNumber.

                      Then to display completed evaluations, you can place a 5-row portal on the layout with label above which says "Evaluations Complete" and only have the Term number field.  You could instead create a calculation in Students called cTermsComplete with:

                      Substitute ( List ( Enrollments::TermNumber ) ; ¶ ; ", " )

                      Then on the student layout, it will list 1, 2, 4

                      • 8. Re: Auto Entering ID from one table to another related table

                        I can see having different tables for each of the evaluation forms, if the data for each is enough from each other.  On the student information layout you could have portals for each of the Evaluation tables showing just the Term or EvaluationDate fields, so that you had a visible reference of which forms had been completed. 

                        It would be easier if all of the evaluations could fit into one table, then you could just have one portal showing EvalType and Term to show which ones have been performed.