How to build an Advanced Inquiry or Report Designer Query in 5 steps

How to build an Advanced Inquiry or Report Designer Query in 5 steps

Have an idea for a query

Before  you start building any query make sure you have an idea for what you want to build. It’s hard to build as you go but if you have an idea or goal of what is needed it will help speed up the process. 



Step 1 - Select your tables


Tables can be found on the left side. You will click and drag your tables over blank white area to the right.



It is recommended you set to view file names and not file descriptions. If you do not see the same table names list in this sheet then toggle this option. 



If no tables are appearing make sure the drop down section has a table selected. Preferably COLLDEBT@ or COLLACCT@.




You will need to drag over any table you will be working with. If you are struggling with knowing which table to bring over for specific data please check out our documentation on 15 most common files. 


Step 2 - Select recnum from each table selected you selected


Put a check box in ever table you brought over in step 1. Every table in the system has an option called recnum.  



Recnum stands for record number and is unique value in each table. Meaning no other record in that table shares that number with other records. 


In COLLACCT@ the account file recnum is the Account Number. 

In COLLDEBT@ the debtor file the recnum is the Master/Debtor number.

In COLLCLT@ the client file the recnum is the client number. 


Step 2 is optional, you do not need to select recnum on ever query/report you run but in till you are comfortable enough to know when you can select and do not need to I advise that you always select it. Some features in Advanced Inquiry and Report designer require you to have them selected. 

 



Step 3 - Check criteria fields 


Advanced Inquiry and Report Designer by default will grab ever account on your system. This option where where we add criteria to shrink down our account pull from everything in the system to what it is we are looking for. 


This step all we are doing is put a checkbox next to the fields we want to do criteria searches on. So if we wanted to look for a certain status code we would check status code box. Put a checkbox next every field that well help you shrink your results down to what you are looking for. 




Step 4 - Add criteria


Now that we have selected the criteria we want use in step 3, we have to fill out what that criteria is.  In the middle section of the screen you will see section with different rows. Find the row you want to add criteria, in this example Status Code. Then go over to the Criteria header and down to the status code row. Once you select that cell you will see a button with 3 dots, click on that to add criteria.  



Once you click on the 3 dots it will open up expression builder. In here is where we will enter our criteria for the field selected, status code for this example. 



Below is common operators  used when filling out criteria 

Operator

Definition

Example

>

Greater than  - This is used mostly with dates and numeric values. This can be used on Alpha but results can be tricky. 

> ‘50.00’

> ‘01/01/2018’

<

Less than  - This is used mostly with dates and numeric values. This can be used on Alpha but results can be tricky. 

< ‘50.00’

< ‘01/01/2018’

=

Equal to - Use this if you know the specific value of what you are looking for. 

=’50.00’

= ‘01/01/18’

=’PAY’

>=    OR   <=

Greater/Less than or equal to - This is used for times you want a value greater or less then a value or equal to it. 

>= ‘50.00’

<= ‘01/01/2018’

!=

Not Equal to - This is the sign for when you are excluding a value. Saying the value cannot be equal to this. 

!=’50.00’

!= ‘01/01/18’

!=’PAY’

IN

This is the same as equal except you can give it multiple qualifiers.  All qualifiers have to be wrapped in parentheses and separated by commas

IN(‘PAY’,’NEW’,’PIF’)


NOT IN

This is the same as not equal except you can give it multiple qualifiers.  All qualifiers have to be wrapped in parentheses and separated by commas

NOT IN(‘PAY’,’NEW’,’PIF’)

LIKE

This is used for when you don’t care or know what comes after a value. Most commonly used for finding C and X codes accounts. This operator needs to have a %. The % sign means to ignore anything after it.  You are NOT able to look for multiple qualifiers when using the LIKE operator. 

LIKE ‘C%’

LIKE ‘X%’


NOT LIKE

Same as LIKE except will give you the opposite. It will exclude these results. Use this in majority of queries and reports when you want exclude C and X codes. 

NOT LIKE ‘C%’

NOT LIKE ‘X%’

BETWEEN

Use this for when you are looking for values between a range. Used mostly on numerics or dates. 

BETWEEN ‘50’ AND ‘100’

BETWEEN ‘01/01/18’ AND ‘12/31/18’

Note: All qualifiers should have single quotes around them ( ‘ ). This is not 100% needed but in till you feel comfortable being able to determine when to wrap criteria in single quotes or not make sure you wrap them in single quotes. 



Once you know what operator to use it will be added to the expression builder. In this example I have said I want it to exclude PIF and SIF accounts. 



Once you click ok it should look like this on our main screen. 




Step 5 - Add additional fields


Last step is to add checkboxes next to any fields we want to show in Advanced Inquiry or Report Designer. 


Steps 3 and 4 we selected fields we wanted to do criteria on but a lot of times you want to show certain data but not do criteria searches on them. Like First and Last names, you rarely do criteria search on those fields but it’s pretty common to have them displayed.