1 Reply Latest reply on Jun 14, 2011 12:23 PM by philmodjunk

    creating a layout with fields related to multiple other tables, and creating new records in those...



      creating a layout with fields related to multiple other tables, and creating new records in those other tables


      Hi there!

      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.

      Thank you.

        • 1. Re: creating a layout with fields related to multiple other tables, and creating new records in those...

          Item one, make sure that you link to organizations and people by ID numbers, not by names. As you point out, names are not unique. Neither are organization names and both people and organizations can change their names.

          I'm not sure if you can decipher how this demo file works, but if it can, it should give you and example of how you might set up a drop down list of names to select the ID number associated with it in a related table. (Such as a table of organizations or a table or people.)

          It uses scripting with script parameters and the search portals (another option to consider here) at the bottom use filtered portals that only work with fileMaker 11 and which rely on script triggers to update them when text is typed into the search fields.


          Don't hesitate to ask questions if you find something in the demo file that you don't understand or can't reproduce in your file.