+ Start a Discussion
Neo NashvilleNeo Nashville 

Autopopulate lookup for each new child record

Hi guys,

I'm trying to auto-populate the lookup field for a custom child object, which is called Purchase Item, with the same lookup field that its parent custom object, Purchase Order, has. The lookup field is called Supplier which is the standard object Account. 

I had a few goes in trying to create a trigger to auto-populate the supplier lookup field that would activate each time a new purchase item on a purchase order is created.

 

I'm not getting any errors with the following code but it does not seem to do what I want:

 

trigger populateSupplier on Purchase_Item__c (before insert , before update){
    
    Set<ID> setPIIds = new Set<ID>();
    for(Purchase_Item__c obj : trigger.new){
        if(obj.Supplier__c == null)
        setPIIds.add(obj.Supplier__c);
    }
    
     MAP<ID , Purchase_Order__c> mapPO = new MAP<ID , Purchase_Order__c>([Select Supplier__c from Purchase_Order__c where id in: setPIIds]);
     for(Purchase_Item__c obj : trigger.new)
       {
        if(obj.Supplier__c == null)
          {
            Purchase_Order__c c = mapPO.get(obj.Supplier__c);
            obj.Supplier__c = c.Supplier__c;
            //Similarly you can assign Address fields as well, just add those field in Contact SOQL as well
          }
       
       }
}

Thanks so much in advance!

Best Answer chosen by Neo Nashville
Dorian Sutton 9Dorian Sutton 9
Hi Neo,

The only way I can see to filter Products by Supplier on a Purchase Item is to override the Create and Edit Purchase Item standard pages with a custom Visualforce page, and to write some Apex to grab the appropriate Products in a custom extension.  

Here's a rough example to get you started (again, I don't know the exact names of your custom objects and fields):

VF Page:
<apex:page standardController="Purchase_Item__c" extensions="PurchaseItemEditExtension">
    
    <apex:form>
    	<apex:pageBlock>
        	<apex:pageBlockButtons>
            	<apex:commandButton action="{!save}" value="Save"/>
            </apex:pageBlockButtons>
            
            <apex:selectList value="{!purchase_item__c.Shop_Product__c}">
                <apex:selectOptions value="{!validShopProducts}"/>
            </apex:selectList>
            
        </apex:pageBlock>
    </apex:form>	
    
</apex:page>
Controller Extension:
public class PurchaseItemEditExtension {
    public Purchase_Item__c pi;
    public Purchase_Order__c po;

    public List<SelectOption> validShopProducts {
        get {
        	List<Shop_Product__c> products = [SELECT Id, Name FROM Shop_Product__c WHERE AccountId = :po.Account__c];
        	List<SelectOption> theOptions = new List<SelectOption>();
            
            for(Shop_Product__c theProduct :products) {
                theOptions.add(new SelectOption(theProduct.Id, theProduct.Name));
            }
            
            return theOptions;
        }
        set;
    }
    
    public PurchaseItemEditExtension(ApexPages.StandardController stdController) {
    	pi = (Purchase_Item__c)stdController.getRecord();
        po = [SELECT Name, Account__c FROM Purchase_Order__c WHERE Id = :pi.Purchase_Order__c];
    }
    
}

You'll obviously need to flesh out the VF page to include all the other necessary fields, as well as any other actions you want buttons for.
 

All Answers

Dorian Sutton 9Dorian Sutton 9
Hi Neo,

It looks like you are not populating the Set of Ids correctly in the first part of your trigger code.

