function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Gaby FrydmanGaby Frydman 

Running multiple queries on a controller

Hi All, 

I currently have a controller that logs people in  via their NIN and DOB. It works great, however I am expanding the functionality by adding multiple states with different forms.

My current code looks like that:
public without sharing class LearnerDetails_Controller {
    public Contact contact {get;set;}
    public Contact editContact {get;set;}
    public Integer state {get;set;}

    public LearnerDetails_Controller() {
        contact = new Contact(Id = null);
        editContact = null;
        state = 0;
    }
    
    public void loadContact() {
        try {
            //Verify NI number and birthday are available
            if (contact.National_Insurance__c == null
             || contact.BirthDate == null)
                 throw new PageException('Please enter your national insurance number and birth date.');
                 
            //Load contact
            List<Contact> contacts = [SELECT id, firstName, lastName, Nationality__c, ID_Issue_Date__c, Document_of_Identification__c, ts2__Mobile_Telephone_Number__c, Email, MailingAddress, MailingStreet, MailingCountry, MailingCity, MailingPostalCode, MobilePhone, Sex__c, ID_Expires__c, ID_Number__c, ID_Visa_Comments__c, Visa_Type__c, Visa_Expires__c, Marital_Status_use__c, Programme__c, Adviser__c, Adviser_s_Location_Portal__c, Adviser_s_Name_Portal__c, Benefit_Type__c, Disability__c, Ethnicity__c, CRB_conflict__c, Qualify_for_free_training__c, Name, General_Comments__c, ILP_Signed__c, F2L_Agreement__c, Years_in_UK__c, Start_Being_Learner__c
                                      FROM Contact 
                                      WHERE National_Insurance__c like :contact.National_Insurance__c
                                        AND BirthDate = :contact.BirthDate
                                      ORDER BY CreatedDate DESC
                                      LIMIT 1];
                                        
            if (contacts.size() == 0) {
                throw new PageException('We\'re sorry, but we could not find any applicant with the details you have entered.');
            }
            
            this.contact = contacts.get(0);
            this.editContact = contacts.get(0).clone();
            this.editContact.Id = null;
        } catch (Exception e) {
            ApexPages.addMessage(new ApexPages.Message(ApexPages.Severity.ERROR, e.getMessage()));
        }
    }
    
    public void doSave() {
        try {
            if (editContact == null) {
                throw new PageException('Please log in first.');
            }
            editContact.Id = contact.Id;
            
            update editContact;
            editContact.Id = null;
            ApexPages.addMessage(new ApexPages.Message(ApexPages.Severity.CONFIRM, 'Your details have been successfully saved.'));
            nextState();
        } catch (DMLException e) {
            editContact.Id = null;
            ApexPages.Message msg = new ApexPages.Message(Apexpages.Severity.ERROR, e.getdmlMessage(0) );
            ApexPages.addMessage(msg);
        }
    }

  public void nextState() {
    state++;
  }
    
    public class PageException extends Exception {}
}

