[SalesForce] SOQL query not returning any record even when it is present. Trigger failing due to this

I have a trigger where I have to Insert a new Record in the "CaseTeamMember" object whenever a new "Case" is created. For this I have to query the "CaseTeamRole = CSO" .

In my developer console when I give the query only as below then I get the CSO record.

Select Id,Name from CaseTeamRole

But when I try to fetch the Id of only the "CSO record then it is not fetching. I have tried queries in multiple ways as below :

1) Select Id,Name from CaseTeamRole where Name LIKE 'CS%' (Before this query used to get the CSO record. Now after sandbox refresh this is also failing)

2) Select Id,Name from CaseTeamRole where Name = 'CSO'

3) Select Id,Name from CaseTeamRole where Name LIKE 'C%'

If I give any other record then those are getting fetched .

This is not allowing me to even create a case now due to SOQL not fecthing any record in trigger 🙁

Please help me in understanding what might be the issue.

Edit
Trigger

Trigger CaseTrigger on Case (After Insert) 
{
    If(Trigger.isAfter)
    {
        If(Trigger.isInsert)
        {
            For(Case C : Trigger.New)
            {
                IF((C.Record_Type_Name__c == 'Delivery Escalation' || C.Record_Type_Name__c.startsWith('CSP')) && (C.CreatedDate == C.LastModifiedDate))
                {
                    CaseTriggerHelper.CreateNewCaseTeamMember(Trigger.New);
                }
            }
        }
    }
}

Class:

Public Class CaseTriggerHelper
{
    Public Static Void CreateNewCaseTeamMember(List<Case> CaseIds)
    {  
        List<CaseTeamMember> CTMList = New List <CaseTeamMember>(); 

        CaseTeamRole CTR = [Select Id,Name from CaseTeamRole where Name LIKE 'CS%'];
        system.debug('---CTR---'+CTR);

        Try
        {
            For(Case C : CaseIds)
            {
                CaseTeamMember CTM = New CaseTeamMember();

                CTM.ParentId = C.Id;
                CTM.MemberId = C.CreatedById;
                CTM.TeamRoleId = CTR.Id;
                CTMList.Add(CTM);
            }
            Insert CTMList;
        }
        Catch(Exception e){
            } 
    }
}

UPDATE

Should I pass a list of cases in my trigger handler in this way?

Trigger CaseTrigger on Case (After Insert) { 
  Set<ID> ids = Trigger.newMap.keySet(); 
  If(Trigger.isAfter && Trigger.isInsert) { 
    For(Case C : Trigger.New) { 
       List<Case> C = [SELECT Id FROM Case 
                          WHERE ((Record_Type_Name__c == 'Delivery Escalation' || 
                                  Record_Type_Name__c.startsWith('CSP')) &&
                                 (CreatedDate == LastModifiedDate))];  
       CaseTriggerHelper.CreateNewCaseTeamMember(C); 
    } 
  } 
 }

Thanks!
Ruchi

Best Answer

I observed the same issue in our full sandbox after a refresh. I believe it is a bug, though it is not yet on Salesforce's known issues list.

If you change your query to Select Id,Name from CaseTeamRole where Name != 'CSO', your query will probably return all results except the record where the Name is CSO.

If you change your query to Select Id,Name from CaseTeamRole where Name like '%s%', your query will probably return two records: CSO and Global Sales Manager.

It looks to me that the results only return if > 1 row is in the result set.

The workaround that could work for you is to query for all CaseTeamRole records and then loop through each result looking for Name = 'CSO'. For example:

for (CaseTeamRole ctr: [select id, name from CaseTeamRole])
{
 if(ctr.Name == 'CSO')
 {..do stuff.. }
}

If you happened to open a case with Salesforce, would be curious to know if they confirmed whether it is a bug or not.

Related Solutions

[SalesForce] Before Insert Trigger – Query Only Related Records To Incoming Record

What exactly is your requirement? it is a little hazy to understand. As far as I can tell you want to do the following.

Check the field Flag_c when an MyActivity_c record is inserted where the recordtypeID on the inserted record is "Record Type Name" and set it to No if its value is yes
Gather a list of existing MyActivity_c records where the recordtype is "Record Type Name" and the field "Contact_c" matches the original inserted record.

A couple of questions

Is Flag__c a Checkbox? maybe consider if it should be (true,false) rather than "Yes" / "No".
Why are we checking if the field is Yes before setting to No, if the field is No already there is no difference.
What do you want to do with the list of existing MyActivity__c records once you have them?

That being said there are a number of issues with this trigger, starting with the fact that you are executing a SOQL query within a for loop which is against best practice. Always try to execute queries outside of any loop structure.

Also when you query a Recordtype you should also try to query the objecttype not just the name and preferably the developer name (Usually the same as name with underscores, but the name may change, hence why its good to query developername)

Here is an example of how you might write this trigger based on what I can gather from your requirements, with comments explaining step by step.

trigger MyTrigger on MyActivity__c (before insert)
{

    // a map to store your Contact__c to related MyActivity__c records
    map<Id,list<myActivity__c>>     mapIdtoActivities   = new map<Id, list<myActivity>>();

    // OR you just get one big old list depending on what you want to do with them
    list<myActivity__c>             listOtherActivities = new list<myActivity>();

    // Query the correct recordtype
    ID rtId = [SELECT Id FROM RecordType WHERE sobjectType='MyActivity__c' AND DeveloperName = 'Record Type Name'].Id;

    for(MyActivity__c r: trigger.new)
    {
        // For the activity records in the trigger context that have this recordtype
        if(r.RecordTypeId == rtId)
        {
            // set the flag to no
            r.Flag__c == 'No'; // Or false if you change to checkbox

            // Create a key value for each Contact__c
            mapIdtoActivities.put(r.Contact__c. null);
        }
    }

    // Query the other activity records related to the ones in your trigger (map keys & recordtype & flag = no)
    for(MyActivity__c OtherActivityRecord : [   SELECT Id, Contact__c FROM MyActivity__c
                                                WHERE RecordTypeId = : rtId AND Flag__c = 'No'
                                                AND Contact__c IN : mapIdtoActivities.keyset()])
    {
        // NOW depending on what you want to do with them you can either create multiple lists per contact i.e.

            // Check if we have already created a list for this contact__c record
            if(mapIdtoActivities.get(r.Contact__c) == null)
            {
                // There is no list of Activities so create one and add the first activity record to it
                mapIdtoActivities.put(r.Contact__c, new list<MyActivity__c>{OtherActivityRecord});
            }
            else    
            {
                // There is a list of Activities so add to it
                mapIdtoActivities.get(r.Contact__c).add(OtherActivityRecord);
            }

         // OR you can just dump them all in one big list

         listOtherActivities.add(OtherActivityRecord);
    }

    // Do something with your shiny new lists here.


    // no need to query the size anymore so I am told
    //if(contactrecords.size()>0)
    //{
        update listOtherActivities;
    //}   
}

[SalesForce] SOQL help with subquery where clause

SELECT Id, Name, CreatedDate, (SELECT Name FROM Status_History__r ORDER BY CreatedDate DESC LIMIT 1)
FROM Service_Order__c WHERE id IN (SELECT Service_Order__c FROM Status_History__c WHERE Name = 'Order Completed')

The above should work I believe and worth trying

Best Answer

Related Solutions

[SalesForce] Before Insert Trigger – Query Only Related Records To Incoming Record

[SalesForce] SOQL help with subquery where clause

Related Topic