Filtering Calculated Un Indexed Value Lists

[alydewji]

Dear All,

 

I am designing a database for my workshop.

 

The system lets users generate LPO Master (Table) with LPO Lines (Table linked to LPOs).

 

An LPO Master can have many LPO Lines.

 

LPO Lines have quantity values of spares ordered (LPOQtyOrdered).

 

After an LPO Master is made, goods ordered, goods received, a Goods Received Note (GRN is made).

 

This is stores in a GRN Master (Table) with Bin Card Lines (Table linked to GRN Master for Stock in, Table linked to Req for Stock Out).

 

You can order 10 bolts, receive 9 bolts today and 1 bolt tomorrow.  So you can make 2 GRN Masters for 1 LPO Master.

 

You can order 10 bolts @ 1 USD and receive 9 bolts @0.95 USD an 1 bolt @ 1.05 USD.

 

The LPO Lines has a field called QtyLeftOnOrder, which deducts the amount received in the GRN.  

 

The LPO Master has a field called LPOStatus that calculates Case(sum(QtyLeftOnOrder)>0;Full;Empty).

 

When creating a GRN, the first thing you do is select an LPO for which the GRN is made from a drop down list.

 

So,

 

Case 1:

LPOLines:LPOQtyOrdered may be 10 Bolts (lets forget about the price)

LPOLines:QtyLeftOnOrder is 10 Bolts

At this Point, LPOMaster:LPOStatus=Full

 

Case 2:

Now you create a GRN Master and receive 9 Items,

At this Point, LPOLines:QtyLeftOnOrder=1

At this Point, LPOMaster:LPOStatus=Full

 

Case 3:

Now you create another GRN Master and receive 1 Items,

At this Point, LPOLines:QtyLeftOnOrder=0

At this Point, LPOMaster:LPOStatus=Empty

 

What I want is that since there will be over 1000 LPO Master that will be created over the year, I want only the Drop Down List to only filter out LPOs whose LPOMaster:LPOStatus=Full.  I want Case 1 and Case 2 display, but Case 3 not to display but currently none display if I filter the list.

 

But somehow, FIlemaker is refusing since this LPOMaster:LPOStatus is based on a calculation and so cannot be indexed.

 

Anyway I can work around this?

 

In the past I have managed to de-active an LPO manually by using a switch where someone makes it active or retired, this seems to work in a value list since the field can be indexed as it is not auto calculating but rather requires me to input manually ACTIVE or RETIRED Status by pressing a button.

 

Please let me know.

 

Thanks,

 

Aly

 

[Fitch]

I would suggest you make the LPOStatus field a stored value. Use script triggers if needed to update it.

You should consider doing this for other fields as well, or your system will bog down as you add more and more records.

 

Welcome to the forums.