Problems with repeating fields populating based on calculated lookups
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
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!