1 Reply Latest reply on Dec 8, 2011 8:36 AM by mgores

    Problems with repeating fields populating based on calculated lookups

    isjigsaw

      Title

      Problems with repeating fields populating based on calculated lookups

      Post

      I have two databases.  One for jobs and one for inventory.

      In the jobs database I have multiple layouts, including one for invoicing.  The invoicing database consists of repeating line item descriptions and corresponging product codes and unit costs.

      The line item descriptions are the relationship between the two databases.

      What I want to do is have FMP look up the product code and unit costs from the inventory database when a line item is entered and automatically erase it if the line item is removed  (I do not want our sales staff to be able to edit or change the product codes or unit cost fields).  If a line item is entered a simple lookup will populate the corresponging fields; however if the line item is removed the corresponding fields remains populated from the initial lookup.

      To try and solve this I chose to make the auto entry a calculation. Here are my two attempts:

       

      If (IsEmpty ( inv description Code ); ""; Lookup ( Inventory::Product_Code))  This is the calculation for the product code

      or

      If ( inv description Code ≠ ""; Lookup ( Inventory::Price ); "")  This is the calculation for the unit price. 

      These are the two calculations I tried, and they work like a charm on repeat one of the repeating fields.  However on the subsequent repeats it does not work.

      The line item field drop down lise is populated from the corresponding field in the inventory database.  Therefore I know that the line items match as the only options available in the dropdown are line item descriptions from the inventory database.

      I should also note that the inventory database has individual records for each product that would be entered as an individual line item on the invoice in the jobs database.  There for each of the lookups initiated from the entries in the line items descriptions would reference a different record in the inventory database.

      It seems to me that this should be a simple thing to accomplish, however it has been perplexing me for a couple days now.

      Any help or suggestions would be appriciated!

        • 1. Re: Problems with repeating fields populating based on calculated lookups
          mgores

          A better approach would be to avoid the repeating fields.  An intermediate table (or join table) called "line items" that is related to Jobs by JobID and related to inventory by ProductID.  The use a portal to the line items table to display the products from inventory.

          Using this method it is possible to make it so that items that are out of inventory will not be available to enter on that layout and no lookups are necessary.