3 Replies Latest reply on Jan 8, 2014 3:40 PM by philmodjunk

    Create Simple View



      Create Simple View


           New to Filemaker Pro 13 so if there is a basic form please feel free to direct me.  Most of my questions are pretty basic.  

           I have a members table that has a unique ID.  I track activities they attend in an activities table.  The member id is the joining field. I want to create a view/form that shows all the members name and information from the members table, and a subform that has all the activities that member has attended from the activities table, all on one form.

           How can I create a selection from the members TB that includes both the members ID and full name?  Id usually means nothing without a name.

           Then how do I automatically fill in the new subform with both the members info, and activites info? 

           I'm not looking for step-by-step, part of learning is trying to figure out these things, but can't seem to figure out how to create a subform that combines the info.



        • 1. Re: Create Simple View

               Your "subform" is called a "portal" in FileMaker. It functions much like an MS Access Subform, but is based on a Tutorial: What are Table Occurrences? instead of a SQL query.


                    How can I create a selection from the members TB that includes both the members ID and full name?

               The "simple beginner level" method is to use Manage | Value Lists to create a value list with the "use values from a field" option. You can select the ID field from your members table as field 1 and a name field from the same table occurrence as field 2. You may need to set up a text field with an auto-enter calculation that combines the contents of first and last name fields in order to have a field to use as your name field.

               You'll find options that hide the first field from view so that the user only sees the name, not the ID, but this value list will still enter the ID number when the user selects the name.

               If you hide the first field and use the Pop up menu option instead of Drop Down List, you'll always see the name in this field as long as the selected ID number exists in your Members table. If you use the drop down list option, you'll see the ID number once you exit the field, but there are layout design techniques that can still show the name field in place of or with the ID number field.

               More sophisticated options are possible that work from this starting point, but that use scripts, calculations and relationships to provide a more user friendly experience--especially when dealing with members that have duplicate names and long value lists, than is possible with this basic level approach.


                    Then how do I automatically fill in the new subform with both the members info, and activites info?

               With a portal to activities placed on a Members layout, (you can create as many different members layouts as you need) you won't need to fill in the members info and if you have activities records that are already linked by ID to a given member record, they will automatically appear in the portal. You can add more activity records directly in the portal. If you enable "allow creation of records via this relationship for the portal's table occurrence, you can add new related records just by entering data in the blank "add" row of the portal. If you don't, you can add a button with a script that adds the new related record into which you can then enter data into those fields.

               And two tables, one for members and one for activities, may not work for what you need. An activity might have many different participating members and a member can participate in more than one activity. Thus, you may have a "many to many" relationship that requires a third table instead of a "one to many" relationship such as you have at the moment with just two related tables.

          • 2. Re: Create Simple View

                 Thanks for the quick response, as much as I like this program I'm finding it has a steeper learning curve than I thought.  I'll digest this for a while.

                 BTW I do have a members Activities table that has the many to many, This has the activity ID and member ID.  I just simplified my question.

                 I did create calc field in the members table that joins last name and first name called Full Name, then I created  a value list that has the Member ID and a second fFull Name field, but it's not sorting by Full Name as I would like.


                 Thanks again

            • 3. Re: Create Simple View

                   There's an option in Manage Database where you can select that the value list sort on the second field--which should be the field for which you specify the full name field. The resulting list of names should be in ascending alphabetical order when you deploy the drop down list or pop up menu. If you hide the ID field, the list should sort on the name field automatically.