1 Reply Latest reply on Sep 29, 2010 10:57 PM by FentonJones

    How to find a record in two related tables



      How to find a record in two related tables


      I have two tables with a one-to-many relationship.  I created a layout with  several fields from each table including the field which is common to both tables.  This field contains unique values in the parent table but may contain one or more duplicates in the children table.  When I exit the edit mode in the layout, the layout shows the data from both table as expected.  I can navigate through the records in the parent table and the data in the fields from each tables is displayed correctly.  So far so good.  If I press the find icon, I can enter any valid record number and the record with the related child records is found - also as expected.  The children table has a field which numbers the children from 1 to x for each record number.  Eg. if there is only one child for a particular parent record, then the child number is 1 and if there are 3 children for another parent record, then the children are numbered 1, 2, and 3.  Now when I navigate through the parent records as described above, the layout always displays data from child no 1 regardless of the number of children for each particular record.

      The problem I have is that if I do a find using the find icon, and enter a record number into the common field and a child number into its field, the find command fails: it says record not found.  Stated another way, I have not found any method whereby I can navigate through the records and for those parents which have more then one child, also navigate through  (display) them.

      I am sure this is possible but the way to do it escapes me.

        • 1. Re: How to find a record in two related tables

          On the parent's layout you need to draw a "portal" around the child field(s). Its relationship will be the same as the field of the related table. You specify how many rows (records) to show, and whether it should have a scroll bar, etc.. That is the usual way to view multiple children of a parent.

          If you choose the [x] Allow creation of related records option, on the child side of the relationship, you will be able to create children of that parent simply by typing values in any of the portal's fields.