Answer:
When you set up a relationship, you specify two fields that join to
form the relationship. From this the relationship is available, and
can be treated as a one-to-one, one-to-many (like the link/linked
setup), and even a many-to-many.
Once the relationship is defined, and independent of the 'show from
other DB' setting, you can now duplicate the behavior of a
link/linked. Try this:
- Set up a relationship between two databases and when you go to the
edit record screen tap the field name of the relationship field. Just
as the link/linked field will then show all child records, so will the
relationship. You can then add a record, and you'll note that the
field that is part of the relationship will be automatically filled
with the value of the joined field from the other database. Again
this is akin to the behavior of the link/linked fields, and again,
this is not using the 'show from other DB' setting/feature at all.
Now add the 'show from other DB' feature into the mix- you can now
take a value from the other database in the join and show it in this
field. Why would you do this? Well, in a parent child patient/visits relationship, you
might set the child database to show the patient's name from the
parent, and in the parent you might set the 'show from other DB' in
the relationship to show the last visit date (ie, if sorted by date,
the last record's visit date). Thus the purpose of this parameter is
purely informational, and inconsequential to the actual relationship.
In this sense it's similar to the 'show value from' field of the link
field in functionality.
There are a few caveats to using Relationship fields in place of
Link/Linked fields:
- The fields you join in a Relationship field have to match types.
That is, you cannot currently match non-like field types. For
example, a Date field cannot be matched to an integer field, and a DB
Popup field cannot be matched to a Unique field. Most fields that
allow text entry can be paired up with eachother- for example, Text,
Note, and DB Popup field types can be paired up. Numeric field types
like Unique and Integer can be paired up, and Link/Linked
fields can also be paired up (allowing you to pull other fields from
an existing link/link relationship).
- Link fields automatically create a value that is unique for all
handhelds and users, so in a multiple user setting, you can be sure
that no one else will be creating a parent record that conflicts with
the parent record you created. With a relationship field, you
typically point the join to a field type that requires the user to
enter data, and thus there is the opportunity for the user to put in
data that is not truly unique. This could cause problems in that the
data for two different parent record can be shared, so care and
instruction must be given to the users of your application such that
they truly enter unique data when required!
- Both the Link and Linked fields show in the Edit Record screen with
the appearance of a button, and users can tap the button to follow the
link. In a relationship field on the Palm OS handhelds, the field name appears as simple bold
text, so it is not quite as evident that the user can tap it.
The solution here for this application is to design a form, and use
the "Follow Link/Linked/External" action, which works equally well
for relationships. We're also considering changing the behavior of
Relationship fields in the Edit Record screen of the Palm to match
that of the desktop and Pocket PC (ie, showing a button instead of
just a label).
- Speed issues: the Relationship field is a truly normalized field-
that means that the 'Show From Other DB' field is not stored in the
relationship field itself, but always looked up from the other
database, so the data is fresh and up to date. On slower handhelds
and even on desktops this can mean a delay is added for each lookup,
and when the databases are large, or the number of lookups are large,
this delay can really add up. The worst screen to feel the effects is
the List View, and the quickest way to alleviate this is to set the
Pixels shown of the relationship field to 0, where possible. This will
allow the list view to draw without having to look up the values from
the other database. In addition, if you set the 'Show From Other DB'
to "No Field", there is really no additional delay at all, so when
setting up a relationship carefully decide if you need to show
something in the relationship, or are just using this as a join for
the link/linked type behavior, as you can optimize the speed further.
We are in the process of optimizing the Relationship Field speed in
our code, so in future versions of HanDBase, the performance for these
field types may be far greater as well.
I've uploaded a file to the HanDBase Gallery:
http://www.ddhsoftware.com/gallery.html?show=number&record=1612
that demonstrates the use of such a setup for a Patient database. The
basic setup is this:
Table 'Patient Example':
Field 1: 'Patient Name': Text Field defining the name of the patient.
Field 2: 'MR #' (Medical Record #): Text Field defining the hospital's
medical record number
Field 3: 'Visits - Tap Me' - Relationship field joining the 'MR #'
field above with that of the Visits Example Database below. It is set
to show the last record from the 'Visit Date' field of the "Visits
Example" table defined below, which will be the last visit date since
the records are from oldest to newest in that database.
Table 'Visits Example':
Field 1: 'MR #': Text Field defining the hospital's
medical record number, and will be filled out automatically when you
follow the relationship field above to add Visits records.
Field 2: 'Patient Name': Relationship field joining the 'MR #' field
above, to that of the "Patient Example" table's 'MR #' field. It is
set to show the 'Patient Name' of the "Patient Example" database.
Field 3: 'Visit Date': Date field, the date of the patient's visit.
Field 4: 'Notes': Note field, a description of the symptoms and
treatment.
This was kept intentionally void of any other useful fields in this
application, just to keep the example simple and clear. Feel free to
modify this to suit your needs!
|
