value list based on relationship?

[Don't Panic]

I'm working on an inventory, invoicing, and customer database for an art studio.

the invoice table uses a seperate line item table with a portal on the invoice layout.

they only sell original artwork, so each record in the product table is unique and only has one in stock.

I want to create a value list that contains all records in the Product(Art) table that are not sold....

whats the best way to do this???

[IdealData]

As you already know if the item is available because it's stock count is 1 (one) then you could use a GLOBAL field in the parent table to match the on the current stock count with the value 1 (one).

You can set the global field manually, or scripted, and the value list should be defined to show only the records that match the global.

Conversely, if you set the global to 0 (zero) you will get all the products that have been sold!

[Don't Panic]

the problem im running into I think is the way im determining the stock status...

I have an Art display field that merges the artist, title, size, and medium. this field is the match field.

so then my sold field looks like,

ArtSold = Case ( ArtDisplay = LineItems::Item; "sold"; "" )

then i have

SoldStatus = Case ( Sold = "sold";0;1 )

the problem is that these calc fields are unstored and use related fields so i cant use them in relationships...

how can i make the product table update the inventory without needing to run a script when the invoice is done?

is there a better way to do inventory control?