AnsweredAssumed Answered

How to avoid duplication in Value Lists?

Question asked by smithtim47 on Nov 9, 2018
Latest reply on Nov 13, 2018 by Cécile


My name is Tim; I am a total newcomer to FileMaker and to the FileMaker Community. I am a volunteer for a very small and very rural local history organization. My project is to construct a database in FileMaker 16 Pro that will allow us to manage and publicize a series of interviews that have been made over the past twenty years or so with community members. As a retired librarian, I think I have a pretty good idea of what I want this database to do, but I don't know how to make FileMaker do it.


I'm part way along on designing the interface for our database, but am really stymied by a problem relating to value lists. I understand things better visually than in database jargon, so I've inserted a screenshot below which, I hope, will illustrate my problem.


I have a tabbed Main Table for the basic information about the interviews. One of the places where I'm getting stuck is in the descriptions of the content of each interview. I've set up separate tables for Subjects, People, and Places and have created portals to each of these in the Main Table. I *think* the portals are working correctly when it comes to seeing their related tables, but I don't know how to avoid duplicating values in each of these tables.

Here's the screenshot to show what I'm taking about:

FM Main and Subject windows.png

The left two-thirds of the image shows the tab on the Main Table with a portal to the Subject Terms table, which is shown on the right. I need for the Subject Terms list to not have any duplicate entries, but I don't know how to keep this from happening. Whether I work in the Subject Terms table itself or via the portal from the Main Table, in either case if I enter a new term, say Farming, it creates a new record in the Subject Terms table.


In the Relationships table I have used Join Tables to connect the Main Table to each of the other tables, such as the one shown for Subject Terms, and I have checked the option to allow creation of records in the child tables from the Main Table. When I tried leaving that unchecked, I was unable to enter anything at all into the portal and, of course, it didn't "see" the child table.


I've also tried experimenting with various options in the Validation section of the Field Options window, but none of what I tried worked.


What I want to be able to do is to work from the portal and re-use (without duplicating entries) pre-existing records in the child table, but also to be able to create new records "on the fly" from within the portal.


Since what I'm trying to do is a pretty fundamental requirement for a database, I'm sure I'm just missing something basic, because of my inexperience with FileMaker. I hope some more experienced folks will come to my rescue and help me up the next step of my learning curve.


Great thanks in advance!