Apex Code Development You need to sign in to do that
Don't have an account?
Damon 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.
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.
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!