Conditional Value List with Calc Field

[rushmore]

A Noob question. I have three tables

Products, InvoiceLineItems and Invoices

where InvoiceLineItem is a join table. Each InvoiceLineItem records a quantity of a product on that line of the Invoice.

The products table tracks the QuantityRemaining of a product using a calc field that takes the sum of the related InvoiceLineItem records.

On my invoice layout I have a portal with InvoiceLineItems. I'm using a value list to allow product selection via drop-down and would like to omit products in the value list with no inventory remaining. I just add a TO of Products and add a 'InvoiceLineItem::gZero < Product::QuantityRemaining' relation to help generate the conditional value list, right?

The problem I'm running into is this: since the QuantityRemaining of a product is dependent on an aggregate function of a related table, it can't be stored or indexed. Likewise the global Zero value. My relation link is bidirectionally inoperative.

I can change the global to a field, but the relationship then works in the wrong direction from what I need. If I change the QuantityRemaining from a calculation to an Auto-Enter number field, I can create a functional relationship and value list, but the auto-enter value is not updated when needed.

Clearly my FMP toolbox is still fairly empty, or I wouldn't be writing this. What simple workaround am I missing?

Thanks in advance.

Arlo

[bcooney]

Anytime I see a post about building an inventory system, I feel obligated to suggest that you consider a transactional system, where the aggregate values are stored. As the record counts increase, the system could get slower and coming up with a report that shows unstored calcs such as Qty in Stock, Qty to Buy, etc., will be inefficient.

As you have already determined, having these values stored will be useful in many ways. Have a look at Todd Geist's Transactional Model on FM Advisor. Also, consider nightly server scripts that set static fields to the unstored calcs, a "cleanup" in case your transactional model fails somewhere.

Search this site for inventory systems, too.

[rushmore]

Thanks for taking the time to reply. I will certainly look into the transactional model for an inventory DB and read Todd Geist's work on FM Advisor.

This is a great resource for those wanting to learn more, and I certainly do. The books I've used so far haven't gotten me out of the shallow end of the pool, so I'm looking for better info and finding it here. It seems like for every problem there's a thousand bad ways to solve it and several good ones too. Unfortunately I'm still at the stage where I'm just happy when my DB works, however inefficiently.

Arlo