Lookup based on Contact and Company fields

[fredlanger1]

This may be simple - it seems like it should be - but I can't figure it out.  I want to create a Lookup that returns all Contacts who have an email address and whose Company is in the Southeast region.  The email field is a Contact field and the operator is "contains data".  The Company field I can't access is Region.  I know in ACT 2008 you couldn't mix Companies and Contacts in a Lookup, but I thought I read that Premium 2009 would do this and was looking forward to it.

Thank you,

Fred   

[Darkfaythe]

Hi

I had an interesting reply from a supposed ACT specialist in the thread I started on this subject a while ago and he claimed that to query across multiple tables was a complex thing to do......hmmmm.

Well here is my reply to his assertion.

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 contacts within a company then I need to have multiple 'current' flags.........<shreeeeeeeeaaaak>  need I say more....

R.S.

Database Specialist

LC Research

New Zealand

[GLComputing]

You might try something like this - http://www.tnhg.com/prod/download/LookupContactsInCompanies.html

[fredlanger]

Has anyone tried the product, Lookup Contacts In Companies, mentioned above?  Has it been updated to work with ACT Premium 2009?  I really need to solve this ridiculous shortcoming in ACT.

It is still amazing to me (and quite frustrating) that I can't do the simple query I mentioned at the beginning of this thread.  I did a search in this Community on the 3 words, Contact, Company, and Lookup and find I am definitely not alone.  I have to scratch my head when I think how easy sneezy it is to create a query in MS Access that returns a subset of records for table A based on parameters set upon the related table B. 

[Darkfaythe]

Sorry but I haven't used the link as described above for 2 reasons.

1.  Why should I pay to query my data?.

2. What we are trying to do as everyone that replied to my initial query agrees is a BASIC SQL query that ANY database can accommodate EXCEPT ACT.

Our organisation has 500 staff and I am currently doing everything I can to have ACT replaced with a CRM that can handle such basic queries like 'what contacts belong to which companies'.

Interesting thing is, ACT themselves have never bothered to give a meraningful reply to this simple request that clearly a lot of people are making.

If I can find a 'workable' and 'common sense' way of achieving such a simple query I will put it in this thread.

<Content edit.  Please keep it courteous.>

Message Edited by dlunceford on 03-24-2009 10:10 AM

[dlunceford]

Hello Darkfaythe,

Unfortunately, I'm not sure if querying across contacts and companies is currently being addressed in a future release or not.  Please submit a feature request to be considered in a future version.  Your requests are submitted directly to development and marketing. 

http://www.act.com/community/feature/

ACT! Reader Utility

The ACT! Reader Utility is designed to meet the power-reporting and data-extraction needs of some ACT! corporate customers. The ACT Reader account provides read-only access to most data tables within ACT!. Once connection is made to the ACT! database, third-party tools such as Crystal Reports® can be used to query these tables for advanced reporting and data extraction.

The ACT! Reader Utility allows the user to establish and set a user-defined password to log in to the ACT! Reader account. The ACT! Reader Utility also provides the ability to define, change, and reset the password.

ACT! SA Password Utility
The ACT! SA Password Utility provides organizations with direct, local access to the ACT! SQL instance by exposing the SA password. This utility is designed for those organizations that need third-party advanced back-up support, which requires an SA password. Customers may also find the SA password useful for querying database tables for custom reporting and data extraction using third-party tools such as Crystal Reports.

For details on these services, please call ACT! Corporate Sales at 866-903-0006.

[m4y]

Hello Darkfaythe,

I agree with you and Sage defends this kind of limitation by the fact that ACT is a Contact Manager and not a true CRM like SageCRM or SalesLogix.


Add-ons are developed by ACC to "break" some native limitations or provide you some new features.
If you need a simple tool to return all contacts belonging a Company List, have a look at UrContactLookup for ACT.
We are also developing a tool to query both company AND contact fields, but it is not yet as much powerful as the native Advanced Query (no brackets, no opportunity fields, etc.). But we are still working on it. Have a look at UrQuery for ACT

[GLComputing]

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.