How to add dynamic filter to ListView Dashlet

Hello. We have a custom module, called dim_pricelines that houses various alternative prices to be used instead of the listed price of a product. This of course is tied with foreign key -> primary key to ProductTemplates. Each product template can have N price alternatives. Now, I would like to use the ListView DASHLET so when a user opens up the Record View of a ProductTemplate, he can see all the alternate prices TIED TO THAT PARTICULAR PRODUCT. What the inbuilt ListView dashlet can do is just display ALL the prices, for all products. How can I dynamically filter out / restrict the listView dashlet to only display items that correspond to the selected product (technically the field product_id from dim_pricelines table must be equal to the GUID of the selected product)

Parents
  • Hi
    What extra information do you need to display in your Dashlet that isn't alr4eady displayed in the related subpanel for your custom module?

    .

  • I do not have a subpanel, cause when I define one in studio, it creates a new field in dim_pricelines table(basically duplicating the info) and points to a third, associative table (even if it's not n => n but 1 => n) and the documentation to query joined tables from Javascript is very lacking. That is why I have preferred dashlet. I solved it by creating a dashlet from scratch and using loadData(..) function to manually query the module and send it to the HBS template. Initially I wanted to levarge filtering to the existing ListView dashlet

  • As per my experience and SugarCRM development guidelines, it is not a good approach. If you create the standard 1-m relationship, it only creates a third table in the DB containing both module's primary keys. Sugar never creates any extra fields in the custom module table. So definitely no duplicate info at any level. Can you please explain what new field Sugar created while creating a 1-m relationship via studio? Are you referring to Flex relate field?

    Secondly, why would you want to query joined tables from javascript? What exactly you want to achieve with that? Keep in mind that SugarCRM is divided into Frontend and Backend frameworks. Javascript provides responsive UI (to support a variety of form factors) and uses modern, single-page client architecture. The query of joined tables and any DB level queries should be done in the backend framework via REST APIs.

    In the end, I would highly recommend using the sugar standard 1-m relationship in order to show the information in the subpanel instead of Dashlet. As it is more aligned with the application structure and would be upgrade-safe plus SugarCloud compatible.

    Kind Regards,

    Junaid

Reply
  • As per my experience and SugarCRM development guidelines, it is not a good approach. If you create the standard 1-m relationship, it only creates a third table in the DB containing both module's primary keys. Sugar never creates any extra fields in the custom module table. So definitely no duplicate info at any level. Can you please explain what new field Sugar created while creating a 1-m relationship via studio? Are you referring to Flex relate field?

    Secondly, why would you want to query joined tables from javascript? What exactly you want to achieve with that? Keep in mind that SugarCRM is divided into Frontend and Backend frameworks. Javascript provides responsive UI (to support a variety of form factors) and uses modern, single-page client architecture. The query of joined tables and any DB level queries should be done in the backend framework via REST APIs.

    In the end, I would highly recommend using the sugar standard 1-m relationship in order to show the information in the subpanel instead of Dashlet. As it is more aligned with the application structure and would be upgrade-safe plus SugarCloud compatible.

    Kind Regards,

    Junaid

Children
  • if (productData['product_template_id']!=null)
    {
    var priceline_bean=app.data.createBeanCollection("dim_pricelines");
    priceline_bean.fetch({
    fields: ["price"],
    filter: [
    {
    producttemplate_id_c: {'$equals': productData['product_template_id']},
    },
    {
    identifier:{'$equals':priceline_identifier}
    }
    ],
    success:function (priceline)
    {
    if (!_.isEmpty(priceline)&&!_.isEmpty(priceline.models)&&!_.isEmpty(priceline.models[0])) {
    productData['list_price'] = productData['discount_price'] = priceline.models[0].get("price"); //<---here
    }
    self._super("_onProductCatalogDashletAddItem", [productData]);
    },
    error:function ()
    {
    app.logger.error("Error retrieving priceline bean for priceline identifier: "+priceline_identifier+" producttemplate ID: "+productData['product_template_id']);
    self._super("_onProductCatalogDashletAddItem", [productData]);
    }
    });

    This is the code snippet that I use. Sugar definitely creates a new field in the table when you define a relationship in studio. If the producttemplate_id_c field is no longer present but it's in a 3rd table, I can no longer query it like this. Of course I could write an API and use SugarQuery class but why do it, when we have this ability in the frontend API.