3 Replies Latest reply on Mar 14, 2016 6:56 AM by beverly

    To show products and last purchase date on a portal


      Good afternoon


      I have standard solution with following scheme


      COSTUMER/ costumer_ORDERS / costumer_orders_LINEITEM / costumer_order_lineitem_PRODUCT.


      I  have a portal showing all products from a client. I can do that by using records from the last TO (costumer_order_lineitem_PRODUCT). This is not a problem.

      Instead of this I would like to have a portal showing:


      Product     Last purchase date (of the product)     Last price (of the product)


      To do this, I think I should use the data from (costumer_orders_LINEITEM). I guess I should filter the portal in order to show only the last LINEITEM record containing each product. Or perhaps there's another way to do. I guess this must a common request.

      Can anybody help me on that?.


      Thanks for your help.

        • 1. Re: To show products and last purchase date on a portal

          If you have a lot of data, a portal filter could be slow. Plus a portal filter is static - hard coded: You can't change it, AFAIK, in code (it's not object oriented, for example).




          Another way to do this would be to use GTTR (Go To Related Record) to the portal, do a Find, and display what you need there in the "many" table. If you need sums and such in the many table, you could create a calculated field in the parent table or use a Summary Field in the many table.


          Just an idea of how I might approach that.


          - m

          • 2. Re: To show products and last purchase date on a portal

            Filtered portal will not scale too well and the calculation you'd need would be expensive. For academic sake, the calculation would be something like "lineitem PK = first PK from sorted list of lineitem PKs for the same product/customer."


            One method may be to use a global key relationship. You would create something like "costumer_order_lineitem_product_LINEITEMsameCustomer." It would match costumer_order_lineitem_PRODUCT::_gk_CustomerID = costumer_order_lineitem_product_LINEITEMsameCustomer = _fk_CustomerID and also on the product ID. (Yes, you'd have to make the Line Item table also redundantly store the Customer ID, but this could be a lookup from the Order table probably.) The relationship would be sorted by timestamp descending, so that the first related record would be the last line item created for that customer for that product. To get that to work, when you load the customer record, set the global foreign key _gk_CustomerID on the PRODUCT table for customer. I'm not sure how well this would scale but if I'm not mistaken this would let you use your existing portal, and not have to use a portal filter.


            Another way might be to create a field on lineitem that stored the flag "is last time this product was ordered by this customer." Then it would be up to the solution to ensure that flag was set and updated properly, but it could then be used in a filtered relationship and be relatively efficient.

            • 3. Re: To show products and last purchase date on a portal

              true! portal filtering can be slow, however variables and global field selections can be used in the filter to make it "dynamic". This does not improve speed for complex filters, but can be used.


              A native find can sometimes be the best step!