A QueryPlus user had a request: “Show me all my Customers who do NOT have an XRef entry for ‘Personal Lines’. This is one of my favorite types of query, because it effectively says ‘show me what doesn’t exist’, which can be challenging. Because the Customer XRef entries are agency-created, the field necessary did not exist in QueryPlus, but was easily created, and is now available for all QueryPlus users. The query in its simplest form contains these fields: 

 Add a filter to the Customer Count of XRef Personal Lines Records field:  

Note that because the query is actually counting something that doesn’t exist, the filter must use the criteria ‘Is Null’ rather than ‘equals 0’. Run the query, and it will return a list of all Customers in the database who do not have a Personal Lines entry in the Customer XRef table. It will include Customers who do have other XRef entries. This query can be modified to show which Customers DO have an XRef entry of Personal Lines by changing the filter to ‘Is Not Null’. Other fields – such as ‘Customer Is Active’, Customer contact information, etc. – can be added at will, but be aware that adding any Policy fields (or others that might have multiple entries for a single Customer) will cause duplicates to appear in the dataset. Because the XRef entries are agency-created, if you need a different ‘count’ of this type, just contact QueryPlus support and we’ll do one for you, too…. For free, of course!