This should be very simple to do. A single show custom dialog can display the data. The mouse click on the field or a button in the portal row puts the focus on that portal row and as long as it keeps the focus, the show custom dialog can refer to fields i the portal's table and they will show data from the clicked portal row.
That leads me to wonder about this part of your description:
I'm using a portal which has many related fields
Does that portal row use fields from more than one table? Are the first and last name fields from the portal's table or a table related to it?
Hi Phil, thanks very much for your response.
In the database, I'm using this on a Users Layout which is showing records from the users table. On this layout, there are a few portals with different information, such as a Notes section which contains a note, and the user ID of the person who made the note. I'd like to be able to click the user ID field (or even an image next to it if that's easier - I'm not fussed) and have a custom dialogue come up with the search results. I was able to get this to work, but ONLY if the active record changed too (so, a find was performed), which isn't what I want. I'd like to be able to have record 1 open and click the field with user id 1234, and have the custom dialogue say "User 1234 is: <<firstname>> <<lastname>>".
Thanks for your help :)
So you have this relationship?
Users::__pkUserID = Notes::_fkUserID
You place a portal to Notes on your Users layout. You click the portal row and what should then appear in the custom dialog that opens? Data from Notes? From Users? Or ????
Yep the relationship is set up and the portal works properly. Lets use an example:
Inside the users layout, I'm on record 1, which could be for the person John Smith. In the portal, there are 2 notes:
Note ID Note Added By 1 This is the first test note for John Smith 12345 2 This is a second note 23456
Show Custom Dialog [ Users::Name ]
Will do what you ask. You can also define a tool tip to display Users::name so that just hovering the mouse over the field shows the name.
But this all seems too easy. Especially since a layout based on the Users table should already have the Users::Name on the layout (but outside of the portal) since all notes in the portal will be for the same user.
Hmm, just gave that a try and just as I thought it came up with the name of the record, not the name attached to the user ID. So I'll give an example of the record I'm currently on now, which is a user 28182 for a guy called Daniel. He has a note attached to him in the portal which has an added_by value of 26202. If I manually go to that user it is for a lady called Eileen. What I need is for the dialogue box to have Eileen's name when I click it, even though the active record is actually for Dan.
I tried your above by using a Script Trigger OnObjectEnter, as well as a button, and both showed the name of a current record rather than the user specified through the portal.
Then you don't have the relationship that I described. As near as I can tell from here, you have a single note field linked to two different records in the same user's table. That's assuming that "Daniel" and "Eileen" are both records in the Users table.
If I now have an accurate picture of the situation, you need these relationships:
Users::__pkUserID = Notes::_fkUserID1
Users 2::__pkUSerID = Notes::_fkUserID2
Then you can do any of the following:
Put the Users 2::Name field directly in the portal row to show the name of that second user.
Use Show Custom Dialog [Users 2::Name ]
Set up a Tool tip with Users 2::Name
Users 2, BTW would be a second table occurrence of Users, both would have the same data source table.
That's great, thanks for your response. It does solve the issue so I"ll mark it as the best answer, however I'd like to keep going with another question if that's okay.
I'm looking to use this as a script which I can reuse on every occurance of a user ID field in the database, where a user can click on a user ID field and it will show the name attached to it. Do you know the best way to do this without having to create another relationship for every occurance? My mind tells me I'd have to do a long winded solution (See below), however I'm really hoping there's a simplified alternative:
1. Save current record's User ID as a variable ($current_id)
2. Get the user ID from the field we've clicked, and save that as a second variable ($click_id)
3. Enter find mode, set field User_id to $click_id.
4. Set 2 more variables - $got_first_name and $got_last_name
5. Enter find mode again, set field user_id to $current_id (to return to the record we were on).
6. Display custom_dialogue with the firstname and lastname.
I haven't tried it (that's just me regurgitating as I think), but do you think this would work, and do you think there'd be an easier alternative?
Cheers! And thanks once again for your answers above :)
Sorry, but you need that relationship in order to correctly access the needed data. You could use ExecuteSQL, but then you are essentially defining the same relationship inside the calculation.
A scripted find could be made to work but you'll need to perform the find in a different window or on a different layout based on a different occurrence of the same table. Otherwise, the find will change the current record on your current layout.
The relationship--which is likely needed for more reasons than what you have determined so far anyway, makes more sense to me.
Would it not be possible to perform a SELECT SQL query? I'm very familiar with SQL, however I've never used it within filemaker before. If I saved the field value as a variable, could I not do ExecuteSQL("SELECT firstname,lastname FROM users WHERE user_id = $userid")? If so, how could I then go about displaying the found data?
ExecuteSQL() performs Select queries.
Thanks for all your help Phil, I've managed to get it working. The entire script is below.
Set Variable [$userid; Value: Get(ActiveFieldContents)]
if [$userid >0]
New Window [Name: “Temp”; Height:1; Width: 1; Top: 3000]
Go to Layout [“Users”]
Perform Find [Restore] – Find Records (users:user_id: [=$userid])
Set Variable [$fname; Value: users::firstname]
Set Variable [$lname; Value: users::surname]
Close Window [Name: “Temp”; Current File]
Select Window [Name: “Database”; Current File]
Show Custom Dialogue ["Found Data: User "&$userid; " & $fname &" "& $sname”]
Commit Records/Requests[No Dialogue]
Have you tried it on a windows machine when the windows are maximized? You may be unpleasantly surprised when your original window suddenly re-sizes. Of course, if you don't maximize windows or only do this on the Mac OS, that's not an issue.