Portal Calc Display

[Anuviel]

I would like to accomplish the following:

I have a table called Products. Three fields In_Stock, Reorder_Level and Order.

In_Stock is a calculation that calculates how many items there is in stock based on sales and so on.

Reorder_Level is basically a manually entered number that sets the inventory level at which the item needs to be ordered again.

Order is a calculation that does:

Case (



Products_Item_Quantity_In_Stock ≤ Products_Item_Quantity_Reorder_Level ;

Products_Item_Code ;



""



)

I have a sort of a home page, starting point that giver a brief view of things needed to be taken care of as well as a brief daily reports and such.

That layout does not display records from table Products mentioned above but from different table (Home table). I set up a relation between Home and Products table to be A = A, both are global unique numbers (that is probably my mistake).

In Home layout I have a portal that displays records from Products table - only field Order is in the portal. Now when that fields = Item_Code as seen in the calculation above that means that the item needs to be ordered. When it is empty, enough of the item is in stock. My problem is that all records are pulled into the portal (as per relation) however I would like to pull only the records that are out of stock.

My portal looks like:

M1

M2

(empty row, not empty but the field has nothing in it as the item is in stock)

M3

How do I go about getting rid of the records that are not out of stock from being displayed in the portal?

Hope all this made sense.,

Thank you kindly.,

[Ender]

How do I go about getting rid of the records that are not out of stock from being displayed in the portal?

It's important to understand that the match field on the many-side of a relationship has to be stored and indexed. That means you can't use a calc that references related values (or globals). But in this case, it's exactly such a value that would be useful to include as a criteria in the relationship (show only those related records that have a current stock level > 0). However, like I said, such a relationship isn't possible because the current stock would depend on the related In Stock value.

A solution is to make a stored copy of the In Stock field. You could then reference that in the match field's calc. The trick then is to find a good way to keep that In Stock field updated as the totals of the related sales and purchases change.

We've discussed various techniques for dealing with this a number of times in the past. This thread seemed pretty productive:

http://fmforums.com/forum/showtopic.php?tid/171928/

[Anuviel]

Thanks, that helped a lot.

I got it, I just made a new number field and made a script which goes through all of the records and sets that number field to In Stock field value. The new field is fully indexed and portal shows only the records that I want.

I also made a button which will run the script as required.

Right now I have about 1000 records and the script is pretty fast - will it pose problems as the number of records increases?

[Ender]

Yes, it will get slower as the number of records increases. That's why we talked about other methods.

[Anuviel]

Thanks, see that now. Well, this will have to work for me for now., don't have time to try the other solutions but will as soon as I can.

thank you