5 Replies Latest reply on Jan 10, 2015 11:39 AM by philmodjunk

    If record with key field exists don't create another one through a relationship

    PeterMontague

      Title

      If record with key field exists don't create another one through a relationship

      Post

      I want to make choose a section name from the table SectionSow_Section through a relationship with the table Scheme of Work.

      The tables match through SectionSow_SECTION::Section and SchemeOfWork::Section. The SECTION table also has another relationship with the SchemeOfWork table through their key fields which use look up to match the serial numbers.

      In most cases the section already exists. I have a drop down list that auto completes as I fill it in. So when I create a new record in SchemeOfWork and fill in the Section field with a previously entered name I expected that the previous record would be called up. But I'm noticing that a new record is being made. A new serial number shows up in SectionSow_Section::SectionID until I commit the record by moving to a new field. Then the serial number of the first SectionSow_Section::SectionID shows up.

      How can I stop the creation of new records if one already exists?

        • 1. Re: If record with key field exists don't create another one through a relationship
          philmodjunk

          To start, you'll need to describe your current design in much more detail... I can vaguely recall some of what you have set up previously but there's no way I'm going to trust that imperfect recollection as the basis for any suggestions. wink

          • 2. Re: If record with key field exists don't create another one through a relationship
            PeterMontague

            I made a scheme of work for teaching. This lists the lessons which I intend to teach over the coming year. I want to match it by section and skills to past exam questions. I have a portal that matches the appropriate exam questions to each lesson depending on the sections and skills that I choose.

            The selection of a section in a particular lesson will limit the choice of skills available to match the lesson with an exam question. Some lessons will have more than one skill so I want to use a portal to allow me to choose more skills.

            But when I make a previously entered section name in a new record I can see that it is creating a new section in the related  SectionSow_Section table. Here is an image of the relationships.

            The SchemeOfWork table is also connected to the __kpSectionID_SOW_SECTION table through sectionID. I have a SchemeOfWork::SectionID look up field which looks up SectionSOW_SECTION::Section ID through the relationship made by the Section join with the SectionSOW_SECTION table.

            • 3. Re: If record with key field exists don't create another one through a relationship
              philmodjunk

              But when I make a previously entered section name in a new record I can see that it is creating a new section in the related  SectionSow_Section table. Here is an image of the relationships.

              A new record in what table one what layout? If this is in a portal row, the portal is to what table occurrence and on a layout based on what table occurrence?

              Is SectionSOW_Section supposed to function as a join table linking Schedule of Work to Section?

              If so, I would think that this is both an expected and necessary thing in many cases.

              • 4. Re: If record with key field exists don't create another one through a relationship
                PeterMontague

                But when I make a previously entered section name in a new record I can see that it is creating a new section in the related  SectionSow_Section table. Here is an image of the relationships.

                A new record in what table one what layout? If this is in a portal row, the portal is to what table occurrence and on a layout based on what table occurrence?

                I am working on the Scheme of Work table on the Scheme of Work layout. I have a drop down list and it auto completes when I am typing. If the section name has already been used it shows up as I type. It is not a portal because each lesson record in the Scheme of work only belongs to only one section.

                I am using the Section as a way of filtering the skills to be used in a lesson.

                Is SectionSOW_Section supposed to function as a join table linking Schedule of Work to Section?

                No. It is named this way because it is joined by the key field called section to Scheme of Work. Scheme of work is joined to another TO of Section called __kpSectioID_SOW_Section. This is joined by the key fields SectionID.

                • 5. Re: If record with key field exists don't create another one through a relationship
                  philmodjunk

                  Not sure, but it sounds like you have a field from another table occurrence than that of your layout's current table/record formatted in this way. Thus you are entering data into a related table's record and in many cases, this will create a new record each time. But I would think that this field should be a field defined in the layout's table, not from the related one.