4 Replies Latest reply on Dec 31, 2010 9:54 AM by philmodjunk

    Adding unlimited instances of a field through a portal

    manager12

      Title

      Adding unlimited instances of a field through a portal

      Post

      I'm trying to create a layout based off a table called "Daily Call" which, among other things, shows a portal to a related table called "Daily Call Breakdown"  which has the following fields:

      -Start Time

      -End Time

      -What

      -Where

      -Full Name

      What I'm attempting to do is define a start and end time of an event inside of a daily schedule. The "What" field is a simple text description of what is happening, same with where.

      The "Full Name" field pulls people's full names that are stored in a seperate table called "Personnel Data". What I want to be able to do is add an unlimited amount of peoples names (or "Full Name" fields) to the row of this portal. In other words if we look at each row of the portal view that shows the fields "Start Time", "End Time", "What", "Where" and "Full Names", as an event in a daily schedule, I want to be able to assign several people (or "Full Name" fields) to each event.

      I'm not even sure where to begin with this. Is using a portal even what I should be using or is there a different method?

        • 1. Re: Adding unlimited instances of a field through a portal
          philmodjunk

          You can't add columns dynamically to a portal, so you'll need to find a different approach. You can add as many rows as you need, so you may be able to set up what you need by thinking vertically instead of horizontally. There are also ways to present a list of data from a related record as a horizontal list, separated by commas or other text, in a single field so that may be an option for you.

          How have you structured your tables? I can see you have two tables: Daily Call and Personnel Data. It looks like you need a third table for assigning personnel to a specific Daily Call record. That might be done with this relationship:

          DailyCall----<Assignments>----Personnel    ( ---< means one to many )

          DailyCall::DC_ID = Assignments::DC_ID
          Personnel::Pers_ID = Assignments::Pers_ID

          The two ID fields in DailyCall and Personnel would be auto-entered serial numbers. The Two in assignments would be number fields. Assigning a person to a given DailyCall record is done by creating a record in Assignments with the ID number form DailyCall and the ID number from Personnel.

          The simplest way to do this is to enable "allow creation of records via this relationship" for assignments in the DailyCall---Assignments relationship and then place a portal to Assignments on the DailyCall layout. You can then set up Assignments::Pers_ID insided this portal as a drop down of ID numbers from Personnel, their names can be listed in a 2nd column of this value list.

          This is a classic Many to Many relationship problem. See this Demo file for a working example. To apply it to your situtation, think of Contracts as DailyCall records and Companies as Personnel.

          http://www.4shared.com/file/PLhjErzu/Contracts_to_Companies.html

          If you must have this in a horizontal format, still get this working for you first as we can then adapt this approach to present the assigned personnel names in a horizontal list.

          • 2. Re: Adding unlimited instances of a field through a portal
            manager12

            I think I understand what you are saying about having a third table and using a many to one relationship but I'm not sure that it solves the problem. I'll see if I can explain it better.

            In simpilest terms I want to assign many people to one time slot. It's almost like I'm making a calendar program. For example lets say that I'm scheduling a meeting. The meeting will start at 11:00AM and Peter, Bob, and Frank need to attend the meeting. I don't want to have a layout where every different person would have their own row, as that would make the person using the database have to enter the event name and start time over and over again. Below is an example of what I don't want to do, as it would waste a lot of time for the person inputing the information.

            Event Name Start Time Person
            Meeting 11:00AM Peter
            Meeting 11:00AM Bob
            Meeting 11:00AM Frank

            Instead I want to have something like the table below

            Event Name Start Time Person
            Meeting 11:00AM Peter, Bob, Frank

            Basically I don't want to have to re-enter "Meeting" and "11:00AM" multiple times as that is one event that I need to add multiple people to.

            I also want to mention that the "Daily Call" layout can have many events. The "Daily Call" layout basically just displays a date. For that date, there are many events. Each event can have many people assigned to it.

            I think, like you said, I need to think more creativley about another way to display this sinceI can't add columns dynamically in FileMaker Pro.

            You mentioned there is a way to display information in one field by separating the data by comas. Could you tell me more about this?

            Thanks for your paitience as I try to explain myself!

            • 3. Re: Adding unlimited instances of a field through a portal
              philmodjunk

              You shouldn't ever need to re-enter meeting times. It should be entered just once in the single record that defines that meeting. Assigning people to that meeting shouldn't requir any more input than selecting each person in turn from a drop down. A many to many relationship implemented via a join table is the best approach here, IMO.

              Personnel---<Personnel_Meeting>----Meetings

              Personnel::PersonnelID = Personnel_Meeting::PersonnelID
              Meetings::MeetingID = Personnel_Meeting::MeetingID

              After you create a new Meeting record, you'd use a portal to Personnel_Meeting to select each person from Personnel in a drop down in the portal. When done, you'll have a list of those people attending that meeting. And a portal to Personnel_Meeting can list all meetings to which that individual is assigned. This portal can be filtered to omit meetings that are now past.

              See this demo file for an example of this approach:   http://www.4shared.com/file/PLhjErzu/Contracts_to_Companies.html

              It matches contracts to companies, but the concept is the same.

              To take the contents of such a portal list and display it in a horizontal list, this calculation field can be used:

              Substitute ( List ( Personnel::FullName ) ; ¶ ; ", " )

              This would be defined in the Meeting table in order for the List function to pull up the correct list of names for a given meeting.

              • 4. Re: Adding unlimited instances of a field through a portal
                philmodjunk

                You have one and only one record for any given meeting. You'd enter that start time in that one single record and thus you'd only enter it once. You can display this meeting time for each individual by simply adding the field to a row in the portal to the join table. Thus, a portal to meetings on a Personnel layout can show any information from the meetings record in each row such as the start time, meeting name and location.

                All you should need to enter in the portal, is the person's ID, which can be done via drop down or pop up that uses a two column value list with the person's ID in column 1 and their name in column 2.