Can anyone help change this SOQL statment so that I get the results that I am expecting?

I have three objects:

1. OppPSQLineItem__c (Relationship Name = Part_and_Service_Quotes__r) (Child)

2. Part_and_Service_Quote__c (Parent)

3. Part_and_Service_Quote_Line_Item__c (Child)

I am trying to filter based on an ID (Cost_Sheet__c) from the object OppPSQLineItem__c and show all the related records in the objects sibling (Part_and_Service_Quote_Line_Item__c). The statement will bring back ALL records contained in Part_and_Service_Quote_Line_Item__c as it is a right outer join. Any way to change to a left join? Am I coming at this from the wrong direction?

SELECT Id, (SELECT Id, Cost_Sheet__c FROM Part_and_Service_Quotes__r WHERE Cost_Sheet__c = 'a0R60000000aIhY'), (SELECT Id FROM Part_and_Service_Quote_Line_Items__r) FROM Part_and_Service_Quote__c

I really appreciate any help and effort anyone in the community puts forth!

Regards,

Tyler

I am hoping that someone might be able to help me figure out a SOQL query.

I have four objects:

1. Opportunity

2. OppoPSQLineItem

3. Part_and_Service_Quote

4. Part_and_Service_Quote_Line_Item

OppoPSQLineItem is a junction object that connects Opportunity and Part_and_Service_Quote. Part_and_Service_Quote_Line_Item is a child of Part_and_Service_Quote.

What I am trying to do is display all Part_and_Service_Quote_Line_Item records for each Opportunity.

I know I can do the following to traverse the junction object for Opportunity and Part_and_Service_Quote information:

SELECT opsq.Id, opsq.Name, Opportunity__r.Id, Opportunity__r.Name, Part_and_Service_Quote__r.Id, Part_and_Service_Quote__r.Name
FROM OppPSQLineItem__c opsq

I can not figure out how to alter this statment so that I show Part_and_Service_Quote_Line_Item information (IE Vendor__c, Make__c, Price__c).

I would appreciate any help or suggestions you might have.

Hi Everyone,

I hope that someone out there might be able to provide me with a little help with some of my code. I am currently trying to modify the Sites self-registration page to work like the Customer Portal page. This modification would allow for a portal user to be created if the contact card has the "Self-Register in Portal" checkbox selected and they enter their email on the SiteRegister page.

I will provide both the VF page and APEX:

1. SiteRegister (VF)

<apex:page id="loginPage" showHeader="true" controller="SiteRegisterController" title="{!$Label.site.register}">
  <apex:composition template="{!$Site.Template}">
    <apex:define name="body">  
       <style>
.tabNavigation {display:none;}
</style>
 <style>

.sidebarCell {display:none;}
</style>
  <style>.sidebarDiv {display:none;}
</style><center>
        <apex:panelGrid bgcolor="004f8c" columns="1"> 
          <br/>
          <br/>
          <apex:panelGrid width="758" cellpadding="0" cellspacing="0" bgcolor="white" columns="1" styleClass="topPanelContainer"> 
            <br/>
            <apex:outputPanel layout="block" styleClass="topPanel">
              <apex:panelGrid width="758" cellpadding="0" cellspacing="0" bgcolor="white" columns="2"> 
                <apex:image url="https://c.na8.content.force.com/servlet/servlet.ImageServer?id=015C0000001MSFY&oid=00D80000000bQYx&lastMod=1304543302000"/>
                <apex:panelGroup >
                  <br/>
                  <apex:outputText styleClass="title" value="{!$Label.site.user_registration}"/>
                  <br/>
                  <apex:form id="theForm" forceSSL="true">
                    <apex:pageMessages id="error"/>
                    <apex:panelGrid columns="2" style="margin-top:1em;">
                      
                      <apex:outputLabel value="{!$Label.site.email}" for="email1"/>
                      <apex:inputText required="true" id="email1" value="{!email1}"/>
                      
                      
                      <apex:commandButton action="{!registerUser}" value="{!$Label.site.submit}" id="submit"/>
                    </apex:panelGrid> 
                    </apex:form>                  
                  <br/>
                </apex:panelGroup>
              </apex:panelGrid> 
             </apex:outputPanel>
            <c:SitePoweredBy />
          </apex:panelGrid> 
       </apex:panelGrid>
      </center>
      <br/>
    </apex:define>
  </apex:composition>  <site:googleAnalyticsTracking />
