Versions Compared

Version Old Version 23 New Version Current
Changes made by

Fiona Hardy

Fiona Hardy

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

When information is shown in a grid, it can be exported to Microsoft Excel

...

to a standard or custom spreadsheet - this guide covers: (click a link to skip to that section)

Table of Contents
minLevel3
maxLevel3
outlinefalse
styledisc
typelist
printabletrue

What is a grid?

  • A grid is any list of information shown where the column headings can be grouped/sorted filtered - e.g. report results, viewing full results from an Organiser panel

...

  • Grids are found throughout

...

  • the system when a list of information is shown

  • An example grid is shown below

    Image Modified

Export grid contents to a standard or custom spreadsheet

Grid contents can be exported using standard or custom export methods:

  • Standard: allows you to export the information shown in the current grid

  • Custom: allows you to choose what information is exported, this might include all/some information shown in the grid, along with other related data currently not shown

If the information you require is already shown in a grid, then choosing to carry out a standard export from that grid is recommended - see steps 1 & 2 below

If not, then it is recommended to view a grid as close as possible to the information required, then carry out a custom export and add/edit the required columns - see steps 1 & 3+

Tip

A custom export is reliant on where the information is stored within database tables

...

For example, if you want to see properties that have had viewings in the last three months along with the applicants that viewed, this cannot be gained from a property report, as the related diary and applicant information is not held here - however, running a diary report would allow this information to be retrieved, as the diary table is where the required information is held

Export to Excel options

1.

Accessing

Access the export to Excel options

From the required grid:

  • Right-click over the column headings and select Export to

  • Two Excel options are offered:

    • Excel Spreadsheet (.xls) - Standard - see step 2

    • Create Custom Excel Template - see step 3 onwards

Image Removed
    • Image Added

2. Standard export

  • Selecting to do a Standard export opens Excel and pulls the content of the grid (shown in step 1) into a worksheet

    Image Added

  • If the grid was grouped, the export to Excel will include a pivot table worksheet too

    Image Added
Tip

The information can now be sorted/filtered/saved etc. within Microsoft Excel

Image RemovedImage Removed

3. Custom export

When selecting to do a Custom export, choose which template type you need (if custom templates have already been created, the screen shown in step 7 will be displayed)

  • Select Create template from standard


Choosing

  • - choosing this option ensures the core columns are included in your new template (rather than starting from scratch)

    Image Added

  • Click Accept


The

  • - the Excel Export Designer screen is displayed

Image RemovedImage Removed
  • Image Added

4. Add/edit data included in export

As the Create template from standard option was chosen in the previous step, standard columns for the report type are automatically shown the Excel Export Designer

Use this screen to edit / remove / change column order shown, as well as add new columns

Image Removed

  • Template Name


Enter

  • - enter an appropriate name for the template
    Once created, the template will be available for all grids of this type - see step 6

  • Share With


Leave

  • - leave set to Everyone

- or

    • Or click this link for options to share with particular Offices or Negotiators 


Or

    • - alternatively, to keep the template for individual use, select Do Not Share

  • Change column header

Double

  • - double-click the heading


The

  • (the screen shown in step 5 will be displayed)

    • In the Header field, enter the new heading for this column


    • - e.g. you may want Manager Name to display as Manager

  • Change column order
    Select the required column heading and click the Move Up/Down buttons

  • Remove columns
    Select column heading and click Delete

  • Add new columns to export


See

  • - see next step

    Image Added

5. Adding a new export column

Merge codes are needed to pull the required information from the database

From the Excel Export Designer (shown in step 6):

  • Click Add 


Complete

  • and complete the Excel Export Column Edit screen


Image Removed

    • Header
      Enter the title for the exported column (i.e. how you want it to be labelled in Excel)

    • Merge Code
      Use the Letter Template Editor to find required codes


See links on this page

    • - for more information on the Letter Template

Editor and useful merge codesinfo

    • Editor and useful merge codes, click here: Templates

    • Type
      Select the format for the column, options are None, Decimal, Price or Percent
      Image Added

Tip

See Custom Excel export examples section below for ideas on exports that can be done including the related merge codes required

Type
Select the format for the column, options are None, Decimal, Price or Percent

6.

Accessing

Access custom Excel templates

Once a custom Excel template has been created it will be available on all grids of the same type


- e.g. an applicant custom template will be available on all applicant grids

  • Right-click over the column headings and select Export to
    All custom Excel templates created for this grid type are shown

Image Removed
  • Image Added

7. Edit/delete custom Excel templates

To edit or delete any custom Excel templates created:

  • Right-click over the column headings and select Export to


  • (as shown in step 6)

  • Select Create/Edit Custom Excel Template
    Options

(shown right)

  • to Edit or Delete existing templates are offered

    Image Modified

Custom Excel export examples

Each section below outlines column headings and their associated merge codes which can be added to the Excel Export Designer to create a custom Excel template

...

Expand
titleStandard certificate expiry details for each property

Column heading

Merge code

Property Reference

Property.Code

Office

Property.Office.Name

Manager

Property.Negotiator.Name

Rent p/m

Property.Lettings.MRentFormatted

Short Address

Property.ActiveAddress.GridAddress

Landlord

Property.CurrentCntName

Status

