How to get around unstored calculations in relationships...???

[angelleye]

I've got one table called Products and another table called Scans. These tables are related by a field called ItemNumber.

As such, when I look at an item's information in my Products layout I can see all of the related Scan records associated with that item. This is good so far.

There is a field in Scans called StatusCalc that is an unstored calculation and ends up with one of the following text values: Post, Posted, Sold, Oversold, RMA, Tech, Stock.

So back in my Products layout I have my list of Scan records and I have to sort through all of the Sold stuff mixed in with all the stuff I still have in stock. I'd like to separate these out into 2 separate lists.

I began by creating the first new table occurance called ScansSold. The idea was to relate this by ItemNumber to Products just like Scans is, but then also add another relationship where Products::zgSold (a global text field with the value Sold) equals Scans::StatusCalc.

When I make the portal look at this new table occurance, though, nothing shows up on the list even though I can see with Data Viewer that both values Sold.

I was informed that the reason it's not working is because StatusCalc is an unstored calculation and you cannot use those with relationships.

So how am I suppose to get around this? I can't create another new field based on StatusCalc because that new can't be indexed either since StatusCalc isn't. I can't change the fields that StatusCalc is using so that they're stored either.

Any information on how to get around this problem would be greatly appreciated. Thanks!

[David Jondreau]

Unindexable fields (ie unstored calc fields and global fields) can only be used on the "parent" side of the relationship.

Does the StatusCalc field have to be unstored? If it comes from a related table or uses a function like Get(CurrentDate) it does...if you keep it as a calc field.

You can make the field a non-calc field and change the value by script, not by calc. What makes a Scan "Sold"? Is it something that can trigger a script?