Related table new record?

[ron G]

I am new to FM and am running FM 9 Advanced.

Here is the setup:

[color:red]3 tables:

[Patients] ______ [LINK] ______ Doctors]

pk_patientID ___ fkPatientID ___ pk_DocID

PatientName ______ fkDocID _______DocName

The LINK table is a linking many to many table between Patients and Doctors.

I am missing some fundamental understanding of how to bring up a patient record and then ADD doctors to that patient. (without using a portal)

If I put the patient name at the top of the layout and the doctor name below it, when I 'add new record' it adds a new patient AND doctor record.

Is the only way to do this to write a script that will call a new layout for the doctors? ugh

Thanks

Ron

[Ender]

Your structure is correct for allowing multiple doctors for each Patient (and multiple patients for each Doctor). To display those multiple related records (from either Patient or Doctor), use a portal of Link records.

To add existing doctors to a particular Patient, there are a couple options. One way is to use the "Allow creation of related records" option on the Link side of the Patient-Link relationship. That will give you a blank row at the bottom of the portal which you can use to add the Link record. Another method uses a script to grab the user-selected Doctor info from a globally stored field, along with the current Patient's ID, and switch to a Link layout to create a New Record, populating the Patient and Doctor foreign keys.

When I said "existing doctors" up there, I meant doctors that are already in the Doctor table. You can use a value list based on a field to show those in the selection field for the user. If the Doctor record doesn't already exist, you'd have to allow the user interface to create the Doctor before creating the Link record.

If I put the patient name at the top of the layout and the doctor name below it, when I 'add new record' it adds a new patient AND doctor record.

Is the only way to do this to write a script that will call a new layout for the doctors? ugh

I think your terms might be mixed up here. A New Record call will only create a new record in the layout's table (it doesn't matter what fields happen to be on that layout).

Also, there's no method of scripting a new "layout".

Keep it up Ron, you're getting there! :)

[Søren Dyhr]

I do by and large agree with Enders words, however if a subsummary report is less likely to be a demand can you avoid the jointable, unfortunately isn't it entirely scriptless I'm afraid, up and until fm8.5 could the task be done like:

http://www.fmforums.com/forum/showpost.php?post/263376/

Unfortunately, have the unequal relation type changed behviour, and a CF seems unavoidable - here have I chosen to use:

http://www.briandunning.com/cf/230

But take a look at the workings of the file, now the template deal with projects and asssigns staff, where you assign doctors to patients.

If you later wish to make some kind of subsummary, would I then perhaps consider to create records via export between tables, since the button to call up the subsummary is obvious ...I would make those jointable records only temporary, and delete them as soon the report hits paper/pdf.

--sd

DWLegacy9.zip

[Ender]

I would have to recommend against such a structure in this case (and in most cases). In addition to the reporting difficulties, there's a high likelihood of needing to remember additional information about the join at some point in the future, and the join table would be the best place to put that.

For example, someone's gonna come by and say, "Hey Ron, that's great to know who a patient's doctors are, but now we want to also know when they became their doctors. And while you're at it, how about keeping a history of when a doctor started and ended their relationship with the patient." Poor Ron would be doing all sorts of tricks trying to get a multikey structure to accommodate such details, or he might resort to multiple structures to keep track of it.

Frankly, I don't think it's useful to recommend such a technique to a novice in the first place. You're gonna put mixed up notions in his head about how to do a many-to-many relationship. Maybe you ought to throw in repeating fields while your at it.

Keep it simple! :)

[Søren Dyhr]

Maybe you ought to throw in repeating fields while your at it.

Nice one! Point taken!

started and ended their relationship with the patient

How is the joke?? Successfull operation in theater, but the patient died!

--sd

[Ender]

Maybe it's different in Denmark, but around here, we do have the ability to change doctors.

[Søren Dyhr]

It is different, since we have a publicly paid health system, and you have to change general practitioner on a specific day in November, by letting the city-council know you wish to in a timely manner.

However if you show up at a arbitrary chosen practioner, with a bullit hole after an attempt to relieve a bank for excess funds ...can't he deny to treat you!

Doctors can while you're assigned to them loose thier right to conduct their occupation ...due to say poor conduct.

--sd

[Ender]

However if you show up at a arbitrary chosen practioner, with a bullit hole after an attempt to relieve a bank for excess funds ...can't he deny to treat you!

