+ Start a Discussion
cdolcdol 

Hitting 1000 VF record limit, need SOQL query

Hello,

 

I've just hit the 1000 VF record limit on a page where I pull all my organization's accounts, to then tie them to contacts.

 

The page is used to organize a heirarchy of our client portfolios, so that my employees can easily tie descision makers to the proper accounts. For instance, if the client has a regional and local manager, the regional manager would be tied to 100 accounts, and the local manager tied to only 1 or 2 accounts. 

 

Right now, we have a custom field on an Account called Billing Contact. This is the contact related to that location. 

 

In our Org, we have a very accurate Parent/Child account hierarchy. For instance, if Hilton Hotels was a client of ours, there is a 'Hilton Parent' account, and then hundreds/thousands of Accounts that have 'Hilton Parent' account listed as their parent. Also, contacts are only ever listed as Billing Contacts in one hierarchy structure. For instance, 'Jane Doe' is never going to be listed as the Billing Contact for a Marriot Hotel, or any other account outside the Hilton Parent/Child tree.

 

So here is what I'd like to do, but given my generally poor SOQL knowledge, I'm struggling:

 

1) Find an Account that the contact is listed as the BIlling Contact for. 

2) Find the Parent Account of that Account

3) Display ALL child accounts of that parent account. 

 

If anyone has an example to lead me towards, I'd appriciate it! 

 

Charlie

SFFSFF

The problem here is not your SOQL - necessarily - but rather that you are trying to display more than 1000 records on your VF page. This is not allowed. You should research standardSetController and set up pagination on your page - that should fix this problem.

cdolcdol

I thought about pagination, but I figured I would like to limit the results on the VF page anyways - As I said, I will never be putting a contact to a client that is outside their heirarchy structure.

 

Essentially, as my company continues to grow, I do not want my employees to have to scroll through 20 pages of pagination to select the proper accounts. I'd like to just display the relavent child accounts, and have it summarize the information at this point in our process.

 

Edit: this also assumes that none of our clients have more than 999 'locations/accounts' in our system. If a client had more than 999 accounts, I'd like to have pagination take over, because we've now distilled the displayed accounts to those relavent to the contact - it just so happens that this is a very large client of ours!

Andrew WilkinsonAndrew Wilkinson

I believe even with pagination you will encounter the error. The issue is with a collection limit being referenced on a visualforce page. Pagination does not change the actual size of the collection. You would need to use the new offset soql feature to do it this way...however...

 

There is a way to display more than 1000 records at a time using a list of a list. As a warning this will affect performance when displaying more than 1000 records. So be wary about how many records you want to display.

 

Here is some sample code.

 

public objectListOfLists List<List<Account>> {get; set;}

public void createLocationsList(){
        List<Account> tempList = new List<Account>();
        objectListOfLists = new List<List<Account>>();
        //loop through the query you had
        for (Account a : accounts){
            tempList.add(a);
            if (tempList.size() == 1000){
                objectListOfLists.add(tempList);
                tempList = new List<Account>();
            }
        }
        if(!tempList.isEmpty()){
            objectListOfLists.add(tempList);
        }
}

 Then...you will need to use an apex repeat along with a pageblock table. The repeat will iterate over each list as an individual list and then the pageblocktable or whatever you use will iterate over the specific list.

 

For example...

 

<apex:repeat value="{!objectListOfLists}" var="oll">

    <apex:pageBlockTable value="{!oll}" var="acc">

         <apex:column value="{!acc.Name}"/>

    </apex:pageBlockTable>

</apex:repeat>

 

 

 

 

cdolcdol

Andrew,

 

Thank you for the reply. I saw something like this elsewhere on the forums. 

 

Right now I only have 1069 accounts, but I'd really like to sort out the SOQL query to make this work further into the future wihtout more attention. 

 

A quick patch I think I could do is only pull Accounts where 'Type = Customer'. We have a lot of 'Type - Prospect' accounts in our system, which is pushing us over the 1000 limit. 

 

However, when using the Force.com IDE, 'type' seems to be a similar command to ' and ', ' where', and 'when'. 

 

Whats the proper syntax to pull accounts where type="customer"?

Andrew WilkinsonAndrew Wilkinson

Yeah you just format your SOQL like below:

 

[SELECT Name, Type FROM Account WHERE Type = 'Customer']

 

or you can do something like this if you wanted it where it contained customer:

 

[SELECT Name,Type FROM Account WHERE Type LIKE '%Customer%']

cdolcdol

I think I'm a little confused 

 

I am guessing the second one you posted, with the LIKE %Customer% , is only useful if I have multiple options on the picklist that contain Customer? So if, theoretically, I had 'Great Customer' ' Awful Customer', etc? 

 

I only have customer as 1 value in the picklist, so I dont think it I'd see any different results

Jake GmerekJake Gmerek

Yes you are correct that you will see no difference.  To answer your first issue though;

 

contact c = my contact;  // i am assuming that you are setting this up somewhere.

 

account a = [select name, parentId from account where billing_contact__c = c.id LIMIT 1][0];  //not sure how you identify a billing contact

 

account parentAccount = [select name, id, type from account where id = a.parentID Limit 1][0];

 

List<account> allChildren = new list<account> ([select name, id, type from account where parentID = parentAccount.ID]);

 

You may be able to combine the last two queries for efficiency, but I could not find the relationship name for parentID offhand.

vishal@forcevishal@force

Are you doing any kind of DML's on this page? Or it is only used for displaying something to the user?  I mean is it something like a read-only page?

 

If yes, then you may like to explore the "readOnly" attribute for visualforce pages. It may even help you in the future considering the record set you are working with.

 

More information here: http://www.salesforce.com/us/developer/docs/pages/Content/pages_controller_readonly_context.htm

cdolcdol

Vishal,

 

I am using the page to select these accounts with a checkbox, so ReadOnly is not really a solution, unfortunately. Thanks for the input though! 

 

Charlie