related records; can't figure it out

[dlazenby]

New user to FMP7; have used MS Access 2003 before; am trying to develop a medical type database and just setting it up.

For the sake of simplicity/learning the principle, I am trying to relate 2 tables/layouts.

Table 1 is patient main table (demographics, basic stuff); fields are:

patientID (unique number, serial autoenter; primary key)

patientName (text field)

Table 2 is patient visit data (new record each time patient is seen by MD); fields are:

PatientVisitID (unique number, serial autoenter; primary key);

PatientID (number field, not primary key; to connect to patient main table)

DateOfVisit (date)

complaint (text field; eg headache, etc)

2 corresponding layouts/forms;

What I want to do is enter my data in the patient main data layout/form, click a button (script) which opens the patient visit data form.

This is the important part: That layout/form (patient visit data) will automatically have the patient ID and name on it, for example the top line; I would then enter the other data (date of visit and complaint).

How do I do this?

I have tinkered with creating a third join table (fields = patientID, patientVisitID). I have tinkered with the portal function.

I need step by step instructions, please.

I purchased Jesse Feilers Teach Yourself FM7 in 24 hours (SAMS publishing) and even have been watching the corresponding video tutorial (VTC software training tutorials) but still am hung up on getting my patient visit data layout to automatically show the corresponding patientID and name.

Please advise.

Doug Lazenby

[CobaltSky]

Hello Doug,

There are a number of ways to do what you want, but one of the simplest is to construct your script (the one that runs when you click the button after entering the patient demographic data) to transfer the PatientID of the current patient to a global field (as a temporary holding place) then switch to the Patient Visit form layout, create a new record and set the patientID value from the global into the patientID field in the new record.

Then on your patient visit layout, if you have relevant fields from the patient details table showing at the top of the layout (sourced from the patient details table via the relationshjip between PatientDetails and PatientVisit), the corresponding patient's details will automatically appear there.

The script would look something like this:

Set Field [PatientDetails::gTempText; PatientDetails::PatientID]

Go to Layout [PatientVisitForm]

New Record/Request

Set Field [PatientVisit::PatientID; PatientDetails::gTempText]

It's short and sweet, and will get you there in one click...

[CoZiMan]

See if this sample works for you.

[CoZiMan]

Again . . . w/attachment.

HospitalVisit.fp7.zip

[dlazenby]

Reply to Ray,

Thanks for your reply. With your method do I need a join table? Do I need a portal?

Doug

[dlazenby]

Reply to CoZiman,

Thank you for that excellent example. That is almost perfect. Would it be possible to have the "View a Visit" list/portal on the patient visit form/layout rather than on the patient main layout.

I say this because my patient main layout will be a "jump off point" to get to multiple other similarly constructed layouts/scenarios.

I would have layouts (these are just a few) for:

1. prescriptions and all the times they had been prescribed (e.g. 5/10 prescription for A,B,C, 5/14 prescription for A again, D, E, etc.

2. hospital orders/office orders (e.g. 5/10, Admit to hospital, Diagnosis- appendicitis; 5/11 more orders- ambulate 3x day, remove foley catheter, etc)

3. consent forms (e.g. 5/10 consent for appendectomy: risks- A, B, C; benefits- A, B, C, etc; 5/12 consent for arteriogram: risks- D, E, F; benefits: D, E, F etc.)

With the "View a Visit" portal on the patient main form it will be cluttered in my instance. Rather, I would like to "jump/click" from the patient main layout with the button you created to the visit layout and there (on the patient visit layout) have the ability to scroll through all the patient visit records of that specific patient.

I would use this functionality to set up similar buttons to get from the main patient layout to the other related layouts (prescriptions, orders, consents, etc)

Again, I really appreciate your help.

Doug Lazenby

[CobaltSky]

dlazenby said:

...With your method do I need a join table? Do I need a portal?

Doug

Hi Doug,

No, the method I described would not require either a join table or a portal.

It would work with either if you required them for other reasons, however.

There should also be no problem in relation to your wish to have a portal of related patient visits on the patient visit form layout. This will require a self-join relationship between two occurrences of the PatientVisit table with a portal row button attached to the 'Go to Related Record' command.

[Oldsneekers]

Hi, Doug,

I am doing the same as you. Used FM5 db in office for years and now converting to FM7. If you wish to compare notes, etc, give me an eMail ([email protected]) Greg, W.

[dlazenby]

Thanks Ray I will give it a try.

Doug

[dlazenby]

Thanks Ray,

That worked as described.

Now if I want to get back to the patient main data layout for that same patient how would the script differ? Would line 3 be "go to related record" instead of "New Record/Request". I assume that I would have to also alter the other 3 lines of code to reflect that I was in the patient visit layout and going back to the patient main layout (modify the "set field", and add a global field for that table (patient visit data table).

Also, let's assume that patient already has had several visits (visit records) and I only want to view those records rather than create a new record; would the "go to related record" command line work.

Also, Could I just substitute that for the "New Record/Request" line and assuming there are no patient visit records it would create a new record. I assume there would have to be some type of If/then command line-- like if no records present (patient visit) then "perform script" --> "New Record/Request".

Thanks again very much for your patience/willingness to help.

Doug Lazenby

[CobaltSky]

Hi Doug,

You will be able to get back to the relevant patient record from the patient visit form using the 'Go to Related Record' command on its own - no need for the other steps (you'll be able to specify the layout you want to 'land' on as a parameter for the Go to Related Record command).

In order to make the script I mentioned earlier take the user to browse the existing vist records (if any) or create one if none exist, you could extend it to:

If [not IsEmpty(PatientVisits::PatientVisitID]

Go to Related Record [show only related records; From table: "PatientVisits"; Using Layout: "Patient Visit Form"]

Else

Set Field [PatientDetails::gTempText; PatientDetails::PatientID]

Go to Layout [PatientVisitForm]

New Record/Request

Set Field [PatientVisit::PatientID; PatientDetails::gTempText]

End If

If there are some patient visit records, the script will extract them into a 'found set' (that's what the 'show only related records' parameter does) and present them for browsing, without creating a new visit record, but if none are present a new one will be created using the procedure outlined previously.