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 

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.