4 Replies Latest reply on Dec 23, 2013 12:00 PM by margagb

    Lookup and relationship problem



      Lookup and relationship problem


           I've spent all afternoon trying to get lookups to work, without success. I want to create receipts for orders that are being fulfilled. The open order line items from the order need to be displayed in the receipt portal, so that I can change the quantities in the receipt if there is a discrepancy between the quantity ordered and the actual received quantity. The quantity in the order should not get updated when I change the quantity in the line item of the receipt. This is what I cannot get to work. Whenever I change the receipt the order changes also.  I have tried I don't know how many different relationships between the tables… but nothing seems to get it right. See the attached image.. Thanks.


        • 1. Re: Lookup and relationship problem

               It would seem that your database is doing exactly what you designed it to do instead of what you want it to do.

               You'll need to do something to separate the values for Quantities ordered and Quantities received. Some businesses make two records for that--the first is the order record and shows the quantities ordered the second duplicates much of the same data but computes it's own totals based on quantities received. Some systems keep all these records in the same two tables, others have a table for orders and a table for receiving. With either approach, you can set up relationships linking an order to it's received shipment such that you can see and compare the quantity ordered with the quantity received in order to note discrepancies between them.

          • 2. Re: Lookup and relationship problem

                 Thanks for your quick response. I have tried to do that, but I do not wish to re-key all the line items that are in the order into the receipt. I'd like the quantities and line items to be retrieved from the order line item table and copied into the receipt line item table… and update the quantities if needed without the quantities changing in the order line item table. I thought I could do that with lookups… but regardless of what I try, the order line item table gets updated with the new quantity also.

                 The FileMaker Pro help gives an example of how to use lookups, i.e. use a lookup to copy the price of an item at the time of purchase into an invoice table. Even if the price in the related table changes, the price in the invoice table stays the same…  

                 Something like that I want to accomplish with the receipt line item table...

            • 3. Re: Lookup and relationship problem

                        but I do not wish to re-key all the line items that are in the order into the receipt.

                   That should not be necessary. Scripts can be used to generate a Received Order record with the correct LineItem data for you, but even that might not be necessary.

                   One simple option that occurs to me at this moment is to simply define two quantity fields--one for Ordered and one for Received. Enter the quantities ordered in one field and the quantities received in the other. That would make it very easy to compare the two. You can use one layout for ordering that just shows the Qty Ordered field in the line items portal. A "order recived" layout can include both fields side by side so you can easily spot discrepancies.

              • 4. Re: Lookup and relationship problem

                     Thanks for your answer. Much appreciated.