7 Replies Latest reply on Jun 4, 2011 1:38 PM by disabled_JackRodgers

    Find Matching Records in 2 Tables



      Find Matching Records in 2 Tables


      I have 2 tables one of all users and another with active users.  The table of all users is larger than the active users table and has many more fields (email, first name, last name, address, city, state, etc.) the smaller table of active users has just email, firstname & lastname fields.

      What I want to do is find the records in the all users table (which has the detail info) based in the users in the active users table.  I've linked the email field in the two tables since that should be unique, so what I basically want to do is show the detailed user informaiton from all users if they exist in the table active users.  I guess it would be something like a query that would look at each row in active users and grab the email field, then if that address is in all users it would display the record from all users.

      Any help on how to do this would be greatly appreciated!


        • 1. Re: Find Matching Records in 2 Tables

          What's the purpose of the Active Users table?

          How, and under what circumstances, do you add a record for an Active User?

          Off hand, I can't see a reason for having both tables. You can just add a status field to AllUsers, ActiveStatus.

          If a user is active, set ActiveStatus to 1. If a user becomes inactive, clear the field (set it to "" ).

          • 2. Re: Find Matching Records in 2 Tables

            in a layout of active users you can place the related fields All users::fieldname directly on your layout to display the related information.


            • 3. Re: Find Matching Records in 2 Tables

              Phil in some large databases or in an application where processes loop through "active users" doing the extra find or processing could really hurt performance. Especially if the active subset is significantly smaller than the whole.

              In general you are right. This is a case where Id really like  to have a constant in my relationships.


              • 4. Re: Find Matching Records in 2 Tables

                You'll note that I asked this as a question.

                It would take an extremely large table of users (1,000's) before a search of the table would produce a noticeable delay and I don't imagine any "looping" would be required. A simple find to match the Get ( AccountName ) or Get ( UserName ) should suffice.

                • 5. Re: Find Matching Records in 2 Tables

                  Though I agree with you, for looping I was refering to other uses of the table within the application where you would loop through the found set and do something.

                  I was just pointing out the rational that might lead someone to do that. Performance being the only real reason I can see (and even thats a bit of a stretch)  Ill be quite now :)


                  • 6. Re: Find Matching Records in 2 Tables

                    Thanks guys for your help!

                    What I neglected to mention is I'm doing this for data migration not storage.  Phil, if I were storing the data you are right I wouldn't need the second table.

                    I have a CMS system that has all the users who have purchased from this client, that's the All Users list I referred to above.  Then on the bulk email system a subset of those users have opted into a list to get updates on their products.  So what I have are 2 CSV lists from the 2 systems.  I needed a way to find those users who had opted into thist email list and get their detail information from the other list.  Importing the detailed All Users list would add users who had not opted in for those e-mails.  Hope that makes it clearer.

                    What I stumbled across last night was probably what you've suggested aammondd.

                    I created a layout that looked basically like:

                    ActiveUsers::Email | AllUsers::Email | AllUsers::Detail1 | AllUsers::Detail2 | AllUsers::Detail3

                    I then sorted the view by Activeusers::Email and when I ran out of Active Users, I had my subset.  I then was able to save that subset as a CSV and import it into the Email system.

                    Not very elegant, and probably not the best way to do it, but it was the best I could come up with and it seems to have gotten me the results I needed.

                    If anyone has a better way to do this, I would love to know as I will have this problem again in the future for this and other clients.

                    Thanks everone for your help!


                    • 7. Re: Find Matching Records in 2 Tables

                      Since you have your link set, a number field would be better since you could then change the email address without breaking the link, you can do a search in a parent file based on the records in a child file.

                      Now, to answer your question: there are two methods.

                      A) create your found set in the active users table and then user Go To Related Record in the all users table. This should list the matching records if you use the proper options.

                      B) Add a constant field in your all users table, calculation = 1. Now the secret until now method of finding parent records via a related search:

                      Go to layout parent

                      enter find mode

                      set field childfile;constantfield = 1

                      perform find

                      This is easier than goint to the child file, doing the search and then doing a gttr but you need that constant field for your question. You can also use this to find by name, etc of your active users.