7 Replies Latest reply on Nov 25, 2008 7:41 AM by TSGal

    Relationship Not Working

    robulack_1

      Title

      Relationship Not Working

      Post

      A customer has authorized products which have prices that are calculated on a current basis. Now I want to set up an order form that will select the appropriate product and pricing. I can select the correct product but unable to get the correct price from the auth table. I've attached the relationship table.

        • 1. Re: Relationship Not Working
          TSGal

          robulack:

           

          Thank you for your post.

           

          Unfortunately, your relationship table image does not display.

           

          Let's assume you have the following tables:

           

          INVENTORY

          *Product ID

          Description

          Price

          Calculated Price

           

          ORDERS

          Order ID

          Customer Name

          *Product ID

          Quantity

          Extended Price

          Subtotal

          Tax

          Shipping

          Grand Total

           

          -------

           

          For this example, I'll assume the "Product ID" field is the key field (marked with an asterisk) that links the two tables together.

           

          In the ORDERS table, if you enter a "Product ID" that exists in INVENTORY, then all information from that record should be available to the ORDERS table.

           

          Can you give me an example?  Please be specific where the information originates.

           

          TSGal

          FileMaker, Inc. 

          • 2. Re: Relationship Not Working
            robulack_1
              

            Don't know what happened to my first response but here I go again:

             

            Customer - CustomerID

             

            CustPurch - CustpurchID

            ItemIDfk

            CustomerIDfk

            Price 

             

            Order - OrderID

            OrderItemID

             

            Item-  ItemID 

            Price

             

            OrderItem OrderItemID

            OrderIDfk

            ItemIDfk

             

            On the orderitem, for orderitemIDfk, I have a value list that looks at the custpurch table and lists products authorized for the customer. I can get the correct item, however it does not select the correct price. It is usually the price for the first item for the customer in the custpurch table.

             

            • 3. Re: Relationship Not Working
              TSGal

              robulack:

               

              Thank you for the clarification.

               

              I'm assuming there is an inventory file.  That is probably through the "ItemID" field, so you should be pulling the Price from that link; not from another table.  It sounds like you are pulling from the custpurch table, which would be items that the customer already purchased; not from the inventory file.  Does that make sense?

               

              TSGal

              FileMaker, Inc. 

              • 4. Re: Relationship Not Working
                robulack_1
                  

                There is no inventory file as it is not tracked. There is an item file which stores all of the data about the item including 9 prices for the item depending on the location and shipment load. This is the basis for calculating the price that goes into the custpurch table for each item.

                 

                The custpurch file is a list of what is authorized to be purchased by the customer, not a table of what has been purchased,  and also includes a price override field for the item. So, if the calculation returns a price of $30 and the override field is $31, the the printed price (stored in custpurch) which is also the invoice price will be $31.

                • 5. Re: Relationship Not Working
                  TSGal

                  robulack:

                   

                  Thanks for the additional information.

                   

                  It sounds like you have the 9 prices in separate records, and you need an additional key besides just the ItemID.  There needs to be an additional key field that narrows the search down to the correct price.  You say this is dependent on Location and Shipment Load, so that key needs to be calculated first so you can link it to the Item file and retrieve the correct price.  Does that make sense?

                   

                  What is the criteria needed to determine the correct Price?

                   

                  TSGal

                  FileMaker, Inc. 

                  • 6. Re: Relationship Not Working
                    robulack_1
                      

                    The 9 prices are in 1 record. The calculation for the correct price for the item for the customer is done at the type we create the authorized item for the customer in the custpurch table.

                     

                    I have created a second instance of the custpurch table and am able to pick the correct price for the order, but not the correct itemID.

                     

                    • 7. Re: Relationship Not Working
                      TSGal

                      robulack:

                       

                      If the nine prices are in one record, and there is one itemID per record, then if you cannot pick the correct itemID, then your relationship is not built on the itemID.  Make sure the itemID is linked to the appropriate itemID in both tables.  Then, at least you are on the right record, and then with your calculation, retrieve the correct price.

                       

                      TSGal

                      FileMaker, Inc.