5/15/2024 2:34:26 PM
|
| |
| slxdeveloper.com Community Forums |
|
| |
|
|
The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
Forum to discuss general T-SQL questions and help with queries related to SalesLogix data. View the code of conduct for posting guidelines.
|
|
|
|
How to get all opportunities for a user?
Posted: 23 Jul 07 8:30 AM
|
This query to bring all the opportunities for a particular user works only for admin role.
select distinct opportunityid from opportunity where seccodeid in
(select distinct CHILDSECCODEID from seccodejoins where profileid in
(Select profileid from secrights where accessid ='admin')) and seccodeid in
(select distinct PARENTSECCODEID from seccodejoins where profileid in
(Select distinct profileid from secrights where accessid ='admin')) order by opportunityid
if i try with any other userid ex:- 'UFFED0000034', it does not show correct number of records. could anyone please tell me how to get the exact number of opportunities for a particular user? |
|
|
|
Re: How to get all opportunities for a user?
Posted: 24 Jul 07 8:25 AM
|
What version of SalesLogix is this for?
Are you implementing this as a part of a script or is this just something being done in Query Analyzer or Administrator SQL?
Are you trying to find out what opportunities a user is a manager of? If that is the case, just do a SELECT opportunityid FROM opportunity WHERE accountmanagerid = 'UFFED0000034' or whoever the userid is you want to look up.
John G.
|
|
|
|
Re: How to get all opportunities for a user?
Posted: 24 Jul 07 10:56 AM
|
Hi John,
Thanks for your reply... I am new to SLX. Pardon me if I asked lame question.
I am working on Saleslogix 7.0 with service pack 1.
The query -- > SELECT opportunityid FROM opportunity WHERE accountmanagerid = 'UFFED0000034' will work only if the defaultseccodeid for the user is account manager. in other words the ISMANAGER is set to True. what about normal sales people who are not managers?
I understand from saleslogix that any account, opportunity is owned by account manager. when a user logs in SLX, (s)he can either be sales guy or manager so on...
In opportunity table, there is a one-to-one mapping to seccodeid and accountmanagerid with account table . but, what i want is, whenever a sales guy logs in he is able to traverse to opportunity navbar but be able to see opportunities even though he is not a manager. thats because he has team rights. i want to know how i can identify a person is part of the team? all i can see is there is some defaultseccodeid, profileid, parent and child seccodeid... how should i find out all the opportunities for a normal user who is not account manager?
I am quite confused with the owner concept. I know that if owner is set to everyone, everybody will be able to see the account/opportunity but don't quite understand how to query the database to get all the opportunities open to a user which include the records with everyone rights.
Sorry for long question... hope you can help.. let me know if it is clear or not...
Thanks in advance,
Rekha |
|
|
|
Re: How to get all opportunities for a user?
Posted: 24 Jul 07 2:38 PM
|
Let me see if I am reading you correctly.
You want to create an Opportunity group tab (or view) that lists all the opportunities for the team the logged in user is a member of?
Is this correct?
John G. |
|
|
| |
|
Re: How to get all opportunities for a user?
Posted: 26 Jul 07 7:51 AM
|
I've been looking at this when time is available. One thing I did notice is the SQL you are using is not referencing the SECCODE table. In the SECCODE table there is a field called SECCODETYPE. The type indicates whether the seccode is a group ('G') - aka Team - department ('D'), user ('U') or system ('S').
You are only interested in seccodes where the type is 'G' because you only want to display opportunities where the user is only a member of a team.
The following SQL will get you what you are looking for. Just replace the value for a.accessid with a variable or the userid you are interested in.
SELECT opportunityid
FROM opportunity
WHERE
seccodeid IN (SELECT a.seccodeid FROM secrights a JOIN seccode b ON a.seccodeid = b.seccodeid WHERE a.accessid = 'U6UJ9A000054' AND b.seccodetype = 'G')
ORDER BY opportunityid
Do the following if you want to create an Opportunity group that shows only opportunities where the opportunity seccodeid is one the currently logged in user has access to
1) Add a new opportunity group
2) Add a condition for opportunity.seccodeid
3) The operator will be 'IN'
4) The 'Value is:' will be
SELECT a.seccodeid FROM secrights a JOIN seccode b ON a.seccodeid = b.seccodeid WHERE a.accessid = :userid AND b.seccodetype = 'G'
5) Uncheck 'Use Value as a Literal'
6) Click 'OK' twice
7) Replace the new opportunity group to Everyone or whoever needs to see it.
I think that should do it!
John G. |
|
|
|
Re: How to get all opportunities for a user?
Posted: 26 Jul 07 9:16 AM
|
Hi John,
Thanks for all your help!!
I was able to figure out the query... please let me know if it is correct
All opportunities that logged in user can see (including Global) set to everyone
==================================================
SELECT OPPORTUNITY.OPPORTUNITYID, OPPORTUNITY.ACCOUNTMANAGERID, OPPORTUNITY.DESCRIPTION,
ACCOUNT.ACCOUNT ACCOUNT, OPPORTUNITY.STATUS, OPPORTUNITY.SECCODEID OPPORTUNITYSECCODEID,
ACCOUNT.SECCODEID ACCOUNTSECCODEID
FROM OPPORTUNITY OPPORTUNITY
INNER JOIN SECRIGHTS ON (SECRIGHTS.ACCESSID = 'UQF8AA000007' AND OPPORTUNITY.SECCODEID = SECRIGHTS.SECCODEID)
INNER JOIN ACCOUNT ON (OPPORTUNITY.ACCOUNTID=ACCOUNT.ACCOUNTID)
ORDER BY OPPORTUNITY.SECCODEID ASC
where accessid is userid from userinfo table
Opportunities Specific to logged in user
=========================
SELECT
OPPORTUNITY.OPPORTUNITYID, OPPORTUNITY.ACCOUNTMANAGERID, OPPORTUNITY.DESCRIPTION,
ACCOUNT.ACCOUNT ACCOUNT, OPPORTUNITY.STATUS, OPPORTUNITY.SECCODEID OPPORTUNITYSECCODEID,
ACCOUNT.SECCODEID ACCOUNTSECCODEID
FROM OPPORTUNITY OPPORTUNITY
INNER JOIN ACCOUNT ACCOUNT ON (OPPORTUNITY.ACCOUNTID=ACCOUNT.ACCOUNTID)
WHERE (OPPORTUNITY.ACCOUNTMANAGERID='UQF8AA000007') ORDER BY OPPORTUNITY.OPPORTUNITYID
where accountmanagerid is the userid from userinfo table.
Thanks again for all your help...
Rekha |
|
|
|
Re: How to get all opportunities for a user?
Posted: 26 Jul 07 9:55 AM
|
That works too. You are also including departments a user is a member of as well. This will work if that is desired. I just remember you stating initially you only wanted display opportunities where a user was a member of a TEAM (group) or the opportunity was assigned to Everyone or Everyone (Read Only).
Did you also want to include departments?
John G.
|
|
|
|
You can
subscribe to receive a daily forum digest in your
user profile. View the site code
of conduct for posting guidelines.
 Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!
|
|
|
|
|
|
|
|