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
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?
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.
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 ??
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...
Why do you not simply do the calculations in the same table as the data (Table 1)?
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.
Amendment. I will have access to all 3 table and the coordinator only have access to table 1 for data collection
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...
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.
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.