Property.Lettings.StatusNameGrid

Role

Property.Lettings.RoleName

Total Bedroom(s)

Property.Attributes.TotalNum

Registered

Property.Register

Available From

Property.AvFrom

EPC Rating

Property.ExtrasField.GetExtra("pBERExempt", false) ? "Exempt" : Property.ExtrasField.GetExtra("pBERRating", "To be confirmed")

Last Call

Property.LastCall

Appliance Insurance Expiry

Property.Lettings.GetLastCert("AI").EndDate.ToShortDateString()

Buildings Insurance Expiry

Property.Lettings.GetLastCert("BI").EndDate.ToShortDateString()

Contents Insurance Expiry

Property.Lettings.GetLastCert("CI").EndDate.ToShortDateString()

Electrical Safety Expiry

Property.Lettings.GetLastCert("ES").EndDate.ToShortDateString()

Emergency Plumbing Expiry

Property.Lettings.GetLastCert("EP").EndDate.ToShortDateString()

Gas Safety Expiry

Property.Lettings.GetLastCert("GS").EndDate.ToShortDateString()

Labour Only Warranty Expiry

Property.Lettings.GetLastCert("LW").EndDate.ToShortDateString()

Legionella Expiry

Property.Lettings.GetLastCert("LG").EndDate.ToShortDateString()

Manufacturers Warranty Expiry

Property.Lettings.GetLastCert("MW").EndDate.ToShortDateString()

Minor Works Certificate Expiry

Property.Lettings.GetLastCert("MK").EndDate.ToShortDateString()

PAT Test Expiry

Property.Lettings.GetLastCert("PT").EndDate.ToShortDateString()

Smoke Alarms Expiry

Property.Lettings.GetLastCert("SD").EndDate.ToShortDateString()

Soft Furnishings Safety Expiry

Property.Lettings.GetLastCert("FS").EndDate.ToShortDateString()

System + Appliance Insurance Expiry

Property.Lettings.GetLastCert("SA").EndDate.ToShortDateString()

System Insurance Expiry

Property.Lettings.GetLastCert("SI").EndDate.ToShortDateString()

Feefo exports

Currently there currently isn't an automated process for providing Feefo data - where a Feefo export is required, the following reports should be set-up 

...

Expand
titleTenant

Tenant details

Name

Populated by

Merge code

Name

Main Tenant Name

Tenancy.MainTenant.Name

Email

Main Tenant Email

Tenancy.MainTenant.Email

Date

Tenancy From Date

Tenancy.FrDate.ToShortDateString()

Description

N/A

<>

Merchant Identifier

N/A

<>

Tags

N/A

<>

Feedback Date

N/A

<>

Product Search Code

N/A

<>

Order Ref

N/A

<>

Product Link

N/A

<>

Customer Ref

Tenancy Code

Tenancy.Code

Amount

N/A

<>

Currency

N/A

<>

Product Attributes

N/A

<>

Locale

N/A

<>

Joint tenants

Name

Merge code

Full property address

Tenancy.Property.Address.FullAddress

Status

Tenancy.StatusName

Main Tenant Name

Tenancy.MainTenant.Name

Main Tenant Address

Tenancy.MainTenant.Address.FullAddress

Main Tenant Email

Tenancy.MainTenant.Email

Main Tenant Home Number

Tenancy.MainTenant.GetPhone("h")

Joint Tenant 1 Name

Tenancy.GetJointTenant(0).Name

Joint Tenant 1 Address

Tenancy.GetJointTenant(0).Address.FullAddress

Joint Tenant 1 Email

Tenancy.GetJointTenant(0).Email

Joint Tenant 1 Home Number

Tenancy.GetJointTenant(0).GetPhone("h")

Joint Tenant 2 Name

Tenancy.GetJointTenant(1).Name

Joint Tenant 2 Address

Tenancy.GetJointTenant(1).Address.FullAddress

Joint Tenant 2 Email

Tenancy.GetJointTenant(1).Email

Joint Tenant 2 Home Number

Tenancy.GetJointTenant(1).GetPhone("h")

Joint Tenant 3 Name

Tenancy.GetJointTenant(2).Name

Joint Tenant 3 Address

Tenancy.GetJointTenant(2).Address.FullAddress

Joint Tenant 3 Email

Tenancy.GetJointTenant(2).Email

Joint Tenant 3 Home Number

Tenancy.GetJointTenant(2).GetPhone("h")

Joint Tenant 4 Name

Tenancy.GetJointTenant(3).Name

Joint Tenant 4 Address

Tenancy.GetJointTenant(3).Address.FullAddress

Joint Tenant 4 Email

Tenancy.GetJointTenant(3).Email

Joint Tenant 4 Home Number

Tenancy.GetJointTenant(3).GetPhone("h")

Filter by label (Content by label)
showLabelsfalse
max5
spacescom.atlassian.confluence.content.render.xhtml.model.resource.identifiers.SpaceResourceIdentifier@a45
showSpacefalse
sortmodified
showSpacetypefalsepage
reversetrue
typelabelspagereporting
cqllabel = "templates" and type = "page" and space = "RW"labelsreporting
Excerpt
hiddentrue

When information is shown in a grid, it can be exported to Microsoft Excel

...