Profile questions and answers are a great way to save information about AMS360/AfW Customers, but running queries and getting good data is a little bit complex.

Use these fields along with Customer Name to create a list of Customers who answered a specific Profile question, or answered it a certain way. For instance, if the question is 'Smoker?' and the desired answer is Yes:

  Profile Answer Description  Filter 'equals Smoker?'

  Profile Answer Description  Filter 'equals Y' (Yes is mostly stored as Y)

That takes care of specific questions and answers, but what about this request we recently got: I want to see a list of all of my Customers who have NOT answered the Profile question 'Decision Maker'. That gets a little complicated, but QueryPlus has a solution.

One complication is that until the question is asked and answered, there is no record of it in the AMS database. Adding to the complexity is the factor that all Profile questions and answers are Agency-specific, so the QueryPlus support team cannot just 'create a field for every possible Profile question and answer combination'.

In the case of a query like the one described 2 paragraphs above, just contact the QueryPlus support team with the exact text of the Profile question, and we will create the fields you need, almost always in a couple of hours. Let's see how that works for the example above.

We created 2 fields:

   Profile Question Decision Maker Text

   Profile Question Decision Maker Count

Filtering the Count field for 'is Null' in a query with Customer Name will give a list of all Customers who have NOT been asked the Decision Maker question. Here are some ways this query can be enhanced.

Add these fields for filtering:

                Customer Type (P = Personal Lines, C = Commercial Lines)                

                Customer is Active (Y = Yes, N = No) [I urge caution on this field, it does not appear well-regulated by the AfW interface]               

                Customer Open Policy Count (‘is Null’ for no open/active Policies, ‘greater than zero’ for Customers with 1 or more 
                 active Policies)                

                Customer Status (S = Suspect, P = Prospect, C = Customer, B = Broker) [again, not well-regulated by the AfW interface]

Those are the most common Customer-level fields to filter on, but there are many more. If you want to filter your list and can’t figure out how to get what you are looking for, just let us know. 

You can also add fields for information, if you are sending the list to CSRs to have them call the Customer:                

                 Customer Resident Area Code/Phone 

                Customer Account Rep Last Name/First Name

As with all QueryPlus queries, the possibilities are endless, so this isn't intended to be the complete solution, merely a template to start with.

Images and graphics can really spice up a report, and the QueryPlus Report Designer allows a wide variety of graphics and images to be imported. Here's how to do it:

1. Click and hold on the Image button in the Toolbox, then drag the box to the desired location on your report:(be sure it is within the borders of a band)

2. Double-click in the box just created to open the Image dialog box. Images can be imported from many sources, including the Internet (by using the 'Image URL' tab), but for this example we'll stay simple and use 'File'. Click the ... box at the top right, then navigate to where the image to be imported is stored, then click  Open, then OK.

3. Now you should see the image in the box.

 4. The image may be way too large for the box it's in (it may even look like the box is still empty). Open the Properties window (View - Properties). There are lots of properties to play work with (go for it!). For this example, we'll just set the 'Stretch' property to True. That causes the image to 'auto-size' for the box it is in.

5. That's all there is. Save the report, click Preview, and the image should be visible on the report, as in the example below. From here, adjust the size, position, border, etc. to suit the report it is in.

We have added 5 new queries/reports to the Knowledge Base:

There are also 4 new Technical Bulletins:

Duplicate data rows in the result set can often be excluded by checking the 'Return Distinct Records' box on the Fields screen in QueryPlus, but this technique must not be treated as a 'magic bullet'. Here is an example of how legitimate data can be excluded by mistake.

 In a query looking for Invoice Transaction Premium, with the Return Distinct Records box checked, we notice this row:

If we un-check Return Distinct Records, we get this:

So there are actually THREE Invoice Transactions on the same Policy, on the same day.

Adding Invoice Number to our query, we see that there were actually 3 different INVOICES for the same Policy on the same day, hence the 3 transactions.

In this case, adding Invoice Number to our query, even when Return Distinct Records is checked, makes each of these 3 rows 'distinct', or unique (because they each have a different Invoice Number, the field on the right), so they all appear on the output.

In a very rare case, it is possible for a single Invoice to have multiple Transactions with the same Premium - this apparently can happen when Invoice Commissions are being adjusted within an Invoice, rather than by cutting a new Invoice. For these cases, there is a field available to differentiate between otherwise identical Invoice Transactions in a single Invoice: Invoice Transaction ID.

 Since 'Return Distinct Records' is often necessary in a query for other reasons

Don't be put off by the seeming complexity of the number (this is the AMS360 version, AfW just gives you a date/time), it's just what Microsoft calls a GUID - Guaranteed Unique ID. It is only in the query to make each Invoice Transaction unique.

 This problem is not limited to Invoices and Invoice Transactions. It can occur in any query that uses Return Distinct Records, if the data in two or more rows looks identical to QueryPlus.

The best defense against this type of problem is to carefully examine your output against Policy records in the AMS interface. If discrepancies appear, investigate them with an eye on duplicated (or missing) records.

A QueryPlus user needed information about current Submissions. Here is the basic query:  

Filters:                Policy Sub Type Flag = S               
                           Policy Submission Policy Date = desired date range 

For a Submission without a Carrier, the Policy Submission Company Name will be blank. 

Add Policy Account Rep (or Exec) Name to identify the individual responsible for the Submission. 

Add other Policy-level fields as necessary for more information about the Submission – e.g. Customer Address, Phone. 

There does not appear to be any reliable direct link between Submissions and the actual Policy that may result. The field Policy Source Policy Policy Number of an actual Policy will normally show the Policy Number of the Submission it was created from, but only if the actual Policy was directly created from the Submission. The Daily Querier’s test databases show numerous Policies that appear to originate from a Submission, but do not contain the ‘Policy Source Policy Policy Number’ link, so be wary of this connection.

A QueryPlus user reported getting duplicate rows in a query using this set of fields:

Which resulted in an output that looked like this: (grouped by Policy Number)

Normally, duplicated rows in QueryPlus – or any SQL query, for that matter – are the result of querying data on multiple levels.

For instance, an Invoice can have multiple Invoice Transactions, so a query with Invoice fields that also has Invoice Transaction fields in it will cause the Invoice fields to be repeated for each Invoice Transaction field.  

In this case, it is the field Policy Transaction Type that is causing those duplicates, and the reason is that while it is logical and reasonable to parse that field as 

       Policy – Transaction Type

 it is actually
 
      Policy Transaction – Type.

So query is returning multiples for any Policy that has multiple Policy Transactions. (I know that field name is confusing, and I apologize for that, but unfortunately we’re stuck with it.) 

The good news is that there is a Policy-level field that will give the same information (Policy – Transaction Type), but without the duplicates: 

       Policy Basic Transaction Type 

This field looks at all the Types of all the Transactions for a Policy, and returns either NBS or RWL, depending on which of these it finds (in some rare cases, there is neither of those Types, so the field is blank). 

So by replacing the Policy Transaction Type field with Policy Basic Transaction Type, your query will lose those annoying duplicates.

Most QueryPlus users are familiar with the 'big five' fields necessary in any query that returns only active Policies. Now there is an easier way to do the same filter. Just use this field (in both AMS360 and AfW):

     Policy is Active

This field has all 5 of the necessary filters built into it, and automatically uses the current date as well.