</apex:page>

 2. SiteRegisterController (APEX)

/**
 * An apex class that creates a portal user
 */
public with sharing class SiteRegisterController {

	public String email1 {get; set;}
    
    public SiteRegisterController () {
    }

    
    
    public PageReference registerUser() 
    {
        List<User> alreadyExists = [Select Id from User WHERE username =:email1 AND IsActive = true LIMIT 1];
        if (alreadyExists.size() > 0) 
        {
            PageReference page = System.Page.SiteLogin;
            page.setRedirect(true);
            return page;
        }
        else 
        {
            List<Contact> c = [Select Id, AccountId, FirstName, LastName, Email FROM Contact WHERE CanAllowPortalSelfReg = true AND Email =:email1];
            
            if (!c.isEmpty()) 
            {
            	Profile profile = [select Id, name from Profile where Name=:'Customer Portal Manager 1'];
				System.debug('***** ContactID1--> ' + c[0].id);
            	SiteRegisterController.createUser(c[0].id, c[0].email, c[0].firstname, c[0].lastname, profile.id);
	        	
	        	ApexPages.Message myMsg1 = new ApexPages.Message(ApexPages.Severity.INFO, 'Successfully registered your Account. Please await for an email containing futher instructions');
	        	ApexPages.addMessage(myMsg1);
	        	//PageReference page = System.Page.SiteRegisterConfirm;
				//page.setRedirect(true);
				//return page;
				return null;
            }
            else
            {
	            ApexPages.Message myMsg2 = new ApexPages.Message(ApexPages.Severity.ERROR, 'This is currently an invitation only system. Please contact the system administrator to gain access.' + ' ' + c.size());
		        ApexPages.addMessage(myMsg2);
		        //PageReference page = System.Page.Exception;
				//page.setRedirect(true);
				//return page; 
				return null;
            }
		}
    }
    
    public static void createUser(String contactid, String email, String firstname, String lastname, String profileid)
    {
    	Database.DMLOptions dmo = new Database.DMLOptions();
		dmo.EmailHeader.triggerUserEmail = true;
		
    	User u = new User();
        u.ContactId = contactid;
	    u.Username = email;
	    u.Alias = u.Username.split('@').get(0);
	    u.Email = email;
	    u.Firstname = firstname;
	    u.Lastname = lastname;
	    u.ProfileID = profileid;
	    u.communityNickname = u.Username.split('@').get(0);
	    //u.PortalRole = 'Test Customer Account Customer User';
	    u.LanguageLocaleKey = 'en_US';
	    u.LocaleSidKey = 'en_CA';
	    u.TimeZoneSidKey = 'America/Los_Angeles';
	    u.EmailEncodingKey='UTF-8';
	            
	    System.debug('***** Username--> ' + u.Username);
	    System.debug('***** ContactId--> ' + u.ContactId);
	    System.debug('***** Email--> ' + u.Email);
	    System.debug('***** Firstname--> ' + u.Firstname);
	    System.debug('***** Lastname--> ' + u.Lastname);
	    System.debug('***** ProfileID--> ' + u.ProfileID);
	    System.debug('***** PortalRole--> ' + u.PortalRole);
	    System.debug('***** Alias--> ' + u.Alias);

	    u.setOptions(dmo);
	    insert u;	        	
	    System.Debug('***** UserId-->' + u.Id);    	
    }
    
    // Test method to bring this class's test coverage over the required 75%
    @IsTest(SeeAllData=true) static void testRegistration() {
        SiteRegisterController controller = new SiteRegisterController();
        controller.email1 = 'tmowbrey@belmar.ca';
        controller.registerUser();
        // registerUser will always return null when the page isn't accessed as a guest user
        //System.assert(controller.registerUser() == null);    
        controller.email1 = 'idonotexist@domain.com';
        controller.registerUser();
        
        Contact c = new Contact(firstName='TestFirstName', lastName='TestLastName', email='iamauser@domain.com', accountid='001U0000006KtbB');
        insert c;
        System.debug('***** ContactIDTest--> ' + c.id);
        Profile profile = [select Id, name from Profile where Name=:'Customer Portal Manager 1'];
        SiteRegisterController.createUser(c.id, c.email, c.firstname, c.lastname, profile.id);
        controller.email1 = 'iamauser@domain.com';
        controller.registerUser();
    }
    
}

 The tests are correctly creating a portal user for tmowbrey@belmar.ca as this contact exists (has no portal user created) and has the Self-Register checkbox selected.

