+ Start a Discussion
sunfishettesunfishette 

SOQL select statement to current user

This is getting pretty thick on my end.  But here goes:

 I have an org that allows multiple partner portal users.  Some are executive level, some are manager level.  I want to allow the visual force page to display opportunity records based on the user log in.  I cannot figure out how to connect them in my SOQL statement.   I have pasted in the applicable code, and when I run it on my end (with some fiddling, I can get the records to display based on the user ID, BUT, if a different user from the same company logs in, they cannot see the records that they should be able to since the Owner ID is tied to someone else.

How do i connect them?  Is there some field I can add to an opportunity to make it easier (well, not easier, then i would have to change ALL my opportunity records).

 

Visualforce Page:VF PAGE:
<apex:pageBlock >
    <apex:tabPanel id="theTabPanel" tabClass="activeTab" inactiveTabClass="inactiveTab" switchType="server" width="100%">
        <apex:tab label="Opportunity" labelWidth="250px" >                                      
            <apex:pageBlockSection id="zero30" title="0-30 Days Opportunity" columns="1">
                <apex:PageBlockTable value="{!RecordDetail}" var="records" rowClasses="red1,red2">
                    <apex:column value="{!records.Opportunity_Name}" headerValue="Opportunity Name" />
                    <apex:column value="{!records.Opportunity_Owner}" headerValue="Opportunity Owner" />
                    <apex:column value="{!records.Amount}" headerValue="Amount" />
                    <apex:column value="{!records.Close_Date}" headerValue="Close Date Due By" />
                </apex:pageBlockTable>      
            </apex:pageBlockSection>
            <apex:pageBlockSection title="31-60 Days Opportunity" columns="1">
               <apex:PageBlockTable value="{!RecordDetail}" var="records" rowClasses="yellow1,yellow2">
                    <apex:column value="{!records.Opportunity_Name}" headerValue="Opportunity Name" />
                    <apex:column value="{!records.Opportunity_Owner}" headerValue="Opportunity Owner" />
                    <apex:column value="{!records.Amount}" headerValue="Amount" />
                    <apex:column value="{!records.Close_Date}" headerValue="Close Date Due By" />
                </apex:pageBlockTable>
            </apex:pageBlockSection>
            <apex:pageBlockSection title="61 Days (or more) Opportunity" columns="1">
                <apex:PageBlockTable value="{!RecordDetail}" var="records" rowClasses="green1,green2">
                    <apex:column value="{!records.Opportunity_Name}" headerValue="Opportunity Name" />
                    <apex:column value="{!records.Opportunity_Owner}" headerValue="Opportunity Owner" />
                    <apex:column value="{!records.Amount}" headerValue="Amount" />
                    <apex:column value="{!records.Close_Date}" headerValue="Close Date Due By" />
                </apex:pageBlockTable>
            </apex:pageBlockSection>     
        </apex:tab>
    </apex:tabPanel>
</apex:pageBlock>

CLASS:
public class getAllRecords {
    public getAllRecords(ApexPages.StandardController Controller) {
    }   
    public Opportunity[]getRecordDetail(){
        Opportunity[]recordList;
        recordList = [SELECT Opportunity.Opportunity_Name,
                             Opportunity.Opportunity_Owner,
                             Opportunity.Amount,
                             Opportunity.Close_Date
                        FROM Opportunity
                       WHERE OwnerId= :UserInfo.getUserID()];
        return recordList;
    }
}

 

 

 

 

 

Thank you in advance for looking and helping.

Best Answer chosen by Admin (Salesforce Developers) 
sunfishettesunfishette

This works:

public class getAllRecords {
    public getAllRecords(ApexPages.StandardController Controller) {
    }   
    public Opportunity[]getRecordDetail(){
        Opportunity[]recordList;
        recordList = [SELECT Opportunity.Opportunity_Name,
                             Opportunity.Opportunity_Owner,
                             Opportunity.Amount,
                             Opportunity.Close_Date
                        FROM Opportunity
                       WHERE Account__c IN
                            (SELECT AccountId
                             From User
                             WHERE username=:UserInfo.getUsername())];
        return recordList;
    }
}

 

 

now I just need to figure out how to filter the records based on their executive / manager / user roles.

All Answers

jhansisridhar_2011jhansisridhar_2011

Hi,

 

I think this syntax will help u out:

 

recordList = [SELECT Opportunity_Name,
                             Opportunity_Owner,
                             Amount,
                             Close_Date
                        FROM Opportunity
                       WHERE OwnerId= :UserInfo.getUserID()];
        return recordList;

 

Hopes this post helps u, mark as solutoion.

sunfishettesunfishette

That is what I already have in my class.  It does not return all the opportunities that belong to the company.  If the ownerId belongs to Jack, but Sue logs in from the same company, she will not see that opportunity.  She needs to be able to see it....  But if I return all the opportunities based on the company name, then managers and/or users might have access to records that only executives should see.  I still have not figured out how to filter opportunities based on login / company / profile.  I am assuming there is a large SOQL process that needs to tie them all together...

sunfishettesunfishette

Ok, I am updating my original question.  I have figured out that my User AccountId is the same as my Account__c field on my opporunity record.  but every variation i can google to connect the two is failing.  If I can connect the two then I can (hopefully) display all records based on the company the user is attached to.  Here is what I am hoping to do:


public class getAllRecords {
    public getAllRecords(ApexPages.StandardController Controller) {
    }   
    public Opportunity[]getRecordDetail(){
        Opportunity[]recordList;
        recordList = [SELECT Opportunity.Opportunity_Name,
                             Opportunity.Opportunity_Owner,
                             Opportunity.Amount,
                             Opportunity.Close_Date
                        FROM Opportunity
                       WHERE Account__c = :UserInfo.getAccountId()];
        return recordList;
    }
}

 

The line in red is where it fails, and I know it has to do with how i am trying to call the User's AccountId ... 

 

thoughts?

sunfishettesunfishette

This works:

public class getAllRecords {
    public getAllRecords(ApexPages.StandardController Controller) {
    }   
    public Opportunity[]getRecordDetail(){
        Opportunity[]recordList;
        recordList = [SELECT Opportunity.Opportunity_Name,
                             Opportunity.Opportunity_Owner,
                             Opportunity.Amount,
                             Opportunity.Close_Date
                        FROM Opportunity
                       WHERE Account__c IN
                            (SELECT AccountId
                             From User
                             WHERE username=:UserInfo.getUsername())];
        return recordList;
    }
}

 

 

now I just need to figure out how to filter the records based on their executive / manager / user roles.

This was selected as the best answer