Linking fields from 1 table to other tables

[AZMark]

I am trying to create a database with with multiple tables, however I want all of the tables and fields to be linked to a single record. That is, Iam creating a database that will contain patient data. The first table in the database will contain patient demographics and I would like to have 3 of the fields from this table applied to all other tables. Is this possible?

Thanks

AZMark

[Ender]

Welcome AZMark!

It's not very clear what you are trying to do. Are you trying to access Patient data from other tables or do something else?

[AZMark]

Thanks for responding. I want the data that is entered from 3 fields in one table to be automaticaly entered into the same fields that I have in the other tables. The fields are as follows:

1. Record ID. This is a auto-enter serial number

2. Patient medical record number

3. Patient Name

I want to avoid having to type this data into the other tables (ie.. tables for intra-operative and post-operative data) after entering the data into the demographic table. Also I would like for the Record ID field ID (serial number) to stay the same on each table for the respective patient.

Any help is greatly appreciated.

AZMark

[Ender]

Got it. Though I would change the name of the Record ID field to Patient ID, as Record ID is often a generic term for the record ID of the current table.

If you haven't already done so, make sure each table that relates to Patient has a Patient ID field, then in the Table Occurance graph, draw a relationship between the Patient ID in the Patient table and the Patient ID in the other tables.

Depending on what your doing, you might use a portal on a layout in the Patient table to populate the related tables. Creating a record through a portal automatically fills in the key (Patient ID).

The other fields can be referenced directly through the relationship to Patient, or entered automatically into Text fields by using lookups. You can add a lookup to your field in the Auto Enter section of the field options. The lookup will be based on the Patient relationship; so the field will only update when the Patient ID is entered. This necessitates having some way to populate the Patient ID, either through the portal, by using a script, or manual entry.

[AZMark]

Thanks for the help I think I got it. I will give it a try.

AZMark

[Tripod]

Seems to me that there is no need to have the data from a field like NAME re-entered at all. That would be redundant. As long as you have the key and the relationship, the data are always available in the other file though the relationship. If you start duplicating the data from one table to the other, you are only going to run into problems updating all the various repeated instances of NAME (etc...) in multiple files. Use the relational capabilities though, and, for instance, if a patient name changes, you only have to update it in one file. This is how it's supposed to work, the data are not supposed to be repeated unless you want to store previous name, etc... Only the key field needs to be in both files.

Hope this helps,

--Tripod

[Ender]

I think in most cases (in FM7) referencing related fields is better; certainly for display purposes this works fine, and the speed of finds on these related fields is fast enough for most things. However, sorting is still slower with related fields than if the fields are in the same table. This is one reason to still use lookups.