+ Start a Discussion
Damon HedgspethDamon Hedgspeth 

Need a query to find missing records in a sequence

I have a invoicing app that tracks incoming invoices each month.  I would like to find out if any invoices are missing.

parent object is 'Service", "Invoice is a child".

The service record contains a numeric value of the day of the month that the invoice is usually due. 
Ideally, I would have a scheduled apex run nightly to check that an invoice record exists for each month since the Service was created. It could store the results in a field on the parent record (Missing_Invoices).

Service - (created 12-10/2015 , Invoice Due Day = 30
Invoice 1-23-16
Invoice 2-24-16
Invoice 4-17-16

The above example would update the missing_invoices field with "Invoice for March is missing".

A start on effinciently coding this would be greatly appreciated. 
MandyKoolMandyKool
Damon,

Can't think of a single query which you can use to achive this. But you might want to try below approach.

1. Create a map to store months and corresponding string values.
    Map<integer, string> {1 => 'Jan', 2 => 'Feb'......, 12 => 'Dec'};

2. Query service with its child invoices something like [Select Id, (Select Id, InvoiceDate__c FROM ChildInvoices) FROM Service__c WHERE yourCriteria

3. Iterate Child Invoice records and use .month() method on InvoiceDate__c field (assuming that your field is of type Date)

4. Now check the month returned by .month() method in a map we created in 1.

5. If the record exists it means the invoice is created for that month; if not you can add it your result list.

Tried to give you a high level solution best on my understanding. Hope this helps you!