function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
matthew.w.hampton.ax1837matthew.w.hampton.ax1837 

Apex to Insert Multiple Child Records

Good Afternoon:

 

I am in the need of a little guidance on the trigger below.

 

I have a custom object (MTU_Location__c) that is the parent in a master-detail to another custom object (MTU_Address__c).

 

I have a trigger bult to create child records based upon certain criteria on the parent object. But what I cannot figure out is how to create multiple child records upon the insertion of the parent.

 

There could be as few as 0 or as many as 15-20 child records that need created upon creation of the parent.

 

Below is the trigger. Any help is greatly appreciated.

 

Thanks,

 

Hampton

 

trigger MTUAddressCreation on MTU_Location__c (after insert, after update) {

    Set<String> mtuAddress = new Set<String>();
     for(MTU_Location__c mtu: trigger.new) {
     mtuAddress.add(mtu.Street_Address__c);
   }
    
    Map<String, Billing_Account__c> billingAccount = new Map<String, Billing_Account__c>();
    for(Billing_Account__c billingAccount1: [Select ID, Name, Account__c, Address_1__c, City__c, State__c, Zip_Code__c, Billing_Account_ID__c from Billing_Account__c where Billing_System__c='CRIS' AND Billing_Account_Status__c='Live' AND Address_1__c in: mtuAddress])
    billingAccount.put(billingAccount1.Address_1__c, billingAccount1); 
    
    List<MTU_Address__c> newAddresses = new List<MTU_Address__c>();
        for(MTU_Location__c location: [Select ID, Name, Street_Address__c, City__c, State__c, Zip_Code__c from MTU_Location__c where Street_Address__c in : billingAccount.keyset()]){
            newAddresses.add(new MTU_Address__c(
                Address__c = location.Street_Address__c,
                City__c = location.City__c,
                State__c = location.State__c,
                Zip_Code__c = location.Zip_Code__c,
                Account__c = billingAccount.get(location.Street_Address__c).Account__c,
                Billing_Account__c=billingAccount.get(location.Street_Address__c).ID,
                MTU_Location__c = location.ID));
  
    }
   insert newAddresses;
}

 

Best Answer chosen by Admin (Salesforce Developers) 
GlynAGlynA

I see the problem.  Because we added the ability to find similar addresses (with suite numbers, etc), the Billing Account address field is no longer an exact match of the MTU Location address, so using it as the key in the mtuAddress map is returning null.

 

Here's the revised code.

 

-Glyn

 

trigger MTUAddressCreation on MTU_Location__c ( after insert, after update )
{
    Map<String, MTU_Location__c> mtuAddress = new Map<String, MTU_Location__c>();
    for ( MTU_Location__c mtu : Trigger.new )
    {
        mtuAddress.put( mtu.Street_Address__c, mtu );
    }

    List<String> comparisons = new List<String>();
    for ( String address : mtuAddress.keySet() )
    {
        comparisons.add( 'Address_1__c LIKE \'' + address + '%\'' );
    }

    List<MTU_Address__c> newAddresses = new List<MTU_Address__c>();

    for ( Billing_Account__c billingAccount1 : Database.query
        (   'SELECT Id, Name '
        +   'FROM sObject '
        +   'WHERE Billing_System__c = \'CRIS\' AND Billing_Account_Status__c = \'Live\' AND '
        +   '(' + String.join( comparisons, ' OR ' ) + ')'
        )
        )
    {
        MTU_Location__c location = null;

        for ( String address : mtuAddress.keySet() )
        {
            if ( !billingAccount1.Address_1__c.startsWith( address ) ) continue;

            location = mtuAddress.get( address );
            break;
        }
        if ( location == null ) continue;

        newAddresses.add
        (   new MTU_Address__c
            (   Address__c          = location.Street_Address__c,
                City__c             = location.City__c,
                State__c            = location.State__c,
                Zip_Code__c         = location.Zip_Code__c,
                Account__c          = billingAccount1.Account__c,
                Billing_Account__c  = billingAccount1.Id,
                MTU_Location__c     = location.Id
            )
        );
    }
    insert newAddresses;
}

 

All Answers

GlynAGlynA

Hampton,

 

