9 Replies Latest reply on Jun 2, 2011 9:45 PM by dtvarnum

    FMPv11 trial, Relationships keys

    dtvarnum

      Title

      FMPv11 trial, Relationships keys

      Post

      Here is a sample of my two tables. I have done this before but seem to be drawing a blank. Should there be a primary key and a relationship key (ie. patients_ID identifies the patients uniquely, and another key in the same databalse that would go into the BP table?  

      Or is one key acting as the primary key and the foreign key? Please point me in the right direction. Thanks

      Screen_shot_2011-05-30_at_12.10.25_PM.png

        • 1. Re: FMPv11 trial, Relationships keys
          LaRetta_1

          A primary key should go in every table.  Your Blood_pressure table should be named Patients.  Then the PatientID would be its primary key (because it is named the same and there is one patient per record).  It should be FM-generated, auto-enter serial (see auto-enter options).

          Then BP_readings would have a primary key also (BP_ReadingsID) same as above, auto-enter serial.  In the BP_Readings table you would add the PatientID (number).  Some developers add a pk or fk to the beginning of the ID but I do not feel that is required.  After all, if a PatientID is the Patients table, it doesn't take a rocket scientist to know that it is the Patients' primary key.  When a PatientID appears in any other table, it is the foreign key.

          "Patients_ID identifies the patients uniquely, and another key in the same databalse that would go into the BP table?"

          There is one patient with many blood pressure readings so the PatientID would be in BP_Readings (know as 1:n or one patient with many readings).  To consider reverse example, let's assume that the BP_readings table were actually a Country table.  There would be many patients in one country so you would put the CountryID in the Patients table.  The 'many' side holds the key of the 'one' side.

          There are also instances where you have a join table (n:n or many-to-many).  A good example would be Invoices to Products.  There can be many products on one invoice and many invoices can hold one product.  So you use a join table (called LineItems) which holds BOTH foreign keys (an InvoiceID and a ProductID).  And you can also have a 1:1 style relationship.

          But again, in your example, you have one Patient with multiple BP readings (1:n) so the PatientID goes in the 'n' side or Readings.

          • 2. Re: FMPv11 trial, Relationships keys
            dtvarnum

            Wanted to post my results but can't seem to get png file posted. Looks like you can put a url of where my pix is located but I don't have a server to put it on, maybe google docs? 

            • 3. Re: FMPv11 trial, Relationships keys
              LaRetta_1

              You can use a free upload service to post your file or png.  Try 4shared.com.  It is simple to step through and upload files to it and it is free.  Sorry, and then it will give you a link.  Then come back here and post the link into a response post.

              • 4. Re: FMPv11 trial, Relationships keys
                dtvarnum

                Here are two links to the FMPv11 database I am working on. It is a database where I login blood pressure readings. Myself and my wife would use it. Please take a look at my relationships and at my Form- layout. I don't think I have any of these correct no so any help is appreciated. 

                 [URL=http://www.4shared.com/photo/l5IkqNiR/Screen_shot_2011-05-30_at_1054.html][IMG]http://dc403.4shared.com/img/l5IkqNiR/0.6005294373879256/Screen_shot_2011-05-30_at_1054.png[/IMG][/URL][URL=http://www.4shared.com/photo/j4ChWXht/Screen_shot_2011-05-30_at_7134.html][IMG]http://dc403.4shared.com/img/j4ChWXht/0.8625342717996225/Screen_shot_2011-05-30_at_7134.png[/IMG][/URL]

                • 5. Re: FMPv11 trial, Relationships keys
                  LaRetta_1

                  You are very close.  Now go to your graph and point your cursor at the PatientID in Patients and drag it over to the PatientID in BP_Pressure OR another way to join them would be to click the second icon from the left (on the bottom of the relational graph (the plus with the box).  In the panel which opens, select Patients on one side (and the PatientID) and select BP_Pressure on the other and also select the PatientID.  Click Add.  It will automatically assume you want the = between them and you do.

                  In this same relational panel, below are options for both of the tables.  On the BP_Pressure table, check the box 'allow creation of related records.'

                  Now on your Patients layout, remove the fields from BP_Pressure.  In layout mode, you can tell that they are not from the Patients table because they begin with ::

                  Select Insert > Portal to place a portal from BP_Pressure directly on your Patients layout.  Do not put the PatientID in this portal - it isn't necessary and you don't want to risk removing the value from the field.  Now you can add a BP record directly into this portal.

                  • 6. Re: FMPv11 trial, Relationships keys
                    dtvarnum

                    Thanks all, I am getting there and hopefully will have good news in a day or two. I will post my final database. Thanks again. 

                    dtv

                    • 7. Re: FMPv11 trial, Relationships keys
                      dtvarnum

                      Well, thanks everyone. I think I am getting really close. I have covered some major principles of relational database management. Everything was working last night and here is the result. However I can't enter new data in the data entry portal. I thought I could yesterday but can't this morning. Any ideas? I think I have the switches correct in the relational screen where I am allowed to add info to the database from either relation. Well, any tips are appreciated. I am going to try another screenshot too. Seems tough to do on this forum. 

                      Blood Pressure Database Screenshot

                      • 8. Re: FMPv11 trial, Relationships keys
                        dtvarnum

                        Well I removed the portal and replaced it with a new portal and now I can enter my data. I guess it was a snaffu or something but I have a working database now. I have to find out a bit more about how to move around this forum. It's not as intuitive as others but I hope after I read a few FAQ's I can be a champ here too. 

                        • 9. Re: FMPv11 trial, Relationships keys
                          dtvarnum

                          Here is the final product (so far). I have learned quite a bit. I think some of the tools are a bit unfriendly but once you know where to go it becomes easier. FMPv11 is powerful. I think the earlier versions (v9 and v10) may be a good way to go if you don't want to spend the farm on v11. The tab feature is nice and I will probably write something with it. Please check out the link below. It's a screenshot of the finished Blood Pressure database. Any critique, tips, suggestions are much needed and of course appreciated. 

                          https://docs.google.com/leaf?id=0ByUzo0WHjY_HN2M3NjYwYTQtMzJmMC00ZDU0LWE1OTUtZDkyNTVmMmQyNzE2&hl=en_US&authkey=CL3_wMQO