Relating portal to portal?

[murray]

I have a database of clients and another of episodes of contact with each client. I have set up a portal in the clientsdb that shows basic details of each episode in each row. Each client has a unique ID (Christian Surname -> surn chr1, Christine Surname -> surn chr2 etc) and the episodeID is surn chr1-001, surn chr1-002, surn chr2-001 etc). The relationship for the current portal is via the client ID, and the episode number is displayed in the current portal.

I would like to have another portal in the clientdb that showed another set of information from the selected epidode when it is selected from the first portal. I would like only that episode's details displayed in the new portal and so would expect to use a single row portal, allowing neater formatting of that data.

This may be a basic idea but so far I haven't been able to get it done. Any help will be greatly appreciated, particularly if it is given in words of one syllable or less as I'm pretty green at this.

Cheers, Murray

[John Caballero]

Here's one way:

Make sure that each record in the related db has a unique ID (an auto-enter serialized # is great for this).

Create a global text field in your main file.

Create a relationship to the related file that uses the global field on the left side of the relationship and the ID field from the related file on the right side of the relationship.

Create a one-record portal in your main file that's based on the above relationship. You don't really need a portal since you're only looking at one record's worth of information - you could just as easily just place the related fields on your layout.

Create a button and place it in your portal (just like placing a related field). Clicking the button will call a script that grabs the ID of the related record and sets it in a global in the main file. This will cause the details of the selected portal row to appear.

As I said, that is one way. Stick around for alternatives.

[Kundinger]

Hi Marray,

Yes... you can do what you are requesting... the key to this is having a proper relationship. I have done this on just about all my client solutions since FMPv3.

You understand that your ClientDB has a relationship with your EpisodesDB... using the 'clientid' as the trigger in the ClientDB to show the related EpisodeDB records.

You need a trigger in the ClientDB to show only ONE EpisodeDB record (detail).

When you 'click' the specific EpisodeDB record in the portal display within the ClientDB... you need to launch a script that will 'setfield' in a new field the 'episodeid' as the trigger in ClientDB... then have a relationship based on this trigger to display the detailed info from the EpisodeDB.

I hope this is easy enough to understand... I'm not sure of your experience level. Let me know how it goes, or if I need to better explain it. I could create you a sample if need be.

Good Luck!

Bob Kundinger

[email protected]

[murray]

Thanks guys for your help on this - I will give it a go now.

John, I wanted to use a portal as I would like to play around with the visibility of the data. I have managed to use the technique in a self join relationship but what I would really like is for the data and labels in my second portal to only appear when the episode is selected from the first portal.

Now Bob I would REALLY like an example of that!

Cheers, I'll let you know how I get on.

Murray

[murray]

John I think I have this working however the values in the global field seem to get "stuck" and the same values stay in the other fields in the portal even when I am changing records between clients, at least until I click the button again anyway.

Is there a way that I can scrub the data so that the fields are blank when not needed, (ie on changing records) other than to use a button and script again? I tried using a Clear script step to clear the global field but this broke the script somehow.

Thanks for your help with this - it is much appreciated.

Cheers, Murray

[dbruggmann]

Attached you find a sample with two portals:

-Portal 1 showing all episodes of the actual client

-Portal 2 showing only details of the episode you chose in portal 1

The way it is set up, you can simply add a new episode by clicking on the last, empty row in portal 1. If you click on a row with an already existent episode portal 2 shows its details. But then the question is, how you want to fill in these details in first hand

Portal Sample.zip

[John Caballero]

Change the global text field to a regular text field. That will make your 2nd portal data "local" to the record you're on.

FYI - you can use the Set Field script step to clear out the value in a field. Use the null field value: "" for text, TextToNum("") for numbers, TextToDate("") for dates, etc.

[murray]

Thanks again guys.

dbruggmann those files look like they will sort this out for me. It is really so good to have examples to pull apart and try to understand.

There are so many functions that I have never used and I'm inspired to go through this database (which started in 1996 in FMP 2 or 3 and which I inherited off someone else) and tidy it up - field names, scripts etc. I still have a fear of "breaking" it so I'll go slow or my questions might start to have a hint of panic.

I think I'll be right, but my next step is to try to make the second portal invisible, with visibility triggered by a value in the "details" field of the episode record. I have successfully sorted out visibility in a self-join but not yet across the client-episode relationship.

Thanks again,

Murray

[dbruggmann]

Just make regular backups of all files (and zip them!)!

Extremly helpful for this kind of work I found the various guidelines or standards for naming fields, layouts, scripts etc. You find posts on this in the Development Standard Forum on this site.