Community
Showing results for 
Search instead for 
Do you mean 
Reply

How do I do a lookup across Company AND Contact??

New Member
Posts: 8
Country: New Zealand

How do I do a lookup across Company AND Contact??

I have searched for this for hours and canniot find anywhere to do this basic kind of lookup.

 

In company I have defined a field which tells me if that company is a current customer and in contact I have defined a contacts status and I want to lookup all 'customers' that have a particular status. Simple enough you might think, and in general SQL terms it is very simple but in ACT terms it seems to be impossible, unless I'm missing something.

I'm running ACT 10 Premium at the moment.

 

Platinum Elite Contributor
Posts: 14,384
Country: Australia

Re: How do I do a lookup across Company AND Contact??

You can't.

 

You might post a request for this feature to be considered for a future version here - http://www.act.com/community/feature

Input from this web page goes directly into a database that management has direct access to read, and which they also track and gather statistics on.

 

What you can do, is add the "current customer" field to the contacts and make it a linked field so it will pick up the data from the company it's linked to. Then, just do a lookup of the contacts on both fields.

New Member
Posts: 8
Country: New Zealand

Re: How do I do a lookup across Company AND Contact??

Thanks for the link to the suggestions page, I have put this to them on that link.

 

Adding a linked field to the Contact doesnt solve anything really because there is more information in both that is needed and used and to link them all across to contact seems a very innefficient and pointless way of trying to solve what is really a very simple thing.

 

It seems totally incomprehensible that A CRM package doesn't allow you to do such a simple query, because once a 'prospect' becomes a client you will invariably need to put information at the 'company' level rather than the 'contact' level.

 

Oh well, the way things are going with all the limitations on data searching and lookups etc I believe management are looking at tossing ACT for a more versatile package now anyway so this will become academic shortly unless they come up with some way of doing these sorts of queries.

 

Many Thanks for your reply Mike, at least now I know it wasn't just my inability to find functionality to allow such a query.

Copper Super Contributor
Posts: 34
Country: United Kingdom

Re: How do I do a lookup across Company AND Contact??

We are finding this problem too. Queries that would be trivial in a simple relational database of companies and contacts (such as say looking up all companies with only one contact) are not just difficult but technically impossible in ACT.
New Member
Posts: 8
Country: United States

Re: How do I do a lookup across Company AND Contact??

Oops - I just posted this very question before looking long enough to find this thread (see my post).  I swore the Version 2009 advertising specifically mentioned or at least implied better Lookups including blended Company and Contact Lookups.  It is quite odd for such an established product that is the "leader" of CRMs to have such a basic inability.
Platinum Elite Contributor
Posts: 6,651
Country: USA

Re: How do I do a lookup across Company AND Contact??


fredlanger1 wrote:
It is quite odd for such an established product that is the "leader" of CRMs to have such a basic inability.

 

 
 Actually what you are trying to do is neither basic or simple when dealing with a relational database like the ACT! program. The information you are wanting to use is located in two separate tables in the database. Because you evidently have a field in the company table for the region, you might be able to add a field to the contact table and link it to the regional field in the company table. I've not tested this but I think you would then have all the necessary information in the contact table to do the lookup that you want.
 
Roy Laudenslager
ACT! Certified Consultant
ACT! Report Expert
Durkin Impact Report Designer
www.techbenders.com
royel@techbenders.com
541-343-8129
New Member
Posts: 8
Country: New Zealand

Re: How do I do a lookup across Company AND Contact??

I think it is the last 4 words in your first sentence that are probably very true, 'like the ACT program' because as anyone knows to query across mutiple tables is actualy one of the most basic SQL queries apart from querying a single table which begs the question about ACT's ability and certainly its claim to be a relational database, and since we know that the underlying database does have full relational capabilities it must mean that it is the restrictions on querying that database placed on us by the ACT program itself is the problem.

 

As for your assertion tht maybe I should put the field from the company table into the contact table, this is just a 'nonsense'  if you consider that the information in the company field relates to the company and not the contact and given that contacts within a company change considerably more often than the actual companies do, then why would I even bother to use ACT I may as well go back to the flat relationship of a spreadsheet <shudder> and all the inherent inaccuracies of double entry.

 

I think that ACT need to allow users to actually query the data stored in the underlying relational database in a relational manner so that it actually can be queried in a meaningful way.

 

For example if I want the lookup all sales managers in current companies and the flag of which is a 'current' company is in the 'company' table where it should be and list the names of those managers, ACT will not allow me to do this because the query is across 2 tables which to me as a database developer is utterly incomprehensible.

 

And to suggest that I put the 'current' flag on the contact table is simply a one way road to disaster given that when the contact changes I now longer have an acccurate way of finding my current companies and if I have multiple contactswithin a company then I need to have multiple 'current' flags.........<shreeeeeeeeaaaak>  need I say more....

 

R.S.

Database Specialist

LC Research

New Zealand

Platinum Elite Contributor
Posts: 14,384
Country: Australia

Re: How do I do a lookup across Company AND Contact??

It always bugs me when people use the term "relational database". I suspect most people just use it as a marketing term but usually get the meaning quite wrong.

 

There are a number of definitions of "relational databases".

The original "guidelines" were 12 rules created by Dr EF Codd (IBM) in the mid-80s - he created the relation database model in 1970 ... I used the term "guidelines" because I believe that to-date no commercial RDMS complies with all 12. I have a feeling that **bleep** Pick's, now defunct, system came closest (certainly at the time).

 

As to the lookup issue, I think there are a few add-ons that do add this ability.

 

You might post a request for this feature to be considered for a future version here - http://www.act.com/community/feature

Input from this web page goes directly into a database that management has direct access to read, and which they also track and gather statistics on.

New Member
Posts: 8
Country: United States

Re: How do I do a lookup across Company AND Contact??

[ Edited ]

Darkfaythe,

 

I'm glad I saw your response to the answer I received above.  I have worked with MS Access databases for years and had the same thought - I could build that query in Access quickly with no problem.  Your response is right along the lines of what I was going to say.

 

I did go ahead and submit this request to the link offered above.  Thank you.

Message Edited by fredlanger1 on 10-14-2008 01:28 PM
New Member
Posts: 8
Country: New Zealand

Re: How do I do a lookup across Company AND Contact??

Thanks for this suggestion Mike, I also have already done just as you suggest a short time after I started this thread.

 

Cheers

 

RS