Community
Showing results for 
Search instead for 
Do you mean 
Reply

Not another "Advanced query" question!

Accepted Solution Solved
New Member
Posts: 3
Country: United Kingdom
Accepted Solution

Not another "Advanced query" question!

Dear Forum,

 

I am attempting to create a group via an Advanced Query.

All my sales are listed under the current month and I'm attempting to identify which of my 5300 contacts DON'T have a booking for the next month! 

The query 'should' exclude all contacts that have an 'Opportunity name' that is 'not equal to' February 2010 but doesn't appear to work!!

 

 

The query is attempting to find all contacts that:

  • have an email address 
  • are a customer
  • or an ex customer
  • or a prospect
  • who don't pay via Direct Debit
  • and who haven't got an opportunity named February 2010

Unfortunately, the dynamic list keeps populating with contacts that already have a booking for February 2010 and I don't know why? The rest of the query appears to work correctly...it's just the final line that upsets the apple cart!

The full query is as follows...

 

Contact,  Field name = email, Operator = Contains data, And/or = And

(Contact, Field name = Status, Operator = Equal to, Value = Customer, And/or = or

Contact, Field name = Status, Operator = Equal to, Value = Prospect, And/or = or

Contact, Field name = Status, Operator = Equal to, Value = "Ex Customer"), And/or =And

Contact, Field name = Payment method, Operator = Not Equal to, Value = "Direct Debit", And/or = And

Contact, Field name = Opportunity Name, Operator = Not equal to, Value = "February 2010", And/or =End

 

If I remove the inverted commas from the February 2010 field my results decrease by 8! and I'm not sure why this is happening as there are far more bookings already in for February 2010??

 

I would really appreciate any help or insight that anyone can offer please as I being driven to distraction with the frustration of it all!

 

Many Thanks

John

 


Accepted Solutions
Solution
Accepted by topic author jaypeegolf
‎09-25-2015 03:20 AM
Copper Elite Contributor
Posts: 118
Country: United_Kingdom

Re: Not another "Advanced query" question!

Hello John,

 

Your query returning Contact records that include the value 'February 2010' in an associated 'Opportunity Name' field (even when you've specifically attempted to exclude these) could merely result from the fact that these particluar Contact records do also have other Opportunites assigned to them that have 'Opportunity Names' with other values in them.

 

For example... Any given Contact with an associated Opportunity Name of 'February 2010' would still be returned by your Query if they also had another Opportunity named 'October 2008' (this latter one results in the Contact's inclusion by virtue of not being equal to February 2010).

 

You'll probably need to re-engineer the logic a little and approach this as a two-stage process:

 

1. Create a 'Lookup' of all Contacts who have an Opportunity Name equal to 'February 2010'

2. Tag these Contact records so that they are identifiable (perhaps use 'Edit', 'Replace Field' and replace the contents of - for example - User Field 1 with 'February 2010')

3. Create an Advanced Query as below and execute:

 

Contact,  Field name = email, Operator = Contains data, And/or = And
(Contact, Field name = Status, Operator = Equal to, Value = Customer, And/or = or
Contact, Field name = Status, Operator = Equal to, Value = Prospect, And/or = or
Contact, Field name = Status, Operator = Equal to, Value = "Ex Customer"), And/or =And
Contact, Field name = Payment method, Operator = Not Equal to, Value = "Direct Debit", And/or = And

Contact, Field name = User Field 1, Operator = Not Equal to, Value = "February 2010", And/or = End


Will Ingleby
ACT! Certified Product Specialist

Solutions for Accounting & CRM (UK)

View solution in original post


All Replies
Platinum Elite Contributor
Posts: 6,537
Country: USA

Re: Not another "Advanced query" question!

Try using the "Does not contain" operator on the last two statements and use only one word for the value (ie, Debit or February). Verify there are no mispellings.

Greig Hollister

Note: Effective 6/1/13, Sage no longers provides support for the Act! software. This is now provided by Swiftpage.

New Member
Posts: 3
Country: United Kingdom

Re: Not another "Advanced query" question!

Hi Grieg,

I don't have the option to select "Does not contain"? The only option I have is "Does not contain data"! which, I guess, relates to empty fields?

 

Where can I find "Does not contain"?

 

Thanks for the suggestion 

John

Platinum Elite Contributor
Posts: 6,537
Country: USA

Re: Not another "Advanced query" question!

What version of ACT! are you using? That operator was added I believe in ACT! 2008. If you don't have the "Does not contain operator", then try "Starts with" or "Contains". The idea is do a partial string search instead of an exact string search.

Greig Hollister

Note: Effective 6/1/13, Sage no longers provides support for the Act! software. This is now provided by Swiftpage.

New Member
Posts: 3
Country: United Kingdom

Re: Not another "Advanced query" question!

Hi Grieg,

I'm using ACT! 2007 (V9.0)

 

It would appear that the difficulty is in excluding contacts and the query operators don't'give much scope for this!!

The only option I have is 'Does not contain data" or 'Is not equal to'

 

Cheers

John

Solution
Accepted by topic author jaypeegolf
‎09-25-2015 03:20 AM
Copper Elite Contributor
Posts: 118
Country: United_Kingdom

Re: Not another "Advanced query" question!

Hello John,

 

Your query returning Contact records that include the value 'February 2010' in an associated 'Opportunity Name' field (even when you've specifically attempted to exclude these) could merely result from the fact that these particluar Contact records do also have other Opportunites assigned to them that have 'Opportunity Names' with other values in them.

 

For example... Any given Contact with an associated Opportunity Name of 'February 2010' would still be returned by your Query if they also had another Opportunity named 'October 2008' (this latter one results in the Contact's inclusion by virtue of not being equal to February 2010).

 

You'll probably need to re-engineer the logic a little and approach this as a two-stage process:

 

1. Create a 'Lookup' of all Contacts who have an Opportunity Name equal to 'February 2010'

2. Tag these Contact records so that they are identifiable (perhaps use 'Edit', 'Replace Field' and replace the contents of - for example - User Field 1 with 'February 2010')

3. Create an Advanced Query as below and execute:

 

Contact,  Field name = email, Operator = Contains data, And/or = And
(Contact, Field name = Status, Operator = Equal to, Value = Customer, And/or = or
Contact, Field name = Status, Operator = Equal to, Value = Prospect, And/or = or
Contact, Field name = Status, Operator = Equal to, Value = "Ex Customer"), And/or =And
Contact, Field name = Payment method, Operator = Not Equal to, Value = "Direct Debit", And/or = And

Contact, Field name = User Field 1, Operator = Not Equal to, Value = "February 2010", And/or = End


Will Ingleby
ACT! Certified Product Specialist

Solutions for Accounting & CRM (UK)