I am new to FileMaker. I currently have an eCommerce solution that tracks sales, purchase orders and inventory for my online store. When entering in purchase orders, you are given a scripted button that adds a record to the inventory table and sets the SKU (id), unit price, QuantityIn (dialog input that defaults to purchase order quantity), QuantityOut(sum of sales quantities) and QuantityAvailable (QuantityIn - QuantityOut) fields. The issue I am having is I want to display the cost in the sales layout so I can calculate profits, but there are several purchase orders (re-orders) with the same SKU and different unit cost. I need to somehow display the unit costs for the older inventory dates and rollover to the next inventory record once I am out of stock to show the next re-order unit cost. I should mention that the sales table is imported and populated monthly by a sales report csv and never entered/adjusted manually.
The relationship is Sales::SKU ------ Inventory::SKU ------- Purchase order::SKU