... So, I have a custom object called "Label_Lookup__c".

Whenever a user visits our custom portal (not hosted on SalesForce), the server will record which label number has been looked at and when.  We wish to be able to record all look-ups, but also to distinguish the first time that each label has been looked up.

To this end, the Label_Lookup__c object has a checkbox field "First_Lookup__c".

I'd like to make sure that whenever Label_Lookup__c objects are created or modified, the earliest and only the earliest "First_Lookup__c" record for each unique "Label_Number__c" is true.

To this end, I wrote the following code:

trigger markFirstLabelLookup on PiqqoLabels__Label_Lookup__c (after insert, after update, after delete, after undelete) { /** * Create and Initialize variables */ // Get Label Lookup List List<PiqqoLabels__Label_Lookup__c> triggerList = (trigger.isDelete || trigger.isUndelete ? trigger.old : trigger.new); // Create set for UNIQUE numbers looked up. Set<String> lookedNumbers = new Set<String>(); // Populate set of unique numbers from the trigger list for (PiqqoLabels__Label_Lookup__c triggerLabelLookup: triggerList) { if(triggerLabelLookup.Label_Number__c == null) return; lookedNumbers.add(triggerLabelLookup.Label_Number__c); } // Create complete list of Label Lookups of these Label Numbers flagged as "First Lookup". List<PiqqoLabels__Label_Lookup__c> labelLookupList = new List<PiqqoLabels__Label_Lookup__c>([ Select Id, First_Lookup__c, Label_Number__c, Lookup_Date__c from PiqqoLabels__Label_Lookup__c where ( (Label_Number__c In : lookedNumbers) AND (First_Lookup__c = true) ) order by Lookup_Date__c asc ]); // Create and initialize map for Earliest Lookups Map<String, datetime> labelLookedEarliestLookupDateMap = new Map<String, datetime>(); Map<String, Id> labelLookedEarliestLookupIdMap = new Map<String, Id>(); // Create list of Lookups which are changed to/from "First Lookup" List<PiqqoLabels__Label_Lookup__c> labelLookedChangedList = new List<PiqqoLabels__Label_Lookup__c>(); //**************************************************************************************************** // Evaluate each in the trigger list to confirm if it is the earliest. for (PiqqoLabels__Label_Lookup__c triggerLabelLookup: triggerList) { if(triggerLabelLookup.Label_Number__c == null) return; if(!labelLookupList.isEmpty()) { // Check each number previously looked for(PiqqoLabels__Label_Lookup__c oldLook: labelLookupList) { // Check if this instance of the trigger label number refereds to the same label number // as retrieved in the complete list. if (triggerLabelLookup.Label_Number__c == oldLook.Label_Number__c) { /** * * isEarliestDate checks 3 dates to verify that the most recent entered is also * the most recent chronologically: * * public static boolean isEarliestDate (newdate, olddate, mapdate) * */ if (triggerDatetimeToolkit.isEarliestDate ( triggerLabelLookup.Lookup_Date__c, // Date from this iteration of the trigger oldLook.Lookup_Date__c, // Date from this iteration of the complete list (labelLookedEarliestLookupDateMap.get(triggerLabelLookup.Label_Number__c)) // Date from the earliest mapping of the id; needed in case a trigger list contains two or more references to the same label number. ) ) { // If Earliest, make sure First Lookup = true labelLookedEarliestLookupDateMap.put (triggerLabelLookup.Label_Number__c, triggerLabelLookup.Lookup_Date__c); labelLookedEarliestLookupIdMap.put (triggerLabelLookup.Label_Number__c, triggerLabelLookup.id); // The default value for First_Lookup is true, thus it shouldn't usually be // necessary to change this value to true; only if (for some strange reason) // the lookup record is modified. /** * * toggleFirstLookup makes sure each labelLook has First Lookup value as desired. * If not, modifies and adds to change list. * * public static PiqqoLabels__Label_Lookup__c toggleFirstLookup(labelLookup, desiredValue, changeList) * */ triggerLabelLookupToolkit.toggleFirstLookup(triggerLabelLookup, true, labelLookedChangedList); // ----------------------------------------------------------- // Unless records have been modified (again, for a strange reason), // it shouldn't usually be necessary to change older values which were true // to false because of a new entry. // Make sure other lookups are now false; if (oldLook.id != triggerLabelLookup.id) { triggerLabelLookupToolkit.toggleFirstLookup(oldLook, false, labelLookedChangedList); } // Needed if two or more records in the same trigger refer to the same // Label Number. If everything in this trigger works, it shouldn't // happen that two old Lookups both refer to the same Label Number and // yet are both flagged first lookup. if (labelLookedEarliestLookupIdMap.get(triggerLabelLookup.Label_Number__c) != triggerLabelLookup.id) { // **** If I understand SalesForce policy well enough, it would be better if I // **** can revisit this record's value without needing to perform this database query. PiqqoLabels__Label_Lookup__c OldEarliestLookup = [ Select First_Lookup__c from PiqqoLabels__Label_Lookup__c where Id = :(labelLookedEarliestLookupIdMap.get(triggerLabelLookup.Label_Number__c)) ]; triggerLabelLookupToolkit.toggleFirstLookup(OldEarliestLookup, false, labelLookedChangedList); } //---------------------------------------------------------------- } else { // If not Earliest, make sure First Lookup = false // By default, First_Lookup__c is true (with the expectation that // each Label Number will usually only be looked at once.) // // If this trigger functions correctly and no Label Lookups are // ever modified after being created, this is should be the only // condition which is ever true and therefore needs to be changed. triggerLabelLookupToolkit.toggleFirstLookup(triggerLabelLookup, false, labelLookedChangedList); } } } } } //**************************************************************************************************** // Update all Label Lookups whose First_Lookup__c changed. upsert labelLookedChangedList;}

public class triggerLabelLookupToolkit {/** * * Tools for working with "Label Lookup" objects in triggers. * * Label Lookups have an API name: PiqqoLabels__Label_Lookup__c * * This class includes: * * toggleFirstLookup - changes "First Lookup" from true to false (or vice-versa) as necessary; adds to change list. * * @author Brian Kessler * @version 1.0 * */ /** * Makes sure each labelLook has First Lookup value as desired. If not, modifies and adds to change list. * @parameter labelLookup record to check and (if necessary) modify * @parameter desiredValue what the value should be * @parameter changeList the list of Label Lookups which have changed (to update later). * @return labelLookup, with verified or fixed value. */ public static PiqqoLabels__Label_Lookup__c toggleFirstLookup( PiqqoLabels__Label_Lookup__c labelLookup, boolean desiredValue, List<PiqqoLabels__Label_Lookup__c> changeList ) { if (labelLookup.First_Lookup__c != desiredValue) { labelLookup.First_Lookup__c = desiredValue; changeList.add (labelLookup); } return labelLookup; }}

 However, now when I try to create a new Label Lookup object, I get the following error:

 How can I fix this?