And I want to add ILP__c, Agreement__c and VAK_ Questinnaire__c to the controller.
public without sharing class LearnerDetails_Controller {
    public Contact contact {get;set;}
    public Contact editContact {get;set;}
    public Integer state {get;set;}
    public LearnerDetails_Controller() {
    contact = new Contact(Id = null);
    editContact = null;
    state = 0;
}
public class loadForms{
    public List<ILP__c> editILP {get;set;}
    public List<F2L_Agreement__c> editAgreement {get;set;}
    public List<VAK_Questionnaire__c> editVAK {get;set;}


    
    public void loadContact() {
        try {
            //Verify NI number and birthday are available
            if (contact.National_Insurance__c == null
             || contact.BirthDate == null)
                 throw new PageException('Please enter your national insurance number and birth date.');
                 
            //Load contact
            List<Contact> contacts = [SELECT id, firstName, lastName, Nationality__c, ID_Issue_Date__c, Document_of_Identification__c, ts2__Mobile_Telephone_Number__c, Email, MailingAddress, MailingStreet, MailingCountry, MailingCity, MailingPostalCode, MobilePhone, Sex__c, ID_Expires__c, ID_Number__c, ID_Visa_Comments__c, Visa_Type__c, Visa_Expires__c, Marital_Status_use__c, Programme__c, Adviser__c, Adviser_s_Location_Portal__c, Adviser_s_Name_Portal__c, Benefit_Type__c, Disability__c, Ethnicity__c, CRB_conflict__c, Qualify_for_free_training__c, Name, General_Comments__c, ILP_Signed__c, F2L_Agreement__c, Years_in_UK__c, Start_Being_Learner__c
                                      FROM Contact 
                                      WHERE National_Insurance__c like :contact.National_Insurance__c
                                        AND BirthDate = :contact.BirthDate
                                      ORDER BY CreatedDate DESC
                                      LIMIT 1];
                                        
            if (contacts.size() == 0) {
                throw new PageException('We\'re sorry, but we could not find any applicant with the details you have entered.');
            }
            
            this.contact = contacts.get(0);
            this.editContact = contacts.get(0).clone();
            this.editContact.Id = null;
        } catch (Exception e) {
            ApexPages.addMessage(new ApexPages.Message(ApexPages.Severity.ERROR, e.getMessage()));
        }
    }
    
    private void loadForms() {
        // Load ILP
        Id contactId = this.contact.Id;
        List<ILP__c> frmILP = [SELECT Id, Additional_Learning_Support__c, Additional_Learning_Support_Comment__c, Agree_to_ILP__c, Any_planned_actions__c, Attended_an_Induction__c, Completed_enrolment_PW__c, Contact__c, Customer_Service_Skills__c, Date_Signed__c, Employ_ability_Skills__c, Employment_Goals_Comment__c, Experience_and_Knowledge__c, Formal_Training__c, Formal_Training_Comment__c, Further_information_and_advice__c, Improve_confidence__c, Information_and_Advice__c, Need_Relevant_Knowledge__c, Practical_Exp__c, Practical_Exp_Comment__c, Received_Copies__c, Where_Programme_Delivery__c, Who_my_advisor_is__c,Why_this_course__c FROM ILP__c WHERE Contact__c = :contactId ORDER BY CreatedDate DESC LIMIT 1];
        if (frmILP.size() > 0)
            editILP = frmILP.get(0);
        else
            editILP = new ILP__c(Contact__c = ContactId);
        // Load Agreement (if any)
       
        List<F2L_Agreement__c> frmAgreement = [SELECT Id, Name FROM ILP__c WHERE Contact__c = :contactId ORDER BY CreatedDate DESC LIMIT 1];
        if (frmAgreement.size() > 0)
            editAgreement = frmAgreement.get(0);
        else
            editAgreement = new F2L_Agreement__c(Contact__c = ContactId);
        // Load VAK
        
        List<VAK_Questionnaire__c> frmVAK = [SELECT Id, q9__c, q8__c, q7__c, q6__c, q5__c, q4__c, q30__c, q3__c, q29__c, q28__c, q27__c, q26__c, q25__c, q24__c, q23__c, q22__c, q21__c, q20__c, q2__c, q19__c, q18__c, q17__c, q16__c, q15__c, q14__c, q13__c, q12__c, q11__c, q10__c, q1__c  FROM VAK_Questionnaire__c WHERE Contact__c = :contactId ORDER BY CreatedDate DESC LIMIT 1];
        if (frmVAK.size() > 0)
            editVAK = frmVAK.get(0);
        else
            editVAK = new VAK_Questionnaire__c(Contact__c = ContactId);
        
    }
}
    
    
    public void doSave() {
        try {
            if (editContact == null) {
                throw new PageException('Please log in first.');
            }
            editContact.Id = contact.Id;
            
            update editContact;
            editContact.Id = null;
            ApexPages.addMessage(new ApexPages.Message(ApexPages.Severity.CONFIRM, 'Your details have been successfully saved.'));
            nextState();
        } catch (DMLException e) {
            editContact.Id = null;
            ApexPages.Message msg = new ApexPages.Message(Apexpages.Severity.ERROR, e.getdmlMessage(0) );
            ApexPages.addMessage(msg);
        }
    }

  public void nextState() {
    state++;
  }
    
    public class PageException extends Exception {}
}

But I get the following error:" Invalid bind expression type of Schema.SObjectField for column of type String at line 27 column 73 "

Have I not defined the variables correctly? Am I missing something? Cant get my head around it..
 
Ashish_Sharma_DEVSFDCAshish_Sharma_DEVSFDC
Hi ,

Replace you contact variable name by some other name such as say  'contactRecord'.

