+ Start a Discussion
Tommy GeorgiouTommy Georgiou 

text field not equal to the same value in workflow

Hi all,


I have a custom text field that I want to include into a workflow where my end result will be a field update(checkbox). What I want as a rule is when this custom text field is the same then the update won't take place. ie. when abc1234 = abc 1234 then nothing will happen. 

My question is how can I create this rule? (For a contact I have a custom object where it has records. In some cases two records have the same custom text field.)
Best Answer chosen by Tommy Georgiou
goabhigogoabhigo
I charge $100 per hour :)
trigger ReservationTrigger on Reservations__c (after insert) {
    // Get the IDs of related contacts
    Set<Id> contactIds = new Set<Id>();
    for(Reservations__c oneReservation:trigger.new){
        if(oneReservation.ReservationStatus__c == 'Confirmed'){
            contactIds.add(oneReservation.Contact__c);
        }
    }
    // Count the distinct Reservation_Number__c from the Reservation objects of all related contacts
    Map<Id, Integer> countDistinctReservationMap = new Map<Id, Integer>();
    for (AggregateResult aggRes : [SELECT DISTINCT_COUNT(ReservationNumber__c) resNum, Contact__c conId FROM Reservation__c WHERE Contact__c IN: contactIds GROUP BY Contact__c ]) {
         Id conId = (Id) aggRes.get('conId');
         Integer resNum  = (Integer) aggRes.get('resNum');
         countDistinctReservationMap.put(conId, resNum);
    }
    // Now fetch the Contacts in a list
    List<Contact> listContacts = [Select Id, customRollupField__c from Contact Where Id IN:contactIds];
    if(listContacts.size()>0) {
         for(Contact con : listContacts) {
              // fetch or get the distinct count or rollup from the map and copy it to the contact's field
              con.customRollupField__c = countDistinctReservationMap.get(con.Id);
         }
    }
    // Update the contacts
    update listContacts;
}

I haven't tested the code. Please check this if it works fine. Also please remove the roll-up field from Contact.

--
Abhi
If my answer helps to solve the issue/problem/doubt, please mark it as Best Answer; so that people who are stuck in the similar issue get benefitted. 

All Answers

goabhigogoabhigo
I am not clear with what you are looking for. To check if the field's value was changed, you can use ISCHANGED() in your workflow criteria and do the field update.

--
Abhi

If my answer helps to solve the issue/problem/doubt, please mark it as Best Answer; so that people who are stuck in the similar issue get benefitted. 
 
Tommy GeorgiouTommy Georgiou
Hi,

User-added image

If you look the image below where in red I highlighted the part that is the same. My rule now contains Record Count > 1 and it counts all the records so my checkbox gets checked. What I want though is Yes to calculate the records but when the ReservationCode is the same in each record then it won't count this as a seperate one
goabhigogoabhigo
Still not clear. This cannot be done using workflow rule. Where do you want this count to be displayed? 
If you are already written code, simply put these record's ReservationCode to a set - set doesn't store duplicate records. Now in the end just check the size of the set - you wil get count.

--
Abhi
If my answer helps to solve the issue/problem/doubt, please mark it as Best Answer; so that people who are stuck in the similar issue get benefitted. 
Tommy GeorgiouTommy Georgiou
Reservations is a custom object where is realted with contacts with a Master Detail relation. In Contacts I have a custom checkbox and a rollup field that counts the records in reservations(with a filter to count only the ones that their status=confirmed)

Then I created a workflow where when recordcount>1 then a field update that checks the custom checkbox. 
But my problem is that I may have more than 3 records with the same reservationcode and I do not want to include them in the process. 

 
Tommy GeorgiouTommy Georgiou
I have also wrote this trigger with a help of a developer but still not sure how to input the ResCode in 
trigger ReservationTrigger on Reservations__c (after insert) {
    // After Insert, if the Status is confirmed update the Contact
    Set<Id> contactIds = new Set<Id>();
    for(Reservations__c oneReservation:trigger.new){
        if(oneReservation.ReservationStatus__c == 'Confirmed'){
            contactIds.add(oneReservation.Id);
        }
    }
    // Get a list of matching contacts  
    List<Contact> existingContacts = [Select Id, Repeater__c,
                                        (Select id from Reservations__r)  
                                        From Contact 
                                        Where Id in:contactIds
                                        And Repeater__c = False];

    // Itterate through the contacts
    List<Contact >contactsToUpdate = new List<Contact>();
    if(existingContacts.size()>0){
        for(Contact oneContact: existingContacts){
            // Check if the Contact has two Reservations, we only want to update on the secon
            List<Reservations__c> existingReservations = oneContact.getSObjects('Reservations__r');
            if(existingReservations.size() == 2){
                oneContact.Repeater__c = True;
                contactsToUpdate.add(oneContact);
            }
        }
        // Update the contacts
        Update contactsToUpdate;
    }
}
goabhigogoabhigo
One problem just by looking at the code I see is:
if(oneReservation.ReservationStatus__c == 'Confirmed'){
       contactIds.add(oneReservation.Id);
}

Should be:
if(oneReservation.ReservationStatus__c == 'Confirmed'){
       contactIds.add(oneReservation.Contact__c); // the API name of Contact field in Reservation object
}
 
You can also check COUNT_DISTINCT() in Aggregate Function in SOQL query. Check this more info -
http://www.salesforce.com/us/developer/docs/soql_sosl/Content/sforce_api_calls_soql_select_agg_functions.htm

