Community
Showing results for 
Search instead for 
Do you mean 
Reply

"Advanced" Queries

Astute Commentator
Posts: 15
Country: United_Kingdom

"Advanced" Queries

Windows 7

Sage ACT! Pro 2012 Version 14.1.108.0

 

I have seen queries in use where parentheses are used.  I need to do this, for example:-

 

((Business Type = "Actor" OR Business Type = "Actor Agency") AND (IdStatus Does not contain "Customer"))

 

I have applied parentheses in the same way I would in MS SQL.

 

All I can see the option for is very simple And/Or which clearly won't do what I need.

 

Is the use of parentheses only possible in Act! Premium?

If it is possible in Act! Pro, how do I go about it?

If it is not possible in Act! Pro, is there a workaround?

 

Thanks,

Jenny

 

Platinum Elite Contributor
Posts: 6,651
Country: USA

Re: "Advanced" Queries

You appear to making this too complicated. It's really just a three statement query. Here is your statement without the extra paraenthesis:

 

(Business Type = "Actor" OR Business Type = "Actor Agency") AND IdStatus Does not contain "Customer"

 

Actually you could do this by chaining simple queries like this:

 

Business Type Starts with "Actor" -- Replace query

IdStatus Does not contain "Customer" -- Narrow query

Roy Laudenslager
ACT! Certified Consultant
ACT! Report Expert
Durkin Impact Report Designer
www.techbenders.com
royel@techbenders.com
541-343-8129
Astute Commentator
Posts: 15
Country: United_Kingdom

Re: "Advanced" Queries

Hi Roy,

 

Many thanks for your reply.

 

I've managed to sort it out.  Don't ask me how, but I didn't notice how to add parentheses.  As soon as I fathomed that out everything was straight forward.

 

I've noted that you have only used one set of parenthes on your example querty, thank you, this is good to know.  I've found MS SQL needs to see parenthese on every group, such as I showed in my example.

 

Cheers,

Jenny

Astute Commentator
Posts: 15
Country: United_Kingdom

Re: "Advanced" Queries

Sage ACT! Pro 2012 Version 14.1.108.0

 

Roy, is there anywhere I can download a user manual, please?

 

The link to a user guide is broken on my install, so the pdf is missing, and I can't find anything on line.

Platinum Elite Contributor
Posts: 6,651
Country: USA

Re: "Advanced" Queries

I've found that adding extra parentheses to a query won't hurt but it can make determining that you have grouped and set presidence correct harder. Long ago I was taught a magic word KISS - Keep It Simple.

Roy Laudenslager
ACT! Certified Consultant
ACT! Report Expert
Durkin Impact Report Designer
www.techbenders.com
royel@techbenders.com
541-343-8129
Astute Commentator
Posts: 15
Country: United_Kingdom

Re: "Advanced" Queries

 

I totally agree on KISS.  But I like to stick to methods I'm used to and feel confident with.

 

Any thoughts on where i can find a manual/pdf, please?

Platinum Elite Contributor
Posts: 6,651
Country: USA

Re: "Advanced" Queries

Your best sources are the ACT! Help and the knowledge base. There was a Quick Start Guide which was actually done by a third party but that hasn't been updated for the last couple of versions. there is a book on Dashboards and Reports (I wrote the part on reports) from Packt publishing. The reports part is still relevant.

Roy Laudenslager
ACT! Certified Consultant
ACT! Report Expert
Durkin Impact Report Designer
www.techbenders.com
royel@techbenders.com
541-343-8129
Astute Commentator
Posts: 15
Country: United_Kingdom

Re: "Advanced" Queries

 

Thanks, Roy, much appreciated Smiley Happy