4 Replies Latest reply on Apr 2, 2013 5:40 PM by CStovin

    Better methods for relating Line Item fields ??



      Better methods for relating Line Item fields ??


           In my CONTACT -->ORDER-->LINES<---INVENTORY

           My portal on the ORDER layout is using records from Lines, but fields from INVENTORY are placed in it.  I'm guessing this is standard fair to help minimize file size so you are not copying redundant data...

           However after working and developing, I realized there are times when I might update or change certain INVENTORY fields such as an Internal Serial#, or a Grouped Set #.  If I do this on the Inventory end it  automatically updates the information in the ORDER::Lines Portal   which I would rather it not do.     I have an Order History and it would be nice to see if certain fields have ever been changed  over the years.

           To accomplish this I still left my relationship graph the same, but created "redundant," fields in Lines with similar names to the fields I was using from Inventory.  Such as INVENTORY::category is now , LINEs::category in the portal.  In the Field options for ( LINES::category  = INVENTORY::category )  and unchecked do not replace if any field....

           Will my database grow too large this way reducing efficiency, or Is their a better way to program this ?



        • 1. Re: Better methods for relating Line Item fields ??

               Well, the fields may look redundant but they really aren't. When you need to capture a "snapshot" of the data in a related field you aren't just making a copy of the data, you are preserving the value of that field at the time you needed that value because future changes to the value in the related table are not relevant and will produce incorrect results. The classic case where this is done is for unit prices looked up from the inventory table. You don't want a price change to affect the data on past invoices so you copy over the current unit price at the time you choose a product ID in the line items table.

               Often, businesses also want client contact data such as a billing or shipping address to be copied as well so that they can be sure to accurately handle issues that may arise when a client complains that they didn't receive a shipment and a review of the invoice record reveals that the shipping address used differs from the client's current address due to an update of their contact info that took place after the order was placed...

               Choosing between looked up (copied) and linked data in your database design is thus one of the standard design decisions you have to make and are usually driven by the needs of the client.

               In addition to the auto-enter calculation that can be used to copy over the values, a looked up value field option in the same dialog can be used. It's a touch more cryptic to set up, but enables you to use the "Relookup" menu command or script step to force FileMaker to copy over a new value from the related table--which can occasionally be a useful feature.

               Yes, adding in these fields will increase the size of your file by a bit, but hard drive capacity these days is measured in gigabytes so the size increase isn't likely to be a major issue for your file.

          • 2. Re: Better methods for relating Line Item fields ??

                 Thanks Phil,  I thought that might be the case and appreciate your confirmation.

                 ON a side note, does it make a difference if I create a LInes::notes field,  or a Inventory::notes field   to be placed in the Lines Portal.


                 This field would be for adding new general information for each product.  If it were housed in the Inventory table, the field would only appear in the Inventory Repair history - which is created through creating a work order as well.

                 My work Order table ->Lines is for creating employee rentals mostly, but I can also use the work order to create a repair work order as the w/o would pull contact info from the contacts table as well.... same thing just a different contact::job_category

                 I created a LINES::notes field and a INVENTORY::notes field both for this same purpose.  I place one at a time in the LINES portal, and the same corresponding field in the INVENTORY  repair history portal layout,  and could not see any difference.... at lease none that I was aware from my brief testing....

                 does it matter ?


            • 3. Re: Better methods for relating Line Item fields ??

                   It makes a difference.  A notes field defined in Lines will be specific to that line item record on that order. The notes field for product will be specific for all products ordered with a given Product ID. I would only define a notes field in Line Items if at least one of the following is true:

                   a) Future changes to Product::notes cannot be allowed to change what is shown for notes in a portal to Line Items.

                   b) The user needs to be able to edit the notes field in the LIneItems portal to create a note specific to that order.

              • 4. Re: Better methods for relating Line Item fields ??

                     cheers!!!  Makes sense.