4 Replies Latest reply on Jul 11, 2012 10:25 AM by philmodjunk

    Create a List from multiple fields

    MarkCampano

      Title

      Create a List from multiple fields

      Post

      I am setting up a layout that has an employee id number (Table: Personnel Records) as the unique identifier and that has three fields (Key 1, Key 2, Key 3) based on the room assigned to that employee.  Room Assignment is a drop-down list (Related Table: Building Information).  In the Building Information Table the room number is the unique identifier and each room can have as many as 3 different keys per room.  I currently have the fields for the keys per room set as Key 1, Key 2, and Key 3.

      I would like to find a way for each of the fields (Key 1, Key 2, Key 3) in the layout from the Personnel Records to be a drop-down list that is a culmination three fields (Key 1, Key 2, and Key 3) in the Table: Building Information.

      I apologize that the fields from both tables have the same name, it's easier for me to track.

      The screen shot attached has the tables, the third table was an attempts at trying to create a repository of all keys to make the drop-down list from.  I am open to any and all ideas.

      Thanks

      Mark

      Screen_Shot_KEYS.jpg

        • 1. Re: Create a List from multiple fields
          philmodjunk

          So "key" is an actual physical key used to unlock a door? That one threw me at first as "key" has a different connotation in Database jargon! Laughing

          And an employee can be issued up to 3 different keys? What will you do if changes are made and an employee needs more than three?

          I bring this up because you can set up your tables/relationships differently  and be able to issue as many keys to a given employee as needed and not be limited to any arbitrary number of keys.

          And your screen shot indicates that only one employee can be assigned to a given room and they can only be assigned to one such room. That seems unlikely to be the case given most "cubicle" arrangements used these days.

          What sparks that comment is this relationship:

          Personnel Records::__kP_Employee ID = BuildingInformation::__KP_Room Number

          That can't be right. Either you put the EmployeeID in both of these fields or you put the room number in both of these fields, but not both. And one of these fields will be a primary key (__KP) and one is a foreign key (_KF).

          Compare these three options:

          Personnel Records::__kP_Employee ID = BuildingInformation::_KF_Employee ID

          This assumes that one employee can be assigned to more than one room

          or

          Personnel Records::_kF_Room Number = BuildingInformation::__KP_Room Number

          This assumes that one room can be assigned to more than one employee

          and you might even need:

          Personnel Records::__kP_Employee ID = Person_Room::_KF_Employee ID

          BuildingInformation::__KP_Room Number = Person_Room::_KF_Room Number

          This allows you to assign multiple employees to the same room but also to be able to assign the same employee to more than one room.

          You'll need to figure out which of these will work for you.

           

          To get the list you've requested, you can use the List function in a calculation field: List ( Key1 ; key2 ; key3 ) and then you can set your value list to draw values from the calculation field, but I wouldn't set it up that way. I'd link a table of keys to the Building information table instead of using separate fields for the keys like you have.

          • 2. Re: Create a List from multiple fields
            MarkCampano

            This is great, thanks.  Sorry about the term "Key", I relaize what you mean now that you said it.

            Perosnnel here refers to Teachers and Paraprofessionals.  Each teacher is assgned one classroom.  The para's do not get a classroom key.  I suppose it is possible that a staff (teacher or administration) could be assigned more than three keys.  One classroom may have 1 teacher and a few paras.

             

            Does this change how you would answer the question?

            • 3. Re: Create a List from multiple fields
              MarkCampano

              When you said "link a table of keys to Building Information Table" did you mean create a table just to for all of the keys in the building?  If so then I could set up a drop-down in Building Information for each of the Keys assigned (Key 1, Key 2, Key 3), correct?

              • 4. Re: Create a List from multiple fields
                philmodjunk

                That's the idea as you are now listing data from a single field so it's less complex. And you can use a conditional value list to list only keys for a specific room in the value list.