function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
GRStevenBrookesGRStevenBrookes 

Complicated Query/Trigger Help - A Challenge!!

Hi,

 

This seems quite complex to me, but I am sure one of you experts out there will see this as easy!!

 

I have a number of custom objects:

 

  1. Service_Agreement__c
  2. Training__c
  3. Employee_Training_Action__c (Joining Object - joining Employee__c and Training__c)
  4. Employees__c
  5. Operator_Skill_Level__c
  6. OSLSA_Join__c (Joining Object - joining Service_Agreements__c and Operator_Skill_Level__c)
  7. EmployeeOSL_A__c (Joining Object - joining Employees__c and Operator_Skill_Level__c)

Effectivly where I am at the moment is that when a value is checked on Service_Agreements__c a new Training__c record is created linked to the SA. Also, The Service_Agreement__c is linked to one or many Operator_Skill_Level__c records through the OSLSA_Join__C object.

 

Each Employee__c record is also linked to one or many Operator_Skill_Level__c records through the EmployeeOSL_A__c object.

 

What I need, is when the Training__c record is created, the relevent Operator Skill Level(s) are retrieved from the related SA and then all of the employees__c that belong to the OSL(s) are related back to the Training__c record.

 

I think I have covered everything off their, Im guessing this is just a complex SOQL query . but any help would be really apprecaited.

 

Thank you in advance.

 

LoganMooreLoganMoore

Assuming the total number of Operator_Skill_Level__c objects is pretty small and almost static, you could use a relationship query on the service agreement to figure this out. I've hacked together some pseudocode to demonstrate. Obviously, this needs to be tweaked quite a bit to actually work.

Map<Id, Operator_Skill_Level__c> operatorSkillLevels = [SELECT Id FROM Operator_Skill_Level__c];
List<Service_Agreement__c> serviceAgreements = [SELECT Id, (SELECT Id, Operator_Skill_Level_Id__c FROM OSLSA_Joins__r) FROM Service_Agreement__c];
for (Service_Agreement__c osa : serviceAgreements) {
    List<Operator_Skill_Level__c> sa_osls = new List<Operator_Skill_Level__c>(); // List of OSLs for this particular service agreement
    for (OSLSA_Join__c oslJoin : osa.OSLSA_Joins__r) {
        sa_osls.add(operatorSkillLevels.get(oslJoin.Operator_Skill_Level_Id__c));
    }
} 


And wham, you've got a list of OSLs for that particular service agreement, and only one extra SOQL query. I had to make some assumptions about object and field names, but I think you'll get the idea.