Trigger to match field in another object and populate a relationship lookup

Question

I am attempting to code my first trigger... without much success.

I have two objects Invoices (API NAME: InForceEW_invoice_c) and Receivables (API NAME: InForceEW_Receivable_c). Both objects are children of an account.

The invoice records are populated first and then receivables are created later (from an integration).

When a receivable record is created or updated I want a trigger to check if there is an invoice record which has the same name (some receivable records relate to a corresponding invoice and they will have the same name) and create a relationship between the two records.

*Some receivables will not have a matching invoice record - not sure if this could cause an error?

The trigger that I have written so far (based on other examples) is:

Trigger LinkReceivable on InForceEW_Receivable_c (before insert, before update) {

InForceEW_invoice_c invo = [Select Id From InForceEW_invoice_c Where Name IN:trigger.newMap.keyset()];

for(InForceEW_Receivable_c theReceivable: trigger.New){

theReceivable.Invoice__c = Invo.Id; } }

This does not work (no surprises there) and is throwing the below error:

Error Message : Upsert failed. First exception on row 0 with id a0AO0000009ku64MAA; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, LinkReceivable: execution of BeforeUpdate

caused by: System.QueryException: List has no rows for assignment to SObject

Trigger.LinkReceivable: line 3, column 1: []

Stack Trace : (InForceEW)

Any help would be most welcome!

UPDATE

I don't have the reputation yet to put an answer on my own question (for 8 hours after posting) so here's an update:

BTW: I managed to get the trigger to work using the below code (thanks to Shane for pointing me in the right direction with the Name not ID!) :

Trigger LinkReceivable on InForceEW_Receivable_c (before insert, before update) {

for(InForceEW_Receivable_c theReceivable: trigger.New){

InForceEW_invoice_c invo = [Select Id From InForceEW_invoice_c Where Name = :theReceivable.Name];

theReceivable.Invoice__c = Invo.Id;

} }

I assume that this is not "bulkified" for multiple upserts?

I'll take a look at your answer Bachovski and see what I can work with that.

