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