As promised and only a little bit late, here are the fields in QueryPlus 360 for Customer Activities:

QueryPlus users have a problem when trying to create a report grouped by Zip Code: the AMS/AFW databases store the entire 9 characters of the Zip Code in one field. This causes reports grouped by Zip Code to have a row for every Zip-9 entry, making for a very long and not very useful listing.

To help with this issue, we have added fields to QueryPlus for the Customer Zip:

And for Location Zip:

The new fields will return the first 3, 4 or 5 characters in the Zip Code. Zip 3 and Zip 4 were added to give QueryPlus users the option to group data by larger contiguous areas.  

There are other Zip Code fields in the databases, and we will be adding Zip 5, 4 & 3 fields for them as time permits. Please don’t hesitate to request Zip 5, 4 & 3 for any Zip Code field you are working on.

A QueryPlus user noticed that some Activities were not being included in a query, but should have been. Investigation revealed that the fields which start with ‘Activity….’ would be more accurately called ‘Policy Activity…’ since they only include Activities which are associated with a Policy.

To remedy this situation for QueryPlus(AFW), we have added a set of fields – shown below – which are called ‘Customer Activity…” Since the Customer ID field in the Activities table is required, these fields will show ALL Activities, whether they are associated with a Policy or not.  

Here are the new fields. Note that there is only a single name field for Employee, Account Exec and Account Rep.

As is always the case with Activity queries, be SURE to use a filter, as many agencies have millions of records in this table!  

AMS360 users have a similar set of fields, which I will post tomorrow.

A QueryPlus user (surprise!) had this request: ‘Show me all current customers assigned to a specific department’.

The fields for this query:

Filters:  

Customer Open Policy Count - is not null 

Customer General Ledger Department Name – as required

Of course, this query can be modified in many ways.

As part of our process of creating the mapping documents, we found that the Suspense table had not been included in QueryPlus for AFW. That oversight has been corrected, and QueryPlus users can now run queries/reports on Suspense records associated with Policies in AFW. The fields that have been added are:

Note that these fields require both a Customer and a Policy, so queries on the Suspense fields can be done either by Customer or Policy.

A QueryPlus user wanted to run a query based on this request:

"Give me all customers who have more than one Rep in the Account Service Groups".  

The first 2 entries in the Service Groups box on the main Customer screen (the Account Exec and Rep) are accessed through the QueryPlus fields ‘Customer Account Exec…..’ and ‘Customer Account Rep…..’, and are required by the AMS360 interface.

All of the other personnel for a Customer are added in the 360 interface through the Customer – Edit Customer – Name & Address – Service Groups – New screen, and their QueryPlus fields are:

1. Customer Additional Personnel Type of Employment

2. Customer Additional Personnel Last Name, First Name

3. Customer Additional Personnel Type of Business

4. Customer Additional Personnel Primary Indicator  

The Type of Business field can contain the following:

0 – All

1 – Personal Lines

2 – Commercial Lines

3 – Non Property & Casualty

5 – Life

6 – Health

7 – Financial Services

The Type of Employment field can be:  

P – Exec

R – Rep

T – Sales Center Rep

B – Broker

The Primary Indicator will be either Y or N based on the checkbox on the interface screen.

So to create the query in the original request above, pick these fields: (QueryPlus version 3.2 required)

Place a filter on the Type of Employment field:

To narrow the results to only Customers with an additional Rep, add a filter to the Last Name field:

Run this query and the dataset will contain only those Customers with at least 1 Rep in the Customer Additional Personnel table, and since there is always 1 Rep in the Customer table, these will be Customers with more than 1 Rep assigned to them.

Some variations:

• To see which Service Group the additional Reps are assigned to, add the Type of Business field to the query.  

• To only see Reps assigned to a particular Service Group, filter the Type of Business field accordingly.

• To only see active Customers, add the Customer Is Active field and filter it for ‘equals Y’.