8 Replies Latest reply on Feb 13, 2012 8:53 PM by philmodjunk

    Drop-Down List based on Calculation but having an over 50 moment!

    ChatAtkins

      Title

      Drop-Down List based on Calculation but having an over 50 moment!

      Post

      Hi There,

      Yes, I am a newbie, and I am over 50 so please bear with me.

      I have an Employees table which has a Status field.  An Employee can have the status of: Student, Teacher, etc.

      I have a Classroom table which lists the Class Name, room number, etc.

      On my Classroom Layout I would like to have a Teacher Drop-Down List which only shows the Employees who have the Status "Teacher", so you can pick which "Teacher" will teach that class and not have a "Student" teaching the class.

      As I said in my title, I'm having an over 50 moment, I've done this before, but for the life of me, I can't figure out how to make this work on this layout.

      Any help would be greatly appreciated.

        • 1. Re: Drop-Down List based on Calculation but having an over 50 moment!
          philmodjunk

          You can use a relationship or a calculation field. The relationship option is more flexible. The calculation field is quicker to set up. Both options are described in this tutorial: Custom Value List?

          • 2. Re: Drop-Down List based on Calculation but having an over 50 moment!
            ChatAtkins

            I'm sorry PhilModJunk,

            I tried to follow the examples, but as I said I'm over 50, and I started programming back in the Dinosaur Days using C.

            So although I can write this out in C, for the life of me I can't figure it out in FM.

            Could you please walk me through this?

            Here are my tables:

            Status Table: STATID, StatusName, StatusCode (e.g. STAT001, Teacher, T,    STAT002, Student, S)

            Employee Table: EMPID, Fname, Lname, fk_STATID, etc

            EmpClass Table: EMPCLID, fk_EmpID, fk_ClassID

            Classroom Table: CLASSID, ClassName, ClassNo, fk_StudID, fk_TeachID, etc.

             

            So if I have this right here are my TO's

            Status ----< Student (TO of Employee) -----<EmpClass >----- Classroom

            Status ----< Teacher (TO of Employee) -----<EmpClass >----- Classroom

             

            IF this is right, how do I get a Drop-Down List on the Classroom Layout to show only teachers (or should it be on EmpClass Layout?)

            Sorry to be so pedantic, but I am really stuck trying to figure out which window or tab to open to make this all work.

            • 3. Re: Drop-Down List based on Calculation but having an over 50 moment!
              philmodjunk

              Do you have just one record in this table for each status? Thus Fk_StatuID is the same value for all "Teachers"?

              If so, using the "option 1" method from the tutorial

              Define a calculatoin field, cTeacherID as:

              If ( fk_statID = "STAT001" ; EmpID )

              Define your value list to list values from cTeacherID to get a value list of teacher IDs.

              A similar calculation field can be defined for students.

              And these value lists can be used on any layout where you need it.

              • 4. Re: Drop-Down List based on Calculation but having an over 50 moment!
                ChatAtkins

                Sigh, PhilModJunk,

                I have to laugh because IT IS STILL NOT WORKING!!! LOL. Again I hate being so pedantic but if I walk you through what I did maybe it will help and to answer your questions:

                Do you have just one record in this table for each status? Thus Fk_StatuID is the same value for all "Teachers"?

                 

                Yes, In Status Table:

                STAT001 = "Student"

                STAT002 = "Teacher"

                STAT003 = "Supervisor" 

                etc.

                 

                You then said:

                Define a calculatoin field, cTeacherID as:

                If ( fk_statID = "STAT001" ; EmpID )

                In my Employee Table, I added a field called cTeacherID, saved it as a calculation.  Clicked on Options, checked that I am using Current Table “Employees”.Added the formula:

                 

                If ( fk_statID = "STAT001" ; EmpID )

                 

                Clicked OK, and noted in the Fields Tab under Employee Table it says:

                 

                cTeacherID    Calculation    Unstored, from Employee, =  ( fk_statID = "STAT001" ; EmpID )

                 

                Then I clicked on Manage | Value Lists...

                Clicked New

                Typed in cTeacherName for new List Name

                Clicked on Use Values from field which popped up window

                Clicked on values from first field and used Employee

                Clicked on cTeacherID

                 

                Now when I click on OK I get this message:

                 

                “The Value List will not work because the field “ cTeacherID” cannot be indexed. Proceed Anyways?

                 

                What do I do now?

                 

                • 5. Re: Drop-Down List based on Calculation but having an over 50 moment!
                  philmodjunk

                  Why does the calculation show that it is unstored? This should not be an unstored calculation field and I see no reason why it would be from the details that you've posted.

                  Is fk_statID a stored or unstored field in the Employees table?

                  Foreign key fields should always be stored/indexed fields.

                  Did you specify "do not store" for the calculation? If you did, please change this so that it can be stored.

                  • 6. Re: Drop-Down List based on Calculation but having an over 50 moment!
                    ChatAtkins

                    Well, this is embarrassing!

                    Again, you are correct, PhilModJunk.  I didn't realize you could "uncheck" the "Do not store calculation results ..."   Which I did.

                    I then checked the Indexing: "All" radio button. But it still didn't work.

                    Next I tried changing the formula from

                    If ( fk_statID = "STAT001" ; EmpID )  

                    to

                    If ( fk_statID = "Teacher" ; EmpID )

                    which worked. No problems with storage!

                    However, now when I click the cTeacher drop-down box on the Classroom Layout, I get the all the Teachers listed but when I click other than the first one it says:

                    The action can not be performed because the field is not modifiable.

                    So why is the drop-down box not working? AND why did I have to change from "STAT001" to "Teacher"?

                    Yours Truly,

                    Dazed and Confused and over 50

                     

                    • 7. Re: Drop-Down List based on Calculation but having an over 50 moment!
                      ChatAtkins

                      NEVER MIND, NEVER MIND,

                      Oh, this is so embarrassing.

                      I figured it out, I was trying to use the field on the layout that was the calculation instead of the field which held the name.

                      SO SORRY, PhilModJunk, please accept my apologies.  Your help have been phenomenal.

                      I sure you must get so tired of helping "Newbies" with these "Beginner" problems.

                      But thank you again for all your help. This is the best site for help.

                      • 8. Re: Drop-Down List based on Calculation but having an over 50 moment!
                        philmodjunk

                        If I got tired of helping Newbies, I wouldn't spend so much time in this forum. Laughing