[SalesForce] Limit # child records to one per day per parent. Trigger or workflow+VR

There are 2 Custom objects Position__c and Job_postings__c. They both have master detail relationship. where Position__c is master and job posting is detail.

Requirement: For a day only one Job posting should be created by the user for a particular Position. On the same day when the user is trying to create another Job posting for the same Position, error message should be thrown. But on the next day the user should be able to create a new Job posting for that same Position.

How can this be achieved using triggers?. Also i want to know whether this can be achieved with workflows and validation rule?.

Best Answer

You will have a trigger on the job posting object. When inserted (before insert), you should check if the position for that job posting already has other job postings. If yes, then you should get the time of the last one inserted. If the date equals today, then you cannot insert the object (using .addError method).

It would probably look like this:

trigger on Job_Posting__c (before insert) {
    if (Trigger.isBefore && Trigger.isInsert) {
        List<Id> positions_ids = new List<Id>();

        // get the positions ids
        for (Job_Posting__c j : Trigger.new) {
            positions_ids.add(j.Position__c);
        }

        // query all job postings for those positions
        List<Job_Posting__c> other_jobs = [SELECT Id, CreatedDate, CreatedById FROM Job_Posting__c WHERE Position__c IN positions_ids];

        for (Job_Posting__c j : Trigger.new) {
            for (Job_Posting__c existent_record : other_jobs) {
                // check if we are comparing two records for the same position
                // if the created date for the existing record equals the current date
                // (that is, the insertion date for the record on Trigger.new), then 
                // we can send that error to the user.
                if (j.Position__c == existent_record.Position__c && j.CreatedById == UserInfo.getUserId()) {
                    Date existent_record_created_date = existent_record.CreatedDate.date();

                    if (existent_record_created_date == Date.today()) {
                        j.addError('Cannot create job position because there\'s another one created on this date.');
                    }
                }
            }
        }
    }
}
Related Topic