0

I am trying to find a minimum value of the field Maintenance_Cycle__c (which is a field on the Product standard object which is looked up by the Equipment_Maintenance_Item__c custom object, which is in turn a child of the Case standard object through master-detail relationship) and then assign it to another field in a new Case record.

My code is as follows:

public with sharing class MaintenanceRequestHelper {
    
    public static void updateWorkOrders(List<Case> cases) {
        List<Case> updatedCases = [SELECT Id,ProductId, Vehicle__c, (SELECT MIN(Equipment__r.Maintenance_Cycle__c) FROM Equipment_Maintenance_Items__r) FROM Case WHERE Id 
                                  IN :cases AND (Type = 'Repair' OR Type = 'Routine Maintenance') AND 
                                  Status = 'Closed'];
        List<Case> newCases = new List<Case>();
        for(Case a:updatedCases){
            Case b = new Case();
            b.Type = 'Routine Maintenance';
            b.Vehicle__c = a.Vehicle__c;
            b.Subject = 'Routine Maintenance Request';
            b.Date_Reported__c = Date.today();
            //to do: Date_Due__c field
            newCases.add(b);
        }
        insert newCases;
        }

I already have an error with this code "Grouped query cannot use child relationships in the SELECT list", which is caused by the MIN function (there is no error without it). I am also doing a MIN function because there will be several values and I need to find the smallest one. I will then need to assign that value somehow to the Date_Due__c field, but I do not even know how to access that value from the query, since it is few relationships away.

2
  • Take a look at Rollup Summary field for MIN in a master-detail
    – identigral
    Feb 26, 2023 at 19:34
  • @identigral This doesn't work because the field I want is not on a child of Cases, but on the Parent of the child of Cases.
    – ApexNoob
    Feb 26, 2023 at 19:55

1 Answer 1

0

Although not optimal, you could retrieve the minimum value separately via an additional SOQL query for you to use in your updates. The below query should work (this is assuming that your lookup field on Equipment_Maintenance_Items__c has an API Name of Case__c. You'll need to modify this if that isn't true):

Equipment_Maintenance_Items__c minCycleItem = [SELECT Equipment__r.Maintenance_Cycle__c FROM Equipment_Maintenance_Items__c WHERE Case__c IN cases ORDER BY Equipement__r.Maintenance_Cycle__c ASC NULLS LAST LIMIT 1];

The additional Order By and Limit clauses in this ensure that you'll only get the single record that you need. You'll then be able to use the value from this single record to update the cases later in your code.

1
  • @ApexNoob I've just noticed your new question, so I've updated this answer to make it explicit that I was suggesting an additional SOQL query just to retrieve the required Equipment Maintenance Items record rather than suggesting a modification to the existing query that you had in your question. Hope this helps! Feb 26, 2023 at 21:00

You must log in to answer this question.

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