Please try below code.
public without sharing class LearnerDetails_Controller {
    public Contact contact1 {get;set;}
    public Contact editContact {get;set;}
    public Integer state {get;set;}
    public LearnerDetails_Controller() {
    contact1 = new Contact(Id = null);
    editContact = null;
    state = 0;
}
public class loadForms{
    public List<ILP__c> editILP {get;set;}
    public List<F2L_Agreement__c> editAgreement {get;set;}
    public List<VAK_Questionnaire__c> editVAK {get;set;}


    
    public void loadContact() {
        try {
            //Verify NI number and birthday are available
            if (contact1.National_Insurance__c == null
             || contact1.BirthDate == null)
                 throw new PageException('Please enter your national insurance number and birth date.');
                 
            //Load contact
            List<Contact> contacts = [SELECT id, firstName, lastName, Nationality__c, ID_Issue_Date__c, Document_of_Identification__c, ts2__Mobile_Telephone_Number__c, Email, MailingAddress, MailingStreet, MailingCountry, MailingCity, MailingPostalCode, MobilePhone, Sex__c, ID_Expires__c, ID_Number__c, ID_Visa_Comments__c, Visa_Type__c, Visa_Expires__c, Marital_Status_use__c, Programme__c, Adviser__c, Adviser_s_Location_Portal__c, Adviser_s_Name_Portal__c, Benefit_Type__c, Disability__c, Ethnicity__c, CRB_conflict__c, Qualify_for_free_training__c, Name, General_Comments__c, ILP_Signed__c, F2L_Agreement__c, Years_in_UK__c, Start_Being_Learner__c
                                      FROM Contact 
                                      WHERE National_Insurance__c like :contact1.National_Insurance__c
                                        AND BirthDate = :contact.BirthDate
                                      ORDER BY CreatedDate DESC
                                      LIMIT 1];
                                        
            if (contacts.size() == 0) {
                throw new PageException('We\'re sorry, but we could not find any applicant with the details you have entered.');
            }
            
            this.contact1 = contacts.get(0);
            this.editContact = contacts.get(0).clone();
            this.editContact.Id = null;
        } catch (Exception e) {
            ApexPages.addMessage(new ApexPages.Message(ApexPages.Severity.ERROR, e.getMessage()));
        }
    }
    
    private void loadForms() {
        // Load ILP
        Id contactId = this.contact1.Id;
        List<ILP__c> frmILP = [SELECT Id, Additional_Learning_Support__c, Additional_Learning_Support_Comment__c, Agree_to_ILP__c, Any_planned_actions__c, Attended_an_Induction__c, Completed_enrolment_PW__c, Contact__c, Customer_Service_Skills__c, Date_Signed__c, Employ_ability_Skills__c, Employment_Goals_Comment__c, Experience_and_Knowledge__c, Formal_Training__c, Formal_Training_Comment__c, Further_information_and_advice__c, Improve_confidence__c, Information_and_Advice__c, Need_Relevant_Knowledge__c, Practical_Exp__c, Practical_Exp_Comment__c, Received_Copies__c, Where_Programme_Delivery__c, Who_my_advisor_is__c,Why_this_course__c FROM ILP__c WHERE Contact__c = :contactId ORDER BY CreatedDate DESC LIMIT 1];
        if (frmILP.size() > 0)
            editILP = frmILP.get(0);
        else
            editILP = new ILP__c(Contact__c = ContactId);
        // Load Agreement (if any)
       
        List<F2L_Agreement__c> frmAgreement = [SELECT Id, Name FROM ILP__c WHERE Contact__c = :contactId ORDER BY CreatedDate DESC LIMIT 1];
        if (frmAgreement.size() > 0)
            editAgreement = frmAgreement.get(0);
        else
            editAgreement = new F2L_Agreement__c(Contact__c = ContactId);
        // Load VAK
        
        List<VAK_Questionnaire__c> frmVAK = [SELECT Id, q9__c, q8__c, q7__c, q6__c, q5__c, q4__c, q30__c, q3__c, q29__c, q28__c, q27__c, q26__c, q25__c, q24__c, q23__c, q22__c, q21__c, q20__c, q2__c, q19__c, q18__c, q17__c, q16__c, q15__c, q14__c, q13__c, q12__c, q11__c, q10__c, q1__c  FROM VAK_Questionnaire__c WHERE Contact__c = :contactId ORDER BY CreatedDate DESC LIMIT 1];
        if (frmVAK.size() > 0)
            editVAK = frmVAK.get(0);
        else
            editVAK = new VAK_Questionnaire__c(Contact__c = ContactId);
        
    }
}
    
    
    public void doSave() {
        try {
            if (editContact == null) {
                throw new PageException('Please log in first.');
            }
            editContact.Id = contact1.Id;
            
            update editContact;
            editContact.Id = null;
            ApexPages.addMessage(new ApexPages.Message(ApexPages.Severity.CONFIRM, 'Your details have been successfully saved.'));
            nextState();
        } catch (DMLException e) {
            editContact.Id = null;
            ApexPages.Message msg = new ApexPages.Message(Apexpages.Severity.ERROR, e.getdmlMessage(0) );
            ApexPages.addMessage(msg);
        }
    }

  public void nextState() {
    state++;
  }
    
    public class PageException extends Exception {}
}

