11 Replies Latest reply on Apr 10, 2015 7:23 AM by philmodjunk

    Help Creating a Report Between Two Related Tables

    sccardais

      Title

      Help Creating a Report Between Two Related Tables

      Post

      I want to create a report showing a list of "Users" that are using identical email addresses.

      Table A is a list of Users that includes a unique Username and an email address that is not unique. e.g. Email addresses in Table A are sometimes shared by more than on User at the same company. Table B is a small list of duplicate email addresses. Table B contains duplicate email addresses. Relating Table B to Table B by matching on Email address results in a many to many relationship.

      For each unique email address in Table B, I want to create a report showing all of the Usernames from Table A. 

      How would I go about doing this?

      (Using FMPA version 13)

        • 1. Re: Help Creating a Report Between Two Related Tables
          philmodjunk

          Relating Table B to Table B by matching on Email address results in a many to many relationship.

          I think that you mean linking Table A to Table B, but either way this is not a many to many relationship. (Just because FileMaker shows crows feet on both ends of the relationship line, this does not mean that you have a many to many relationship.)

          I think that you are describing this relationship:

          Table B==<Table A

          Table B::Email = Table A::Email

          Where each email address in Table B has one record for each unique email address and Table A (users) has potentially many records with the same email address.

          If that's correct, you can set up a List view layout based on Table A. Add a Sub summary layout part "when sorted by" Table A::Email.

          Put Table A::Email in the sub summary layout part and the user name fields in the body. Perform a find for all records that have at least one related record in Table B, by specifying an asterisk in the Table B::Email field, then sort your records by Table A::Email.

          • 2. Re: Help Creating a Report Between Two Related Tables
            sccardais

            Where each email address in Table B has one record for each unique email address and Table A (users) has potentially many records with the same email address.

            This is not correct.

            Table B does have duplicate email addresses. It's possible that several Users with unique Usernames from Table A all use the same email address.

            Your solution would work if I could remove the duplicates from Table B. I can remove duplicate emails from Table B by using Excel or Google Sheets but I would like to do it within FMPA.

             

             

            • 3. Re: Help Creating a Report Between Two Related Tables
              sccardais

              The attached screen shot shows the set up I've created after removing duplicate email values from Table B. 

              Relationship Table B == <Table A

              Table B::email_address = Table A::Email_User

              Is the sub summary report layout shown on the screenshot the layout you had in mind? (Note: I removed the body part in this example.)

              I added a field to count the # of related records in Table A which seems to calculate correctly but the Summary field that totals c_CountRelated is not working. It is dispaying the value of the count of the last record in the layout rather than the total of c_CountRelated.

               

              • 4. Re: Help Creating a Report Between Two Related Tables
                sccardais

                I thought it might help if I summarized my goal.

                In Table A, I want to count the total number of Usernames that are using the same email address and identify them by Username and Organization.

                 

                • 5. Re: Help Creating a Report Between Two Related Tables
                  philmodjunk

                  I get the goal, what is unclear is exactly what you have in table a and what you have in table B. I wasn't sure that I understood your original description, which is why I restated what I thought you were describing. You have indicated that is incorrect, but I still am unclear as to why you have this data in two different tables. I simply don't follow what is the purpose to the two tables and how they are different from each other.

                  • 6. Re: Help Creating a Report Between Two Related Tables
                    JesseH

                    If I understand you correctly, you just want a list of the different e-mail addresses and which user names use said e-mail address. If this is the case then all your data is located in Table A.

                    Create a new layout, select the Report option, and organize records by e-mail address. This should do what you need. If you would also like to get information from Table B you would need a unique link between the two tables, but since there are multiple records with the same e-mail address in both tables i don't think that relationship would work.

                    • 7. Re: Help Creating a Report Between Two Related Tables
                      sccardais

                      Phil:

                      I understand your question. To keep my replies short (er), I didn't provide all of the background.

                      Table A contains a Master List of all Users for our product. Each month, we create a list of randomly selected Users to email an invitation to complete a Survey. The list is filtered to exclude Usernames that have submitted surveys in the past 12 months. This list is exported to Excel and forwarded to a third party that administers this survey.

                      The 3rd party rejects all records with duplicate email addresses. So, if three users - with different Usernames but the same email address - are on the list we send to the 3rd party, they reject two of the three. Table B is the reject list.

                      In this specific instance, I wanted to see how many Users from Table A were using email addresses in Table B but on a grander scale, I would like to know all of the Users in Table A that are using the same email address.

                      We sometimes get complaints from Users about receiving too many Survey Invitations and I'm trying to understand the implications of excluding Users from the monthy survey based on email addresses that have responded in the previous 12 months versus Usernames that have responded. In one drastic case, 106 different Usernames share the same email address. If one of them submits a survey, the remaining 105 would be excluded for a year.

                      Hope this helps and thank you for your on-going help.

                      • 8. Re: Help Creating a Report Between Two Related Tables
                        sccardais

                        Jesse

                        Thank you for your reply.

                        I think you are right. All of the data needed is in Table A. Another person in our company sent me Table B (duplicates rejected by 3rd party survey administrator) asking why we were sending duplicate emails. To explain, I wanted to use her list (which I imported into Table B) to illustrate the problem caused by multiple Users sharing the same email address.

                        Regarding the report option you described, I want a list of email addresses that are shared and the Usernames of the people that are sharing the same email address. I think the report you described would create a report of every email.

                        Thanks again for the reply.

                         

                        • 9. Re: Help Creating a Report Between Two Related Tables
                          JesseH

                          Scott

                          You are right it would. I don't have a solution right now to only show the groups with multiple records. I will see what I can do when I get a chance to break away from work.

                          • 10. Re: Help Creating a Report Between Two Related Tables
                            JesseH

                            Scott

                            OK. I have a solution for you. Create the layout as described above. Next, create a script which will do the following;

                            Enter Find Mode[]

                            Set Field [e_mail_address ; "!"]       (the ! needs to be in quotations and this finds duplicates)

                            Perform Find[]

                            Sort Records[]  (on this step specify the same sort order as when you set up your layout)

                            You can create a button on the layout to perform the script and modify it to "hide when printing" if you are going to print this report.

                            Hope this works for you.

                            • 11. Re: Help Creating a Report Between Two Related Tables
                              philmodjunk

                              The fact that the second table is the "reject list" is key. If 3 people share the same email, 2 are listed in that table.

                              I'd do it this way to get a list of all records in Table A that match to a record in Table B:

                              Perform this Find:

                              enter find mode and specify an * in the email field from Table B. This can be scripted or manual.

                              Now set up the report that I described in my first post here. Make it a list view layout with a sub summary part "when sorted by Email field". Put the email field in the sub summary layout part. Put the contact fields into the body and be sure to sort your records by the email field after performing the find.