The issue occurs when actually deploying and using the code in actuallity. Everytime I put in tmowbrey@belmar.ca I receive "'This is currently an invitation only system. Please contact the system administrator to gain access." with a c.size() of 0. I can not figure out why.

Please let me know if I can provide more information.

Regards,

Tyler Mowbrey

Hey folks, I'm in a new role now, so I will be coding a lot more.  Looking forward to hanging out in here more often!

I'm working with Dynamic Visualforce at the moment (apex:dynamicComponent) to build a simple Lead form.  It's a long story why it has to be built dyamically.  I'm extending the standard Lead controller, and leveraging the standard {!save} method.  It works great when the record saves... they are brought to the new record and all the info they entered is there... woohoo!

The problem is when they click save but haven't filled in one or more required fields.  The form refreshes and correctly identifies the fields they forgot to populate and notifies them as is appropriate.  BUT, all the info they had entered is cleared out!

Here is a small example that shows what I mean:

Visualforce Page:

<apex:page extensions="dynamicTestController" standardController="Lead">

    <apex:messages />

    <apex:form >
        <apex:pageBlock mode="edit">

            <apex:pageBlockButtons >
                <apex:commandButton value="Save" action="{!save}"/>
            </apex:pageBlockButtons>
            
            <apex:dynamicComponent componentValue="{!mySection}"/>
            
        </apex:pageBlock>
    </apex:form>
</apex:page>

Apex Controller:

public with sharing class dynamicTestController {

    public Lead theLead {get;set;}

    public dynamicTestController(ApexPages.StandardController controller) {
        theLead = (lead)controller.getRecord();
    }

    public Component.Apex.PageBlockSection getMySection() {
        
        Component.Apex.PageBlockSection section = new Component.Apex.PageBlockSection(columns=1);
        
        Component.Apex.InputField field1 = new Component.Apex.InputField();
        field1.expressions.value = '{!theLead.LastName}';
        
        Component.Apex.InputField field2 = new Component.Apex.InputField();
        field2.expressions.value = '{!theLead.Company}';
        
        Component.Apex.InputField field3 = new Component.Apex.InputField();
        field3.expressions.value = '{!theLead.Phone}';
        
        section.childComponents.add(field1);
        section.childComponents.add(field2);
        section.childComponents.add(field3);
        
        return section;
    }
}

You can see that when you populate LastName and Phone, but NOT Company and click save the form is cleared out... not cool!

I've concocted a workaround, which is to use a custom save method that returns nothing so I can avoid refreshing of the form.  I then use the apex:messages component to display any errors at the top of the page and redirect with JavaScript if there are no errors.  It works, but then errors that should be on a specific field are at the top instead and I would rather not have to rely on JS for navigation like that.

Any thoughts?

Cheers,

michaelforce

Can anyone help change this SOQL statment so that I get the results that I am expecting?

I have three objects:

1. OppPSQLineItem__c (Relationship Name = Part_and_Service_Quotes__r) (Child)

2. Part_and_Service_Quote__c (Parent)

3. Part_and_Service_Quote_Line_Item__c (Child)

I am trying to filter based on an ID (Cost_Sheet__c) from the object OppPSQLineItem__c and show all the related records in the objects sibling (Part_and_Service_Quote_Line_Item__c). The statement will bring back ALL records contained in Part_and_Service_Quote_Line_Item__c as it is a right outer join. Any way to change to a left join? Am I coming at this from the wrong direction?

SELECT Id, (SELECT Id, Cost_Sheet__c FROM Part_and_Service_Quotes__r WHERE Cost_Sheet__c = 'a0R60000000aIhY'), (SELECT Id FROM Part_and_Service_Quote_Line_Items__r) FROM Part_and_Service_Quote__c

