5 Replies Latest reply on Sep 27, 2013 9:45 AM by user28097

    allow creation via relationship problem?

    user28097

      I have 3 tables:

      - Purchase Order Header

      - Purchase Order Details

      - Item Vendor Associations

       

      I want to create a record in Item Vendor Associations for each unique combination of item number and vendor number in the Purchase Order Details table. This is to allow vendor pricing to be saved, then retrieved the next time I order the same item from a vendor.

       

      I have set the match criteria between Purchase Order Details and Item Vendor Associations to be the Item Number and Vendor Number fields. I have checked the box to allow creation via relationship on the Item Vendor Associations table. No records are being created.

        • 1. Re: allow creation via relationship problem?
          ErikWegweiser

          Hi, Kevin: It sounds like the relationship is properly constructed. However, there is no automatic mechanism for triggering the creatioon of the Vendor Associations record, just because a unique combintion exists in the PO Details (Line Items) table and the relationship from Details to Associations allows it.

           

          [note, I'm presuming that you don't want to simply do a lookup for the most recent price of the same item for the same vendor from Details alone, as the most recent price may not be the normal price you'd want to use next time].

           

          Basically, any time a Detail record is committed, you want to set something from the Details table to the Associations table via that uniqueness relationship. How depends partly on the method used for creating the Detail records. You might have a button that creates the Detail record from a PO, in which case that routine could also create the first Associations record via the relationship. Or you might have an OnRecordCommit on the PO layout that loops through all Detail records, creating any unique Associations records. Just a couple quick ideas.

           

          [note also that Associations would not be automatically updated with the latest price of an item for a vendor without some logic programming to do so on your part].

           

          Helpful?

          • 2. Re: allow creation via relationship problem?
            erolst

            The option means that you can use a portal to enter new rows/records; it doesn't mean “Create a related record in this table whenever a record in the other table is created” (analogous to the “Delete related records …” option). This is probably for the better, because there would be no check if a vendor for that item and the specific price point already exists (which would be the case the next time you look up the data in this table to create new line items (aka Order Details) …).

             

            You should script the creation of line items and check in time if that combination of item/vendor/price already exists, to prevent the creation of duplicate entries. It might be better to use a dedicated layout and processes to associate vendors with items and prices (as in “define a vendor product catalog”) before you create an order. And speaking of which … what's missing in your setup is a Vendors table; as is stands, a vendor only exists if you create a line item for an order with a vendor ID (which originally comes from …?) And if you order products from those vendors, it might make sense to record their names, adresses, contacts …

            • 3. Re: allow creation via relationship problem?
              user28097

              Very helpful. Thanks for the prompt and thorough feedback.

               

              -- Kevin

               

              - Kevin J. Rees, Consultant

              - Kelisys Technologies, Inc.

              - 352.735.5238 voice/fax

               

               

              From:  ErikWegweiser <noreply@filemaker.com>

               

              Hi, Kevin: It sounds like the relationship is properly constructed. However,

              there is no automatic mechanism for triggering the creatioon of the Vendor

              Associations record, just because a unique combintion exists in the PO

              Details (Line Items) table and the relationship from Details to Associations

              allows it. [note, I'm presuming that you don't want to simply do a lookup

              for the most recent price of the same item for the same vendor from Details

              alone, as the most recent price may not be the normal price you'd want to

              use next time]. Basically, any time a Detail record is committed, you want

              to set something from the Details table to the Associations table via that

              uniqueness relationship. How depends partly on the method used for creating

              the Detail records. You might have a button that creates the Detail record

              from a PO, in which case that routine could also create the first

              Associations record via the relationship. Or you might have an

              OnRecordCommit on the PO layout that loops through all Detail records,

              creating any unique Associations records. Just a couple quick ideas. [note

              also that Associations would not be automatically updated with the latest

              price of an item for a vendor without some logic programming to do so on

              your part]. Helpful?

              Reply to this message by replying to this email -or- go to the message on

              • 4. Re: allow creation via relationship problem?
                user28097

                Thank you very much for the prompt reply. I guess I misunderstood the

                description on page 111 of the User Guide which seemed to imply that it

                worked the way I expected:

                 

                "When this option is selected, entering data in a related field that has no

                corresponding related record creates a related record based on the match

                field in the current table."

                 

                -- Kevin

                 

                - Kevin J. Rees, Consultant

                - Kelisys Technologies, Inc.

                - 352.735.5238 voice/fax

                 

                 

                From:  erolst <noreply@filemaker.com>

                 

                The option means that you can use a portal to enter new rows/records; it

                doesn't mean ³Create a related record in this table whenever a record in the

                other table is created² (analogous to the ³Delete related records Š²

                option). This is probably for the better, because there would be no check if

                a vendor for that item and the specific price point already exists (which

                would be the case the next time you look up the data in this table to create

                new line items (aka Order Details) Š). You should script the creation of

                line items and check in time if that combination of item/vendor/price

                already exists, to prevent the creation of duplicate entries. It might be

                better to use a dedicated layout and processes to associate vendors with

                items and prices (as in ³define a vendor product catalog²) before you create

                an order. And speaking of which Š what's missing in your setup is a Vendors

                table; as is stands, a vendor only exists if you create a line item for an

                order with a vendor ID (which originally comes from Š?) And if you order

                products from those vendors, it might make sense to record their names,

                adresses, contacts Š

                • 5. Re: allow creation via relationship problem?
                  user28097

                  Should have added that I do have a Vendor table and Item table, just trying the describe the essential problem in my post.