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
Eager-2-LearnEager-2-Learn 

One Export file created from multiple objects in SFDC?

Hi, I am going to try to explain my situation in another way.  Below are the two seperate queries that some how I want to join together so that the final results are stored in a single custom object.  I have included AccountID in both queries in hopes to tight them together by that common field.  Each fields data should be stored in the custom object.

 

Of course I will have WHERE clauses on both queries but to keep it simple I did not include the WHERE clause.

 

Select o.AccountId, o.Id, o.Lead_Rep__c, o.OwnerId, o.Owner.Email, o.Owner.FirstName, o.Owner.LastName, o.Owner.Phone from Opportunity o

Select c.AccountId, c.Account.EIN__c, c.Email, c.FirstName, c.LastName, c.Phone from Contact c

 Does anyone have any clues or examples of what I am trying to do?

 

If I can get the results into a custom object then I can export using the DataLoader

Best Answer chosen by Admin (Salesforce Developers) 
Jeremy_nJeremy_n

Take a look at this, see if it does what you intend.

 

list<New_Object__c> newobjs = new list<New_Object__c>();

list<Contact> cons = [select id, AccountID from Contact where Checkbox__c = true];

set<id> accids = new set<id>();
for (Contact c : cons) accids.add(c.AccountID);

map<id, Account> accmap = new map<id, Account>([select id, Name, (select id from Opportunities where StageName = 'Sold' and Effective_Date__c >= :system.today().addDays(-365) sort by Effective_Date__c desc limit 1) from Account]);

for (Contact con : cons) {
	newobjs.add(new New_Object__c(
		AccountID__c = con.AccountId,
		ContactID__c = con.id,
		Account_Name__c = accmap.get(con.AccountID).Name
	));
}

insert newobjs;

 

Two queries, joined by one map on AccountID. Let me know if you have questions.

 

Jeremy

 

All Answers

SuperfellSuperfell

Do you really need to copy the data into a custom object? you could just run both queries from the data loader.
Jeremy_nJeremy_n

I agree, I don't know why you need to make a new object to store this information.

 

On the other hand, maybe I don't understand what you're doing. Those two queries don't seem to be related to each other, so I don't know what your output is intended to look like. Can you give us a use case?

 

Jeremy
Eager-2-LearnEager-2-Learn

I need to produce one file and in this file it will have Opportunity and related account information along with the related account's contact information.  I need to get this out in one file to be feed to a Legacy system.  I created the two queries and in both queries I included the AccountID.  I thought that would tie them together in hopes to produce one file.  Once I get one file to the Legacy system.  They can clean it as they need it.  They want one file not two files.  I hope this help you to help me.  Thanks.

 

Oh, I did not give this detail but there will be a custom checkbox field on the Contact object and for any given account there will only be one contact that can have this field checked.  Therefore, in the query that is returning contact information there will only be one record per account in that query result.  I was trying not to include to much info so that you could just help with the concept of how I can build an array of data or custom object with data from reading two different objects or tables if you will.  If this was Visual Basic or MS Access I would have been done by now but I am new to the Apex world and they don't seem to have muli-dimention arrays and I am still struggling with how lists are iterated in Apex.  I appreciate your help.
Jeremy_nJeremy_n

Okay, I think I understand better. That detail about only using one Contact per Account makes a big difference.

 

I would do this in Apex using map collections to tie things together. Set up a map between AccountID and your Contact, and fill it with all the Contacts you'll need. Then as you build your CSV, you'll iterate through Opportunities, and for each Opportunity, you can get Account and Contact information from this map, using Opportunity.AccountID to key it.

 

How does that sound?

 

Jeremy
Eager-2-LearnEager-2-Learn

It sounds really great but I was hoping for some code snippet that does something like you are describing.  It doesn't have to be a finished product as I am not asking for someone to write my code but I just need a code snippet example to give me a head start.

 

For example, am I creating two maps from the queries that I have posted above then some how tieing them together with another map or something like that?  Then wouldn't he final result have to be in a custom object to that the DataLoader can export the data?
Jeremy_nJeremy_n

Is this just something you're doing one time? If that's the case, I would pull out the two queries you describe, and just use spreadsheets to join the files together, keyed on AccountID.

 

If you'll need this to happen a lot, and are looking to automate it, that might make the apex worth it. A little Excel does wonders, though.

 

Jeremy
Eager-2-LearnEager-2-Learn

Yes, as previously mentioned this export will be feed to a legacy system on a daily or weekly basis.  The duration has not been decided.

 

Let me describe my requirement in another way.

 

