4 Replies Latest reply on Feb 22, 2016 6:41 AM by Handri

    Tracking a Product Record




      I have a a rental solution as it has Products, Invoices, Contact, Supplier.


      I want to track which is the product is the TOP 10 rented product.

      How do track the records ?


      In my opinion :

      1 . By adding a counter on each products and adding the value to the record as it appears on the invoice.


      How to implement this ?






        • 1. Re: Tracking a Product Record

          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 )



          • 2. Re: Tracking a Product Record

            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.

            • 3. Re: Tracking a Product Record

              Hi Jason,

              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 ?







              • 4. Re: Tracking a Product Record

                Hi Mike,


                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.