0

I'm creating a visualforce page with search field for Account Name and the search result output should have the following details in a table: Account Name, Billing Country, Shipping Country, CONTACT NAME, Type, Year Created.

Now, I am able to display and search individual accounts like the one in the figure:

enter image description here

But what I need is to display the Account Names repeatedly for every Contacts it has, like the next figure: enter image description here

My search query goes like this

string searchquery='SELECT Id, Name, BillingCountry, ShippingCountry, Type, Year_Created__c, (SELECT Id, Name FROM Contacts__r) FROM Account WHERE Name LIKE \'%'+acct.Name+'%\' Limit 20'; 
acc= Database.query(searchquery);

But whenever I try to display the Contacts__r field in visualforce page using

<apex:column value="{!a.Contacts__r}" /> 

I get this error:

Could not resolve field 'Contacts__r' from value binding '{!a.Contacts__r}' in page accountsearch

What could I have been doing wrong? Anyone who can shed some light regarding this is greatly appreciated!

EDIT-------------------------------------------------------- I tried using a wrapper as @Ankita has mentioned but I can't seem to make it work. Can you please help me debug my code?

public class AccountSearchController {

Public list <account> acc {get; set;}
Public list <contact> con {get; set;}
Public String country {get; set;}
Public String contactName {get; set;}
Public Account acct{get; set;}
Public Contact cont{get; set;}

public List<aTableRow> tableRows { get; set; }

public class aTableRow {
    public Contact theContact { get; set; }
    public Account theAccount { get; set; }
    public aTableRow( Account a, Contact c) {
        theContact = c;
        theAccount = a;
    }
}


Public AccountSearchController (ApexPages.StandardController controller)
{
    acct=(Account)controller.getRecord();
    acct.OwnerId = UserInfo.getUserId();

    cont=new Contact();
    cont.OwnerID = UserInfo.getUserID();

    search();

    con = [SELECT Id, Name, AccountId FROM Contact WHERE Contact.AccountId IN :acc];

    tableRows = new List<aTableRow>();
    for(Account a: acc){
        for(Contact c: con) {
            tableRows.add(new aTableRow(a,c));
        }
    }

}

Public void search(){

    string searchquery='SELECT Id, Name, BillingCountry, ShippingCountry, Type, Year_Created__c FROM Account WHERE Name LIKE \'%'+acct.Name+'%\' Limit 20'; 
    acc= Database.query(searchquery);

    string searchqueryCountry='SELECT Id, Name, BillingCountry, ShippingCountry, Type, Year_Created__c FROM Account WHERE BillingCountry LIKE \'%'+country+'%\' Limit 20'; 
    acc= Database.query(searchqueryCountry);

    string searchqueryContact='SELECT Id, Name, BillingCountry, ShippingCountry, Type, Year_Created__c FROM Account WHERE Contact__r LIKE \'%'+contactName+'%\' Limit 20'; 
    acc= Database.query(searchqueryContact);

    string searchqueryType='SELECT Id, Name, BillingCountry, ShippingCountry, Type, Year_Created__c FROM Account WHERE Type LIKE \'%'+acct.Type+'%\' Limit 20'; 
    acc= Database.query(searchqueryType);   



}

}

  • Why don't you use <apex:relatedList> tag. And by the way, the correct API name for Contact is Contacts and not Contacts__r – Rohit Mourya Jul 17 '17 at 10:26
  • I'm afraid that will not have worked as I'm doing a search functionality, meaning the list should only return the results with the specific characters. And I used Contacts__r because I was querying in Accounts which has a child relationship with contacts. – Raven D. Collins Jul 17 '17 at 11:17
0

Account and Contact have master detail relationship. So, one account can have multiple contacts. To display the Account and its multiple contact, change your SOQL to fetch the Contact records for the given Account Name and then iterate through the Contact records.

Your query should be:

string searchquery='SELECT Id, Name, Account.BillingCountry, Account.ShippingCountry, Account.Type, Account.Year_Created__c FROM Contact WHERE Account.Name LIKE \'%'+acct.Name+'%\' Limit 20'; 
List<Contact> = Database.query(searchquery);

VF page:

<apex:pageBlock title="My Content">
    <apex:pageBlockTable value="{!contacts}" var="contact">
        <apex:column value="{!contact.Account.Name}"/>
        <apex:column value="{!contact.Account.BillingCountry}"/>
        <apex:column value="{!contact.Account.ShippingCountry}"/>
        <apex:column value="{!contact.name}"/>
        <apex:column value="{!contact.Account.Type}"/>
    </apex:pageBlockTable>
</apex:pageBlock>

For the scenario that if no contact is present for the Account, create a Wrapper like this

