Hello everyone. One function of the database system I'm currently designing is a Notes section. One file, Clients, contains client information, while another, Notes, contains all related notes. Simple enough. Here's what I'm trying to accomplish:

I'd like to never see the Notes file. I've created a layout in Clients that contains a portal showing all notes via a relationship (Show_All_Notes). There's also a Note field that is related via a second relationship (Show_Current_Note). In the Notes database, there is a calculated field called Current_Note that combines the Client ID (from the match field) and the current Note ID. An invisible button covers the Notes portal row in the Client database. When the user clicks the row, a global field called Current_Note_Match is populated with the current Client ID and the Note ID of the row clicked. The Show_Current_Note relationship relates Current_Note_Match (global) in Clients with Current_Note (calculated) in Notes and the selected note appears in the text field below the portal.

So far so good...

Here's my problem. When the user moves to another record, the global still contains data. So, while he or she will see a different list of notes for the other client, the last note viewed will still appear in the Note field. Any ideas what to do here? Since globals can't be calculated (ugh!), I need to clear this value or find another way of making the relationship that causes it to break when switching to another client record. Any help would be much appreciated!

-Rob

Use a regular field instead of the global.

I was hoping to not have to add an extra calculated field, but I think you're right. I sure hope FileMaker 7 addresses some of these issues. Why the heck don't we have things like On Next Record events, etc??? Anyway, thanks John!

You won't need an extra calculated field - your current calculation will reference the regular text field instead of the global (and it's the regular text field that will get populated with the IDs when a user clicks a portal row).

?