Search the user guide:

glossary
Contents

• Helpful?
• 0154 Yes
• 0154 No

Queries (as in the word inquiry) find and print records from your database using some user-defined criteria

A query is a key part of a custom report; without it, a custom report would show every record in the database. Queries let you focus in on some specific sets of data, such as jobs for one client. Together with the sorting and subtotaling options, queries let you truly customize what you see on a report.

Queries can be simple (e.g., jobs for one client) or complex (e.g., open jobs for three clients that have the same AE/team). The query function is the closest you’ll get to programming. They are very structured, but have lots of variations for flexibility.

Queries are based on mathematical operators, such as equal to, greater than, contains, less than, etc. The query itself is a formula that contains a field name, an operator, and a value. When the custom report is printed, the records in the database are matched against the query. If the record is a match, it is sorted and printed; if not, the record is skipped. Any field on a custom report can be used to make a query. If a field isn’t part of the report, it can’t be used as a query. You can add fields to a report just for queries; they can be marked as invisible to keep them from appearing on the printed page.

Complex queries use formulas for several different fields to find a specific set of records from your database. To print an open job list for four clients with the same AE/team, you’ll need to create three different query formulas: one to select open jobs, another to select jobs for four clients, and a final query to find jobs with a certain AE/team. When the report is printed, only jobs that match these three queries will be included. .

To find records with a simple query

1 Click on the Fields button to open the Fields/Queries window.

Queries. Any field appearing on a custom report can be used to make a query. The query formula uses operators like greater than, equal to, and contains. The completed query can be easily changed by highlighting it.

2 Select a field in the Field list, such as JOBS.J_PROD_STATUS.

3 Click on a comparison operator, such as >=.

4 Enter a value to be used by the query, such as 10.

Press Enter to save this query. The query formula will appear in the Query column on the Field list. You can modify the query by selecting it with your mouse then making any changes.

5 Click the Preview button to test this query. The custom report will be printed to the screen showing only records selected by your query. If the query worked, you’ll see some information; if not, you may see nothing. It may take several tries to find the right query you’re looking for.

To find records with a complex query

For this query to work, the custom report needs these fields:

JOBS.J_PROD_STATUS,
JOBS.J_CLIENT_NUM,
JOBS.J_AE_TEAM, and
JOBS.J_NUM.

1 Select the JOBS.J_PROD_STATUS field, choose the >= operator, then enter the query value of 10 (this assumes that jobs with status 10 or higher are open jobs).

2 Select the next field, JOBS. J_CLIENT_NUM, choose the = operator, then enter the query value as: ABC,RETAIL,OFF,PARKS. This query formula will print any job opened for any one of these four clients.

3 Select the final field, JOBS.J_AE_TEAM, choose the = operator, then enter the query value as: BEBE (this assumes BEBE is the AE name).

4 Press Enter to save this query, then click the Preview button to test this query. The custom report will be printed to the screen showing jobs selected by this query.

Example Queries
To find this	Enter this query
Jobs for client ABC	JOBS.J_CLIENT_NUM = ABC
Closed jobs by due date	JOBS.J_CLOSED = 1, J_DUE_DATE >= 1/1/95
Jobs by AE/Team BEBE	JOBS.J_AE_TEAM = BEBE
Open jobs	JOBS.J_PROD_STATUS >= 1,<=899
Today’s unfinished job tasks	JOB_TASK.X_DUE_DATE >= #D (#D means today)
All unfinished job tasks	JOB_TASK.X_FINISHED = 0
Unbilled job tasks	JOB_TASK.X_UNBILLED > 0
Never billed job tasks	JOB_TASK.X_BILLED = 0
Job tasks with estimate balances	JOB_TASK.X_ESTIMATE - X_BILLED > 0
Job tasks by group 1010	JOB_TASK.X_GROUP = 1010
Estimate tasks for client ABC	JOB_TASK.X_KIND < 2, JOBS.J_CLIENT_NUM = ABC
A/P for vendor TYPO	AP.AP_VENDOR_NUM = TYPO
Costs for vendor TYPO	COSTS.COST_VENDOR = TYPO
Unposted costs for vendor TYPO	COSTS.COST_POSTED < 1, COSTS.COST_VENDOR = TYPO
Time sheets only	COSTS.COST_KIND = 1
Today’s time by staffer AE	COSTS.COST_DATE = #D, COSTS.COST_VENDOR = AE, COSTS.COST_KIND = 1
Unbillable time	COSTS.COST_KIND = 1, COSTS.COST_BILLABLE = 0
Job cost transfers for job 101	COSTS.COST_KIND = 3, COSTS.COST_JOB_NUM = 101
Unpaid invoices for AE John	AR.AR_AE = John, AR.AR_TOTAL-AR.AR_AMT_PAID <> 0
Period 2 JEs for sales only	JE.JE_SOURCE = 2, JE.JE_PERIOD = 2
Balance sheet G/L accounts	ACCOUNTS.AC_CATEGORY = 0
YTD billings for task ART	AR-ITEMS.AX_TASK = ART, AR-ITEMS.AX_DATE >= “1/1/95”
Creative tasks from Task Table	TASK_TABLE.T_GROUP = 1010 (where 1010 is the “creative” group)
Active vendors only	VENDORS.V_ACTIVE = 1

Operators:

In the Fields window, clicking the Titles button lets you change column headings for all fields on the custom report.

Queries can use selections with wild card characters, making it easy to find records without knowing exact codes. The Like query uses a pattern matching system with wild card characters like “_” and “%”. The “_” (an underscore) matches any characters up to the end of the string. The “%” character matches any single character.

These query “operators” are used to select records. They include:

Equal to	=	To clear the report’s query and start over, choose Clear All Queries from the Query pop-up menu. Or, you can simply delete the query for each field with your mouse.
Not equal to	< >
Greater than	>
Greater than or equal to	> =
Less than or equal to	< =
Less than	<
Accept a query value	¯
Cancel a query value	X

   

Learn more:

Custom report basics
Making custom reports
Working with data files
The C&P file structure
Modifying custom reports
Exporting data with custom reports