Custom Logic in Reports

By default, reports only have limited filtering abilities through the Report Filters section. The "Show" dropdown box lets you choose between all records or records with you marked as the owner, and you can limit the date range of results. Sometimes, you need more flexibility and more refinement for your search results. For that, use the Custom Logic area.

Custom Logic is a tool to further filter your results. For each report, you are allowed up to 10 custom conditions to limit your results. To create these filters, click Custom Logic underneath the Report Filters section and above the results.

Each line represents a single condition, and has three parts:

  • Data field
  • Logical operator
  • Data value

The data field is simply a list of all fields, default and custom, that the record has. In this example, we are creating custom logic for a report of Accounts, so the drop-down list for the Data field has a list of all Account fields (for more information about creating custom fields, see "Creating Custom Fields" in the User Guide)

The data value is a possible entry that a particular record will have in their chosen data field. For example, if the data field is "Type", one possible data value would be "Customer". If the data field is "Amount", then a possible data value would be "1234.56".

The logical operator tells the system how to compare each record with the contents of the data value box. It is important to remember that a record will only display in the report if it matches every line of custom logic. The logical operator field has 8 choices:

  1. equals
    • If the record's value does not exactly match the data value, then it will not be listed. If it does exactly match, then it will be listed only if all other custom logic matches as well.
    • For example, when making a report for Accounts, setting the data field to "Type", the logical operator to "equals", and the data value to "Customer" will only match Account records of the Customer type.
  2. not equal to
    • If the value of a record does not match exactly with the data value, then it will be listed in the report.
    • For example, choosing the data field "Shipping State", the logical operator "not equal to", and the data value "KS" will show in the report every record that does not have a shipping address in the state of Kansas.
  3. less than
    • If the value of a record is less than the value in the data field, then it will be listed in the report.
    • For example, if you want to select all accounts with fewer than 50 employees, you would select the "Employees" data field, the "less than" logical operator, and put "50" in the data value. This will match all account records whose "Employees" data field is less than 50. It will not match account records whose "Employees" data field is equal to 50.
  4. greater than
    • Similar to the "less than" operator, this will match records whose data value is greater than the data value specified in the logic.
    • For example, if you select the "Annual Revenue" data field, the "greater than" logical operator, and put "3600003" in the data value, then this will match all records whose annual revenue is higher than $3,600,003.00.
  5. less or equal
    • This logical operator combines the "less than" operator with the "equals" operator. This operator will match any record that is less than the data value, but it will also match any record that is equal to the data value.
    • As in the "less than" example, choosing "Employees", "less or equal", and "50" will pull all account records who have 50 or fewer employees, not just who have fewer than 50 employees.
  6. greater or equal
    • This logical operator combines the "greater than" operator with the "equals" operator. This operator will match any record that is greater than the data value, but it will also match any record that is equal to the data value.
    • As in the "greater than" example, choosing "Annual Revenue" "greater or equal", and "3600003" will match all account records whose annual revenue is $3,600,003.00 or higher, not just records that are higher. In the image below, there is a record that was not included in the "greater than" sample image due to having exactly the amount in the data value box.
  7. IN (...)
    • This is a special operator that acts as multiple "equals" logical operators. The "IN (...)" operator takes a list of possible data values and compares the record's data value to each of them. If any of them match, then the record matches. All list elements are separated by commas.
    • For example, selecting "Billing State" for the data field, "IN (...)" for the logical operator, and "VA, TX, NY, CT" for the data value, this will only match records whose billing addresses are in Virginia, Texas, New York, and Connecticut.
  8. NOT IN (...)
    • Like "IN (..."), this is a special operator that acts as multiple "not equal to" logical operators. The "NOT IN (...)" operator takes a list of possible data values and compares the record's data value to each of them. If none of them match, then the record matches.
    • For example, for reports on Accounts, selecting "Type" for the data field, "NOT IN(...)" for the logical operator, and "Customer, Vendor" for the data value,

The "less than", "greater than", "less or equal" and "greater or equal" logical operators are not only limited to number or currency values. When done correctly, they can also be used on words.

As an example, consider the task of looking for all accounts whose name begins with the letter 'C'. To do this, you can use two lines of custom logic. The first line matches all records starting with 'C'. Because a record named 'C' would still fit what we're looking for, we use the "greater or equal" operator. The second line matches all records that start with something before 'D', but not 'D' itself.

Because any record that begins with the letter 'C' would have to fit inside this range, these two lines of custom logic will match what you are looking for.