Filtered Portal issue in multi relationship table

[Mountainoak]

I have created two tables joined by a join table. My goal is to have a filtered portal on the layout of the first table which displays a filtered list in a portal. I'd like to be able to choose one or more of the records that show in the portal and save them to the record i'm working in.

Concrete this means I have a patient who undergoes surgery to a specific region of the body. I'd like a filtered portal to show the types surgery dependent on the selected region.

My problem is that the portalfield remains empty.

I have read and tried almost all the example files on this forum and can't get it to work.

Would somebody please be so kind and patient with to help me solve this issue.

Thanks.

Portalissue.fp7.zip

[VICH]

you could do this using conditional value lists instead...might be alittle cleaner and will help with learning relationships also. you end up with two fields. the first would be the body location. the second types of surgery. the types of surgery change based on what part of the body you choose.

http://fmforums.com/forum/showpost.php?post/170316/

that post is what helped me learn to do it.

[Søren Dyhr]

Although you can perform wonders with the tunneling, should you see if this attachment get's you somewhere, the other side of the many to many have not been dealt with yet, since the data there seems a bit unclear! What is the mission the tool should pursue??

Why not make a list of the main and sub categories, on a sheet of paper, to prevent the redundancies that exists with you data, make the list a tree structure and be careful thing doesn't land in two branches ... or at two different levels.

--sd

PortalissueSD.zip

[Mountainoak]

Thanks for your answers.

I have changed the tables in accordance to the redundancy, hopefully the right way.

The ultimate goal is to select a discipline (so to say body region) in the patient layout. then have a portal display the filtered surgeries. so i can select one surgery out of the list and assign it to that patient.

I assume the surgery table should have a foreign key to the discipline field so that the surgeries are linked to a discipline, or am I wrong?

The reason I haven't used conditional valuelists is that the surgery list isn't static. Meaning I should be able to add a new surgery to the list if it isn't in there yet and this new surgery should show the next time in that list. I figured out that this isn't possible in a value list.

Portalissue-1.fp7.zip

[Fenton]

First, a "conditional" value list is the same as a "filtered" value list, AFAIK; just a different way to say the same thing. And it is never static, as it is using values from a field. So adding/removing a value is more of an interface problem, how to get to/leave that table easily.

This is one way to do your filtered value list. First you choose a Region (global field in Patient). This filters the Disciplines, via a filtered value list, for another global field. The discipline global field is used as the originating side of a filtered relationship to the Surgeries. Which produces a portal of surgery choices you can click to create a record for PatientSurgeries (a patient could have more than 1 surgery, so it's a portal).

An added enhancement would be to "dwindle" the remaining filtered surgeries to only those you have not chosen before. But what if a patient needed the same surgery twice?! So we'll ignore that for now.

Portalissue-fej.zip

[Søren Dyhr]

a "conditional" value list is the same as a "filtered" value list, AFAIK; just a different way to say the same thing

I was thinking the same, and if you (Mountainoak) look at your original template, does it attempt to use relational builds of the list in the second list.

It's however an interesting thought if there might be a way to avoid the globals somehow via tunneling ... I struggled for quite a while to see if you could accomplish more with the relations than I initially came to think of, when I started poking into your first template.

What astonished me was the use of only 2nd thru 6th portalrow in the layout, what was it I couldn't see? Next issue was the two checkboxes in the relational def. - deletion as well as "allow creation" were tagged in both directions. Was this a strike of utter cleverness or just a result of trial and error???

But after some further thoughts is beginning to make some sense, what you until now have ignored is that the RG isn't an ERD, could I urge you to read this:

http://www.digfm.org/ref/FM7_key_concepts.pdf

Take a look at Fenton's graph, he boldly arrange his graph in TOG's one for each layout. You need to ditch approaches learned from say Access or such.

--sd

[Fenton]

Yes, I don't that it's all that bold. But I felt that the "reference" tables, of what discipline goes with what region, etc., should be kept separate from the Patient data entry tables. They are not really related, hence should go in their own Table Occurence Group (TOG).

I sometimes name these kind of tables with a "_ref" suffix, to explicitly differenciate them from other data entry. Otherwise mixing them up seems a common cause of confusion (for me also).

[Søren Dyhr]

I don't that it's all that bold

If you were to expect a ERD would it be move you couldn't follow, hence the ironic "bold" ...

--sd

[Mountainoak]

Thanks for your answers.

This is indeed the thing I was trying to achieve.

I have read the article and it explained a lot but raised the complexity as well.

Fenton, I've tried to modify your much appreciated file, but still struggle with the fact that I can't add a new type of surgery to the pat-g_disc_surgery portal e.g. if you select the first region you 'll see two records ( CCE, Splenecomie) I 'd like to be able to add another type here by just writing in the portal.

The second thing is that the portal with patient surgeries if you select a surgery by mistake and you delete it the poral row will remain empty. Is there a way round this?

[Fenton]

To add another Surgery by typing there is possible. By checking [x] Allow creation of related records for that relationship, on the Surgery side. And making the field enterable. I had made the field a button, so it ran the same script as the "+" button. But you can use the "+" button instead.

As far as deleting a surgery if added mistakenly, there should be a button in the portal row to delete the portal row. PatientSurgery is a kind of join table (though only added to from the Patient side).

I also renamed the TOs and the TOGs more appropriately, so they sort properly, and so you can clearly see the difference. I named the "reference" TOG (table occurrence group) with a "ref_" prefix. It is kind of a funny TOG, because there is no real "anchor". Both Region or Category could be the anchor, but I don't even know what Category is. It's not critical that it has a single anchor anyway, in this case (IMHO).

Portalissue2-fej.fp7.zip

Edited January 1, 2008 by Guest

[Mountainoak]

Thanks Fenton this works really nice.

I'd like to ask you though why you use the globalstorage fields for selecting the discipline and region. Does this mean that there will be no storage of the region and discipline field input at the level of the individual patient. So that e.g. for a report later on this information will be "recreated" by the table relationships?

The second thing I'm wondering about is the following:

I need to generate 2 reports, one giving patient details per category and the other by region.

As the region and catgories can't be matched one to one I generated the discipline table which splits up some regions (e.g. stomatologie and oftalmologie). The reason for this is that I thought this respecs the redundancy rules and makes data entry easier as I only need to select a discpline for each patient and the relationships will automatically select the region and category.

I have tried to make this work based on your file but have a problem with the scripts.

Would you be so kind to have another final look?

Thanks

Portalissue2-fej.fp7.zip

[Fenton]

No, you do not store any of this "surgery" data at the "patient" level. Because it is not really "patient" data, it is "patient-surgery" data. Otherwise a patient could have 1 and only 1 surgery, ever.

As long as you do not violate the principles of logic, you can store "meta-data", such as Discipline and Category, at a more granular level if you require it for reports. But it is redundant to some extent; because it is available via the Patient-Surgery ->Surgery ->Discipline ->Category relational line.

But, yes, if you want to report on large sets of patient data, summarizing by either Discipline or Category, then you should create their IDs in Patient-Surgery, and look them up via the Surgery ID relational line.

If you add their respective parent TOs to the Relationship Graph, then you will also be able to reference their names, to show on reports (or sort by, but that would be slower than sorting by ID). I kind of overdid it, but this way you can summarize (in Patient-Surgery) by Discipline, Category or Region.

Portalissue3-fej.fp7.zip