9 Replies Latest reply on Jul 29, 2012 11:38 PM by bnuttman

    Value list showing only active dropdown values

    bnuttman

      Here is the situation: I have have 2 tables. One for students and one for counselor. the Students table as a counselor_id field that is related to the counselor_id field in the counselor table. The counselor table has a counselor_id, name, phone and active(yes or no) fields.

      When creating a record for a new student, I have a dropdown list based on the counselor table that allows the user to choose the counselor for the student. This works fine.

      The counselor table contains all the counselors ever at the school, about 200, and only 10 are still at the school and marked active.

      What I would like is to have the counselor dropdown only to display the active counselors.

      I tried to do this by creating a new TO relationship based on the active counselors, but then, for the students with inactive counselors, the layout display only shows the counselor_id from the student table.

      Is there anyway to do this?

        • 1. Re: Value list showing only active dropdown values
          AlanStirling

          Hi bnuttman

           

          You are on the right lines here, but you need a second relationship (or Table Occurrence), which only shows the active counselors in the value list that drives the Counselor dropdown list, but keep the original unlimited link to all counselors for the lookup between Counselor_IDs.

           

          This way, new entries are picked from the list of Active Counselors, but previous entries can still match the Counselor_ID of previous counselors.

           

          Don't be afraid of using extra relationships to refine the way that you want FileMaker to work.  Later you will find ways to reduce this count of relationships, but don't worry about this for now.  The same applies to FileMaker fields, which take up a minute amount of space if left empty, but can be useful to hold data for a small number of the total records.

           

          Best wishes - Alan Stirling, London UK.

          • 2. Re: Value list showing only active dropdown values
            bnuttman

            Are you saying that I need to display 2 fields on the layout? One for the dropdown, and one for the original link that matches the counselor_id of previous and current counselors?

            • 3. Re: Value list showing only active dropdown values
              AlanStirling

              Hi bnuttman

               

              No, you do not need two fields as the Drop-down menu can use a different relationship to the Counselor_ID link, all attached to the same field.

               

              From your original description, I presumed that the counselor_ID field linked to the Counselor table, such that entering a matching value in this field displayed the data for the selected counselor, whether active or not. This part of your system should remain as it was.

               

              As I understand, you have added a Drop-down menu to this field, which originally used a value list to show all counselors. These two parts of your system can be driven by different relationships.

               

              You say that you have been able to make a drop-down menu with only the active counselors, so it is now a small step to keep this relationship to drive the drop-down list, whilst adding a new relationship to a copy of the counselor table on the relationship graph that links all counselors. This relationship should be used as described in the second paragraph above.

               

              Perhaps your issue is to understand that in FileMaker, you can add as many relationships as you need between the same two tables - but you need to duplicate one of the tables on the relationship graph (making a Table Occurence with a different name) for each new relationship, since each pair of table occurrences on the relationship graph can only support one relationship.

               

              If you still find this difficult to grasp, then I suggest that you take a look at one or more of the free templates that are installed with FileMaker, so as to see some examples of what can be accomplished in the Relationship Graph.

               

              Best wishes - Alan Stirling, London UK.

              • 4. Re: Value list showing only active dropdown values
                bnuttman

                I did some research on adding TO's but am still not able to make this work.

                It might be easier to see an example so I am attaching a small file with a sample of what I am trying to do. If you can make your solution work with this, it might be quicker than further trying to explain it.

                 

                Thanks for your help,

                Bruce

                • 5. Re: Value list showing only active dropdown values
                  bnuttman

                  Here is another file implementing what I think is your suggestion with the addition Table Occurance and you can see, I must be missing something here....

                  • 7. Re: Value list showing only active dropdown values
                    bnuttman

                    That was genius.... Thank you.

                    • 8. Re: Value list showing only active dropdown values
                      keywords

                      You need to study erolst's example more closely; yours is quite different.

                      • 9. Re: Value list showing only active dropdown values
                        bnuttman

                        It is quite different and the solution takes a different direction than I was thinking of, but it accomplishes what I was looking to do, so it works for me.