10 Replies Latest reply on Aug 6, 2012 1:22 AM by SinanJabori

    How to create relationships



      How to create relationships


      Basically, I created a database for our patients with different layouts. Each Layout represents a category of things we want to capture. For example, Demographic data layout contains patients Name, Age, diagnosis....etc. the other layout is pain symptons and has tabs such as shoulder, arm ..... My problem is that I have imported some of those tabs from an excel sheet and added some of them manually on file maker. I want the doctor to filled out all the information in all the layouts for ONLY one patient. but now everything is discontected and I can't figure out to connect them all together. I have taken screen shots of my tables, fields and relationships look like. 


      Thank you for your help 


        • 1. Re: How to create relationships
          /files/744c144c1f/Screen_Shot_2012-08-01_at_3.26.48_PM.png 728x546
          • 2. Re: How to create relationships
            /files/d1c7f7224a/Screen_Shot_2012-08-01_at_3.27.04_PM.png 780x629
            • 3. Re: How to create relationships

              This looks very odd.

              You show 3 tables but two of them have 0 fields defined while the other has 201 fields--a very large number of fields to put in one table.

              It appears that all of your data exists in the same table, _cervical rib spreaks..., as the other two tables do not have any defined fields.

              Please note that in FileMaker you can create many different layouts and each can refer to records in the same table.

              • 4. Re: How to create relationships

                How can I have all the layouts connected to one patient or a record number? Each layout should have info about the same patient.

                • 5. Re: How to create relationships

                  It not that simple.

                  You should have multiple tables defined for this so that you can set up relationships between them.

                  One table should be created for patient demographics with a a serial number field to uniquely identify each patient. This is called the Primary key for that table. Social Security and other externally supplied numbers may look like a good choice to use as the primary key, but aren't as they are vulnerable to data entry errors and even duplicate values.

                  Other tables should then be created and linked to the patient table so that you can have many records in the related table all linked to the patient.

                  I can't tell you exactly what tables to create for this as it depends on the needs your database is intended to meet, but I can see that you currently do not have any such tables defined in your database. It looks, from the number of fields, like you have tried to put all the fields into a single table.

                  • 6. Re: How to create relationships

                    I'm not part of "you guys" I'm just a fellow user.

                    I don't know that you can solve this without redoing it. There's too much that I don't know about your data and the tasks you need to do with it to give you specifics.

                    I suggest further reading/tutorials on FileMaker and database design. Then sit down with a blank piece of paper and work out what tables you need, what data should be put in each and then we can help you figure out how to take the data from your spread sheet and move it into each of the different tables while maintaining the appropriate relationships between the tables.

                    Note: Filemaker may be easier than most applications to use, but good design of tables and relationships is not a simple operation for any relational database--yet is crucial to getting it to function accurately, quickly and with an interface that is both easy to use and which minimizes user errors.

                    • 7. Re: How to create relationships

                      Of course there is, but you have to figure out what tables you need and to which to copy the fields before you do so.

                      If you have FileMaker Advanced--pretty much a "must have" if you intend to do any serious development work with FileMaker, you can Open Manage | Databae | Fields and you can then select fields, choose copy, switch to a different table and choose paste. This moves the field definitions, but not the data that they contain. You'll need to use other methods to move the data. Import records, Repalce field Contents and scripts are all possibilities for that.

                      If you do not have FileMaker Advanced, you can't copy and paste fields, but you can use Import Records with the "new table" option to import a copy of the table (and you can import from the same file as the file into which you are importing). This imports the fields AND the all the records so it does that part for you, but now you have all the fields in the new table. Your next step would be to go into Manage | Database | Fields and delete the fields that you don't need/want in the new table and just keep the ones you want.

                      • 8. Re: How to create relationships

                        I'm afraid that you have missed the point.

                        To respond in detail at this point requires that I make some pretty wild guesses as to how you might need to use your database.

                        In one table, you define fields for patient demographics. You'd define fields such as:

                        __fkPatientID :an auto-entered serial number
                        First name
                        Last name
                        And so forth.

                        In a second table, you might log the symptoms a patient reports with fields such as:

                        _fkPatientID : a number field
                        IntensityScale (1...10)

                        Because a patient can have multiple symptoms and may visit the doctor multiple times, you might have any number of records in the second table that are linked to the first table by PatientID.

                        In Manage | database | relationships, you'd link the two tables by dragging from __pkPatientID to _fkPatientID to link them in a relationship.

                        That's the basic structure to a relational database such as FileMaker. You likely will end up with many such tables linked in a variety of relationships like the above in order to structure this in a way that gets the job done in an efficient, user friendly fashion.

                        The next part of the task is to design layouts so that they can use these relationships for recording data and keeping data linked to the correct patient.

                        • 9. Re: How to create relationships

                          Click the Fields tab in Manage | Database

                          Select the table in the drop down.

                          Enter a field name and click create.

                          Click options or double click the new field definition to open field options.

                          Click the check box for auto-entering a serial number

                          When you drag from __pkPatientID to _fkPatientID on the relationships tab you link the two tables in a relationship using these two fields as the "match fields". If a record in one table has the same value in PatientID as a record in the other table, they are linked in a relationship.

                          It sounds like you need to spend some time reading up on FileMaker or going through some tutorials to get the basic concepts down. I'll be glad to help here, but a forum like this is best suited for answering specific questions where the tutorials and other training materials will provide a more broadbased foundation to your understanding of FileMaker and relational databases.

                          • 10. Re: How to create relationships

                            Is your application supported by Windows XP or 7? Also, could I link all the computers, that will be used to fill out forms from, to one server. In other words, could we save all the records into one online server instead of having to export the records from each computer.


                            Thank you for all your help.