11 Replies Latest reply on Jun 5, 2012 11:10 AM by philmodjunk

    help with table

    ChiehSuaiTan

      Title

      help with table

      Post

       HI,

      I am a newbie trying to create a database for my research work

      I have created 2 table for this purpose

      1. Table 1, which is my parent table, which contain all the basic information that I want to capture: e.g.

      ID, Urine output, Drug level and 100 other different fields

      2. Table 2, which is my calculation table, which contains all the calculations that I want to do with the basic information that is collected in table 1. eg

      ID, Urine output, Urine output score, Drug level , Drug level score, overall score and 50 other scoring system.

      how do I

      1. Auto-populate table 2 with values from table 1 for similar fields? e.g, table 1 urine output will be automatically entered into table 2 urine output?

      2. After I am done with the calculations in table 2, how to I get the results back in table 1?

      If it is too tedious, maybe I should combine the 2 table together as one. If so, how to I do it? I don't want to retype all the calculation/formula from table 2 into table 1

      Thanks!

        • 1. Re: help with table
          philmodjunk

          and 100 other different fields

          You might need a table of 100+ records instead of 100+ fields. I cannot tell you that with confidence as I have only a limited understanding of what you want to do here.

          The main qustion here is how will a record or records in one table match to a record or records in the other?

          There are two basic methods for what you describe and each has advantages/disadvantages. Both require a relationship linking the two tables:

          See this thread to learn the details: Auto Fill

          • 2. Re: help with table
            ChiehSuaiTan
            Thank you I meant I have created 2 database table to record data. Each records will have 100 fields to be fill up and 50 calculation fills. Hence I have spilt them into 2. Can I auto update database table 2 when I enter into records into database table 1?
            • 3. Re: help with table
              philmodjunk

              None of that changes my first post. If you have two tables then you'll need a relationship that links table 1 to table 2. The relationship you choose to design will control which record in table 1 links to a record in table 2.

              • 4. Re: help with table
                ChiehSuaiTan

                Thanks! Your first post is exactly what I want to do! I have set up the relationship between the two tables but when I type into one table, it doesn't automatically update into the second table...

                sorry but I am really a newbie in FM, is there a script that i need to set up to automate the process? if so, how to do go about it ??

                • 5. Re: help with table
                  philmodjunk

                  But what relationship did you define between your two tables? There are a number of ways to do this and the details matter.

                  What data are you entering in one table that needs to "automatically update into the second table"?

                  If you had this relationship:

                  Table1---<Table2   (---< means one to many)

                  Table1::PrimaryKey = Table2::ForeignKey

                  were PrimaryKey is an auto-entered serial number field and ForeignKey is a number field

                  Then Any fields defined in Table1 can be added to the layout for Table2 and the data in them will display automatically--as long as the value in ForeignKey in the current table in Table2 to the correct record in Table1. The reverse is also true, but only for the first related record in Table2. A Portal on Table1 can display multiple related records from Table2.

                  And all this is just a guess based on very little information about your database...

                  • 6. Re: help with table
                    Sorbsbuster

                    Why do you not simply do the calculations in the same table as the data (Table 1)?

                    • 7. Re: help with table
                      ChiehSuaiTan
                      Thanks for all your help. I guess i would have save myself all the trouble if I had started off as one table as pointed out by sorbsbuster.. As I am new to fm, I started off one table to collect all the results from a patient encounter. Table1 consist of unique id, blood pressure readings, blood test results such as blood count and toxin level. Being unsure if I will be successful, I created a second separate table in case I mess things up as I was still experimenting with the formula/calculation.  Table 2 consist of the similar field such as blood pressure readings, and blood test results. However, all calculation will be made in table 2 and the results will be used for data analysis. Originally, I planned for the coordinator only to have access to table 1 and only I will have access to both table 1 and 2 .. However, I want table 2 to be auto populated when my coordinator enter the data into table 1 during data collection.. I was also planning to create a table 3 at the end to assign unique Id to protect the identify of patients in the study such that it will autoassign an unique id based on the initial of the name  and Date of birth. Only myself will have access to table 3 and the coordinator will have only access to table 2 to collect data.
                      • 8. Re: help with table
                        ChiehSuaiTan
                        Amendment. I will have access to all 3 table and the coordinator only have access to table 1 for data collection
                        • 9. Re: help with table
                          philmodjunk

                          This seems a strange set of tables and relationships.

                          Use an auto-entered serial number to assign a PatientID to each patient.

                          I'd set up tables such as this:

                          Patients::PatientID = PatientVisits::PatientID

                          And, depending on the type of data collected, I might very well add a third table as:

                          PartientVisits::VisitID = PatientStats::visitID

                          For a new developer, the key detail is that a new record in PatientVisits will not automatically have a value in PatientVists::PatientID to link it to a Patient record unless you design the database to do this. A portal to PatientVisits on the Patient layout can be used to automatically create new related records with a matching ID number simply by entering data into a blank row of the portal. A drop down of PatientIDs and names can be used to select a Patient for new records created on the PatientVisits layout. These are the simplest two methods. Other methods can also be used.

                          The same situation will exist for a PatientVists to PatientStats relationship should you employ that third table.

                          For both, PatientVists and PatientStats layouts, fields from Patients can be added to display info about that patient or visit once a matching value is entered into the PatientID or VistiID field.

                          Note that the relationships I have defined here are to make possible a one to many relationship. One patient can vist (or be visited) many times. PatientStats can be collected many times for each visit...

                          • 10. Re: help with table
                            RickWhitelaw

                            If tables and relationships are set up properly there is no reason to store a piece of data , other than PK and FK, in more than one place. Also, unless all 100 of your fields are going to be used for every record, it makes more sense to have different tables for different tests, dates etc. For example, Table 1 might be Patients. Another table would record Blood pressure readings with fields like reading, date and FK. Data for this table could be entered on the Patients Table Layout as long as "Allow Creation of Records . . ." is checked. Another table could record cholesterol level etc. That way, if on a given date, a Patient only has one test, you're not storing a record with 100 fields, 99 of which are blank.

                            Oops, just saw Phil's post which covers much of what I've just said. (EDIT:)And the Patients Visit Table between Patients and "Tests" is a very important detail. My idea of seperate Test Records would be entered on a Layout of Patient Visits.

                             

                            RW.

                            • 11. Re: help with table
                              philmodjunk

                              A good "tag team" Rick as you put in a much better explanation than I as to why you might add more tables for recording test data.