1 2 Previous Next 20 Replies Latest reply on Aug 24, 2012 1:02 PM by philmodjunk

    Enter text into a field based on information contained in two other fields

    ClairJudas

      Title

      Enter text into a field based on information contained in two other fields

      Post

      Table: Students
      Table: Staff

      In table 'Students', have fields...
      Room
      Teacher

      In table 'Staff', have fields...
      StaffFirstName

      StaffLastName

      Staff Positions

      In table 'Staff', staff positions are entered using a popup menu. (i.e. teacher, consultant, social worker, etc.)

      In the 'Students' table, want the 'StaffFirstName' and 'StaffLastName' to fill-in to the 'Teacher' field, in the Student table based on the 'Room' number (Students table) and 'Staff Positions' (Staff table, popup)

       

      For example: for student: Jim, want to have his teacher's name filled into the 'Teacher' field based on Jim's room number (All staff are assigned to room numbers) and the Position popup.

      Also have other staff positions I want to fill in automatically.

      Is this do-able?

      Thanks for your help,

      Clair

        • 1. Re: Enter text into a field based on information contained in two other fields
          philmodjunk

          You need some more fields added to Staff:

          __pkStaffID--an auto-entered serial number field

          Room Number--this assumes that your teachers always teach in one room--often this is not the case in upper grades and even in primary grades there can be exceptions to this.

          cFullName--a calculation field that combines staff first and last names such as:  StaffLastName & ", " & StaffFirstName. This field can be used in value lists and also added to the student layout to display the name of a student's current teacher.

          Then you need a field: _fkStaffID in your students table and this relationship:

          Students::_fkStaffID = Staff::__pkStaffID

          The simplest way to choose a teacher for a given student is to then set up _fkStaffID as a drop down list of __PkStaffID's with cFullName as the secondary field in the drop down list. You can then select a teacher by name from such a list, but the ID number is what is entered into the field.

          Then you can add Staff::cFullName and Staff::RoomNumber fields to your Student layout in order to display the namd and room number of the selected teacher.

          Since each staff member has only a specific position as far as I can tell, I'm not quite clear on what you want for your position "pop up". You can certainly add the Staff::StaffPositions field to your Student layout to show the student's teacher's position and you can modify the value list for StaffID to be a conditional value list that only lists Staff where Position = Teacher or you can even filter it further by grade levels.

          • 2. Re: Enter text into a field based on information contained in two other fields
            ClairJudas

            Thanks for the quick response.

            I'm new to this so need some clarification...

            The way you've named fields with the  '_pk', 'c', '_fk'

            I assume there is meaning attached to that. Would you enlighten me please?

             

            As you mentioned, some staff (consultants, social workers, nurse, etc.) are assigned to more than one room. In the Staff 'Room' field i've listed the rooms to which they are assigned (one room number per line in the field). Will that work?

             

            Thanks,

            Clair

            • 3. Re: Enter text into a field based on information contained in two other fields
              philmodjunk

              __pk is a commonly used naming convention for identifying the "primary key". _fk stands for Foreign keys. The two underscores for primary key and one from foreign key conveniently sort the field names to the top of lists of field names. The names themselves have no effect on function. Those are controlled with field options settings sucha s defining __pkStaffID as an auto-entered serial number field.

              c is often used to identify calculation fields.

              It may not be the best approach and how do you determine (say as a paper and pencil excersize) which room is correct for a given student when you select the teacher?

              You may need a related table of room numbers and periods and/or subjects linked to your staff table.

              • 4. Re: Enter text into a field based on information contained in two other fields
                ClairJudas

                Thanks for the clarification(?)! I can't say I know what a primary key or foreign key is.

                Some background may help with the staff...

                This is a school for students with disabilities. Each classroom has one teacher and one (or more) educational assistants. On staff are also a Social Worker, Psychologist, two educational Consultants, a nurse and other staff. Each of the staff people are assigned to one or more classrooms (rather than to specific students). Students are assigned to one classroom/teacher.

                Periods and/or subjects don't enter in, as the students are in the same classroom, with the same teacher all day.

                It's a unique setting.

                Again, thanks for your help.

                Clair

                • 5. Re: Enter text into a field based on information contained in two other fields
                  philmodjunk

                  A Primary Key is a database field that uniquely identifies each record in the table. A foreign key is a field in a related table that matches to a value (usually a primary key field) in the other table. The terms aren't terribly important here as long as you understand that only one record in Staff will have any given value in __pkStaffID.

                  Actually it's not all that unique a setting. One of own my children goes to a "non public school" that sounds just like this.

                  the students are in the same classroom, with the same teacher all day

                  What I am trying to narrow down here is whether or not selecting a teacher can automatically select the room or if additional information is needed to make that determination.

                  I could be wrong, but it sounds like teachers are assigned to one and only one room, but other staff may be assigned to more than one room. If that is the case, we can pretty much ignore the mutiple room desginations for the other staff as they aren't involved in the process of selecting a teacher and seeing the room number appear on a given student record.

                  • 6. Re: Enter text into a field based on information contained in two other fields
                    ClairJudas

                    Still, it is a small world.

                    Oh, Okay, I get the Primary and Foreign key. All K-12 students in the state have a unique identifier, 'IMS_ID', a 10 digit number assigned by the state. That is what I've been using to create relationships between tables.

                    Teachers have only 1 room assigned to them... In the staff table the 'Room' field has only one number in it.

                    I would like to be able to 'auto-fill' some other staff positions in the student table also. Those staff people serve more than one room.In the Staff table their Room field has multiple room numbers, each on its own line.

                    If I need to reconfigure a table or create some new tables, I'd be happy to do that. I want the secretarial staff to only have to enter a staff member's name once and have it flow to where it needs to go. My thought was to have a 'Staff' table and be able to pull from that. Doesn't seem to be quite as easy as I had hoped! :-)

                    So it looks like one solution for teachers and another for staff will multiple room assignments?

                    • 7. Re: Enter text into a field based on information contained in two other fields
                      philmodjunk

                      All K-12 students in the state have a unique identifier, 'IMS_ID', a 10 digit number assigned by the state. That is what I've been using to create relationships between tables.

                      Actually, that's not your best choice for a Primary Key. The safest primary keys are internally generated by your database. By all means keep such data in your database, but I don't recommend that you use it in relationships to other tables. (Though there can be some special use exceptions.)

                      I would like to be able to 'auto-fill' some other staff positions in the student table also. Those staff people serve more than one room.In the Staff table their Room field has multiple room numbers, each on its own line.

                      Before we can look at solutions for listing associated staff other than the teacher, I need to know more. Pretend you were doing this with paper and pencil. If you know that "Jimmy" is assigned to "Mrs. Smith" from Room 3, how can you tell, just by looking at paper records, which additional staff will be working with Jimmy?

                      I think a staff table that lists all staff is a good structure, but any setup requires careful attention to the details. I'm trying to gather those details with my questions.

                      • 8. Re: Enter text into a field based on information contained in two other fields
                        ClairJudas

                        Why do you suggest using a primary key generated inside the program, rather than the state issued student ID?

                         

                        When I visualized automating filling in the 'Teacher' field in the Student table I was thinking of this process...

                        Each record in the Student table has a 'Room' field (contains one room number)

                        Each record in the Staff table has a 'Room' field (may contain multiple room numbers)

                        Each record in the Staff table has a 'Position' field (contains only one position)

                        Have a script 1) read the Student 'Room' field, 2) look for Staff 'Room' fields that contain the matching Room number (multiple staff may be found), then 3) look in the Staff 'Position' field to find the desired position, e.g. 'Teacher', 'Psychologist', or 'Social Work', etc. (narrows the selection to one) and 4) return the matching result. I thought this would work for all staff.

                         

                        In each student record, when the Room number was filled in the support staff names would be auto-filled from the Staff table.

                        Any thoughts or suggestions?

                         

                         

                        • 9. Re: Enter text into a field based on information contained in two other fields
                          philmodjunk

                          Why do you suggest using a primary key generated inside the program, rather than the state issued student ID?

                          It puts you at the mercy of any changes they might make to the student IDs--either to the number or how it is formatted. Any errors on their part with a student's ID can also create problems for you. These may be very unlikely scenarios but you can't put the chance that they might happen at zero. A primary key ideally is a value that never ever changes once created. Having to change the value of a primary key breaks any link to existing related records unless you also make matching updates to them before changing the primary key's value--a situation to be avoided at all costs. The only way to guarantee that your primary keys will never need changing is to use an internally generated serial number.

                          Now to implement a solution that will work for you:

                          Define these relationships:

                          Students------<Staff_Room>-----Staff

                          Students::RoomNumber = Staff_Room::RoomNumber
                          Staff::_pkStaffID = Staff_Room::_fkStaffID

                          Enable "Allow creation of records via this relationship" for Staff_Room in the Staff to Staff_Room relationship. (Double click the relationship line to open a dialog where you can select this option.)

                          On a Staff layout, you can place a portal to Staff_Room and create one record for each room for which a given staff is assigned.

                          On your student layout add a portal to Staff_Room, but include staff name and position fields from the staff table in this portal row.

                          When you select or enter a room number in Students::RoomNumber, the portal will list all staff assigned to that room.

                          This, BTW, is a many to many relationship where Staff_Room is the join table that makes it work. A given student can be linked to many staff members. A given staff member can be linked to many students.

                          • 10. Re: Enter text into a field based on information contained in two other fields
                            ClairJudas

                            Hi,

                            I'm not familiar with the FileMaker conventions you are using below. If you could give me a bit of description to go with those, that would be very helpful.

                            Students------<Staff_Room>-----Staff

                            Students::RoomNumber = Staff_Room::RoomNumber

                            Staff::_pkStaffID = Staff_Room::_fkStaffID

                             

                            Thanks, Clair

                            • 11. Re: Enter text into a field based on information contained in two other fields
                              philmodjunk

                              Students------<Staff_Room>-----Staff

                              is a text based representation of what you need to create in Manage | database | relationships. ---< stands for a "one to many" relationship and you'll see something similar on the relationships tab if you get the relationships set up correctly. Each name stands for a table (actually a table occurrence) and you would drag from a field in students to a field in Staff_Room to create a relationship.

                              Students::RoomNumber = Staff_Room::RoomNumber

                              Staff::__pkStaffID = Staff_Room::_fkStaffID

                              Documents which fields should match to which in these relationships. You'd drag from __pkStaffID to _fkStaffID to link Staff_Room to Staff in the relationship you need for this method to work. (You can use your own preferences for how you name these fields. I've used two underscores for the primary key field __pk and one for the foreign key field _fk so that a primary key will sort before a foreign key field in a list of fields from the same table.)

                              • 12. Re: Enter text into a field based on information contained in two other fields
                                ClairJudas

                                Okay...

                                FYI: I went ahead and created the Student IDs as you suggested. That was time consuming, making the changes to the relationships, etc. I set up a serial number that auto creates, starting with the number of the next new record. I entered the IDs for the existing records by hand (I'm sure there was an easier way!). BTW, should the ID fields be formatted as text or number? I was thinking text, as no calculations would be made using those, and to me they are more like names than numbers.

                                The relationships you described have been created, as has the Portal in the Student record, to show the Staff room assignments. Room numbers have been assigned to staff, again. :-) All seems to be working well.

                                So... if you recall, the final product was the ability to have the correct staff person Auto-fill into the 'Teacher' or 'Consultant' field in the Student Table.

                                Are you still up for that?

                                Clair

                                • 13. Re: Enter text into a field based on information contained in two other fields
                                  philmodjunk

                                  I entered the IDs for the existing records by hand (I'm sure there was an easier way!)

                                  Yep. Replace field contents is a tool that has a serial number option that will both load a field with serial numbers for every record in your table and update the "next serial value" setting on the same field at the same time.

                                  I recommend that serial numbers be stored as numbers, but text works also. The main reason that I recommend number type fields for this is that you occaisonally need to use this field when sorting records and if you use text fields, 1000 will sort as less than 2.

                                  Since you now have multiple staff personnel you want to see on that layout, not just the teacher. I suggested that you put a portal to Staff_Room on your layout and add name and position fields from Staff to this portal row. This will list all personnel assigned to that room in a single list. You can use sorting to sort the teacher to the top of the list or use two portals with portal filtering so that the teacher is listed in one and the remaining staff is listed in the other.

                                  • 14. Re: Enter text into a field based on information contained in two other fields
                                    ClairJudas

                                    Yep. Replace field contents... That 'thunk' you just heard was the heel of my hand against my forehead.

                                    Numbers it is...

                                    I did create the portal that shows all of this staff for a particular room. Works great.

                                    So, it sounds like I could create a three-field (Position/FirstName/LastName) portal that will filter to show only the 'Teacher' assigned to that classroom, and another one that would only show the 'Consultant' assigned to that classroom? I think that would fit my needs. How would I phrase that filter?

                                    Clair

                                    1 2 Previous Next