I need every account record and the most recent opportunity associated with that account record.  In addition, out of those records I only need the ones that have an associated account contact where the custom checkbox is checked.  This custom checkbox will reside on the Contact object.

 

The best that I could do to get to the solution was the two queries.  I thought if I could join them together within Apex then populate the results into a custom object that I could use the data loader on a daily or weekly basis (scheduled) to extract the data.  To populate the custom object I would us Apex Scheduler.

 

Would there be away to combine thosee two queries in Apex SOQL without breaking governlimits?

How can I iterate through this query and put all the field information into one custom object?  I kept it simple so that I can hopefully get a loop snippet on how to iterate through a query such as this one.  I think that answer may be a solution as long as it does not cause governor limits!

 

Select a.Id, (Select Email From Contacts limit 1), (Select Id, Owner.Name, Name From Opportunities WHERE StageName = 'Sold' and Effective_Date__c = LAST_N_DAYS:365 limit 1) from Account a

 

 
Jeremy_nJeremy_n

Take a look at this, see if it does what you intend.

 

list<New_Object__c> newobjs = new list<New_Object__c>();

list<Contact> cons = [select id, AccountID from Contact where Checkbox__c = true];

set<id> accids = new set<id>();
for (Contact c : cons) accids.add(c.AccountID);

map<id, Account> accmap = new map<id, Account>([select id, Name, (select id from Opportunities where StageName = 'Sold' and Effective_Date__c >= :system.today().addDays(-365) sort by Effective_Date__c desc limit 1) from Account]);

for (Contact con : cons) {
	newobjs.add(new New_Object__c(
		AccountID__c = con.AccountId,
		ContactID__c = con.id,
		Account_Name__c = accmap.get(con.AccountID).Name
	));
}

insert newobjs;

 

Two queries, joined by one map on AccountID. Let me know if you have questions.

 

Jeremy

 

This was selected as the best answer
Eager-2-LearnEager-2-Learn

I really appreciate the super nudge that you have given me.  Although, I have much testing to do, from the my DEV environment (little data) it appears to be working.  Of course I have more fields to add to the custom object and the queries but you really have put me weeks ahead.  Thank you very much and have a great holiday weekend.

 

public class EmpPortal {
    public static void EmpPortal(){
        list<EmpPortal__c> newobjs = new list<EmpPortal__c>();
        list<Contact> cons = [select id, AccountID from Contact where PortalContact__c = true];
        set<id> accids = new set<id>();
        for(Contact c : cons) accids.add(c.AccountID);
        map<id, Account> accmap = new map<id, Account>(
                                                      [Select id, Name, (Select id 
                                                                         From Opportunities 
                                                                         Where StageName = 'Sold' 
                                                                         Order By Effective_Date__c desc limit 1) 
                                                       From Account]);
    
        for (Contact con : cons) {
        newobjs.add(new EmpPortal__c(
                    AccountID__c = con.AccountId,
                    ContactID__c = con.id,
                    AccountName__c = accmap.get(con.AccountID).Name));
        }

        insert newobjs;
    }
}

 
Eager-2-LearnEager-2-Learn

I thought that I had this thing in the bag but now I keep getting an error when I try to reference the owner.Firstname field.

Any ideas what I am doing wrong.  I tried various types of dot notation references but they do not work.

The code below gives me the following error when I run the code:

SObject row was retrieved via SOQL without querying the requested field: Account.Owner

 

public class EmpPortal {
    public static void EmpPortal(){
        list<EmpPortal__c> newobjs = new list<EmpPortal__c>();
        list<Contact> cons = [select id, AccountID from Contact where PortalContact__c = true];
        set<id> accids = new set<id>();
        for(Contact c : cons) accids.add(c.AccountID);
        map<id, Account> accmap = new map<id, Account>(
                                                      [Select id, Name, (Select id, Owner.Firstname, 
                                                                         Owner.Lastname, owner.email 
                                                                         From Opportunities 
                                                                         Where StageName = 'Sold'                                                                         
                                                                         limit 1) 
                                                       From Account]);    
        for (Contact con : cons) {
        newobjs.add(new EmpPortal__c(
                    AccountID__c = con.AccountId,
                    ContactID__c = con.id,
                    AccountName__c = accmap.get(con.AccountID).Name,
                    OwnerFirstName__c = accmap.get(con.AccountID).Owner.FirstName));
        }

        insert newobjs;
    }
}

 
Jeremy_nJeremy_n

I see, you may need to get that list of Accounts as a list, and put it into two maps, then. One for Account information, and one for Opportunity Owner. Maybe another for Opportunity if you need that too. Instead of casting the query directly into a map, try this:

 

 