BTW: How do you get the right formatting to show code in your post on here (I'm having to use block quote)?

RESOLUTION UPDATE:

It works!!!!

Finally got success and it's all down to the great answers that have been provided here.

Ultimately it was Bachovski's answer that I utilised, with a tweak to update the invoice rather than the receivable.

And the penny finally dropped that the "Update" method just needs a list of records to "update". You have to update the field beforehand and the "Update" method just pumps it through... please tell me if I've got that wrong!

Here is the final code that worked:

Trigger LinkReceivable on InForceEW__Receivable__c (after insert, after update) {

// build a set of receivable names Set <String> receivableNames = new Set <String>();

for(InForceEW__Receivable__c theReceivable : trigger.New) {
    receivableNames.add(theReceivable.Name); } 

// Map the invoice names and the actual invoice records based on the receivable names

Map <String, InForceEW__invoice__c> matchingInvoicesMap = new Map <String,     InForceEW__invoice__c> ();

for (InForceEW__invoice__c invoice : [Select Id, Name From InForceEW__invoice__c Where Name IN :receivableNames]) {

matchingInvoicesMap.put(invoice.Name, invoice);  } 

List <InForceEW__invoice__c> >InvoicesToUpdate = new List <InForceEW__invoice__c> (); 

// go through the records in the trigger again and check whether we have found a matching receivable and invoice 

for(InForceEW__Receivable__c theReceivable : trigger.New) {

  if (matchingInvoicesMap.get(theReceivable.Name) != null)

    {
    // we found a matching one

    matchingInvoicesMap.get(theReceivable.Name) .receivable__c = theReceivable.Id;

    // add it to a separate list and update it

    InvoicesToUpdate.add(matchingInvoicesMap.get(theReceivable.Name));

    }


}   
update InvoicesToUpdate;   
}

Answer 1

The expression trigger.newMap.keyset() contains ID's of the records and not names. Therefore your query should look like [Select Id From InForceEW_invoice_c Where Id IN :trigger.newMap.keyset()]; which SHOULD return the targeted invoices.

This will NOT return any records again, since your trigger is on the receivable object and you're querying invoices with ID of an receivable.

What you need to do to get all invoices that match a name of a receivable in your trigger, is to create a set of names (based on the receivables in your trigger) and then query all invoices which have the same name as the receivables in the trigger and work with them.

Something like this:

// build a set of receivable names
Set <String> receivableNames = new Set <String> ();

for(InForceEW_Receivable_c theReceivable : trigger.New)
{
    receivableNames.add(theReceivable.Name);
}

// Map the invoice names and the actual invoice records based on the receivable names
Map <String, InForceEW_invoice_c> matchingInvoicesMap = new Map <String, InForceEW_invoice_c> ();

for (InForceEW_invoice_c invoice : [Select Id, Name From InForceEW_invoice_c Where Name IN :receivableNames])
{
    matchingInvoicesMap.put(invoice.Name, invoice);
}

List <InForceEW_Receivable_c> receivablesToUpdate = new List <InForceEW_Receivable_c> ();

// go through the records in the trigger again and check whether we have found a matching receivable and invoic
for(InForceEW_Receivable_c theReceivable : trigger.New)
{
    if (matchingInvoicesMap.get(theReceivable.Name) != null)
    {
        // we found a mathing one
        theReceivable.Invoice__c = matchingInvoicesMap.get(theReceivable.Name).Id;

        // add it to a separate list and update it
        receivablesToUpdate.add(theReceivable);
    }
} 

update receivablesToUpdate;

Now the updating of the receivables will cause the trigger to fire again and again leading to recursive trigger and result with an error. You need to implement some sort of functionality that will prevent this. I will leave that part on you as an exercise...

Answer 2

I think I understand what you're trying to do.

trigger.newMap.keyset() is the map of ids from the inserted records. You will probably want to get the Name (invoice number) instead.

For more best practices, that query should return a list--imagine lots of InForceEW_Receivable_c getting updated at once. Then you can go through the list, and handle the event that there might not be anything in the list at all.

Answer 3

It works!!!!

Finally got success and it's all down to the great answers that have been provided here.

Ultimately it was Bachovski's answer that I utilised, with a tweak to update the invoice rather than the receivable.

And the penny finally dropped that the "Update" method just needs a list of records to "update". You have to update the field beforehand and the "Update" method just pumps it through... please tell me if I've got that wrong!

Here is the final code that worked:

Trigger LinkReceivable on InForceEW__Receivable__c (after insert, after update) {

// build a set of receivable names Set <String> receivableNames = new Set <String>();

for(InForceEW__Receivable__c theReceivable : trigger.New) {
    receivableNames.add(theReceivable.Name); } 

// Map the invoice names and the actual invoice records based on the receivable names

Map <String, InForceEW__invoice__c> matchingInvoicesMap = new Map <String,     InForceEW__invoice__c> ();

for (InForceEW__invoice__c invoice : [Select Id, Name From InForceEW__invoice__c Where Name IN :receivableNames]) {

matchingInvoicesMap.put(invoice.Name, invoice); 
} 

List <InForceEW__invoice__c> >InvoicesToUpdate = new List <InForceEW__invoice__c> (); 

// go through the records in the trigger again and check whether we have found a matching receivable and invoice 

for(InForceEW__Receivable__c theReceivable : trigger.New) {

  if (matchingInvoicesMap.get(theReceivable.Name) != null)

    {
    // we found a matching one

    matchingInvoicesMap.get(theReceivable.Name) .receivable__c = theReceivable.Id;

    // add it to a separate list and update it

    InvoicesToUpdate.add(matchingInvoicesMap.get(theReceivable.Name));

    }  
}  
update InvoicesToUpdate;  
}