Can I list field entries from matching records from one database in another database?
I have two separate, large databases that are linked together. One (Agents) contains records for each of our clients. The other (GI) contains records for each of their customers. They are connected in such a way that I can take the client's reference number from the Agents database and enter it in a field in the GI database and all of that client's contact information from the Agents database appears in that record in the GI database. Now I would like to do the reverse - with a twist.
Since each agent has several customers, their name and contact info appears in several records in the GI database. I would like to create a field in the Agents database that looks at the linked reference number field for that agent and finds every record in the GI database that contains that agent's reference number, then lists the customer name from each matching record in the GI database field in that field in the agent's record in the Agents database.
Example: John Smith has a record in the Agents database with reference number 0001. When I set up a record for one of his customers in the GI database, I enter 0001 in the agent's reference number field and all of his contact info appears in that record in the GI database. Now, he has multiple customers in the database we'll call ABC Company, XYZ Company and 123 Company. I would like a field of some sort in the Agents database to find every record in the GI database with reference number 0001 and have it return the customer name for each matching record in this field in the Agent database. So, for John Smith, this field would show:
or: ABC Company, XYZ Company, 123 Company
Can this be done? We are currently on FileMaker Server 11 and FileMaker Pro 11.