13 Replies Latest reply on Oct 29, 2014 5:28 PM by PalmDBS

    populating fields from other tables

    khymer

      This is my first time using Filemaker Pro and I am stuck. I have been watching videos on Lynda.com numerous times and am clearly missing something. I am trying to create an equipment checkout system for my photography program at a college. I have three tables: Students, Equipment and LoanAgreement. I am trying to populate fields in the loan agreement from the other two tables but must not be setting things correctly in the Inspector or in my tables. I can get my first field (conactnamelast) to populate from the student table and the popup menu works but other fields are not populating. I want each student's info (name, address, A Number, class # etc) to appear on each loan agreement. I have attached 2 screen shots so yo can see my settings. Thank you for the help.

      Karen

       

       

      relationships.pngedit layout view.pngStudent Fields.png

        • 1. Re: populating fields from other tables
          erolst

          khymer wrote:

          I am clearly missing something […] I am trying to populate fields in the loan agreement from the other two tables.

          Karen

           

          Karen –

           

          what you're missing is one of the main benefits of a relational database: storing (and maintaining) data in a single place, then using it repeatedly in other places via relationships.

           

          This means: don't copy student or equipment data into the LoanAgreements table; all you copy from these tables are the primary keys that you use as foreign keys in LoanAgreements. When these keys are set, you use the relationships to display the related data from either/both related tables – simply by putting the related fields on the LoanAgreement layout.


          EDIT: And “how do I get the keys into the join table”, you may ask? Well, a value list is one way – and since you need to set keys, not names, you define the 1. field as Student_pk, and the 2. field as something human readable, e.g. a calculated full name field. Then check the option top display values from the 2. field only. This way you see the names in the list, but are actually inserting a key.

           

          Then format your foreign student_fk field in LoanAgreement with that value list, and select a name from the list to insert the student's key, and thus relate the LoanAgreement record to the selected student.

          • 2. Re: populating fields from other tables
            PalmDBS

            Two ways to do this.  One, as erolst stated, is to simply view the data in the related table, not to copy the data.  This is more likely what you are wanting to do in reagrds to contacts.

             

            If, however, your loan agreement needs to have a snapshot of the info - to keep the contact name, address, equipment price, etc from the time the loan agreement regardless of what it may change to in the future - you can use lookups.  An instance where this makes sense is in an invoice that has line items with product, quantity, unit price, etc).  The product price may change over time, but the invoice needs to have the product price AT THAT TIME.  So the line item "unit price" field would do a lookup (linked by product ID) from the Products table to grab the unit price.

             

            I'm not sure if this is the intent in this particular business case for the contact, but it might very well be what is needed if there is a price associated with the equipment that could change over time.  If you need the data AT THE TIME THE CONTRACT IS ENTERED, then go into your field definition for each field (ie Loan Agreement::ContactNameFirst) and set it up with Auto-Enter Loookup with the "calculation" value set as the related field (Students::ContactNameFirst).

             

            Mike

            • 3. Re: populating fields from other tables
              khymer

              Thank you for such a fast reply!  I don't beleive I am copying student or equpment data into the loanagreement table.  I did creat new field in Loanagreement layout and named it to match the fields in Student and Equipment tables but I did not enter data.  I thought I did set up parent keys and foreign keys.  I can get the _fkstudent on the loan agreement to populate (and show the drop dwon list of all names) with the last names from student table but other info does not also show - how do I get the other fields on my student table to also show on loan agreement? 

              attaching copy of my loan agreement fields for your reference.loan agreement fields.png

              • 4. Re: populating fields from other tables
                PalmDBS

                On your layout for your LoanAgreement, add the fields from the RELATED table, Students::ContactNameFirst.  You don't even need the field in the LoanAgreement table if you aren't intending to copy it.

                • 5. Re: populating fields from other tables
                  khymer

                  Thank you, Mike.  For now my need is  to view data from the parent tables (equipment and students) as Erolst indicated - just not sure I quite get how to do that.  I will wait for antoher reply from my last repsonse.  This stuff is hard for me, I am artist not data person but must complete this!

                  Karen

                  • 6. Re: populating fields from other tables
                    PalmDBS

                    Add a field on the LoanAgreement layout (or double click on your existing ContactFirstName field, but in the Specify Field window, choose Students from the top drop down, then your ContactFirstName field.  THis will display the related value.

                     

                    Screen Shot 2014-10-29 at 3.31.12 PM.png

                    • 7. Re: populating fields from other tables
                      erolst

                      khymer wrote:

                      I don't beleive I am copying student or equpment data into the loanagreement table.[…|  how do I get the other fields on my student table to also show on loan agreement?

                      By placing a field from the related Student table onto the LoanAgreement layout – i.e. add a new field to the layout, then in the dialog box Specify Field open the popup (that reads Current table: …), select the Students table, and then a field from Students.

                      khymer wrote:

                      I don't beleive I am copying student or equpment data into the loanagreement table.

                       

                      Obviously not – seeing the fields in LoanAgreements, there is no field to copy them into – but then you named this thread “populating fields from other tables”. How is one to interpret the term “populating“ (given that this is a common beginner's mistake).

                       

                      And consider what PalmDBS said about capturing a snapshot of data at a specific point in time.

                      • 8. Re: populating fields from other tables
                        khymer

                        Thank you.  I beleive I did this it still does not populate the field.  Here is my screen shot so you can see if I did it right.  1.png

                        • 9. Re: populating fields from other tables
                          PalmDBS

                          There might be a key issue then.  What are the values of LoanAgreement foreign key and of the Customer Primary key that you want linked?

                          • 10. Re: populating fields from other tables
                            khymer

                            First Name, Last name, phone number, address(city, state, street, zip) email, PimaAnumber, course name, CRN.  Is that what you are asking for?  Sorry I am new to the language/verbiage.

                            Karen

                            • 11. Re: populating fields from other tables
                              erolst

                              PalmDBS wrote:

                              What are the values of LoanAgreement foreign key and of the Customer Primary key that you want linked?n

                              khymer wrote:

                              Sorry I am new to the language/verbiage.

                               

                              Jargon? Slang? Lingo? Brogue?

                               

                              Anyways, a LoanAgreement record is related to a Student record if the Student foreign key field in LoanAgreements has the same value as the primary key field in Students – which are both fields that you have defined, as per your screenshots.

                               

                              Make a note of the value in the student foreign key in your LoanAgreement record, then check (“manually”) if there is a Student record that has that value as its primary key.

                               

                              And why don't you just post a (zipped) copy of your file (via the “Use advanced editor” button)?

                              • 12. Re: populating fields from other tables
                                khymer

                                Thank you again.  I am done for the day but will do as you suggest tomorrow.  I would be happy to post a zipped copy of the file - did nto know that was an option - what great help you are providing me!  I am now optomistic I may get this all working soon!

                                Karen

                                • 13. Re: populating fields from other tables
                                  PalmDBS

                                  By "Foreign Key" I mean the key of the record in the related (foreign) table.

                                   

                                  For examply, a Customer might have a Primary Key of 23 in the __pkStudents field in the Students table.

                                   

                                  A loan might have a primary ID of 4 in the LoanAgreement table.  When you select the related student, you would be storing the key in the loan table as a foreign key (it is foreign because it is a linked record) as _fkStudent.  So your relationship in the diagram is correct, so long as the value of _fkStudentID matches the value of the __pkStudentID field in the record you'd like to match.

                                   

                                  It looks like your initial screenshot of the valuelist is incorrect, though.  In the value list First field you would have select the __pkStudentID field, as that's the value you want stored.  Once you select that, check the box to the right that says "Also display values from second field" and choose a field that has the full name (you may need to create a new, calculated field called LastFirst - ContactNameLast & ", " & ContactNameFirst).  Then, below, check the box that says "Show values only from second field."

                                   

                                  This will allow your drop-down to show you a name like "Hymer, Karen" yes actually store the ID in your field.

                                   

                                  On the LoanAgreement layout, set up the _fkStudentID field to choose value from this value list.  Now you can choose a customer, and the customers primary ID will store in the _fkStudentID field and the records will relate.