1 2 Previous Next 19 Replies Latest reply on Mar 31, 2016 8:52 PM by dsvail

    Easiest (and non-techie) way to relate two fields?

    LMac

      Hello:

       

      I am creating a data base that keeps track of students and the classes they are taking. The student's details are stored on a table (?) called, Contact List:

      Screen Shot 2016-03-30 at 3.19.06 PM.png

      Enrollment details are captured through a layout called Classes and stored on another table similar to the contact list (above):

      Enrollment.png

       

      Once a student registers, enrollment is just a series of dropdown boxes.

       

      Is there a simple way to auto-populate a student's number (the box to the right of Stefano Mathias) once the user chooses a name? I am not very versed in FMP and the relationship creation, and in fact, it's difficult for me, but I am happy to learn if someone has the patience to give a step-by-step. If there is an easier, step-by-step, I would appreciate it.

       

      Thank you.

       

      Lara

        • 1. Re: Easiest (and non-techie) way to relate two fields?
          dsvail

          Lara,   this is pretty easy using a value list if your tables/relationsips are setup properly.  It would be better to choose the Student ID first which auto populate the name field (I am assuming the Student Number is an auto.created serial number...but I question that looking at the table Contact List).   Can you show us the relationship graph and/ or send us a copy of the file?  Yes, let's make sure the relationships between tables is setup properly.

          • 2. Re: Easiest (and non-techie) way to relate two fields?
            macwombat

            HI Lara

             

            If I might make a suggestion that will make your life easier.  Have 3 tables.  Students.  Enrolments.  Classes.

             

            The Students table has 1 record for every student with their personal information etc.

            The Enrolments table keeps 1 record for each student for each class.  So if a student is enrolled in 5 classes there will be 5 records for that student - 1 for each class.

            The Classes table keeps 1 record for each class that is run.  So if you have a class called Citizenship that will have 1 record.

             

            Each of the 3 tables needs to have its own primary key field - ie. StudentID, EnrolmentID, ClassID.  These should be auto enter serial number fields that cannot be modified.

             

            The Enrolment table also needs foreign key fields for StudentID_kf and ClassID_kf - that way you can associate each enrolment record with 1 student and with 1 class.

             

            The layout for Enrolling students should be based on the Enrolment table and will have dropdown/pop up menu fields for StudentID_kf and ClassID_kf - these will allow you to select from the student list and the classes list to populate the enrolment record.

             

            I have attached a simple sample file for you showing this setup.

             

            HTH.  Chris

            • 3. Re: Easiest (and non-techie) way to relate two fields?
              macwombat

              I forgot to add that the relationship between your Students table and your Enrolments table is based on Student::StudentID = Enrolments::StudentID_kf.

               

              The relationship between your Enrolments table and your Classes table is based on Enrolments:ClassID_kf = Classes::ClassID

              • 4. Re: Easiest (and non-techie) way to relate two fields?
                LMac

                Hello again:

                 

                Thank you for your help on this. This database project is a favor to a nonprofit organization and while I love messing around with computers, I admit that I am not a computer tech., in any way (i.e. I wouldn't pay me to do anything on computers!), so I really appreciate your help.


                I have attached the file. I made everything menu driven to reduce user error, but nothing is password protected so you can see what I've done.


                I tried my hand at the relationship thing...but it did not work--nor does it really make sense when I look at it from the outside. That, frankly is why I was trying to get away from using that space.


                I welcome your thoughts and am grateful for your time.


                Lara

                • 5. Re: Easiest (and non-techie) way to relate two fields?
                  KevinRoach62

                  I think for most of us newbies that's hard to figure out is where to set the option or how to set the option for relationship type, for example one to many? I can't find how to set it anywhere! Lara and I both will need to set that so we can have one student or one customer with a relationship to many other entries.

                  • 6. Re: Easiest (and non-techie) way to relate two fields?
                    dsvail

                    Kevin,  why it is important to have a unique primary key field in all your tables ... in your table Customer it is the field "ID".  Taking your case as an good example ...we have an example of a one to many relationship where we have a customer who can have multiple Work Orders ... the "many" side of the relationship.  to relate the two tables we need to have (what is called a foreign key) in the WorkOrders table.  In your case it is called "CustomerID" .  To actually make the relationship (in the Relationships Tab of Manage Database)  you click on ID in Customers and drag over to CustomerID in WorkOrders.  You can view and edit that relationship by clicking on the square box on the line connecting the two tables ... you now have a one to many relationship.  attached is a screen grab hopefully better depicting  the "how to".

                    • 7. Re: Easiest (and non-techie) way to relate two fields?
                      erolst

                      KevinRoach62 wrote:

                      I think for most of us newbies that's hard to figure out is where to set the option or how to set the option for relationship type, for example one to many?

                       

                      Kevin –

                      there is no such option. What type a given relationship is depends on your implementation.

                       

                      e.g. if you give your LineItems table a foreign ID for Orders, but not vice versa, that is a sure sign that Orders --< LineItems is intended as a one-to-many, not the other way around.

                      • 8. Re: Easiest (and non-techie) way to relate two fields?
                        KevinRoach62

                        Sorry if I seem dense to this and don't me to high jack the thread, but maybe this will also help Lara.

                         

                        I can't seem to get the many relationships when I have exactly(other than naming) just like the picture example. Mine are all one to one instead of one to many.Relationships.jpg

                        • 9. Re: Easiest (and non-techie) way to relate two fields?
                          erolst

                          You need a CustomerID (a “foreign key”) in Workorders – exactly like you have a WorkorderID in Expenses. Consider

                           

                          Customer::customerID --< Workorders::customerID

                           

                          What you have done is relating the two tables by their respective primary keys which doesn't work. Relate the primary key of the “one” to the foreign key of the “many” table.

                           

                          So, your three tables should have the following key fields:

                           

                          Customers:

                          customerID (primary key, auto-enter, unique)

                           

                          Workorders:

                          workorderID (primary key, auto-enter, unique)

                          customerID (foreign key, same data type as Customers::customerID)


                          Expenses:

                          expenseID (primary key, auto-enter, unique)

                          workorderID (foreign key, same data type as Workorders::workorderID)

                           

                          So you can build

                           

                          Customers::customerID --< Workorders::customerID / Workorders::workorderID --< Expenses::workorderID

                          • 10. Re: Easiest (and non-techie) way to relate two fields?
                            KevinRoach62

                            Ok I'll set it up that way!

                             

                            Thanks,

                            • 11. Re: Easiest (and non-techie) way to relate two fields?
                              KevinRoach62

                              OK I think I set it up exactly as suggested. Here is pics and still no one to many relationshipsCustomer Relationship Settings 1.jpgcustomerID settings 2.jpgcustomerID auto enter tab 3.pngcustomerID validation tab 4.pngworkorders relationship 5.pngcustomerID workorders relationship 6.pngcustomerID workorders relationship auto enter 7.pngcustomerID workerkorders validation 8.jpgworkorders workorderID auto enter tab 9.jpgworkorders workorderID validation tab 10.jpg

                              • 12. Re: Easiest (and non-techie) way to relate two fields?
                                KevinRoach62

                                And more pics of workorders to expenses.expenses workorderID realtionships.jpgworkorders workorderID auto enter tab 9.jpgworkorders workorderID validation tab 10.jpgcustomerID to customerID.jpgworkorderID to workorderID.png

                                • 13. Re: Easiest (and non-techie) way to relate two fields?
                                  dsvail

                                  Kevin,  … only how you setup the field on creation and how you use it.  i.e.. when you are setting up a “Primary Key Field” you are setting it as a Number, Auto Create, Serial… and that number will be the record identifier …you will not let it ever change.  In the related table you will setup a “Foreign Key Field” as type… Number.…it’s value will be set when you create related records.  FM knows when you make the relationship between two tables by dragging a field that is setup as a serial value to another table that it is setting up a one to many relationship… you can see this represented on the relationship graph. experiment with changing the relationship between two tables with different fields … by clicking on the ‘square’ on the line between the tables on the graph and how it changes the line connecting the tables.

                                   

                                   

                                  Alex Mitchell

                                  Digital Systems

                                  (970) 904-6600

                                  • 14. Re: Easiest (and non-techie) way to relate two fields?
                                    dsvail

                                    this is wrong …. in WorkOrders the customerID field need to be of type Number …that’s it.  Turn off "Auto-enter Serial, Unique"

                                     

                                     

                                     

                                    Alex Mitchell

                                    Digital Systems

                                    (970) 904-6600

                                    1 2 Previous Next