Query Builder
Query builder is used to apply custom filter on modules like Contacts, Companies and Projects. You can Create and Save Queries on Query builder and then those queries will be available for
A. filters in Contact, Companies, Project, Review Project, Finished project, Service Schedulers, Accounts, Vouchers, PaymentLog, EmailLog, SMSLog and Client Login Modules.
B. Creating Gadgets on dashboards .
More than 350 Parameters available in QueryBuilder, click on this link to view updated parameter list Parameters List
1- Click on create new button on query builder page.
2- Type proper name of query like Cash and Bank Balance so that we can use it for filter in future.
3- In builder section choose parameter “Account : type equal Bank Accounts”,
click +add rule and choose “Account : type equal Cash in hands”,
click +add rule and choose “Account : type equal Bank OD Accounts”, select OR operator between them.
4- You can create personal or shared type query.
5- Save this query for future use.
1- Click on create new button on query builder page.
2- Type proper name of query like Creditors Balance so that we can use it for filter in future.
3- In builder section choose parameter “Account : type equal Sundry Creditors”,
click +add rule and choose “Account : Balance not equal to 0”,
select AND operator between them.
4- You can create personal or shared type query.
5- Save this query for future use.
1- Click on create new button on query builder page.
2- Type proper name of query like Debtors Balance so that we can use it for filter in future.
3- In builder section choose parameter “Account : type equal Sundry Debtors”,
click +add rule and choose “Account : Balance not equal to 0”,
select AND operator between them.
4- You can create personal or shared type query.
5- Save this query for future use.
1- Click on create new button on query builder page.
2- Type proper name of query like Expense so that we can use it for filter in future.
3- In builder section choose parameter “Account : type equal Purchase Accounts”,
click +add rule and choose “Account : type equal Direct Expenses”,
click +add rule and choose “Account : type equal Indirect Expenses”,
4- Select OR operator between them.
5- You can create personal or shared type query.
6- Save this query for future use.
1- Click on create new button on query builder page.
2- Type proper name of query like Income so that we can use it for filter in future.
3- In builder section choose parameter “Account : type equal Sales Accounts”,
click +add rule and choose “Account : type equal Direct Incomes”,
click +add rule and choose “Account : type equal Indirect Incomes”,
4- Select OR operator between them.
5- You can create personal or shared type query.
6- Save this query for future use.
1- Click on create new button on query builder page.
2- Type proper name of query like Payments so that we can use it for filter in future.
3- In builder section choose parameter “Transaction : Type equal Payment”
4- You can create personal or shared type query.
5- Save this query for future use.
1- Click on create new button on query builder page.
2- Type proper name of query like Receipts so that we can use it for filter in future.
3- In builder section choose parameter “Transaction : Type equal Receipt”
4- You can create personal or shared type query.
5- Save this query for future use.
1- Click on create new button on query builder page.
2- Type proper name of query like Taxes Balance so that we can use it for filter in future.
3- In builder section choose parameter “Account : Type equal Input Tax”
click +add rule and choose “Account : Type equal Output Tax”,
4- Select OR operator between them.
5- You can create personal or shared type query.
6- Save this query for future use.
1- Click on create new button on query builder page.
2- Type proper name of query like Taxes Balance so that we can use it for filter in future.
3- In builder section choose parameter “Account : Type equal Input Tax”
click +add rule and choose “Account : Type equal Output Tax”,
4- Select OR operator between them.
5- You can create personal or shared type query.
6- Save this query for future use.
1- Click on create new button on query builder page.
2- Type proper name of query like Unsettled Payments so that we can use it for filter in future.
3- In builder section click on +ADD GROUP button,
then choose parameter “Account : Type equal Sundry Debtors” ,
click +add rule and choose “Account : Type equal Sundry Creditors”,
select OR operator between them.
4- Again click on +ADD GROUP button,
then choose parameter “Transaction : Type equal Payment” ,
click +add rule and choose “Transaction : Is Settled equal to false”,
select SUB-AND operator between them.
5- Select AND operator between both group.
6- You can create personal or shared type query.
7- Save this query for future use.
1- Click on create new button on query builder page.
2- Type proper name of query like Unsettled Receipts so that we can use it for filter in future.
3- In builder section click on +ADD GROUP button,
then choose parameter “Account : Type equal Sundry Debtors” ,
click +add rule and choose “Account : Type equal Sundry Creditors”,
select OR operator between them.
4- Again click on +ADD GROUP button,
then choose parameter “Transaction : Type equal Receipt” ,
click +add rule and choose “Transaction : Is Settled equal to false”,
select SUB-AND operator between them.
5- Select AND operator between both group.
6- You can create personal or shared type query.
7- Save this query for future use.
1- Click on create new button on query builder page.
2- Type proper name of query like ‘Auto Billing of ITRs after Lead’ in our case so that we can use it for filter in future.
3- In builder section click on +ADD RULE button,
then choose parameter “Project : Transaction Exists equal False”,
click +add rule and choose “Service : Search equal Income Tax Return”,
click +add rule and choose “Project : Status Greater Lead”,
select AND operator between them.
4- You can create personal or shared type query.
5- Save this query for future use.
6- You can use this query anywhere. We use it in Personal Gadgets and Automation.
7- This query gives all ITR projects which billing is pending.
1- Click on create new button on query builder page.
2- Type proper name of query like “GSTN clients without return service” in our case so that we can use it for filter in future.
3- In builder section click on +ADD GROUP button,
4- Then choose parameter ‘Company Relation Service : Search equal GSTR 1 Monthly’,
click +add rule and choose ‘Company Relation Service : Search equal GSTR 1 Quarterly’,
click +add rule and choose ‘Company Relation Service : Search equal GSTR 4’,
click +add rule and choose ‘Company : Name doesn’t contain GSTN’,
5- Select OR operator between them and tick on NOT checkbox also.
6- You can create personal or shared type query.
7- Save this query for future use.
Note - Company : Name Parameter only work if your company name has been appended with word ‘GSTN’.
1- Click on create new button on query builder page.
2- Type proper name of query like “Clients with wrong GSTN services” in our case so that we can use it for filter in future.
3- In builder section click on +ADD RULE button,
then choose parameter “Company Relation Service : Search equal to GSTR 4” ,
4- Click on +ADD GROUP button,
then choose parameter ‘Company Relation Service : Search equal to GSTR 1 Monthly’,
then choose parameter ‘Company Relation Service : Search equal to GSTR 1 Quarterly’,
select OR operator between them, and select AND operator between outer rule and group.
5- You can create personal or shared type query.
6- Save this query for future use.
1- Click on create new button on query builder page.
2- Type proper name of query like “Unbilled GSTR 4 Projects” in our case so that we can use it for filter in future.
3- In builder section click on +ADD RULE button,
4- Then choose parameter ‘Service : Search equal to GSTR 4’,
5- Click +add rule and choose ‘Project : Transaction Exists equal to false’.
6- Select AND operator between them.
7- You can create personal or shared type query.
8- Save this query for future use.
9- You can use this query anywhere. We use it in Personal Gadgets.
1- Click on create new button on query builder page.
2- Type proper name of query like “GSTN Clients with ITR service pending” in our case so that we can use it for filter in future.
3- In builder section choose parameter ‘Service : Search equal to Income Tax Return’,
4- Click +add rule and choose ‘Project : Status less than Completed’,
5- Click +add rule and choose ‘Company : Name contains GSTN’,
6- Select AND operator between them.
7- You can create personal or shared type query.
8- Save this query for future use.
9- You can use this query anywhere. We use it in Personal Gadgets.
Note -
- Company : Name Parameter only work if your company name has been appended with word ‘GSTN’.
- Income Tax Return name used in our case, your service name might be different like ITR.
1- Click on create new button on query builder page.
2- Type proper name of query like “New Clients ITR Pending” in our case so that we can use it for filter in future.
3- In builder section choose parameter ‘Service : Search equal to Income Tax Return’,
4- Click + add rule and choose ‘Company : Create Date greater or equal Start of Last Month’,
5- Click + add rule and choose ‘Project : Create Date greater or equal Start of Last Month’,
6- Click + add rule and choose ‘Project : Status Less than On Hold’,
7- Select AND operator between them.
8- You can create personal or shared type query.
9- Save this query for future use.
10- You can use this query anywhere. We use it in Personal Gadgets.
For example, I want list of all pending projects of Mr. Robin, for that i make query
- Goto Query Builder page and click on create new button.
- Type proper name of query like “Pending Projects of Mr Robin” in our case so that we can use it for filter in future.
- In builder section choose parameter ‘Project Member : Search equal to Mr Robin’,
- Click + Add Rule and choose parameter ‘Project : Status less than Completed’,
- Select AND operator between them.
- You can create personal or shared type query.
- Save this query for future use.
- You can use this query in project page for finding pending work of particular staff.
Note :- Put your staff name in place of Mr Robin.
For achieve this we create query
- Goto Query Builder page and click on create new button.
- Type proper name of query like “Work In Progress” in our case so that we can use it for filter in future.
- In builder section choose parameter Project : Status equal to In Progress. (There are 7 options in dropdown, which option you want to find select, in our case we select ‘In Progress’ )
- AND operator automatically selected in single query parameter.
- You can create personal or shared type query.
- Save this query for future use.
- You can use this query in project page for finding In Progress work.
To find this we create query
- Goto Query Builder page and click on create new button.
- Type proper name of query like “Company Service attach count is zero” in our case so that we can use it for filter in future.
- In builder section choose parameter Company Relation Service : Name, then select ‘is not empty’ from drop down,
- AND operator automatically selected in single query parameter, tick on NOT (Nagation) check box,
- You can create personal or shared type query.
- Save this query for future use.