I think this is what you have in mind.  The code assumes that each MTU_Location__c record has a unique Street_Address__c, otherwise it won't work quite right.  It inserts a new MTU_Address__c record for each Billing_Account__c that has a matching Address_1__c field and meets the other criteria.  It takes advantage of the fact that all fields are available in a trigger to avoid querying the MTU_Location__c records.

 

trigger MTUAddressCreation on MTU_Location__c ( after insert, after update )
{
    Map<String, MTU_Location__c> mtuAddress = new Map<String, MTU_Location__c>();
    for ( MTU_Location__c mtu : Trigger.new )
    {
        mtuAddress.put( mtu.Street_Address__c, mtu );
    }

    List<MTU_Address__c> newAddresses = new List<MTU_Address__c>();

    for ( Billing_Account__c billingAccount1 :
        [   SELECT  Id, Account__c, Address_1__c
            FROM    Billing_Account__c
            WHERE   (   Billing_System__c = 'CRIS'
                    AND Billing_Account_Status__c = 'Live'
                    AND Address_1__c IN :mtuAddress.keySet()
                    )
        ]
        )
    {
        MTU_Location__c location = mtuAddress.get( billingAccount1.Address_1__c );

        newAddresses.add
        (   new MTU_Address__c
            (   Address__c          = location.Street_Address__c,
                City__c             = location.City__c,
                State__c            = location.State__c,
                Zip_Code__c         = location.Zip_Code__c,
                Account__c          = billingAccount1.Account__c,
                Billing_Account__c  = billingAccount1.Id,
                MTU_Location__c     = location.Id
            )
        );
    }
    insert newAddresses;
}

 

If this helps, please mark it as a solution, and give kudos (click on the star) if you think I deserve them. Thanks!

 

-Glyn Anderson
Certified Salesforce Developer | Certified Salesforce Administrator
matthew.w.hampton.ax1837matthew.w.hampton.ax1837

Awesome, that worked beautifully. Thank you so much!
matthew.w.hampton.ax1837matthew.w.hampton.ax1837

OK so one more question...

 

In line

 

AND Address_1__c IN :mtuAddress.keySet()

 

I have discovered that a unique MTU_Location__c address (ex. 123 Main St) will have multiple billing accounts within the building with different sutie numbers (ex. Address_1__c = '123 Main St Suite 1', '123 Main St Suite 2', etc). In this case, I would want child records created for both.

 

How do I re-write that line with the fuzzy logic to search the values in the mtuAddress.keySet() for the value of Address_1__c?

 

Thanks,

 

Matt
GlynAGlynA

Matt,

 

That's trickier but this should do it.  Let me know if you have any questions or problems.

 

trigger MTUAddressCreation on MTU_Location__c ( after insert, after update )
{
    Map<String, MTU_Location__c> mtuAddress = new Map<String, MTU_Location__c>();
    for ( MTU_Location__c mtu : Trigger.new )
    {
        mtuAddress.put( mtu.Street_Address__c, mtu );
    }

    List<String> comparisons = new List<String>();
    for ( String address : mtuAddress.keySet() )
    {
        comparisons.add( 'Address_1__c LIKE \'' + address + '%\'' );
    }

    List<MTU_Address__c> newAddresses = new List<MTU_Address__c>();

    for ( Billing_Account__c billingAccount1 : Database.query
        (   'SELECT Id, Name '
        +   'FROM sObject '
        +   'WHERE Billing_System__c = \'CRIS\' AND Billing_Account_Status__c = \'Live\' AND '
        +   '(' + String.join( comparisons, ' OR ' ) + ')'
        )
        )
    {
        MTU_Location__c location = mtuAddress.get( billingAccount1.Address_1__c );

        newAddresses.add
        (   new MTU_Address__c
            (   Address__c          = location.Street_Address__c,
                City__c             = location.City__c,
                State__c            = location.State__c,
                Zip_Code__c         = location.Zip_Code__c,
                Account__c          = billingAccount1.Account__c,
                Billing_Account__c  = billingAccount1.Id,
                MTU_Location__c     = location.Id
            )
        );
    }
    insert newAddresses;
}

 

If this helps, please mark it as a solution, and give kudos (click on the star) if you think I deserve them. Thanks!

 

-Glyn Anderson
Certified Salesforce Developer | Certified Salesforce Administrator
matthew.w.hampton.ax1837matthew.w.hampton.ax1837

Now I am getting the null pointer exception error at line 27. Normally, I know how to solve this however cannot figure out how to do so on this one. Any ideas?

 

