creating a layout with fields related to multiple other tables, and creating new records in those other tables
So I need to create a "data entry" layout, and I'm trying to figure out how to make the pieces all line up.
What I have is several tables: Projects, People, Organizations, and Listings. Projects are associated with multiple people and multiple organizations (in that they are associated with multiple people, and the people are associated with multiple projects but only one organization). Any one organization can be associated with multiple people (it's employees), with many of those people assocaited with any given project. Listings are the instances of a person or organization being associated with any single project.
What I need is a layout where a user can quickly and smoothly create large (ish) numbers of records (Listings) for a new Project in a few different ways:
(assuming the information for the Project itself is already entered into the Projects table)
- When creating a new record in the Listings table, the user enters a name of a person. Filemaker should determine if this name already exists in the People table, and if so pulls up a window asking if the user wants to use that existing person's information or if they want to create a new record (for a different person with the same name, for instance. too many john smiths in the world), to associate this person with the new Project. Old information should include the known relation with a known Organization. The ideal world would be a drop-down menu that pops up as the name is being validated, suggesting, for instance, John Smith of Microsoft or John Smith of Jerry's Jams, allowing the user to select the correct John Smith and pull that person's information from their record in the People table
- When creating a new record in the Listings table, the user enters the name of an organization. Filemaker should determine if this is a known Organization, and bring up a list of all People associated with that Organization, allowing the user to either select one of the known People or create a new People record tied to that Organization. Again, the ideal would be a drop-down menu that lists, for isntance, John Smith- CFO of Microsoft, Jane White - VP of Microsoft, etc. Again, this is all to associate someone with the new Project. But this time coming from the Organization.
- When creating a new listing, the user enters a name and organization and filemaker recognizes NEITHER, i want it to automatically create new records in both the Organization table and the People table, propigating information from the new Listing into the new Org/People records.
I can do bits of pieces of most of this, but I'm having trouble with the concept of the name and organization fields being both options for creating new records AND being fields that would get filled in by related records in another table. I'm not sure how to deal with that. Any suggestions??
I'm a newbie with filemaker and still figureing out how to write the elegant solutions. Any and all suggestions are highly appreciated.