As it is currently written, your Set will not contain any values, because after your if statement:
if(obj.Supplier__c == null)
you are then adding the value of Supplier__c to the list, which is always going to be null.  Try adding a System.debug statement or a checkpoint at line 7 to confirm this is the case:
System.debug('The Ids are: ' + setPIIds);
What I think you meant to do is add the Id of the parent Purchase_Order__c record to the list instead (since it's a custom object I don't know what you've called it, but replace PurchaseOrderId with the correct name):
setPIIds.add(obj.PurchaseOrderId);
That will give you a set of Purchase Order Ids you can use to query for the Supplier code.

Incidentally, I'd strongly advise wrapping conditional code blocks in curly brackets { } , even it there is only one line.  It makes your code a lot easier to read.

Hope that helps.
Neo NashvilleNeo Nashville

Hi Dorian, 

First of all, thanks for looking at my code.


My logic to using the null if statement is because I thought that was the trigger criterion, meaning: whenever the "obj.Supplier__c ==null" activate the trigger. Or did I completely misunderstand that part?

The Id of the parent object "Purchase Order" is already on each purchase item since it is its child. The Purchase Order custom object is in turn a child of Account (Supplier). What I need to have is the supplier's lookup field (which is the "Supplier__c" reference on both the purchase item and the purchase order) automatically populated on the purchase item. This is currently not the case as the purchase item is not a child of the supplier object. 

If I understand correctly, what you are suggesting is to use the purchase orders' Ids to track down the relevant supplier/account Id for the purchase item?

 

 

Dorian Sutton 9Dorian Sutton 9
Hi Neo,

I think you have slightly misunderstood how triggers work, yes.  Before we get to that - can you explain why you need to have a lookup field to the same supplier on both the Purchase Order and the Purchase Item records?  

If you need to access the Supplier or any other value from the parent Purchase Order on a Purchase Item then you can do it with a Formula Field.  Having unneccessary relationship fields in your objects is bad for data integrity.  With your proposed setup you could end up having a Purchase Item related to a different Supplier than it's Parent Purchase Order which could get you into all kinds of trouble.
Neo NashvilleNeo Nashville
I need to have the supplier lookup field populated because of a filter criterion that I set in the purchase item to look for products  (Which is another custom object, yes custom) from only that supplier.and yes, i already tried every other possible filter option out there but there is no way of basing your filter criteria on fields from different objects, such as purchase order, with the normal lookup filter option. Your second statement about ending up with a different supplier on the purchase item is not possible for two reasons.  1: i have a validation rule that triggers if the supplier is different than on the purchase order. 2: the trigger which my request is for has to populate exactly that supplier from the purchase order. I also already tried workflows and process builder but they did not work as workflows do not work with lookup fields and process build builder shows execution errors. If you could help me with the above code that would be really awesome!  Sent from Samsung Mobile
Dorian Sutton 9Dorian Sutton 9
Fair enough.  You were pretty close with your original trigger - you need to create a set of Parent Purchase Order Ids for the Purchase Items that are missing a Supplier, then use that set to query the Purchase Order object and retrieve the Supplier info into a Map that you can reference when looping through your Trigger records.  Something like this ought to do it:
 
trigger PopulateSupplier on Purchase_Item__c(before insert, after insert) {

    // Generate a set of Purchase Order Ids for the Items that don't contain a Supplier
    Set<Id> purchaseOrderIDs = new Set<Id>;

    for(Purchase_Item__c theItem :Trigger.new) {

        if(theItem.Supplier__c == null) {
            purchaseOrderIDs.add(theItem.PurchaseOrderId); // Replace the Parent Id name here if different
        }

    }

    // Retrieve the missing supplier values, and copy to a map
    List<Purchase_Order__c> purchaseOrders = [SELECT Supplier__c FROM Purchase_Order__c WHERE Id IN :purchaseOrderIDs];

    Map<Id, Purchase_Order__c> purchaseOrdersById = new Map<Id, Purchase_Order__c>(purchaseOrders);

    // Now loop through the Trigger records and update Supplier if necessary
    for(Purchase_Item__c theItem :Trigger.new) {

        if(theItem.Supplier__c == null) {
            theItem.Supplier__c = purchaseOrdersById.get(theItem.PurchaseOrderId); // Replace the Parent Id name here if different
        }

    }

}
Incidentally, you should only have one Trigger per object as you cannot guarantee the order that they will execute in (for example somebody might write another Purchase Item trigger that expected the Supplier field to be populated).  The usual standard is to name them after the object to prevent confusion (i.e. PurchaseItemTrigger) then move the logic out to a trigger handler class if you need to perform multiple tasks.
Neo NashvilleNeo Nashville

Thanks Dorian,


Now I'm getting a compile error on line 23:

 

Error: Compile Error: Illegal assignment from Purchase_Order__c to Id at line 23 column 13


I've tried changing the parent Id Name to Purchase_Order__r.Id as well as Purchase_Order__c.Id but the error still persists. 

 

I think this might have something to do the Supplier__c being a lookup field from the Account object. Any ideas?

 

Thanks again Dorian

Dorian Sutton 9Dorian Sutton 9
Oops - this is what happens when you try to write code and not actually run it.  We need to extract the value of Supplier__c from the Purchase Order sObject that we retrieve from the Map - try this:
theItem.Supplier__c = purchaseOrdersById.get(theItem.PurchaseOrderId).Supplier__c;
Neo NashvilleNeo Nashville
Yes, able to save the code. Yet, the supplier on new purchase items is still not auto populating. Hmm...it could be that the before insert, before update have something to do with it
Neo NashvilleNeo Nashville

By the way, you do know that the purchase order is already auto-populated on the purchase item, right? Maybe referencing that purchase order's supplier's ID would be easier? 

In the code, I'm failing to see where the connection to the purchase item's purchase order's supplier is made.

Sorry for all the questions, I'll mark your answer as the best as I rally do appreciate your help.

 

Dorian Sutton 9Dorian Sutton 9
Hi Neo,

The SOQL query at line 15 should retrieve the Supplier field from the parent Purchase Order any Purchase Items without a Supplier.  

Can you post the exact code you are using at the moment so I know that field names are correct?

Are you testing this by creating a new Purchase Item or updating an existing one?  Or both? 
Neo NashvilleNeo Nashville

Creating new purchase item's only. There aren't any purchase item's without a supplier frankly as the shop product needs to populate before you can save the purchase item. And the only way to populate the shop product lookup is by populating the supplier lookup which will give only the items of that supplier for selection.


Im currently at this point with the code:

trigger PopulateSupplier on Purchase_Item__c (before insert, after insert) {

    // Generate a set of Purchase Order Ids for the Items that don't contain a Supplier
    Set<Id> purchaseOrderIDs = new Set<Id>();

    for(Purchase_Item__c theItem :Trigger.new) {

        if(theItem.Supplier__c == null) {
            purchaseOrderIDs.add(theItem.Supplier__c); // Replace the Parent Id name here if different
        }

    }

    // Retrieve the missing supplier values, and copy to a map
    List<Purchase_Order__c> purchaseOrders = [SELECT Supplier__c FROM Purchase_Order__c WHERE Id IN :purchaseOrderIDs];

    Map<Id, Purchase_Order__c> purchaseOrdersById = new Map<Id, Purchase_Order__c>(purchaseOrders);

    // Now loop through the Trigger records and update Supplier if necessary
    for(Purchase_Item__c theItem :Trigger.new) {

        if(theItem.Supplier__c == null) {
            theItem.Supplier__c = purchaseOrdersById.get(theItem.Purchase_Order__r.Id).Supplier__c;
        }

    }

}

Neo NashvilleNeo Nashville

I also already tried this:

 

trigger PopulateSupplier on Purchase_Item__c (before insert, after insert) {

    // Generate a set of Purchase Order Ids for the Items that don't contain a Supplier
    Set<Id> purchaseOrderIDs = new Set<Id>();

    for(Purchase_Item__c theItem :Trigger.new) {

        if(theItem.Supplier__c == null) {
            purchaseOrderIDs.add(theItem.Purchase_Order__r.Supplier__r.Id); // Replace the Parent Id name here if different
        }

   

Dorian Sutton 9Dorian Sutton 9
Hi Neo,

The Trigger.new collection does not include any values from parent objects, so you can't use theItem.Purchase_Order__r.Supplier__r.Id - it is not necessary to do this because Trigger.new will contain the Id of the parent Purchase Order.  We just need to extract it into a Set so your trigger is bulkified.

The code between line 6 and line 12 should generate a set of Purchase Order Ids from the Purchase Items that are referenced by the Trigger.  We can then use SOQL to grab the Supplier from those Purchase Order records.

You need to replace theItem.Supplier__c on line 9 with theItem.PURCHASEID - where PURCHASEID is the name of the Master-Detail relationship field to Purchase Order on the Purchase_Item object.  What is the name of that field?

Once you've changed that, please add the following at line 13:
System.debug('Purchase Order Ids: ' + purchaseOrderIDs);
and after the SOQL statement at line 15:
System.debug('Purchase Orders: ' + purchaseOrders);
And we'll see what the logs can tell us.
 
Neo NashvilleNeo Nashville

The name of the M&D relationship of the Purchase Order on the Purchase Item is Purchase_Order__r.Id.

I've modified the code with your suggestions above. Not sure where I should be looking out for messages for the logs except for the one in the developer console on the bottom. It's currently without any errors; empty.

 

 

Dorian Sutton 9Dorian Sutton 9
Hang on a minute - did I understand your previous post correctly?  You can't save a Purchase Item record without a value for the Shop Product, and you can't populate the available Shop Products until you have a value for the Supplier, which you are trying to populate in your trigger?

If that is the case, this approach is not going to work.  The trigger code is not going to execute until you save a new Purchase Item record and it passes the internal validation rules.  How are you ever going to save a Purchase Item record for the trigger to execute if there has to be a Shop Product and you need the Supplier field to populate that?

I think you may need to reconsider the architecture you are putting in place here.
Dorian Sutton 9Dorian Sutton 9
Also, Purchase_Order__r.Id is not the name of the Purchase Order Id field on the Purchase Item record.  Go to setup and have a look at the custom fields for this object.  What is the API Name for the Mater-Detail field - it should be something like Purchase_Order__c?
Neo NashvilleNeo Nashville

Hi Dorian,

 

Sorry for the confusion. I had a hunch my trigger might not be getting activated for some reason as I assumed wrongly that the "before insert" function makes sure whenever I create a new purchase item, the trigger already automatically activates and populates the supplier lookup field before saving. 


So upon creating, not saving, a purchase item i need the supplier lookup to already be populated. This probably means i need to define a new trigger point. But where and how should it be activated to achieve what I'm looking for?

 

If its on the purchase order, am I able to define a criterion that says, whenever a purchase order's purchase item is created, automatically populate the supplier's lookup field as the same supplier on the purchase order?

Neo NashvilleNeo Nashville

Thats correct. Purchase_Order__c is the API name. I got confused ther with the "ID" part

Dorian Sutton 9Dorian Sutton 9
Hi Neo,

Thanks for the updates.  I have to go out for a while but I will come back to your problem later.

One thing I can tell you right now is that there is no possible Trigger that you can write that will execute before a new record is displayed to the User.  It might be necessary to replace your Purchase Item edit page with a custom visualforce page instead.

If anybody else is watching this, feel free to chime in.
Dorian Sutton 9Dorian Sutton 9
Hi Neo,

The only way I can see to filter Products by Supplier on a Purchase Item is to override the Create and Edit Purchase Item standard pages with a custom Visualforce page, and to write some Apex to grab the appropriate Products in a custom extension.  

Here's a rough example to get you started (again, I don't know the exact names of your custom objects and fields):

VF Page:
<apex:page standardController="Purchase_Item__c" extensions="PurchaseItemEditExtension">
    
    <apex:form>
    	<apex:pageBlock>
        	<apex:pageBlockButtons>
            	<apex:commandButton action="{!save}" value="Save"/>
            </apex:pageBlockButtons>
            
            <apex:selectList value="{!purchase_item__c.Shop_Product__c}">
                <apex:selectOptions value="{!validShopProducts}"/>
            </apex:selectList>
            
        </apex:pageBlock>
    </apex:form>	
    
</apex:page>
Controller Extension:
public class PurchaseItemEditExtension {
    public Purchase_Item__c pi;
    public Purchase_Order__c po;

    public List<SelectOption> validShopProducts {
        get {
        	List<Shop_Product__c> products = [SELECT Id, Name FROM Shop_Product__c WHERE AccountId = :po.Account__c];
        	List<SelectOption> theOptions = new List<SelectOption>();
            
            for(Shop_Product__c theProduct :products) {
                theOptions.add(new SelectOption(theProduct.Id, theProduct.Name));
            }
            
            return theOptions;
        }
        set;
    }
    
    public PurchaseItemEditExtension(ApexPages.StandardController stdController) {
    	pi = (Purchase_Item__c)stdController.getRecord();
        po = [SELECT Name, Account__c FROM Purchase_Order__c WHERE Id = :pi.Purchase_Order__c];
    }
    
}

You'll obviously need to flesh out the VF page to include all the other necessary fields, as well as any other actions you want buttons for.
 
This was selected as the best answer
Neo NashvilleNeo Nashville

Also, when previewing the vf page I'm getting this error:

 

List has no rows for assignment to SObject 

Dorian Sutton 9Dorian Sutton 9
How are you accessing the Visualforce page?  The trick of selecting the valid purchase items will only work if your Purchase Item already has a parent Purchase Order Id.

The code in the extension is firing a QueryException because it can't query the Purchase Order record, because there is no Purchase Order Id.

This is why I said earlier that you need to override the standard Create and Edit buttons on the Purchase Item object to point to the Visualforce page.  If you then try to create a Purchase Item from the related list on the Purchase Order detail page, the page will then be instantiated with a value for the Purchase Order Id.

If you're not familiar with how to override standard pages, check this out:
https://help.salesforce.com/HTViewHelpDoc?id=links_customize_override.htm

You will need to make sure that your users do not have the option to create Purchase Items directly (for example remove the Tab so they are only visible in the related list on Purchase Orders).  You should probably also catch the query exception and write some kind of sensible error message to the user if they somehow make their way to the page without a valid Purchase Order Id.

Good luck.
Neo NashvilleNeo Nashville

Thanks Dorian,

The VF page is already assigned to the New and Edit buttons on the purchase item object. As I explained earlier, the purchase order id is already populated since the only way to add a purchase item is via the purchase order object. In other words, the purchase item is the child (junction object) of purchase order. 


But I do understand why there would be an error if you tried previewing the vf page since there is no purchase order id assigned to the purchase item this way.

Thanks for all your help!