Create related records with both fixed and variable fields

Hello all ;

It seems I got rusty in programming with FM after being lazy for few
years!

Your help for me is appreciated

I want to have a database for medical office where every patient will
have many fixed fields ( e.g. name, date of birth etc..) and many
fields that should be updated each visit ( new symptoms ,
medications , change of address etc. ) . Each patient will have a
unique number e.g Medical Record Number.

I have built the patients data, I want to add a table to the database
file so I can add visits by date , hence once I enter the date of the
visit ( in a portal ) I will be able to create a new related record
will the fixed fields already filled in and the other variable fields
are blank to be able to fill them with the details of the visit.

The portal is important to have , so I will be able to know the total
visits of the patients and dates.

I have tried few combinations of relationships but it seems not
working well as I expected.

Cheers

Mushabab Al-Murayeh, MD

In article
,
Mushabab wrote:

> Hello all ;
>
> It seems I got rusty in programming with FM after being lazy for few
> years!
>
> Your help for me is appreciated
>
> I want to have a database for medical office where every patient will
> have many fixed fields ( e.g. name, date of birth etc..) and many
> fields that should be updated each visit ( new symptoms ,
> medications , change of address etc. ) . Each patient will have a
> unique number e.g Medical Record Number.
>
> I have built the patients data, I want to add a table to the database
> file so I can add visits by date , hence once I enter the date of the
> visit ( in a portal ) I will be able to create a new related record
> will the fixed fields already filled in and the other variable fields
> are blank to be able to fill them with the details of the visit.
>
> The portal is important to have , so I will be able to know the total
> visits of the patients and dates.
>
> I have tried few combinations of relationships but it seems not
> working well as I expected.
>
> Cheers
>
> Mushabab Al-Murayeh, MD

You have two tables, one for patient data and one for Visit data. For
purposes of discussion, I will name the tables Patient and Visit.

First, each table should have a primary key field that has a value that
is automatically set when a record is created, is unique, always
present, and defined to prevent modification during data entry. It is
customary to use a number field, with the value set automatically as a
serial number. This primary key field is for use in relationships to
"child" tables.

Call these primary key fields
__kpPatientID in the Patient table, full field name
Patient::__kpPatientID
__kpVisitID in the Visit table, full field name Visit::__kpVisitID

It is good to put a double underscore at the front of the field name of
the primary key, so that when you sort fields by name, the primary key
field will be at the top of the list. It is also good to prefix the name
with kp, to indicate that it is the primary key for the table.

Second, the Visit table should have a number field to hold the value of
the primary key of the related Visit table. Call this field
_kfPatientID, full field name Visit::_kfPatientID

A single underscore at the front of the field name will make it sort
near the top, but below the primary key when viewing fields sorted by
name. The kf at the start of the field name indicates it is to hold a
"foreign" key in a relationship. This field should be set to prohibit
modification during data entry.

Now create a relationship between the two tables as follows:

Patient::__kpPatientID = Visit::_kfPatientID

Set the relationship to allow creation of a Visit record by way of the
relationship. This is a checkbox in the Relationship definition.

This relationship allows you to have many Visit records for one Patient,
but only one Patient for any Visit. It is called a "one-to-many"
relationship. It allows you to create Visit records via a portal in a
layout of the Patient table.

The layout for the Patient should be based on the Patient table. It
should have a portal to the related Visit table. The portal should be
defined to show records from the related Visit table. The fields in the
portal should be taken from the related Visit table, and should include
at least one field that you will enter data into. Because the
relationship is defined to allow creation of a Visit record via the
relationship, simply entering data into a field of the Visit table in
the portal will cause creation of a related Visit record. You would
probably want the Date field for the related Visit in the portal, plus
other fields as desired.

You can define the portal to have a convenient number of rows. If you
want a lot of fields in the portal, you may need to increase the width
of the portal or the depth of the row to accommodate all the fields. You
definitely need a pushbutton in the row to take you to the related Visit
record. You may want to have only a few fields in the portal, at least
one being the visit date, and then use the pushbutton to go to the
related Visit and finish filling out the Visit data in the Visit layout.

You can define the portal to have a scroll bar that will show if the
number of related records exceeds the number of portal rows.

You can define the portal to sort the Visit records based on a field of
the Visit table. You might want to have the Visit records in the portal
sort by date in descending order, so that the most recent Visit appears
in the top row.

You may want to have a delete pushbutton in the portal row, so that you
can delete a visit entered in error.

Other refinements are possible, but this should get you started.

--
Bill Collins
FileMaker 11 Certified Developer