0

I have a Location object with a related list called Product Items on the Location. I would like to display the same Product Items on the Service Territory Member object.

  • Service Territory Member has a master-detail to Service Territory.

  • Service Territory Member has a lookup to Product Item and Location.

  • Service Territory Location has master-detail to Service Territory.

  • Service territory Location has a lookup to Location.

My first choice was to create a Product Item related list on the Service Territory Member, but it doesn't show up in the page layout related list section. So I'm trying and do it from code. The SOQL query is not returning any data. Is there a better way to write it?

public with sharing class ProductItemController {
    
    @AuraEnabled(cacheable=true)
    public static List<sObject> getProductItems(Id recordId){
        ServiceTerritoryMember stm = [SELECT Id, ServiceTerritoryId FROM ServiceTerritoryMember WHERE Id = :recordId];
        ServiceTerritoryLocation stl = [SELECT Id, LocationId FROM ServiceTerritoryLocation WHERE Id = :stm.ServiceTerritoryId];

        List<ProductItem> products = [SELECT Id, Quantity_Reserved__c, Quantity_Remaining__c, ProductName
                                     FROM ProductItem
                                     WHERE LocationId = :stl.LocationId]; 
        return products; 
    }
}
3
  • If you have a lookup to ProductItem on ServiceTerritoryMember then you should be able to add the related list. Are you unable to add it as an option when editing your page layout for ServiceTerritoryMember? Dec 19, 2020 at 2:46
  • yes, that's right Zack. ProductItem does not show up in the related list on the page layout. Dec 19, 2020 at 3:51
  • Since Service Territory Member has a lookup to Product Item, can you query the related list and then return stm.Product_Items__r? The query would be something like ServiceTerritoryMember stm = [SELECT Product_Items__r FROM ServiceTerritoryMember WHERE Id = :recordId] developer.salesforce.com/forums/?id=906F0000000BNe7IAG Dec 19, 2020 at 6:30

1 Answer 1

2

The Object relationship that you have described can be represented as given the image below.

enter image description here

Primary purpose of related lists is to display many associated records for a given record and the related lists are available in Salesforce for the following type of objects:

  1. In the Master-Detail relationship, master object will have related lists for displaying child records (since, a single master record will be associated with many child records).
  2. In a (1-to-many) lookup relationship, the object that is associated with many records will have the related lists. For example, Object A has 1-to-many relationship (or associated records) with Object B, then Object B is will be available as related list on Object A. It doesn't make sense to have a related list for Object A on Object B (just for the sake of displaying 1 related record).

Assuming that my interpretation of the schema design is correct, you could see that Product Items can be a related list on Location, Service Territory Member can be a related list on Product Items etc. But, Service Territory Member will not have any of the other objects as related list.

With the given schema diagram, you can see that a Service Territory Member record is not restricted to be associated with a Product Item which is strictly defined via Service Territory > Service Location > Location > Product Item (i.e. unless you have facilitated that via customization). In fact, there could one set of Product Item records associated via Service Territory, but a totally different set of Product Item records associated directly with Service Territory Member. I believe this is reason behind your SOQL query not returning any result set.

In your case, a Product Item record can have many associated Service Territory Member records, but a Service Territory Member will have exactly one associated Product Item record. You could do a simple query as follows, but that would return a single Product Item record (owing to the current schema design).

SELECT ProductItem__r.Id, ProductItem__r.Quantity_Reserved__c, ProductItem__r.Quantity_Remaining__c, ProductItem__r.ProductName 
FROM ServiceTerritoryMember 
WHERE Id = :recordId

IMO, you could simple display the associated Product Item lookup field in the Service Territory Member detail page instead.

On the contrary, if you are trying to display all Product Item records that are eligible to be associated with a Service Territory Member record, then you probably need to check if the correct data is in place already or not (because your code seems fine, although they could be done in 1 or 2 SOQL queries).

7
  • thx Arut for the very thorough reply. I popped in your query but received the error "Did not understand relationshp..." when attempting to push it to the org. I tried a similar query and it return all ProductItem records but once I added the WHERE Id = :recordId clause, it did not return any records. Do you know why this happend and how I can resolve it? Here is my new query: Dec 21, 2020 at 1:03
  • ``` List<ProductItem> productList = [Select ProductName, QuantityOnHand, Quantity_Remaining__c, Quantity_Reserved__c From ProductItem WHERE Id = :recordId ]; return productList; ``` Dec 21, 2020 at 1:06
  • The answer is based on my interpretation of schema explained in your post and I wouldn't know a definitive answer unless I look into the org (but that's outside the scope of this question & site guidelines). Its possible that the actual schema design is different that my interpretation (or may be I have some incorrect names in the query). You should check the actual names of sobject & fields and run the query first to check if its working fine. Use my answer to understand how object relationships & related lists work and try to work out the correct solution.
    – arut
    Dec 21, 2020 at 4:16
  • As the for the new query you have posted, I believe the recordId is the Service Territory Member record ID (based on the code snippet given in your question)? So, no wonder it doesn't return any records when you use it in the WHERE Clause while querying ProductItem.
    – arut
    Dec 21, 2020 at 4:21
  • The Service Location object has 2 related list: (1) Service Territory related list and (2)Service Territory Member related list. The LWC ProductItem component will live on the Service Territory Member and that's why I included it in the WHERE clause. For the time being, I did add a ProductItem lookup field on ST member object as a work around for now. This Work Order app is built on the Lightning Field Service framework and it can get pretty convoluted. I appreciate your suggestions. Dec 24, 2020 at 1:15

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .