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!

Data & Imports Forums - T-SQL & Queries
Forum to discuss general T-SQL questions and help with queries related to SalesLogix data. View the code of conduct for posting guidelines.

Forums RSS Feed

Back to Forum List | Back to T-SQL & Queries | New Thread

View: Search:

Author

Thread: Help creating group which shows all accounts and contacts (incl accounts with no contacts)

Carol McGrath
Posts: 8

Help creating group which shows all accounts and contacts (incl accounts with no contacts) Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 13 Nov 06 6:20 PM

Hi
I have been asked to create groups which show the account manager, account, phone, address and contact details (first Name, last Name, mobile, email)
That is the easy part - they get to see all accounts with contacts BUT..
they want to be able to see every account they manage - i.e. all the accounts and where there is no contact (show as blank fields)as well as those with contacts in the one group. For instance one rep has 1434 accounts but only 313 have contact names, they want to see all 1434 and have blanks showing where they have no names
I can do this as a query outside of SLX but I want it to be something that the reps can look at and export to excel whenever they want rather than me running something for them.

I have tried to make this query work on my own but I need your help...

[Reply][Quote]

Frank Chaffin
Posts: 475

Re: Help creating group which shows all accounts and contacts (incl accounts with no contacts) Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 13 Nov 06 6:54 PM

Assuming that you use the AccountManagerID field to indicate the user that manages the account you can create one group that should work for all users.

Create an Account Group. While editing the group
-Make sure the join from Account to Contact is a left join
-Add a condition for AccountManagerID.
Set the "operator" to =
Set "value is" to :userid
**Check** Set "use value as literal"

:userid is a special syntax that will return the userid of the currently logged in user.

[Reply][Quote]

Carol McGrath
Posts: 8

Re: Help creating group which shows all accounts and contacts (incl accounts with no contacts) Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 13 Nov 06 7:06 PM

Thanks Frank for the prompt response
I have the account manager values as you specifiy. I tried using the left join but I still don't get all accounts showing - i still only see the 313 records with contacts
I want to see every account belonging to rep, all contacts but where there is no contact, I still want to see the account displaying...

[Reply][Quote]

Frank Chaffin
Posts: 475

Re: Help creating group which shows all accounts and contacts (incl accounts with no contacts) Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 13 Nov 06 7:10 PM

Carol,

Left joins should get you what you want.

Did you make sure all the joins used by the group are left joins?

What field are in the group layout?

[Reply][Quote]

Carol McGrath
Posts: 8

Re: Help creating group which shows all accounts and contacts (incl accounts with no contacts) Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 13 Nov 06 7:21 PM

Hi Frank
I am only using the account ,contact and address tables
Left join on parent (account)

Fields in query
Account
Contact. FirstName
Contact.LastName
Accountmanagerid
Mainphone
Fax
Contact.Mobile
Contact.Email
Address1
Address2
Address3
City
Industry
Status

[Reply][Quote]

Frank Chaffin
Posts: 475

Re: Help creating group which shows all accounts and contacts (incl accounts with no contacts) Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 13 Nov 06 9:06 PM

In the Query Builder do a "View SQL" then cut the select statement and post it so I can take a look.

[Reply][Quote]

John Gundrum
Posts: 632

Re: Help creating group which shows all accounts and contacts (incl accounts with no contacts) Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 14 Nov 06 7:40 AM

I used Query Analyzer to come up with the following SQL that returns what you are looking for. I think the issue might be that you are going to have to change the JOIN type between the account and contact table.

SELECT 
	a.Account,
	c.FirstName,
	c.LastName,
	a.Accountmanagerid,
	a.Mainphone,
	a.Fax,
	c.Mobile,
	c.Email,
	b.Address1,
	b.Address2,
	b.Address3,
	b.City,
	a.Industry,
	a.Status
FROM account a JOIN address b ON a.accountid = b.entityid
		LEFT JOIN contact c ON a.accountid = c.accountid

John G.

[Reply][Quote]

LaNae Christenson
Posts: 23

Re: Help creating group which shows all accounts and contacts (incl accounts with no contacts) Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 14 Nov 06 7:52 AM

If you want to include all accounts whether or not they have contacts, you will need to change the join to a right join. That should give you what you need.

[Reply][Quote]

Page 1 of 1

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!

slxdeveloper.com is brought to you courtesy of Ryan Farley & Customer FX Corporation. This site, and all contents herein, are Copyright © 2024 Customer FX Corporation. The information and opinions expressed here are not endorsed by Sage Software.	code of conduct \|

page cache (param): 5/1/2024 8:38:57 AM