A QueryPlus user was interested in compiling a list of Customers added during a specified time period.

In AMS360, There are 2 dates available:

            Customer Entered Date –       the date the Customer record was created

            Customer Added Date –        the most recent date the Customer Type (Suspect, Prospect, Customer)  was changed.

In AFW, there aren’t any dedicated date fields, but since the Customer ID field itself is a date, it is available:

            Customer ID -             the date the Customer record was created

When combined with other Customer status fields, like:

•             Customer is Active
•             Customer Type
•             Customer Accounts Receivable Closed Date (indicates that all Invoices have zero balance)
•             Customer Open Policy Count
•             Customer Latest Policy Expiration Date

There are a whole lot of queries and reports available for basic Customer status.

As always, please feel free to ask for assistance, on this or any other query/report you are looking for. We’re here to help!

NOTE: The Technical Bulletin in this entry has been revised, and is published as Rev A. 

The results of the recent investigation into the Employee and Security Group structure in AMS360 have been published in Technical Bulletin 13, which is available in the QueryPlus Knowledge Base, and is reproduced below. All of the queries and reports mentioned in the Technical Bulletin are also available in the Knowledge Base.

 QueryPlus Technical Bulletin         

Number:           13                                                                   
Subject:            Security and Authorizations in AMS360 - Rev A
Applicability:   360QP
Revision:         3.2 and above
QP Database:   8/1/2008 or later  

This bulletin describes the structure of the Security Group Authorizations in the AMS360 database, and includes several queries for extracting information from the database.

The hierarchy of the data is as shown below: (refer to the AMS360 interface ‘Security Group Setup’ screen)

AMS360 SCREEN FIELD NAME               AMS360 DATABASE FIELD NAME

            Employee                                                        Employee

                        Security Group                                    Authorization Group

                                    Task                                        Authorization Item

                                                Feature                        Authorization Item Group

                                                Group                          Authorization Item Sub-Group

Note: Please be aware that the QueryPlus field names are based on the AMS360 Database field names.

The most important concept to understand is that permissions (the Access drop-box on the Secured Item List screen) are granted at the SECURITY GROUP/TASK level, and that Employees obtain permissions by virtue of their membership in a particular Security Group – there are no permissions granted directly to Employees.

In the Secured Item List, the fields called Feature and Group are attributes of the Task field. Only the Task field is ‘connected’ to the Security Group that the Task belongs to.

All of the fields required for Security Group Authorization queries in QueryPlus are in the Basic Agency Information module. On the Step 1 – General screen, click Basic Agency Information:

Click ‘Fields’ and then choose:

System:           Stand-Alone Tables

SubSystem:     Standard

Component:    Employee

To run a basic query, choose these fields:

Note: the ‘Item Item’ in the last field is not an error. This field name should be read as:

 (Authorization Group)    (Authorization Item)    (Item Permission Text)

In several AMS360 databases, this query returns one completely blank record along with the desired data. This appears to be a quirk of the AMS360 database, so add a filter:

            Authorization Group Description Short --- Is Not Null

This query will typically return thousands of records, so several filtering options are:

            Filter for one Authorization Group Description Short

            Filter for ‘not equals No Access’ (as a QueryPlus user said, ‘I need to see who DOES have access to what – the people who DON’T have access will let me know if they need it!’)

The dataset for this query can be sent to the QueryPlus Report Designer to be turned into a variety of reports. See the QueryPlus Knowledge Base for several templates, including cross-tab reports.

The query detailed above will produce a dataset/report which shows all of the permissions for all of the combinations of Authorization Group/Item/Item Group/Item Sub-Group. In order to show permissions for Employees, a separate query using a different modeling of the fields is required. Use these fields:

Note that ‘Employee’ has been added at the beginning of the field names. It is critical to use these fields rather than the ones shown in the example further up the page, because these fields have been linked to the Employee’s record.

Again, please be aware that this query, if un-filtered, will return a LOT of records. That isn’t necessarily bad, but the query can take a long time to run, and it’s often difficult to deal with a bewildering mass of data. Filter appropriately!

Another simple query idea is to show just the Employee and the Security Groups he/she is a member of:

I did say it was simple! Add 2 filters:

            Employee Full Name is not null

            Employee Authorization Group Description Short is not null

This query can be turned into a report grouped by Group, showing which Employees are members, or even a cross-tab report with Employees as rows and Groups as columns. See the QueryPlus Knowledge Base for templates.

There are other fields in the Authorization tables that are useful in queries, so feel free to experiment.

As always, for any questions, comments, suggestions, report help or anything else, PLEASE contact your QueryPlus Technical Support team, because we are here to help.

A QueryPlus user was creating a greeting card list and found that Premiums from multi-year Policies weren’t being included. Investigation showed that there were 3-year policies which had all of the Premium in transactions right at the beginning of the term, whereas the payments were being made quarterly.

Since all of the Premium was outside the 1-year window for the list the user needed, the list couldn’t use the Premium transactions. In order to get the data from the Invoice side, a new field was required:

            Customer Total Invoice Amount for Past Year

The other requirement for the list was that it would only include Customers with Active Policies, so the query also includes this existing field:

            Customer Open Policy Count (filter for ‘greater than 0’)

Use the ‘Top XX Records’ feature of QueryPlus to return only the number of Customer names desired.

Add any Customer-level address or contact information, and you have a mailing list.

Sessions 2 & 3 of the informational training webinars are now available in the QueryPlus Knowledge Base. Here are the files:

            Filtering and Mailing Lists - 10-21-08

            Comparing Policies and Data Scrubbing - 11-5-08

The most recent one includes all of the QueryPlus and Report Designer files for the topics covered in the session. We will continue this practice with future sessions.

While working with a QueryPlus user on a mailing label list, we noticed some shortcomings in the Customer Name fields. This led to some modifications and new fields:

In AFW…

Customer Name (Firm or Full Name) now includes the middle name if it exists: Public, John Q

Customer Name (Firm or Full Name) for Mailing reverses the order: John Q Public

In AMS 360, things are a little more complex:

Customer Name (Firm or Full Name) is the field provided by the database, and is left unchanged. This field returns Last Name First Name Middle Name with no punctuation or special characters.

Customer Name w/Sp Char returns Last Name, First Name with punctuation and special characters.

Customer Name w/Sp Char and Middle Name returns Last Name, First Name Middle Name with punctuation and special characters.

Customer Name for Mailing w/Sp Char and Middle Name returns First Name Middle Name Last Name with punctuation and special characters.

These fields cover all of the ways to show Customer Name that we have thought of. Of course, if you need Customer Name in a different format, just let us know.