map<id, Account> accmap = new map<id, Account>();
map<id, Opportunity> accid2oppmap = new map<id, Opportunity>();
list<Account> accs = [Select id, Name, (Select id, Owner.Firstname, 
	Owner.Lastname, owner.email 
	From Opportunities 
	Where StageName = 'Sold'                                                                         
	limit 1) 
	From Account];    

for ( Account a : accs) {
	accmap.put(a.id, a);
	accid2oppmap.pt(a.id, a.Opportunities[0]);
}

 

You can add other maps that do the same kind of thing, they'll all be keyed on Account ID.

 

By the way, you're querying on ALL accounts every time, but I thought you only wanted accounts that had Contacts with PortalContact = true. You could probably use accids to reduce the size of that query result if you don't need all of them.

 

Also, you're picking a "Sold" Opportunity from the list, but you aren't guaranteeing that it's the most recent one.

 

have fun,

 

Jeremy

 
Eager-2-LearnEager-2-Learn

You are awesome but I am really getting lost now.  Man I wish there was writings better what is out in SFDC. I need a book that starts off crawling SOQL and gradually gets you running.  I thought I was starting to understand the prior code but now I don't even understand how I am getting the contact information into the picture.

 

You are right about the most recent opportunity.  I am working with DEV data (nothing recent) for now I just removed that out of the WHERE clause.  And yes, I do only need data that pertains to contacts that will have the checkbox checked.

 

One of the problems that I have is examples are around triggers and hardly nothing with just a simple class. 

 

What away to start my weekend, haaaaa:smileyindifferent:
Eager-2-LearnEager-2-Learn

Ok I blinked a couple of times and came up with this and ran it and the first and lastname information is on the EmpPortal custom object. :smileyvery-happy:

I do see what you are saying about the accs being to large because I do not have some way of only including the cons.AccountID's.

 

This is a new concept that is taking me time to grasp so I do appreciate your patience and support.

    public static void EmpPortal2(){
        list<EmpPortal__c> newobjs = new list<EmpPortal__c>();
        list<Contact> cons = [select id, AccountID from Contact where PortalContact__c = true];
        set<id> accids = new set<id>();
        for(Contact c : cons) accids.add(c.AccountID);

        map<id, Account> accmap = new map<id, Account>();
        map<id, Opportunity> accid2oppmap = new map<id, Opportunity>();
        list<Account> accs = [Select id, Name, (Select id, Owner.Firstname, 
                                                       Owner.Lastname, owner.email 
                                                From Opportunities 
                                                Where StageName = 'Sold'                                                                         
                                                limit 1) 
                              From Account];    

        for ( Account a : accs) {
            if ( a.opportunities.size() > 0 ) {
                accmap.put(a.id, a);
                accid2oppmap.put(a.id, a.Opportunities[0]);
            }
        }


        for (Contact con : cons) {
        newobjs.add(new EmpPortal__c(
                    AccountID__c = con.AccountId,
                    ContactID__c = con.id,
                    AccountName__c = accmap.get(con.AccountID).Name,
                    OwnerFirstName__c = accid2oppmap.get(con.AccountID).Owner.FirstName,
                    OwnerLastName__c = accid2oppmap.get(con.AccountID).Owner.LastName));
        }

        insert newobjs;
    }

 
Jeremy_nJeremy_n

Hey Tom,

 

The way to filter your Account query for just the relevant ones is to use that "accids" set you're not using. Modify your Account query to have "where ID in :accids", and you will then only be getting the relevant Accounts. It looks like you're just about there.

 

Jeremy
Eager-2-LearnEager-2-Learn

You are amazing brother.  I am going to quite for the weekend while I an ahead.  You have allowed me to be able to have a great holiday weekend.  I will add to this first thing Tuesday.  My next thoughts if I get this all working as desired is....

 

I will post this as a separate post but I just wanted to talk out loud.  I will create a purge class to clean the custom table prior to this code running that will populate the custom object.  In a scheduling tool like AutoSys you can creat dependency jobs so I was wondering how to do something like that in Apex/SFDC.

 

For example.  I see this process like this:

 

Purge Job runs

EmpPortal class code runs to populate the custom object

Export process runs to extract the custom object data

 

That would be one job but I know the DataLoader is external so I probabily will have that schedule hours later and check the export file to make sure it contains data but how can I make sure that the EmpPortal does not run unless the purge job was successful?  I know a work around would be to have the EmpPortal code check to make sure the custom object is empty but I am wondering from a job scheduling perspective if this is doable?