Using the client and job database query engines
The client database query engine allows you to create your own reports that both include and are filtered by any mixture of your client data. If you need to output or filter by any jobs data then you should use the jobs database query engine.
Where are these report writers?
- The client report writer is found in the menu labelled with your business name, it is the second option in the menu.
- The jobs report writer is found in the workflows menu, it is towards the bottom, below the list of your workflows jobs and pool reports.
How to create a report?
The first step is to decide which fields you wish to output in your results.
- Click on the field selector dropdown (numbered 1 in the image below)
- Click on any fields you wish to output, the options will include.
- Standard client information, including:
- Title, First name, Surname for individuals.
- Organisation name.
- Complete name.
- Glide client code (our unique ID for the client).
- Client type.
- Client status (prospect, active, lost).
- Client code.
- Date added to Glide.
- All custom client fields you have setup on your summary screen. (See this article for how to add more)
- All custom client fields you have setup on your workflow tabs of the client screen. (Same article as above)
- On the jobs report writer you shall have the following additional options:
- Glide job code (our Unique ID for the job).
- Job date.
- Workflow system.
- Job state (Not started, in progress, complete).
- Job holder.
- Job current stage name.
- Job current stage value.
- Actual dates for all stages.
- Target dates for all stages.
- Job deadlines.
- All job fields.
- All job positions.
- Standard client information, including:
- Click out of the field selector dropdown to close it.
Then you can set some filters (aka rules).
- Note that this step is entirely optional, if you not set any filters then the output will be for all client or all jobs. Typically you would want to apply some filters.
- Click on the green + Add rule button (numbered 2 in the image below).
- Add in your filter (numbered 3 in the image below). Any filter has 3 components:
- Firstly the dropdown will list all of the same choices that you chose from above (when deciding which fields to output), e.g. Stage dates, client status etc. Choose the field you wish to filter by. Each rule focused on one field but you can have as many rules as you need.
- Once you select a field a second dropdown will appear to the right, this will contain a series of logic expressions such as Equals, Greater than etc. The precise list of logic expressions will depend on the field you have selected. Select the appropriate choice for this rule.
- Finally there is a data entry field for you to enter the value(s) for your rule. For example if you have chose a date field such as job date and then chosen the logic expression between, then you will see 2 date boxes for your values.
- Repeat step 3 to add further rules as required.
- If you have more than one rule then be careful to set the AND / OR setting appropriately (numbered 4 in the image below), if you choose AND then each of your filters will be applied to the data and the output will only return clients/jobs that match all of the criteria. If you choose OR then clients/jobs will be returned where any one of the filters applies.
If needed you can add extra groups of filters.
- To add another group of filters click on the green + Add group button (numbered 2 in the image below).
- Separate groups of rules allow you to have a group that have a different AND / OR setting. For example if your primary group if set to AND you may have two rules, to return jobs that are VAT returns AND where the Job date is this year. You may then have another group of rules which further filter the jobs returned to those where the job holder is either Bill OR Ben.
- Each group has its own NOT indicator, tick this to return those jobs that are not returned by the query.
Now let's run the report.
- Firstly consider ticking the box to include links (numbered 5 in the image below). This will hyperlink references to client, jobs and users so you can click to go to the appropriate page in Glide.
- When you are ready click the green Execute query button to run the report. The results will appear below in a table. You can click on some column headers to sort by that column, rearrange columns and export the data to a variety of formats.