1 Reply Latest reply on Jun 25, 2014 7:38 AM by philmodjunk

    Is LOOKUP the correct function?



      Is LOOKUP the correct function?


           I use Filemaker Pro to track department expenses by fiscal year. The parent table is "Orders," this is orders that we place to vendors. The child table is "Invoices" for invoices received against the orders. The two table are related through the "OrderID" field. When I generate an order the FY field in "Orders" is automatically populated based on the order date. When I enter an invoice for that order the FY field in the invoice record is filled via the LOOKUP function from the "Orders" table. This works great until I need to change the FY in the "Orders" table for some reason, usually it's due to a typo on my part. If I change the FY on an order the FY field in "Invoices" does not update automatically. I have to go in and manually change the FY. Is there another function that will update records in "Invoices" automatically? Or, is there a script that will do the same thing?

           Thanks in advance for any insights.


        • 1. Re: Is LOOKUP the correct function?

               You've put your finger exactly on the issue that determines whether you should copy (look up) data from a related table or simply link to it dynamically. When you copy the data, subsequent updates in the original table don't update records that looked up that value--a useful feature when you are looking up prices for items and need to be able to change the price for future orders without it affecting the price on past orders. But when you dynamically link to the data, any change in the original record automatically updates for you.

               In this case, I'd just add the FY (fiscal year, I presume?) field from Orders to your invoices layout. The relationship you describe should allow you to add any fields from orders that you need to see on your invoice layout just be adding the fields directly to your layout. (I am assuming either just one order record for every invoice record or that all order records should show the same FY value.)