Free help: USER GUIDE

Contact Us

Home >  Clients & Profits X User Guide > My  > Selecting Records

  Clients & Profits X Online User Guide

Search the user guide:


glossary
Contents
  • Currently 4.00/5
  • 1
  • 2
  • 3
  • 4
  • 5

Rating: 4.0/5
(1 vote cast)

  • Helpful?
  • 0 Yes
  • 0 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

Comments (0)

Sort by:  Most Recent  |  Oldest

For subscribers: VIDEOS } FAQs/TECH NOTES } LEARN
flag   © 2011 Clients & Profits, Inc. w e  l o v e  a d v e r t i s i n g