I'm a fan of ExecuteSQL for these types of things. The calc might look like:
ExecuteSQL("SELECT COUNT(ProductID) FROM Invoices WHERE ProductID = ?" ; "" ; "" ; Products::ID )
Your basic thinking is sound. While you can use unstored calculations to provide summary information, they will perform poorly as the data load grows. Therefore, it's sometimes better to use a stored (indexable) number field and set it via script. Jason's ExecuteSQL method can be used for this purpose. You can also use summary functions or calculations.
Thank you for the advice. Yes I think thats the answer. But .... I am still like fresh to Filemaker and I dont understand the ExecuteSQL properly. But I will try my best to use those. By the way, what kind of field I have to provides to use this Execute SQL ?
and at the end, how can I make a layout / report for the top 10 products ?
Thank you for your advice. I am new to this, and I need to learn from a people like you . I will store it as an indexable field.