--
Abhi
If my answer helps to solve the issue/problem/doubt, please mark it as Best Answer; so that people who are stuck in the similar issue get benefitted. 
Tommy GeorgiouTommy Georgiou
But the code worked fine. The problem is how to get the ResCode rule on the trigger. 
goabhigogoabhigo
I am surprised! Your code is not updating the count in Contact object, it is simply updating the object - line 24, 28. I am not sure if the count is correct and is distinct. As I mentioned above, instead of the current code use the aggregate function to count the distinct records and update the contact's field. Do not use standard roll-up summary field, use another number field - which is read only in page layout. Now update this field through the trigger.
Since you already have written the trigger, check the workflow condition inside trigger, and update the checkbox too from the trigger code itself. no need of workflow.

--
Abhi
Tommy GeorgiouTommy Georgiou
Hi, 

I followed your instruction and changed it to 
if(oneReservation.ReservationStatus__c == 'Confirmed'){
       contactIds.add(oneReservation.Contact__c);

How to get the COUNT DINSTICT into the trigger?
goabhigogoabhigo
I charge $100 per hour :)
trigger ReservationTrigger on Reservations__c (after insert) {
    // Get the IDs of related contacts
    Set<Id> contactIds = new Set<Id>();
    for(Reservations__c oneReservation:trigger.new){
        if(oneReservation.ReservationStatus__c == 'Confirmed'){
            contactIds.add(oneReservation.Contact__c);
        }
    }
    // Count the distinct Reservation_Number__c from the Reservation objects of all related contacts
    Map<Id, Integer> countDistinctReservationMap = new Map<Id, Integer>();
    for (AggregateResult aggRes : [SELECT DISTINCT_COUNT(ReservationNumber__c) resNum, Contact__c conId FROM Reservation__c WHERE Contact__c IN: contactIds GROUP BY Contact__c ]) {
         Id conId = (Id) aggRes.get('conId');
         Integer resNum  = (Integer) aggRes.get('resNum');
         countDistinctReservationMap.put(conId, resNum);
    }
    // Now fetch the Contacts in a list
    List<Contact> listContacts = [Select Id, customRollupField__c from Contact Where Id IN:contactIds];
    if(listContacts.size()>0) {
         for(Contact con : listContacts) {
              // fetch or get the distinct count or rollup from the map and copy it to the contact's field
              con.customRollupField__c = countDistinctReservationMap.get(con.Id);
         }
    }
    // Update the contacts
    update listContacts;
}

I haven't tested the code. Please check this if it works fine. Also please remove the roll-up field from Contact.

--
Abhi
If my answer helps to solve the issue/problem/doubt, please mark it as Best Answer; so that people who are stuck in the similar issue get benefitted. 
This was selected as the best answer
goabhigogoabhigo
My bad, I meant Reservation_Code__c, the one which you hightlighted in red above. 
What are you asking in the second part - And supposly yes should be an autonumber? - I did not understand this.
Tommy GeorgiouTommy Georgiou
Thank you very much for your help!! Owe you !! The ReservationNumber__c in your trigger in mine will be the ReservationID from the image that was mentioned earlier?
goabhigogoabhigo
See my answer above. I guess you removed your previous post. Hope thats clear now, by Reservation_Number__c I meant ReservationCode__c
Tommy GeorgiouTommy Georgiou
Hi Abhi,

Discard my last comment.

I have tried the trigger but I do get an error like 
Error: Compile Error: sObject type 'Reservation__c' is not supported. If you are attempting to use a custom object, be sure to append the '__c' after the entity name. Please reference your WSDL or the describe call for the appropriate names. at line 11 column 35


 
Tommy GeorgiouTommy Georgiou
Changed it to Reservations__c and then the error is 

 [Error] Error: Compile Error: Invalid aggregate function: DISTINCT_COUNT at line 11 column 35
Tommy GeorgiouTommy Georgiou
Ok got it right. Had to change it to Count_Dinstict
goabhigogoabhigo
My bad again... Please use COUNT_DISTINCT.
for (AggregateResult aggRes : [SELECT COUNT_DISTINCT(ReservationNumber__c) resNum, Contact__cconId FROM Reservation__c WHERE Contact__c IN: contactIds GROUP BY Contact__c ]) {
Tommy GeorgiouTommy Georgiou
Yes I have already changed it. 

Moment of truth!!! It's saved now and I am trying to create and see a test contact if it works. Does this works for existing contacts with existing reservations?
goabhigogoabhigo
Should work if you update reservations. Let me know if it doesn't, I will charge $99 per hour ;)
Tommy GeorgiouTommy Georgiou
Worked like a charm for the test contact will update the rest know!!

Thank you very much!!!
goabhigogoabhigo
Glad that it helped. Yes, please let me know if you face any issues.

--
Abhi
Tommy GeorgiouTommy Georgiou
Hi Abhi,


I need your help wirting the test class in order to cover the lines and push it into production. 

Due to my poor knowledge I think that a test class should include  

Account a = new Account(Name = 'Test');
        insert a;     

     Contact c = new Contact(AccountId=a.Id, lastname='testing', firstname='apex', Email= 'xxxxx');
        insert c;

but here is where I stuck. How to get the custom object onto the test class with the count dinstinct??
Tommy GeorgiouTommy Georgiou
Hi Abhi,

I am trying to insert mass records onto the custom object and I get an error like :


ReservationTrigger: execution of AfterInsert caused by: System.QueryException: Non-selective query against large object type (more than 100000 rows). Consider an indexed filter or contact salesforce.com about custom indexing. Even if a field is indexed a filter might still not be selective when: 1. The filter value includes null (for instance binding with a list that contains null) 2. Data skew exists whereby the number of matching rows is very large (for instance, filtering for a particular foreign key value that occurs many times) Trigger.ReservationTrigger: line 11, column 1

Any Ideas how to overcome this?