+ Start a Discussion
NecroGigglesNecroGiggles 

Need to update a Null look up field between 2 objects based on conditions.

I need to link "Campaign Members" to a custom object "KPITarget" based on values of both objects using a lookup field. 
The conditions are based on vales from both objects 3 fields that need to match on both objects and a date fields that just needs the month and year to mach. The last thing is the "HasResponded" field must be true on Campaign member.

I think this needs to be a class called by a Trigger but I am not sure since there will be no link between the objects at the start.
Create a List of all Campaign Member Id's getting the values of the 3 fields, Date Range and, HasResponded That the trigger fires off.
Create a List of all KPITargets Id’s that match the values of the Campaign Member, There should only be one.
The update the look up field on Campaign member with the ID of the KPITarget that matches.
 
How does that sound?
 
Best Answer chosen by NecroGiggles
UC InnovationUC Innovation
You have to use the Apex Date class methods:

https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_methods_system_date.htm
 
trigger CampaignMemberTrigger on CampaignMember (before insert, before update) {
    // Get a list of fields from the sObject Marketing_KPI_Target__c and name the list kpiTargetList
    List<Marketing_KPI_Target__c> kpiTargetList = [SELECT Type_of_Sale__c, ECI_Product__c, CurrencyIsoCode,KPI_Target_Date__c FROM Marketing_KPI_Target__c];
    
    for (CampaignMember member : Trigger.New) {
        for (Marketing_KPI_Target__c kpiTarget : kpiTargetList) {
            if (kpiTarget.Type_of_Sale__c == member.Type_of_Sale__c &&
                kpiTarget.ECI_Product__c == member.ECI_Product__c &&
                kpiTarget.CurrencyIsoCode == member.CurrencyIsoCode &&
               	kpiTarget.KPI_Target_Date__c.month() == member.Response_Tracking_Date__c.month() &&
				kpiTarget.KPI_Target_Date__c.year() == member.Response_Tracking_Date__c.year() &&
                member.HasResponded == True)
            {
                member.Marketing_KPI_Target__c = kpiTarget.Id;
                continue;
            }
        }
    }
}

 

All Answers

UC InnovationUC Innovation
You will probably need two before insert/update triggers, one on the Campaign Member and one on the KPITarget to catch changes on either records and update the linkage accordingly.  The approach would be similar for both triggers.  Take the CampaignMember trigger for example:

1. Query for all KPITargets with the fields you need to compare against the Campaign Members and put it in a list.
2. Loop through Trigger.New (which is the list of CampaignMember records that were triggered) and compare each CampaignMember record against each KPITarget record in the list.  If there's a match, assign the KPITarget record to the lookup field of the CampaignMember record.
 
NecroGigglesNecroGiggles
The problem I am running in to is I don’t know how to get the values from Trigger.new and pass them to a SOQL query on KPItarget and if I get a match to up date the “Marketing_KPI_Target__c” lookup field on Campaign Member with the ID of the Soql query.
UC InnovationUC Innovation
Here's an example:
 
trigger CampaignMemberTrigger on CampaignMember (before insert, before update) {
    List<KPITarget__c> kpiTargetList = [SELECT Field1__c, Field2__c, Field3__c FROM KPITarget__c];
    
    for (CampaignMember member : Trigger.New) {
        for (KPITarget__c kpiTarget : kpiTargetList) {
            if (kpiTarget.Field1__c == member.Field1__c &&
                kpiTarget.Field2__c == member.Field2__c &&
                kpiTarget.Field3__c == member.Field3__c)
            {
                member.Marketing_KPI_Target__c = kpiTarget.Id;
                continue;
            }
        }
    }
}

I only consider the 3 fields, but you can incorporate the date and HasReponded fields to it.  You could also have do an SOQL query within the first for loop to find the KPITarget record without having to use a for loop to find it, but you will run into SOQL query limits if you do when processing the trigger in bulk.

Note that since this is a before trigger, you just need to assign member.Marketing_KPI_Target__c = kpiTarget.Id and Salesforce will save this once the trigger is done.  You don't need to explicitly call an insert/update.
NecroGigglesNecroGiggles
Works great thank you just one last thing. In the If statement how to I compere just the month and year in the 2 date fields?

kpiTarget.KPI_Target_Date__c == member.Response_Tracking_Date__c &&
 
trigger CampaignMemberTrigger on CampaignMember (before insert, before update) {
    // Get a list of fields from the sObject Marketing_KPI_Target__c and name the list kpiTargetList
    List<Marketing_KPI_Target__c> kpiTargetList = [SELECT Type_of_Sale__c, ECI_Product__c, CurrencyIsoCode,KPI_Target_Date__c FROM Marketing_KPI_Target__c];
    
    for (CampaignMember member : Trigger.New) {
        for (Marketing_KPI_Target__c kpiTarget : kpiTargetList) {
            if (kpiTarget.Type_of_Sale__c == member.Type_of_Sale__c &&
                kpiTarget.ECI_Product__c == member.ECI_Product__c &&
                kpiTarget.CurrencyIsoCode == member.CurrencyIsoCode &&
               	kpiTarget.KPI_Target_Date__c == member.Response_Tracking_Date__c &&
                member.HasResponded == True)
            {
                member.Marketing_KPI_Target__c = kpiTarget.Id;
                continue;
            }
        }
    }
}


 
UC InnovationUC Innovation
You have to use the Apex Date class methods:

https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_methods_system_date.htm
 
trigger CampaignMemberTrigger on CampaignMember (before insert, before update) {
    // Get a list of fields from the sObject Marketing_KPI_Target__c and name the list kpiTargetList
    List<Marketing_KPI_Target__c> kpiTargetList = [SELECT Type_of_Sale__c, ECI_Product__c, CurrencyIsoCode,KPI_Target_Date__c FROM Marketing_KPI_Target__c];
    
    for (CampaignMember member : Trigger.New) {
        for (Marketing_KPI_Target__c kpiTarget : kpiTargetList) {
            if (kpiTarget.Type_of_Sale__c == member.Type_of_Sale__c &&
                kpiTarget.ECI_Product__c == member.ECI_Product__c &&
                kpiTarget.CurrencyIsoCode == member.CurrencyIsoCode &&
               	kpiTarget.KPI_Target_Date__c.month() == member.Response_Tracking_Date__c.month() &&
				kpiTarget.KPI_Target_Date__c.year() == member.Response_Tracking_Date__c.year() &&
                member.HasResponded == True)
            {
                member.Marketing_KPI_Target__c = kpiTarget.Id;
                continue;
            }
        }
    }
}

 
This was selected as the best answer