Let us know if it helps you.
Gaby FrydmanGaby Frydman
Hi Ashish Again! 

No, that wouldnt solve it as I am still on the same contact. 

The process is such:

We check all contacts for matching NIN and DOB and if found, we load ther details defined in 
//Load contact
            List<Contact> contacts = [SELECT id, firstName, lastName, Nationality__c, ID_Issue_Date__c, Document_of_Identification__c [AND OTHERS, SAVING SPACE]
                                      FROM Contact 
                                      WHERE National_Insurance__c like :contact.National_Insurance__c
                                        AND BirthDate = :contact.BirthDate
                                      ORDER BY CreatedDate DESC
                                      LIMIT 1];

In the VFP we have them displayed as State==0.  The person who is filling out this form then clicks on "Save and Next" button which takes them to State==1 where I need them to complete another form with fields loaded from 
 
private void loadForms() {
        // Load ILP
        Id contactId = this.contact1.Id;
        List<ILP__c> frmILP = [SELECT Id, Additional_Learning_Support__c, Additional_Learning_Support_Comment__c, Agree_to_ILP__c, Any_planned_actions__c, Attended_an_Induction__c, Completed_enrolment_PW__c, Contact__c, Customer_Service_Skills__c, Date_Signed__c, Employ_ability_Skills__c, Employment_Goals_Comment__c, Experience_and_Knowledge__c, Formal_Training__c, Formal_Training_Comment__c, Further_information_and_advice__c, Improve_confidence__c, Information_and_Advice__c, Need_Relevant_Knowledge__c, Practical_Exp__c, Practical_Exp_Comment__c, Received_Copies__c, Where_Programme_Delivery__c, Who_my_advisor_is__c,Why_this_course__c FROM ILP__c WHERE Contact__c = :contactId ORDER BY CreatedDate DESC LIMIT 1];
        if (frmILP.size() > 0)
            editILP = frmILP.get(0);
        else
            editILP = new ILP__c(Contact__c = ContactId);
        // Load Agreement (if any)
       
        List<F2L_Agreement__c> frmAgreement = [SELECT Id, Name FROM ILP__c WHERE Contact__c = :contactId ORDER BY CreatedDate DESC LIMIT 1];
        if (frmAgreement.size() > 0)
            editAgreement = frmAgreement.get(0);
        else
            editAgreement = new F2L_Agreement__c(Contact__c = ContactId);
        // Load VAK
        
        List<VAK_Questionnaire__c> frmVAK = [SELECT Id, q9__c, q8__c, q7__c, q6__c, q5__c, q4__c, q30__c, q3__c, q29__c, q28__c, q27__c, q26__c, q25__c, q24__c, q23__c, q22__c, q21__c, q20__c, q2__c, q19__c, q18__c, q17__c, q16__c, q15__c, q14__c, q13__c, q12__c, q11__c, q10__c, q1__c  FROM VAK_Questionnaire__c WHERE Contact__c = :contactId ORDER BY CreatedDate DESC LIMIT 1];
        if (frmVAK.size() > 0)
            editVAK = frmVAK.get(0);
        else
            editVAK = new VAK_Questionnaire__c(Contact__c = ContactId);
        
    }

However I am concerned that I have not defined loadForms and editILP, editVAK and so correctly - 
public without sharing class LearnerDetails_Controller {
    public Contact contact1 {get;set;}
    public Contact editContact {get;set;}
    public Integer state {get;set;}
    public LearnerDetails_Controller() {
    contact1 = new Contact(Id = null);
    editContact = null;
    state = 0;
}
public class loadForms{
    public List<ILP__c> editILP {get;set;}
    public List<F2L_Agreement__c> editAgreement {get;set;}
    public List<VAK_Questionnaire__c> editVAK {get;set;}