1 2 Previous Next 15 Replies Latest reply on Feb 18, 2014 1:48 PM by Nedra305

    Performing a Background Search

    Nedra305

      Title

      Performing a Background Search

      Post


           Hi all,

           I was hoping someone would please be able to help me with an issue I'm facing. To put it simply, I'm trying to perform a find request, without actually entering find mode or changing the record. I'm using a portal which has many related fields, and one of them contains a unique user ID from the user table. I want to be able to click this user ID and have a Custom Dialogue Box which says "This user is: Firstname Lastname" (Where Firstname Lastname are obviously the related information).

           Currently, I've only been able to get this to work by allowing it to switch to the record, so it's really only showing information from the current record, which isn't useful. For example:

           If I'm on user 1000, and I look at the portal, the user ID field might be 1234. If I click this field, I want a dialogue box to appear saying "User 1234 is John Smith".

           I thank you in advance for your help.

        • 1. Re: Performing a Background Search
          philmodjunk

               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?

          • 2. Re: Performing a Background Search
            Nedra305

                 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 :)

            • 3. Re: Performing a Background Search
              philmodjunk

                   So you have this relationship?

                   Users------<Notes

                   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 ????

              • 4. Re: Performing a Background Search
                philmodjunk

                     For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

                • 5. Re: Performing a Background Search
                  Nedra305

                       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
                  When I click a value in the "::added_by" field, I want a custom dialogue box to appear which has the name of the person it relates to (so, if the field contains the ID 12345, I want it to find the name of user 12345 and display it in the dialogue box). However, I need this to occur WITHOUT changing the record which is open (so, it does a find to populate the dialogue box without moving from the current record.
                  • 6. Re: Performing a Background Search
                    philmodjunk

                         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.

                    • 7. Re: Performing a Background Search
                      Nedra305

                           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.

                      • 8. Re: Performing a Background Search
                        philmodjunk

                             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------<Notes>-------Users 2

                             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.

                        • 9. Re: Performing a Background Search
                          Nedra305

                               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 :)

                          • 10. Re: Performing a Background Search
                            philmodjunk

                                 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.

                            • 11. Re: Performing a Background Search
                              Nedra305

                                   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?

                                    

                                   Cheers

                              • 12. Re: Performing a Background Search
                                philmodjunk

                                     ExecuteSQL() performs Select queries.

                                • 13. Re: Performing a Background Search
                                  Nedra305

                                       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]

                                       

                                            end if

                                  • 14. Re: Performing a Background Search
                                    philmodjunk

                                         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.

                                    1 2 Previous Next