Documents, Spreadsheet, Output
You can export client (and other address sets), policy, claims, and diary data from Durell into an Excel spreadsheet. To create a spreadsheet from your client data (or other address set), starting from Durell’s “Main Menu”…
Select Records to Include
As described above, you use the “Select records” drop-down to specify which client or other address set records to include in the spreadsheet. You can, for example, include all the records in the current address set, or just those that currently have their mail shot marker (or “MS flag”) set to “Y” (see Mailshot, Bulk Selection of Clients). To export a different address set, first use the green “Binoculars” button (e.g. to move to “Prospects, Individual” or “Suppliers”), then return to the “Link to Excel” in the “Document Viewer”.
Including Policy and Diary Records
To include policy, claims or diary data simply use the “Select records” dropdown, as shown below. Note that the “Current set, mailshot = Y”, as shown above, refers to address sets, not policies or claims, etc. Note too that you can use the mail shot facilities to select policies by multiple criteria (e.g. all Axa policies expiring before 12/12/2004).
Include Client Policy Details
The above options apply to ALL selected records, or at least all with their mailshot markers set. If you want just the current client's policies use the tick-box "Include client policy details", which is a very powerful tool described in the section Documents, Listing Client's Policies.
Select Which Data Fields to Display
The data fields that will be displayed in the spreadsheet will be those included in the data set as selected by the “Data field set” drop-down. The set-up of data sets is accessed via the “Setup” button on the “Link To Microsoft Excel” dialogue, as per the example below (see Documents, Data Sets). The data set shown below resulted in an Excel spreadsheet illustrated directly beneath it. The order of the spreadsheet fields is dependent on the sequence of the selected data fields, so in most cases you should clear the tick box above the “Selected” fields list, then use the “Move” buttons to sequence them, as required.
Two Useful Tips re Data Field Names
1) Having selected a table, if you're looking for a field like "AdviserName", as illustrated below, simply keep clicking its first letter (which in this example would be letter "A") and each time the highlighted bar will jump to the next field starting with that letter.
2) If you're not sure which fields hold the data you wish to include, simply create a dummy policy entering different values in each field (e.g. value "1" in the first, value "2" in the next, etc). Save the policy then create a spreadsheet for just the "Current record" (i.e. that policy). Create and use a data set that includes ALL of the data fields for the selected table (e.g. Policy details 1, as shown below) by using the ">|" button to select them all with one click. Then export this policy's data to Excel where you'll be able to see each of your data values under its field name (which will be in the first row).
Selecting Policy Data
The two examples, below, show fields being selected from the “Policy Details 1” table, and then how they appear in the resulting Excel spreadsheet.
Bug with Date Fields
There is a bug in Microsoft’s Office 2000 regarding the layout of date fields. This bug does not affect later versions, such as Office XP or 2003. To fix Office 2000 on your computer starting from your Windows Desktop with no running programs…
Then in the Registry Editor, as shown below…
Re-importing Address Set Data
If you intend to re-import your address set data after modifying it in your Excel spreadsheet (e.g. for the bulk updating of a group scheme) you must ensure you include the “Record” field from the “Client Details” table, in which case the records will be re-allocated to their original client number (see Set-up, Import). Failure to include this field will result in all the re-imported records being re-created anew (i.e. as duplicates).