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
<02b35ac6-49b8-46de-a984-778bb61c7e9b (AT) w21g2000vby (DOT) googlegroups.com>,
Mushabab <malmurayeh (AT) saudimedicine (DOT) net> wrote:

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

On 10/12/10 4:17 PM, Mushabab wrote:

I use a dedicated 'create new visit' button adjacent to the visits
portal, and never use an acrr rel to create related records. The rel is
Patients:atient_id to Visits:atient_id. Do not sort the rel
(slower), sort the portal by visit_date.

The script parses the patient_id from the parent patient record to a
variable ($_patient_id), then goes to a behind the scenes vistis layout
(bts_visits) to create a new visit record (the visit table primary key
is an auto enter (serial; V000001..., unique, not null, not user
modifiable), and the script hard codes the $_patient_id variable value
to the vist record, enters the (current) date, and whatever else
required, then returns to the patient form. The go and return is
invisible to the user.

A delete button can be provisioned, as Bill suggests, on the portal row.
This SHOULD be scripted, not just a simple delete assignment, so that
any checks or access/privilege controls can be implemented as required.

As for namimg conventions, I prefer to keep it simple and obvious
patient_id
visit_id

primary and foreign keys are obvious by the _id suffix, and the table
context.

Bill <bbcollins (AT) earthlink (DOT) net> wrote:

Remember you don't have to copy the permanent datas of the patient in
the Visit table. But you can show them in the Visit Layout, and choose
if you can or not modify thm from the Visit Layout.
Of course, a patient data modified from one Visit layout will be
modified in all the other Visits of the same patient.

So, you will have to be cautious with "semi permanent" data like
adressa, phone number, marital status, etc... depending of the use of
your layout.

For certain cases, automatic import from Patient table to Visit table
during the process of creating the visit can be a solution (it's a field
option). Another more complicated but more rigorous solution is
historicisation of patient datas and date-linked relations with Visit
table.

--
Philippe Manet
en fait, c'est manet avant @