I don't really know. But I'd say the database structure shouldn't be the one to deny me. If doctors can change, then they probably will at some point. You can choose a structure that accommodates such things, or you can live with the inconvenience of it not.

Doctors can while you're assigned to them loose thier right to conduct their occupation ...due to say poor conduct.

Or they simply move or change to a different clinic/hospital (they still allow people to move over there, yes?) Just more cases of needing a change of doctor.

[ron G]

I think I need to be more clear.

The problem is that Patient Ron has doctor Sweitzer. I can add doctor Sweitzer to a layout that has patient field showing Ron and a blank field into which I put Dr. Sweitzer.

BUT, if I want to add a 2nd doctor for Ron and select Add New Record, I get a new blank record for the patient and doctor's tables. What I want is just a new blank record in the doctor's table that shows the key field from Patients. (The fk_patientID would be added automatically since they are related and additions are allowed)

I don't want to add a new record in Patients that would duplicate the patient "Ron".

And, since in reality there are a *lot* of fields in the doctor table, [color:red]a portal is not practical.

(My background is in dBase IV so I tend to look a things from a programming perspective)

Thanks again.

[Ender]

Well, I tend to see things from a practical perspective, and a portal is usually the practical approach for one-to-many interfaces like this. There's no reason a portal needs to have all the fields from the related table. Just a couple will do to make it clear what the portal rows are about.

I'd suggest you reread my first post and give it another shot. If you're still having trouble, maybe you can say more precisely what the problem is.

[Søren Dyhr]

I get a new blank record for the patient and doctor's tables

Are we talking, about the "Allow Creation of Related..." feature when dealing with the relations drawn??

When trying to digest you specs, did it occure to me that you might be wish to use the cut up feature when dealing with portals?

--sd

[LaRetta]

I've read this many times and it feels like there is a small thing being missed. If Allow Creation is turned on (on the Patient side) to the Link table and the record is being created in the link table then a new patient will be created when you insert the PatientID. The whole problem might be perspective - which table is Ron standing in when the new record is being created?