I really appreciate any help and effort anyone in the community puts forth!

Regards,

Tyler

I am hoping that someone might be able to help me figure out a SOQL query.

I have four objects:

1. Opportunity

2. OppoPSQLineItem

3. Part_and_Service_Quote

4. Part_and_Service_Quote_Line_Item

OppoPSQLineItem is a junction object that connects Opportunity and Part_and_Service_Quote. Part_and_Service_Quote_Line_Item is a child of Part_and_Service_Quote.

What I am trying to do is display all Part_and_Service_Quote_Line_Item records for each Opportunity.

I know I can do the following to traverse the junction object for Opportunity and Part_and_Service_Quote information:

SELECT opsq.Id, opsq.Name, Opportunity__r.Id, Opportunity__r.Name, Part_and_Service_Quote__r.Id, Part_and_Service_Quote__r.Name
FROM OppPSQLineItem__c opsq

I can not figure out how to alter this statment so that I show Part_and_Service_Quote_Line_Item information (IE Vendor__c, Make__c, Price__c).

I would appreciate any help or suggestions you might have.

Hi Everyone,

I hope that someone out there might be able to provide me with a little help with some of my code. I am currently trying to modify the Sites self-registration page to work like the Customer Portal page. This modification would allow for a portal user to be created if the contact card has the "Self-Register in Portal" checkbox selected and they enter their email on the SiteRegister page.

I will provide both the VF page and APEX:

1. SiteRegister (VF)

<apex:page id="loginPage" showHeader="true" controller="SiteRegisterController" title="{!$Label.site.register}">
  <apex:composition template="{!$Site.Template}">
    <apex:define name="body">  
       <style>
.tabNavigation {display:none;}
</style>
 <style>

.sidebarCell {display:none;}
</style>
  <style>.sidebarDiv {display:none;}
</style><center>
        <apex:panelGrid bgcolor="004f8c" columns="1"> 
          <br/>
          <br/>
          <apex:panelGrid width="758" cellpadding="0" cellspacing="0" bgcolor="white" columns="1" styleClass="topPanelContainer"> 
            <br/>
            <apex:outputPanel layout="block" styleClass="topPanel">
              <apex:panelGrid width="758" cellpadding="0" cellspacing="0" bgcolor="white" columns="2"> 
                <apex:image url="https://c.na8.content.force.com/servlet/servlet.ImageServer?id=015C0000001MSFY&oid=00D80000000bQYx&lastMod=1304543302000"/>
                <apex:panelGroup >
                  <br/>
                  <apex:outputText styleClass="title" value="{!$Label.site.user_registration}"/>
                  <br/>
                  <apex:form id="theForm" forceSSL="true">
                    <apex:pageMessages id="error"/>
                    <apex:panelGrid columns="2" style="margin-top:1em;">
                      
                      <apex:outputLabel value="{!$Label.site.email}" for="email1"/>
                      <apex:inputText required="true" id="email1" value="{!email1}"/>
                      
                      
                      <apex:commandButton action="{!registerUser}" value="{!$Label.site.submit}" id="submit"/>
                    </apex:panelGrid> 
                    </apex:form>                  
                  <br/>
                </apex:panelGroup>
              </apex:panelGrid> 
             </apex:outputPanel>
            <c:SitePoweredBy />
          </apex:panelGrid> 
       </apex:panelGrid>
      </center>
      <br/>
    </apex:define>
  </apex:composition>  <site:googleAnalyticsTracking />
</apex:page>

 2. SiteRegisterController (APEX)

/**
 * An apex class that creates a portal user
 */
