1 Reply Latest reply on Jul 10, 2012 1:49 PM by philmodjunk

    Portal returns only first applicable record



      Portal returns only first applicable record



      I'm a high school newspaper adviser, and I'm trying to write a project management database that will allow my newspaper students to track articles through the writing process and streamline communication.

      I've got separate tables for staff members, articles and tasks.  Tasks can be assigned to a staff member and an article, and thus create a many to many relationship between them.

      I've read that you can use related records as queries, and report the results of that query through a portal.  So I've created a layout that allows a user to input a date limit and a staff member name (saved as global values in fields called GDate and GName in the Article table), and a portal that should return all the tasks assigned to that staff member with due dates prior to that date.  I've created another table occurance ("TasksSearchable") for the Tasks table.  I've set up a Nonequijoin, using an Equal relationship between GName in Articles and Staff Member in TasksSearchable, and a Less than or equal to between GDate in Articles and DueDate in TasksSearchable.

      But I'm not getting the results I'd like.  When I input the search values into the global fields, I do get results in the portal.  But I see the same record (in this case, a task) repeated a couple of times, instead of all of the different tasks that I want it to return.  Interestingly, I've noticed that if I manually count how many tasks I should be getting based on my search critera, the portal returns that many records.  But it's the the first record in the Tasks table, repeated that many times.

      Does anyone know what I might be doing wrong?  Your help will be appreciated!

        • 1. Re: NoFields

          You may be able to get away with using names in a relationship if your staff is small, but it's almost always better to use an ID number for your staff members instead of their name. Names are not unique, names get changed (even for students) and are also subject to unusual spelling that can make data entry errors easy to commit. Using a serial number from the staffmember table avoids those issues and you still can select by name from a value list if you set up the value list to use both the name and the ID number.

          Before we tackle the idea of using a portal to pull up specific records, we need to confirm the basic design of your database system.

          I think you set up your relationships initially like this:


          Normally, the details for these relationships would look like this:

          Staffmembers::__pkStaffID = Tasks::_fkStaffID

          Articles::__pkArticleID = Tasks::_fkArticleID

          With those relationships, you can place a portal to Tasks on the StaffMembers layout and it will list all tasks assigned to that staff member. Fields from Articles can be included in the portal row to provide a more complete description of each task. In like manner, a tasks portal on the Articles layout would list all tasks associated with that article and fields such as the staffmember's name can be included in the portal to show who was assigned that task.

          Please note that this structure assumes that only one person will be assigned to a given task. If multiple staff members can be assigned to the same task, the relationships shown here will need to be modified to support that.