public AcctContactWrapper {
     public String acctName {get;set;}
     public String billingCountry{get;set;}
     public String contactName{get;set;}
     // include rest of the fields here

}

The query should be same

string searchquery='SELECT Id, Name, BillingCountry, ShippingCountry, Type, Year_Created__c, (SELECT Id, Name FROM Contacts) FROM Account WHERE Name LIKE \'%'+acct.Name+'%\' Limit 20'; 
acc= Database.query(searchquery);

List<AcctContactWrapper> wrappers = new List<AcctContactWrapper>();
for(Account acctObject : acc){
   if(acctObject.Contacts != null && acctObject.Contacts.size > 0){
       AcctContactWrapper wrapperObj = new AcctContactWrapper();
       wrapperObj.acctName  = acctObject.Name;
       wrapperObj.billingCountry = acctObject.BillingCountry;
       // fill for other fields
       wrappers.add(wrapperObj);
   } else {
       for(Contact c : acctObject.Contacts){
           AcctContactWrapper wrapperObj = new AcctContactWrapper();
           wrapperObj.acctName  = acctObject.Name;
           wrapperObj.billingCountry = acctObject.BillingCountry;
           wrapperObj.contactName= acctObject.Contact.Name;
           // fill for other fields
           wrappers.add(wrapperObj);
       }
   }
}

VF page:

<apex:pageBlock title="My Content">
    <apex:pageBlockTable value="{!wrappers}" var="wrapperObject">
        <apex:column value="{!wrapperObject.acctName}"/>
        <apex:column value="{!wrapperObject.billingCountry }"/>
        <apex:column value="{!wrapperObject.contactName}"/>
    </apex:pageBlockTable>
</apex:pageBlock>

Refer Wrapper in Salesforce

  • This would've worked if all the Account has Contacts and vice versa. But for the Accounts with no Contacts, the query will not return any Account details (as we are querying in Contacts records). That is the reason why I am doing my query in Accounts. So now my conflicting problem is how would I be able to display Accounts with multiple contacts repeatedly while also being able to display an Account without a contact. :( – Raven D. Collins Jul 17 '17 at 11:13
  • You can achieve this functionality using a wrapper. Create a wrapper which stores the information of both Account and Contact. – Ankita Jul 17 '17 at 11:16
  • This is the first time that I have come across a "wrapper" since I am a newb in salesforce. I will have to read about it for a sec, but if you can point me to a resource that will help me understand it quickly, it will also be great. Thanks either way! :) – Raven D. Collins Jul 17 '17 at 11:21
  • Updated the answer for Wrapper. Please ignore the typos in the code. Do mark it useful and resolved if it works for you. – Ankita Jul 17 '17 at 11:30
  • @RavenD.Collins Is it not working for you? – Ankita Jul 18 '17 at 5:49
0

You just need to replace object API of contact,

Use Contacts instead of Contacts__r in your soql

__r represents a custom relationship. There are two uses for __r. We use it when we query a custom relationship from child to parent, or from parent to child.

0

These are arranged in pageBlockSection with 4 columns. So first 4 item will be placed horizontally(here first we will place all 4 labels), then input items will be placed in next row. Refer below code -

<apex:page controller="contactController">
  <apex:form>
  <apex:pageBlock title="Search Criteria">

      <apex:pageBlockSection columns="4">
                <apex:pageBlockSectionItem>
                    <apex:outputLabel value="Account Name"></apex:outputLabel>
                </apex:pageBlockSectionItem>
                <apex:pageBlockSectionItem>
                    <apex:outputLabel value="Billing Country"></apex:outputLabel>
                </apex:pageBlockSectionItem>
                <apex:pageBlockSectionItem>
                    <apex:outputLabel value="Contact Name"></apex:outputLabel>
                </apex:pageBlockSectionItem>
                <apex:pageBlockSectionItem>
                    <apex:outputLabel value="Account Type"></apex:outputLabel>
                </apex:pageBlockSectionItem>
                <apex:pageBlockSectionItem>
                    <apex:inputField value="{!con.Account.Name}"/>
                </apex:pageBlockSectionItem>
                <apex:pageBlockSectionItem>
                    <apex:inputField value="{!con.Account.BillingCountry}"/>
                </apex:pageBlockSectionItem>
                <apex:pageBlockSectionItem>
                    <apex:inputField value="{!con.FirstName}"/>
                </apex:pageBlockSectionItem>
                <apex:pageBlockSectionItem>
                    <apex:inputField value="{!con.Account.Type}"/>
                </apex:pageBlockSectionItem>

      </apex:pageBlockSection>

  </apex:pageBlock>
  </apex:form>
</apex:page>

public class contactController {
  public Contact con {get;set;}

  public contactController(){
    con = new Contact();
  }
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.