+ Start a Discussion

how to prevent duplication of records based on two fields

help please. how can i prevent duplication of records based on two fields?
    Objects                          Fields
Seat__                            Schedule__c(lookup)
Reservation__c            Seat__c(lookup), Schedule__c(lookup), passenger_name__c

i have this code:
trigger PreventSeatDuplication on Reservation__c (before insert, before update) {

    Map<String, Reservation__c> resMap = new Map<String, Reservation__c>();
    for (Reservation__c res : System.Trigger.new) {
        if ((res.Seat__c != null) && (System.Trigger.isInsert || (res.Seat__c != System.Trigger.oldMap.get(res.Id).Seat__c))) {
            if (resMap.containsKey(res.Seat__c)) {
                res.Seat__c.addError('Seat not available.');
            } else {
                resMap.put(res.Seat__c, res);
    for (Reservation__c res : [SELECT Seat__c FROM Reservation__c WHERE Seat__c IN :resMap.KeySet()]) {
        Reservation__c newRes = resMap.get(res.Seat__c);
        newRes.Seat__c.addError('Seat not available.');

but it only prevents the same seat # for the same name, for example, if there is already a record on Reservation__c(Passenger_Name__c='test1', Seat__c='1', Schedule__c='sched001') how can i prevent Reservation__c(Passenger_Name__c='test2', Seat__c='1', Schedule__c='sched001') from saving?
I tend to handle this by creating a unique external field on the record and populating it via a workflow formula concatenating the fields together.  Then the platform will carry out the unique check when the workflow executes and an error will be generated if another record already has that "key". 
Ryan GardnerRyan Gardner
In your trigger, you are looking at seats but not at schedules. I think what you need to compare against are all seats that are already taken on the schedule for the seat in your trigger. Something like this:
//resMap maps a schedule to the full list of seats taken for that schedule
Map<String, Set<String>> resMap = new Map<String, Set<String>>();
Set<String> schedules = new Set<String>();

//build set of all schedules in play
    for (Reservation__c res : System.Trigger.new) {

//for this, i'm assuming you can't be inserting or updating a reservation unless a schedule has already been created
//i'm getting the full list of reservations for each schedule in the context of this trigger 
    for (Reservation__c res : [SELECT Schedule__c, Seat__c FROM Reservation__c WHERE Schedule__c IN :schedules.KeySet()]){
  Set<String> seats = resMap.get(res.Schedule__c);
  if(seats != null){
   resMap.put(res.Schedule__c, new Set<String>{res.Seat__c});

//now we know what seats are booked for each schedule, now we can compare to our trigger
for(Reservation__c res : Trigger.new){
  String seatNum = res.Seat__c;
  String schedule = res.Schedule__c;
  //get all booked seats for current schedule in play
  Set<String> seatsThatAreTaken = resMap.get(schedule);
  //if seat is already booked
   res.addError('Seat\'s Taken');
You might also want to add a check in that last loop that looks for if the same seat is being booked twice in the trigger, because I didn't put something for that in. But should be easy to handle.