Thanks,

 

Hampton
GlynAGlynA

I see the problem.  Because we added the ability to find similar addresses (with suite numbers, etc), the Billing Account address field is no longer an exact match of the MTU Location address, so using it as the key in the mtuAddress map is returning null.

 

Here's the revised code.

 

-Glyn

 

trigger MTUAddressCreation on MTU_Location__c ( after insert, after update )
{
    Map<String, MTU_Location__c> mtuAddress = new Map<String, MTU_Location__c>();
    for ( MTU_Location__c mtu : Trigger.new )
    {
        mtuAddress.put( mtu.Street_Address__c, mtu );
    }

    List<String> comparisons = new List<String>();
    for ( String address : mtuAddress.keySet() )
    {
        comparisons.add( 'Address_1__c LIKE \'' + address + '%\'' );
    }

    List<MTU_Address__c> newAddresses = new List<MTU_Address__c>();

    for ( Billing_Account__c billingAccount1 : Database.query
        (   'SELECT Id, Name '
        +   'FROM sObject '
        +   'WHERE Billing_System__c = \'CRIS\' AND Billing_Account_Status__c = \'Live\' AND '
        +   '(' + String.join( comparisons, ' OR ' ) + ')'
        )
        )
    {
        MTU_Location__c location = null;

        for ( String address : mtuAddress.keySet() )
        {
            if ( !billingAccount1.Address_1__c.startsWith( address ) ) continue;

            location = mtuAddress.get( address );
            break;
        }
        if ( location == null ) continue;

        newAddresses.add
        (   new MTU_Address__c
            (   Address__c          = location.Street_Address__c,
                City__c             = location.City__c,
                State__c            = location.State__c,
                Zip_Code__c         = location.Zip_Code__c,
                Account__c          = billingAccount1.Account__c,
                Billing_Account__c  = billingAccount1.Id,
                MTU_Location__c     = location.Id
            )
        );
    }
    insert newAddresses;
}

 

This was selected as the best answer
GlynAGlynA

Staring at the code, I thought of another problem you might run into someday.  If any of the MTU Location addresses contains one of the characters: single-quote, percent, or underscore, they will mess up the query.  If you find this problem, add this method to your code:

 

public static String scrub( String inputString )
{
    return String.escapeSingleQuotes( inputString ).replaceAll( '%', '\\\\%' ).replaceAll( '_', '\\\\_' );
}

and change this line:

 

    comparisons.add( 'Address_1__c LIKE \'' + address + '%\'' );

to this:

 

    comparisons.add( 'Address_1__c LIKE \'' + scrub( address ) + '%\'' );

 

I've seen problems like this with names like "O'Hara" in the database.

 

-Glyn
matthew.w.hampton.ax1837matthew.w.hampton.ax1837

OK...so now I am running into another issue.

 

Billing_Account__ID__c is a large custom object with over 100k records. I have an External ID field (Billing_Account__ID__c) that I have included in the SOQL (as you will see). I only want Billing_Account__c records where Billing_Account_ID__c begins with '513' or '859' and Billing_System__c = 'Cris' and Account_Status__c = 'Live'.

 

When I do some digging into the records in the custom object, I only see 89,000 records, yet I am still getting the non-selective error.

 

Please see below and let me know any advice you have that might help.

 

Thanks,

 

Hampton

 

trigger MTUAddressCreation on MTU_Location__c ( after insert)
{
    Map<String, MTU_Location__c> mtuAddress = new Map<String, MTU_Location__c>();
    for ( MTU_Location__c mtu : Trigger.new )
    {
        mtuAddress.put( mtu.Street_Address__c, mtu );
    }

    List<String> comparisons = new List<String>();
    for ( String address : mtuAddress.keySet() )
    {
        comparisons.add( 'Address_1__c LIKE \'' + address + '%\'' );
    }

    List<MTU_Address__c> newAddresses = new List<MTU_Address__c>();

    for ( Billing_Account__c billingAccount1 : Database.query
        (   'SELECT Id, Name, Account__c, Address_1__c, Billing_Account_ID__c '
        +   'FROM Billing_Account__c '
        +   'WHERE (Billing_Account_ID__c LIKE \'513%\' OR Billing_Account_ID__c LIKE \'859%\') AND Billing_System__c = \'CRIS\' AND Billing_Account_Status__c = \'Live\' AND'
        +   '(' + String.join( comparisons, ' OR ' ) + ')'
        )
        )
    {
        MTU_Location__c location = null;

        for ( String address : mtuAddress.keySet() )
        {
            if ( !billingAccount1.Address_1__c.startsWith( address ) ) continue;

            location = mtuAddress.get( address );
            break;
        }
        if ( location == null ) continue;

        newAddresses.add
        (   new MTU_Address__c
            (   Address__c          = billingAccount1.Address_1__c,
                City__c             = location.City__c,
                State__c            = location.State__c,
                Zip_Code__c         = location.Zip_Code__c,
                Account__c          = billingAccount1.Account__c,
                Billing_Account__c  = billingAccount1.Id,
                MTU_Location__c     = location.Id
            )
        );
    }
    insert newAddresses;
}

 
GlynAGlynA

