How do I display a product name and category on related module

I have added a related field on the Opportunities (renamed to Pipeline in our case) module for selecting a product from our catalog.

This works, but I need to update the name of the Opportunity to a concatenation of the Product and Category names. The product name is available through the related field, but I cannot find how to get the category name with sugarlogic.

The product categories are also not available through studio, so I can't just use another 'relate' field.

How will I be able to access both the product name and the category on the Opportunities page?

0 Lokesha L N over 8 years ago

Hi,

There is a relationship between product category and product module right? You can get name of category from Product. Once you get the category and product name , you can update the name of opportunity.
0 Derick van Niekerk over 8 years ago in reply to Lokesha L N
Thanks Lokesha L N, but I'm not sure I understand this answer.
The name gets updated with sugarlogic. I have the name of the product as a related field, and the Name will be updated with something like this:
related($producttemplates_opportunities_1,"name")
...which would yield me the product name, but the category name is in a different table and does not seem to be available in studio.
How do I get the category from the product using sugarlogic?
0 Lokesha L N over 8 years ago in reply to Derick van Niekerk

Try to write logic hook for getting product name and category by join query with product and Category module.
Use this query in Logic hook
SELECT p.name as product , pc.name as category FROM `opportunities_cstm` o join product_templates p on o.`producttemplate_id_c` = p.id join product_categories pc on p.category_id = pc.id WHERE p.deleted=0 and pc.deleted=0
Hope it helps
0 Derick van Niekerk over 8 years ago in reply to Lokesha L N

Thanks Lokesha L N, this is what I have done as a work-around, but I would have liked this to be in SugarLogic so the UI can update dynamically before the user hits the save button. At the moment this is the functionality they are used to (easy before because product and category are standard drop downs)
The logic hook does not give any feedback until after the save has been completed.
I am accepting this answer because it looks like there is no way to do it with SugarLogic and, even though it's not ideal, it does solve my problem, thanks .