Further, it seems Ron wants to see the doctor record after creating one (and that's why he doesn't want a portal to display the new doctor's information). If so, it is matter of switching to doctor layout to add the remaining (or all) of the doctor details.

Ron? You are working with two top Developers here but it might help if you either post your file or a simple demo showing your structure of this piece and what you are doing.

[ron G]

Good points and thank you. The problem is not in displaying records, it is in adding a new doctor to an additional patient. I tried basing my layout on Patients and I tried basing it on Doctors. Neither seemed to facilitate what I want. I guess I will next try basing it on Links (when what you try doesn't work, try what you think shouldn't work).

My Patient layout has tabs... one for Patient information, One for Doctor information, one for Prescriptions, one for Supplements etc... Maybe the crux of the problem is that I don't understand how to use tabs to add records....

Thanks guys...

[Søren Dyhr]

My Patient layout has tabs... one for Patient information, One for Doctor information, one for Prescriptions, one for Supplements etc... Maybe the crux of the problem is that I don't understand how to use tabs to add records....

To me does it seem like it should take place in the Patients layout, do you need to add several doctors to the patient, each having different functions?

Is the problem that you wish to enter the doctors in a rather flat'ish manner, but definitely need the entries to make up a many 2 many structure?

Perhaps my tennis-tournament template gives you a lead to what you wish to achieve??

http://www.fmforums.com/forum/attachment.php?attid/11586/

Please note that the portal can be cut up and each line can be relocated sans outline, the issue is that if you later take a look from the doctors tables point of view is the data fully relational. What this template doesn't provide elaborately is a logging of who have done what and when. Here should a audit-log on record basis facilitate it.

The flipping between tournaments, isn't really practical when it comes to patients - due to the vast number they are likely to come in, yet another portal are very likely to be, what changes the global field responsible for the tournament/patient in focus.

If this really is something, would I gladly implement the missing features and attach it to this thread??

--sd

[Fenton]

This is actually kind of a tricky problem. Ender has laid out the basics of how to add an existing Doctor to a Patient; in a portal on the Patient layout, in a Doctors tab (unless you want this info always visible; but you can put some of it always visible, and more of in a dedicated tab; you can put a field on a layout more than once (of course you'd only do that when there's tabs).

The problem is how to add a new Doctor, while keeping the "1-screen" interface; especially a problem if, as you say, there's a lot of data for a doctor. There are two questions you have to answer first.

1. Can there ever be 3 doctors? If so, then showing all the data from 3 doctors in a portal has got to be kind of an interface overload, and can/should not be done.

2. Is showing all the data from even 2 doctors already an overload? Personally I think it probably is. But I don't know how much data, or the size of your tabs.

Here's how I think I'd do it.

Have the doctors portal, in the "Doctors" tab, on the Patient layout. Have it 2 rows tall, but leave enough room for a 3rd row, in case; or just turn on the scroll bar in the portal. The relationship will be to the "join" table. You will only be able to add an existing doctor directly in the portal; but read on.

Have a button "New Doctor" above the portal. Its script will:

Set the Patient ID into a Variable.

Go to the Doctors table, create a new record, commit record, set its serial ID (you must have one) into a Variable.

Go to the join table, create a record, set both IDs from the variables, commit record

Go to the original layout, go to the "Doctors" tab (named layout object)

Go to the "doctors join::FirstName" field, Go to Portal Row [Last]

Now you've created both necessary records, with IDs, so the relationships are established. You can begin entering doctor data. At some point you'll run out of room for doctor fields.

Click a "View" button in the portal row.

Go To Related Record [ Doctors (thru the join table), New Window, Doctor data entry layout ]

Enter the rest of the Doctor data

Close Window

There would actually be a bit more steps to do with opening that Doctor window, first checking that it's not already open, opening the right size and location, using Loop/Pause to create a "modal" effect. This must be done correctly or you will either: 1. Have extra windows all over the place, or, 2. Get stuck in an endless loop.

I like the modal new window in these situations because it gives you the extra room you need, but preserves essentially the 1-screen feeling.

[Ender]

My Patient layout has tabs... one for Patient information, One for Doctor information, one for Prescriptions, one for Supplements etc... Maybe the crux of the problem is that I don't understand how to use tabs to add records....

It is important to remember that different tabs in a tab control are all based on the same layout, and a layout is based only on one table occurrence. So if you're basing the layout on Patient, all the tabs in that layout will be from the context of the Patient, and the Doctor fields will only show the first related Doctor's record. This is probably not desirable since you loose the point of having a many-to-many relationship.

There are methods of filtering of filtering the relationship to show a selected related record's fields. But I don't think this is what you've got, nor do I think this would be useful for doing data entry of your Doctor information.

I'd suggest you keep it simple and use separate layouts for your Doctor information. You can switch between them using buttons. The buttons can even be made to look like tab controls.

[Søren Dyhr]

Have different tabs different doctors entered??

--sd

[Ender]

Have different tabs different doctors entered??

Huh?

[Søren Dyhr]

Just examining why the portals is beeing rejected? I could imagine a system where differnt lines of the noble trade, were send to differen tabs if the patients case required it. Say a tab for physiotherapy and another for assigned as a resposible for the medication given. A dynamic structure like LDAP or apples addressbook, where some of the tabs perhaps have little or no use at all.

--sd

[Ender]

I think the fundamental issue here is Ron's understanding of how layouts (and their tabs) relate to their table occurrences. My comment was addressing this.

Unless you're gonna give up the many-to-many structure, it's best to build the join record after the two parents are created. The best way to populate the parents is using layouts based on their corresponding TOs.

Once that's done, then you can go back to populating the join record however you wish. A portal is still good for that.

[Fenton]

That's what I was saying also. Beginners often get thrown by the "portal of join table" concept, because they cannot see how to create that other parent record. But if you script creation of the other parent record, and the portal row record (join table), then it's just a matter of filling in fields. Because all you need is the IDs to create the records.

It also saves you from going to the Doctors table, creating a record, filling in the fields, then having to go back to Patient and choose that doctor again, which is OK, but a superfluous step.

Actually the method I've used is a slight variation of what I posted. I would pop-up a new window for the Doctor, on a layout dedicated only to this process. It would be modal, sized to leave the existing window somewhat visible. It would create the new doctor record. There would be a button to Add Doctor to Current Patient. Then it would create the join record with both IDs, and close the window. Same as the posted method basically, but in a slightly different order, with less steps.

But this method absolutely depends on controlling that Add Doctor window. If you want to be really sure, you can check the captured PatientID against the current one, and show a message if they are different. Though the modal window makes it almost impossible for them to wander around.

[Vaughan]

"I would pop-up a new window for the Doctor... It would be modal"

Ahhh, that's a real challenge (for me) making it really, truely modal. I've lost confidence in it.

On Windows, the window will un-maximise all the existing windows. In Mac OS X the user can use Expose to switch windows even if the script is paused.

[ron G]

Thanks for writing. Yes, I need to add multiple doctors (possibly) for each patient. For example, most people have a GP and if the patient is referred to a specialist then that doctor should be added.

I am *surprised* that FM doesn't have a 'portal like' mechanism to add records to the 'many side' of a relationship. Seems like there should be a button which would allow me to specify which table to add records.

Ultimately, it sounds like the idea of putting an input layout in a window is the way to go....

Edited September 4, 2007 by Guest

[ron G]

This is actually kind of a tricky problem. Ender has laid out the basics of how to add an existing Doctor to a Patient; in a portal on the Patient layout, in a Doctors tab (unless you want this info always visible; but you can put some of it always visible, and more of in a dedicated tab; you can put a field on a layout more than once (of course you'd only do that when there's tabs).

The problem is how to add a new Doctor, while keeping the "1-screen" interface; especially a problem if, as you say, there's a lot of data for a doctor. There are two questions you have to answer first.

1. Can there ever be 3 doctors? If so, then showing all the data from 3 doctors in a portal has got to be kind of an interface overload, and can/should not be done.

2. Is showing all the data from even 2 doctors already an overload? Personally I think it probably is. But I don't know how much data, or the size of your tabs.

Here's how I think I'd do it.

Have the doctors portal, in the "Doctors" tab, on the Patient layout. Have it 2 rows tall, but leave enough room for a 3rd row, in case; or just turn on the scroll bar in the portal. The relationship will be to the "join" table. You will only be able to add an existing doctor directly in the portal; but read on.

Have a button "New Doctor" above the portal. Its script will:

Set the Patient ID into a Variable.

Go to the Doctors table, create a new record, commit record, set its serial ID (you must have one) into a Variable.

Go to the join table, create a record, set both IDs from the variables, commit record

Go to the original layout, go to the "Doctors" tab (named layout object)

Go to the "doctors join::FirstName" field, Go to Portal Row [Last]

Now you've created both necessary records, with IDs, so the relationships are established. You can begin entering doctor data. At some point you'll run out of room for doctor fields.

Click a "View" button in the portal row.

Go To Related Record [ Doctors (thru the join table), New Window, Doctor data entry layout ]

Enter the rest of the Doctor data

Close Window

There would actually be a bit more steps to do with opening that Doctor window, first checking that it's not already open, opening the right size and location, using Loop/Pause to create a "modal" effect. This must be done correctly or you will either: 1. Have extra windows all over the place, or, 2. Get stuck in an endless loop.

I like the modal new window in these situations because it gives you the extra room you need, but preserves essentially the 1-screen feeling.

==============

===================================

Seems to me this is the way to go. But, A) It is hard to believe that I am the first person to run across this situation and : Filemaker, in the previous 8 iterations of the product hasn't addressed it with a [color:red]simple procedure. Anyway, thanks a lot guys. Your ideas are very educational!

[Ender]

Sorry man, I still don't think you get it. It's not a problem with FileMaker, it's a matter of building the interface to fit the process and the structure.

[Søren Dyhr]

Seems like there should be a button which would allow me to specify which table to add records

Then make one, here's the recipee:

http://web.mac.com/zueiv/iWeb/FileMaker/Script%20Killing%20Techniques/AFA363C6-2D85-4C6D-82E2-273F3722A69E.html

--sd

[Søren Dyhr]

I do here snip Mike's wise words...

It's not a problem with FileMaker, it's a matter of building the interface to fit the process and the structure

I've made you a tiny template as a sort of demonstation, but it contains a teaser as well!!!

One thing that isn't the first you notice is that the buttons found in the portal of the second tab, is not using the default way of dealing with the present running script, when the button is touched is the previous paused script aborted, while the button underneath just terminates the pause!

--sd

ClassicM2Mtwist.zip