Not sure if this will work, but try splitting the query into two queries - each one more selective than the original:

 

List <Billing_Account__c> theBillingAccounts =
    Database.query
    (   'SELECT Id, Name, Account__c, Address_1__c, Billing_Account_ID__c '
    +   'FROM Billing_Account__c '
    +   'WHERE Billing_Account_ID__c LIKE \'513%\' AND Billing_System__c = \'CRIS\' AND Billing_Account_Status__c = \'Live\' AND '
    +   '(' + String.join( comparisons, ' OR ' ) + ')'
    );

theBillingAccounts.addAll
(   Database.query
    (   'SELECT Id, Name, Account__c, Address_1__c, Billing_Account_ID__c '
    +   'FROM Billing_Account__c '
    +   'Billing_Account_ID__c LIKE \'859%\' AND Billing_System__c = \'CRIS\' AND Billing_Account_Status__c = \'Live\' AND '
    +   '(' + String.join( comparisons, ' OR ' ) + ')'
    )
);

for ( Billing_Account__c billingAccount1 : theBillingAccounts )
{
    //  etc.
}

 

You may have already done this, but the way to see how many records there are is: Setup | Data Management | Storage Usage.  This will tell you how many records of evrything is in your database, and how much of your storage they consume.

 

-Glyn
matthew.w.hampton.ax1837matthew.w.hampton.ax1837
Good Morning:

So I had to do some tweaking to this for multiple reasons too deep to get into on here.

What I need to do know is when an MTU_Location__c record is inserted, loop through the Billing_Account__c records and update a field on Billing_Account__c with the MTU_Location__c.Name value.

Here is the query I have currently:

trigger MTUAddressCreation on MTU_Location__c (after insert)
{
    Map<String, MTU_Location__c> mtuAddress = new Map<String, MTU_Location__c>();
    for ( MTU_Location__c mtu : Trigger.new )
    {
        mtuAddress.put( mtu.Street_Address__c, mtu );
    }

    List<String> comparisons = new List<String>();
    for (String address : mtuAddress.keySet())
    {
        comparisons.add('Address_1__c LIKE \'' + address + '%\'' );
    }

    List<Billing_Account__c> newAddresses = new List<Billing_Account__c>(); 
    for (Billing_Account__c billingAccount1 : Database.query
        (   'SELECT Id, Name, Address_1__c, Billing_Account_ID__c, Billing_Account_ID_Formatted__c, Billing_System__c, Billing_Account_Status__c, MTU_Name__c'
        +   'FROM Billing_Account__c'
        +   'WHERE (Billing_Account_ID_Formatted__c LIKE \'859-%\' OR Billing_Account_ID_Formatted__c LIKE \'513-%\') AND Billing_System__c = \'CRIS\' AND Billing_Account_Status__c = \'Live\' AND'
        +   '(' + String.join(comparisons, ' OR ')+')'
        )
        )
    {
        MTU_Location__c location = null;

        for (String address : mtuAddress.keySet())
        {
            if (!billingAccount1.Address_1__c.startsWith(address)) continue;

            location = mtuAddress.get(address);
            break;
        }
        if (location == null) continue;

        billingaccount1.MTU_Name__c = location.Name;
    }
    update newAddresses;
}

I am getting an error on the line in bold above that is:

System.QueryException: unexpected token: (: Trigger.MTUAddressCreation: line 16, column 1

Any help is appreciated.

Thanks,

Matt