Using Portals & Relationships to Determine Inventory Value

[jonjonthegreek]

I am trying to come up with a set of calculations to give me the inventory value of our raw materials, based on the most recent purchases.

I have 2 tables here, Purchases Line Items and Supply Inventory. Supply Inventory contains the fields Item_Name, Quantity_Remaining, and what will hopefully be a calculation for value of our remaining supplies. Purchases Line Items contains the fields Item_Name, Quantity_Purchased, Purchase_Date, Item_Price. The tables are related by Item_Name, sorted by Purchase_Date, because I want my inventory value calculation to assume that our remaining inventory items are the ones we purchased most recently (i.e. FIFO).

So for example, I have a record in Supply Inventory that lists SupplyA and 10 units remaining. On the Purchases Line Items table, I have several records that show my purchases of SupplyA: 2 units purchased on 10/10/2012 at $10, 7 units purchased on 9/10/2012 at $12, and 5 units purchased on 8/10/2012 at $8. Therefore, I want my calculation to show that the value of my 10 remaining units is $112 (2*10+7*12+8*1)

I simply cannot figure out how to make this work via a portal, calculation, or otherwise. Any help would be much appreciated. I am using FMP 11 Advanced.

[comment]

Ouch. This is not going to be easy. You need a recursive process - either a script or a custom function - to go over the related purchases, until you reach the remaining amount. Lot of work and a plenty of room for error. Are you sure you need this?

[jonjonthegreek]

Thanks. I figured we would need something like that, but I'm not sure it's worth the work. Appreciate the reply.

["... you mean these fans?"]

Has anybody given you a solution to this yet??

 

If not, let me know.

 

I had to do the same thing and I can help you with this.

 

It is pretty simple.

 

Thanks.

 

Tom