public with sharing class SiteRegisterController {

	public String email1 {get; set;}
    
    public SiteRegisterController () {
    }

    
    
    public PageReference registerUser() 
    {
        List<User> alreadyExists = [Select Id from User WHERE username =:email1 AND IsActive = true LIMIT 1];
        if (alreadyExists.size() > 0) 
        {
            PageReference page = System.Page.SiteLogin;
            page.setRedirect(true);
            return page;
        }
        else 
        {
            List<Contact> c = [Select Id, AccountId, FirstName, LastName, Email FROM Contact WHERE CanAllowPortalSelfReg = true AND Email =:email1];
            
            if (!c.isEmpty()) 
            {
            	Profile profile = [select Id, name from Profile where Name=:'Customer Portal Manager 1'];
				System.debug('***** ContactID1--> ' + c[0].id);
            	SiteRegisterController.createUser(c[0].id, c[0].email, c[0].firstname, c[0].lastname, profile.id);
	        	
	        	ApexPages.Message myMsg1 = new ApexPages.Message(ApexPages.Severity.INFO, 'Successfully registered your Account. Please await for an email containing futher instructions');
	        	ApexPages.addMessage(myMsg1);
	        	//PageReference page = System.Page.SiteRegisterConfirm;
				//page.setRedirect(true);
				//return page;
				return null;
            }
            else
            {
	            ApexPages.Message myMsg2 = new ApexPages.Message(ApexPages.Severity.ERROR, 'This is currently an invitation only system. Please contact the system administrator to gain access.' + ' ' + c.size());
		        ApexPages.addMessage(myMsg2);
		        //PageReference page = System.Page.Exception;
				//page.setRedirect(true);
				//return page; 
				return null;
            }
		}
    }
    
    public static void createUser(String contactid, String email, String firstname, String lastname, String profileid)
    {
    	Database.DMLOptions dmo = new Database.DMLOptions();
		dmo.EmailHeader.triggerUserEmail = true;
		
    	User u = new User();
        u.ContactId = contactid;
	    u.Username = email;
	    u.Alias = u.Username.split('@').get(0);
	    u.Email = email;
	    u.Firstname = firstname;
	    u.Lastname = lastname;
	    u.ProfileID = profileid;
	    u.communityNickname = u.Username.split('@').get(0);
	    //u.PortalRole = 'Test Customer Account Customer User';
	    u.LanguageLocaleKey = 'en_US';
	    u.LocaleSidKey = 'en_CA';
	    u.TimeZoneSidKey = 'America/Los_Angeles';
	    u.EmailEncodingKey='UTF-8';
	            
	    System.debug('***** Username--> ' + u.Username);
	    System.debug('***** ContactId--> ' + u.ContactId);
	    System.debug('***** Email--> ' + u.Email);
	    System.debug('***** Firstname--> ' + u.Firstname);
	    System.debug('***** Lastname--> ' + u.Lastname);
	    System.debug('***** ProfileID--> ' + u.ProfileID);
	    System.debug('***** PortalRole--> ' + u.PortalRole);
	    System.debug('***** Alias--> ' + u.Alias);

	    u.setOptions(dmo);
	    insert u;	        	
	    System.Debug('***** UserId-->' + u.Id);    	
    }
    
    // Test method to bring this class's test coverage over the required 75%
    @IsTest(SeeAllData=true) static void testRegistration() {
        SiteRegisterController controller = new SiteRegisterController();
        controller.email1 = 'tmowbrey@belmar.ca';
        controller.registerUser();
        // registerUser will always return null when the page isn't accessed as a guest user
        //System.assert(controller.registerUser() == null);    
        controller.email1 = 'idonotexist@domain.com';
        controller.registerUser();
        
        Contact c = new Contact(firstName='TestFirstName', lastName='TestLastName', email='iamauser@domain.com', accountid='001U0000006KtbB');
        insert c;
        System.debug('***** ContactIDTest--> ' + c.id);
        Profile profile = [select Id, name from Profile where Name=:'Customer Portal Manager 1'];
        SiteRegisterController.createUser(c.id, c.email, c.firstname, c.lastname, profile.id);
        controller.email1 = 'iamauser@domain.com';
        controller.registerUser();
    }
    
}

 The tests are correctly creating a portal user for tmowbrey@belmar.ca as this contact exists (has no portal user created) and has the Self-Register checkbox selected.

The issue occurs when actually deploying and using the code in actuallity. Everytime I put in tmowbrey@belmar.ca I receive "'This is currently an invitation only system. Please contact the system administrator to gain access." with a c.size() of 0. I can not figure out why.

Please let me know if I can provide more information.

Regards,

Tyler Mowbrey