5 Replies Latest reply on May 14, 2014 2:26 PM by philmodjunk

    Unwanted duplicates of parent records

    EmelieLeht

      Title

      Unwanted duplicates of parent records

      Post

           Hi,

      Problem:Creation of child-record through a portal in the grandparent layout creates an extra, related, parent-record in the parent-portal on the same layout. How can I avoid this?

           I have stock/order/purchase data base system to keep track of ordered and produced articles. These articles have all got a varying number of related documents.

           I am at the moment trying to build a layout for a purchase order (grandparent) where you first state the articles you want to purchase through a portal. This creates the purchase-item-record (parent) with information gahtered from a related articles-table.

           Finally I want to be able to choose which of each articles' related document I want to include in the ourchase order. This is done through a second portal, also on the grandparent layout, where a purchase-doc-record (child) is created. The information on related documents are garehterd from another table containing this information.

           Adding records to the article-portal (parent) haven't been a  poblem bus as soon as I add a record of a related document (child) this creates an extra related parent record. And this happens even though I have not allowed creation of records child -> parent. This phenomena pnly occurrs when I work through the portals on the grandparent layout. If I create a record directly in the purchase-doc table this doesn't create an extra parent record, so I figure there is something spooky with the portals here that I can't fugure out.

           Any help is mostly welcome!

           Best, Emelie

        • 1. Re: Unwanted duplicates of parent records
          philmodjunk

               If you have this relationship:

               Parent-----<Child------<GrandChild

               creating a new record in a portal to GrandChild placed on the Parent layout could create a new record in Child, but this should only create one such record and should only happen if "allow creation" is enabled for child in the Parent-Child relationship and for grandChild in the Child-GrandChild relationship.

               Can you upload a screen shot of the actual relationships that you have set up? And identify every case where the "allow creation" option has been enabled in a relationship between any occurrence of these three tables?

               But there are basic design issues with your layout design. The portals that you have described imply the one to many, relationships I have shown here between parent and child and child to grandchild. But there is no way, when creating a new GrandChild record to specify which record in Child is supposed to link to that new GrandChild record.

               Thus, you may find setting up the two portals as a pair of "master detail" portals a useful alternative approach: Need layout solution for nested portals...

          • 2. Re: Unwanted duplicates of parent records
            EmelieLeht

                 No, the relationships are not as simple as that I'm afraid. I'll upload a screenshot of my relationship and the granparent layout.

                 The granparent here is the Purchase order, the parent: Purchase order items, the child: Purchase order docs.

                  

                 As you see there is many to many relationships here, and maybe this is why it fails to start with. I am very new to database-design so if there is a better way to design this, please let me know.

                 In the layout screenshot the Ordered articles-portal shows the Purchase order items and the Article documents-portal shows the Purchase order docs.

                 I want it to work so that I can first add the articles to order in the Ordered articles-portal and then what related documents to include in the Article documents-portal

                 I am reading you post on 'Master detail' design meanwhile and see if I can get some things straight. 

            • 3. Re: Unwanted duplicates of parent records
              philmodjunk

                   Actually, the relationship is very close to what I described. And while there are "crows feet" on both ends of the relationship lines between the child and grand child tables, this isn't really a many to many relationship as that requires either a join table or a text field loaded with multiple IDs separated by returns.

                   But I am not sure that I understand your intent here. Is each docs record need to be linked to a specific Record in Purchase Order Items?

                   Will you need to link more than one document to the same purchase order item?

                   The current relationship only allows you to link a single docs record to any one Purchase order items record and if that works for you, you don't need two portals for this. If it doesn't work for you, we need to change the relationships and layout that you have set up.

              • 4. Re: Unwanted duplicates of parent records
                EmelieLeht

                     Actually the article-document relation is already created in the art doc relation table. I just want to retrieve and display selected information from this table somehow, And yes, one article can have several related documents.

                     Preferably I would like to, for each ordered article, be able to chose which related documents that I want to include in the order. This doesn't neccesarily be to displayed in a portal but I thought it was the easiest way to do it. What I want to include is basically one list of ordered articles and one list of included documents. The documents list should show which article each document is linked to. This is why I wanted to use the aready existing art doc relation table.

                • 5. Re: Unwanted duplicates of parent records
                  philmodjunk

                       Yes but we were discussing items--records in the Purchase Order Items table, Not Articles in the Articles table.

                       How they are related to records in Articles does not appear relevant to the issue at hand unless An Article is the same thing as a purchase order item.

                       

                            Preferably I would like to, for each ordered article, be able to chose which related documents that I want to include in the order.

                       From that I conclude that each item in Purchase Order Items specifies a record in Articles. This in turn would appear to specify what documents might be selected for that purchase order item--which could be more than one document for a given record in Purchase order items, but always documents selected from the list determined by the Articles to Documents many to many relationship.

                       That sound correct?

                       As I've stated before, your current relationship between Purchase Order Items and documents does not support selecting more than one document for a given record in purchase order items. That's due to the match fields you've selected in the relationship.

                       At the relationship level, it would seem that you need these relationships:

                       Purchase Order Items::__pkPurchaseOrderItemID = Purchase Order Docs::_fkPurchaseOrderItemID
                       Documents::Document Number = Purchase Order Docs::Document Number

                       You'll note that this is very much the same relationship structure linking Articles to Documents. The difference is that while the join table used for Articles to Documents may list, for example 5, links to 5 different documents, Purchase order docs, for the same specified article may list just a subset of those 5 linking records found in art doc relation