Difference between revisions of "Orgs & People Extract"

From Charitylog Manual
Jump to: navigation, search
m (General Details)
(Introduction)
 
(107 intermediate revisions by 6 users not shown)
Line 1: Line 1:
 
[[File:helpheader_small.png|right]]
 
[[File:helpheader_small.png|right]]
  
[[File:cl_DE_1.png]]
+
[[File:Orgs_%26_People_Extract_Menu.png]]
  
  
 
=Introduction=
 
=Introduction=
 +
The Orgs & People Extract function is used to create definitions for reporting or as a data source for mail merging in another application, e.g. Microsoft Excel/Word. They can be used in 2 ways:
  
Data Extraction/Mail Merge Definitions use person/org records without consideration of projects, allowing you to export all person/org records on the system.  The data extraction tool allows the following three facilities:
+
1) Directly from the Contact Management menu, for exporting people regardless of the kind of work you've done with them.
# Export Records direct to spread sheet.
 
# Send to the [[Blue Folder Lookup]] tool.
 
# Generate a mail merge with [[Standard Letters| Letters or Email]]
 
  
 +
2) Via a [https://wiki.dizions.co.uk/index.php/Blue_Folder_Lookup blue folder] in a report, for instance to extract everyone who's had a contact in a particular project.
  
[[File:cl_DE_2.png]]
+
For saved Extracts, you must set a set a Lawful basis for this to function. The June 2024 version made specifying a [https://ico.org.uk/for-organisations/uk-gdpr-guidance-and-resources/lawful-basis/a-guide-to-lawful-basis/ lawful basis] compulsory for using this feature. An audit of the basis, e.g. consent or legitimate interests, may be logged against each person whose details are extracted.
  
=Editing/Creating a definition=
+
The below video gives an overview of how they can be used.
  
To edit an existing definition click on the name on the left or to create a new one click the 'Create New Definition' button.  On the following screen you will be able to configure:
+
{{#ev:youtube|VskxjTHNY4Y|790}}
  
==General Details==
+
=Running an Extract=
  
[[File:DataExt_General.PNG]]
+
To run an existing extract, ensure the Lawful basis has been selected, then choose from either ''Send to Blue Folder'' or ''Extract to Spreadsheet''. You can always click on an extract to check or edit the options.  
  
* '''Definition Name''' - The name of the definition.
+
[[File:neworgextract.png]]
* '''Description''' - Used to explain what the definition is set up to do, this displays on the first page.  Example 'Exports only Clients in North Postal District'.
 
* '''Include this extract in mail-merge audit?''' - When set to yes this will be in the Audit and will show in the letters and communications section of the person/org record, if its either a mail merge or an export to spreadsheet.
 
* '''Export function of this data extraction''':
 
** '''Export to Spreadsheet''' - Exports the results directly to a spreadsheet with the fields specified by the [[Filed Set]].
 
** '''Send to [[Blue Folder Lookup]]''' - Send the person/org record id's to the [[Blue Folder Lookup]].  <span style="color:#d33">It is important that a field set was not previously specified as this will produce an error.  To check/change this then set to Export to Spreadsheet and set the 'Field Set' drop down to 'Select a Field Set', then change the Export Function back to 'Send to Blue Folder Lookup'</span>
 
* '''Field Set''' -
 
* '''Export name to use in "save as"''' -
 
* '''Suppress people/organisations based on contact consent rules for letters''' -
 
* '''Suppress people/organisations based on contact consent rules for emails''' -
 
* '''Suppress anonymous and anonymised people/organisation records''' -
 
* '''Send only one letter/email to both "related and partnered" people, when both are in mail merge (and marked as such)''' -
 
* '''Export active people/organisation records''' -
 
  
=Running an existing definition=
+
=Creating a Orgs & People Extract=
  
 +
To edit an existing definition, click on the name on the left or to create a new one click the 'Create New Orgs & People Extract' button.  On the following screen you will be able to configure:
  
=="Who To Extract?" tab==
+
==Branch Reporting Section (requires [[Modules| Branch Module]])==
  
By default, Data Extractions include Clients and ignore other organisation/person Types. If you want to look at other organisation/person Types, click on the "Who To Extract?" tab.
+
In this section you can specify to report on records in;
 +
*All Branches
 +
*Selected Branches
 +
*Selected Branch Groups (views)
  
If you choose to Exclude a certain Type, this will override the Included ones. For example, if you Include Clients and Exclude Volunteers, Clients will be Included, but anyone who is both a Client and a Volunteer will be Excluded.
+
==Report Type Filters==
  
 +
[[File:organdpeopleextract.png]]
  
[[File:cl_DE_10.png|border]]
+
* '''Definition Name''' - If you are saving the extract it would make sense to give it a specific name so you can easily distinguish it from the others.
 +
* '''Description''' -optional, but allows you to add a detailed description of the filters etc.
 +
* '''Export Function''' - You can either send to [https://wiki.dizions.co.uk/index.php/Blue_Badge_Holder Blue Folder Look up] for additional options, or extract direct to Spreadsheet
 +
* '''Spreadsheet Name''' - if you are exporting to spreadsheet, you can preset the name
 +
* '''Field Set''' - This allows you to choose which [https://wiki.dizions.co.uk/index.php/Field_Sets fields] will be included in the extract. Note you must set up the field set first.
 +
* '''Exclude anonymous and anonymised people/organisation records''' - will be set to Yes as a default. You could choose to include anonymous records for the extract.
 +
* '''Export active people/organisation records''' - Will default to Yes, but you could decide to export inactive records also.
 +
* '''Include this extract in mail-merge merge audit?''' - will default to Yes. but you could opt to not include in the mail merge audit. Especially if this extract is for reporting purposes and not mail merge.
 +
* '''Apply contact preferences for emails?''' -
 +
* '''Apply contact preferences for letters?''' -
 +
* '''Lawful Basis''' - You will be required to specify the basis by which this extract is being run. You will need to choose from one of the following: Consent, Contract, Legal Obligation, Legitimate Interests, Public Task, Vital Interests. Lawful basis still needs to be applied as the extract may be used for mail merge and/or other communication systems.
  
 +
==Consent Based Extracts==
  
If you select '''Not Relevant''', this simply means that that Type will not affect the output; organisations/people with only that Type will not be shown, but organisations/people with that Type as well as another one will not be excluded either.
+
If you choose ''Consent'' as the lawful basis, you will see the below option. You will then be required to specify which consent rule should apply. This means it will only extract records where there is a valid response to that consent rule. It will also ignore records where that [https://wiki.dizions.co.uk/index.php/Consent_Rules consent] has expired.
  
 +
[[File:consentbasedextract.png]]
  
 +
==Who to Include/Exclude==
  
==Selection Criteria tab==
+
[[File:Mail_Merge_Orgs_Types.png|1050px]]
  
On the "Selection Criteria" tab, there are various settings that relate to the way this data extraction will behave.
+
In this section you will find the list of People and Organisation types you are using on your system.  The names displayed are the ones specified in [[Customise Orgs & People]]. For each record type you can choose one of the following options:
  
* '''Definition name''' - the name of the data extraction, which will be displayed on various screens.
+
*'''Who to Include''' - This will include this record type when running the definition.
* '''Description''' - displayed on the main list of data extractions.
+
*'''Who to Exclude''' - This is to exclude a record type when running the definition. If a person/organisation is set to more than one record type, the exclude will not use this record for the include.
* '''Name for "Save As"''' - this will be used if the data extraction is used to create a spreadsheet.
 
* '''Export to a Spreadsheet or Create Mail-merged Letters/Emails?''' - this is where you choose the destination for this extraction. The choices are:
 
** ''Export to Spreadsheet'' - produces an Excel spreadsheet which you can save to your computer for further use
 
** ''Send to Blue Folder Lookup'' - this will take the data from the data extraction straight to Charitylog's Blue Folder Lookup page, where you can manipulate the data and also add other data - for example, you could use this method to add information from another Data Extraction to the output of this one.
 
** ''Batch Send SMS Messages'' - creates a batch of SMS text messages to the people identified by the data extraction. You will need to have [[Textanywhere]] set up to use this feature.
 
** ''Merge With'' - [[Standard Letter Templates]] will be named here. You can create batches of letters or emails (depending on the way the template in question is set up).
 
<div class="toccolours mw-collapsible mw-collapsed" style="width:1024px">
 
* '''Send letters or emails (based on 'preferred method of communication')?''' - click "Expand" to show this section.
 
<div class="mw-collapsible-content">
 
This option only applies to quite a specific scenario. For the option to be applicable, you need to be using the "Preferred Method of Communication" field on the [[Details Screen]] to keep a record of peoples' communication preferences, and you need to be merging letters/emails using a [[Standard Letter Templates|Template]] that is set to produce ''Letters or Emails'' based on that preference. The options are:
 
** ''Create letters for all (ignore preferences)''
 
** ''Create emails for all (ignore preferences)''
 
** ''Create letters (only for non-email recipients)'' - i.e. anyone who has an non-email method named as their Preferred Method of Communication will not be included in the merge
 
** ''Create emails (only for non-letter recipients)'' - the opposite; anyone who has an email method named as their Preferred Method of Communication will not be included in the merge
 
** ''Create letters and emails (use preferences)''
 
  
For each [[Communication Methods|Method]] there is a field which can be set - "Is this an email method?". This field governs whether someone using that method as their "Preferred Method Of Communication" should be included in the merges or not. The option of "create emails (only for non-letter recipients)" should really be called '''"Create emails (only for email recipients"'''.
+
Example
</div></div>
+
You wish to send a mail out to staff and a second mail out to volunteers. You do not want to send anyone two mail outs. The staff mail out is only set to include staff, the second mail out would be set to include volunteers and exclude staff. Anyone that is both a staff member and volunteer would only be included in the staff mail out.
* '''Suppress Letters When Client Requests It?''' - this relates to a setting in [[Contact and Consent Rules]]. If set to "No", mail merges will create letters for everyone even if they have the "Suppress Letters on Mailmerge Extract" option set to "Yes" in their [[Contact and Consent Rules]] area.
 
* '''Suppress Extract of People Whose Preferred Contact Method is Email?''' - this setting is purely included for our customers who want to export data to Excel for mail merging. The assumption is that you might want to create emails from Charitylog but letters from Excel.
 
* '''Include This Extract in Mail-Merge Audit?''' - if set to "No", mail merges created with this configuration will not be shown in the [[Letter History Report]].
 
* '''Hide 'Anonymous Client' records''' - this refers to organisation/person records created using the "Record a Contact (for an anonymous client)" feature. These are created with a flag that marks them as "Anonymous", so even if they have been given some level of identifiable data - like a name or a postcode - they are still marked as "Anonymous". This setting lets you decide whether those people should be included in a data extraction/mail merge. or left out.
 
* '''Merge "Related and Partnered" Clients and Send Single Letter to Both''' - people who are linked using the [[Relationships Screen]] have a preference which tells the system to only send one letter rather than two if they are both present in a mail merge. This is useful for partners etc.
 
* '''Active Records Only?''' - defaults to "Yes". Refers to the [[Active Field]].  
 
  
 +
==Organisation & People Filters==
  
===1. Choose Selection Criteria (optional)===
+
'''Note: The Orgs & People Extract excludes deceased people by default.  
  
 +
If you require to report on deceased clients, you will need to specify this using the Extra Criteria to set "Deceased = Y OR Deceased = N" '''
  
[[File:cl_DE_3.png|border]]
 
  
 +
[[File:Mail_Merge_Extra_Criteria.png|1050px]]
  
If you click the "Choose Selection Criteria" '''button''', you can introduce filters for the data extraction based on the standard fields in the system. For example, you could choose to only extract data for clients within a certain set of postal districts.
+
The extra criteria section allows you to add conditions (arguments/queries) to the records being exported.  These arguments/queries can include items, such as "only people in a certain location or a certain client type based on demographic data".  This section will display the number of arguments/queries setup in the criteria.
  
* '''These filters only apply when the data extraction (or mail merge) is run from this screen. If the data extraction is applied on a Blue Folder Lookup page, these filters will not be applied.'''
+
To add or edit the criteria click on the 'Add Extra Criteria' button.
  
Clicking on the button will take you to the "Mail Merge Criteria" page.
+
The criteria page will always start with a line with the condition set to 'WHERE'.
  
 +
* '''Condition''' - The first condition will always be set to where, this is the start of the argument/query.  You can add multiple lines to the argument/query using the following conditions:
 +
** '''AND''' - This is in addition to the first line.  The system will find data based on the first line '''and''' the second line.
 +
** '''OR''' - This will look at the first line to match data '''or''' match data based on the second line.
 +
Example for AND
 +
Looking for people in specific town that have any disability.
 +
Where 'Town' = 'Town Name' AND 'Has Disability' = 'Yes'
 +
 +
Example for OR
 +
Looking for people in two postcode areas, L1 and L2.
 +
Where 'Postcode' text begins with 'L1 ' or 'Postcode' text begins with 'L2 '
 +
* '''Field Name''' - This is a dropdown list of people/org fields that can be used.  If you select a field that is populated from a drop down menu you will be display a selection screen where you can select the required values (will imply 'or' for each option), this will populate the 'Selection Value' for you when you click the 'Save Details' button on the pop up screen.
 +
* '''Selection Criteria''' - This is specify the argument/query that is to be used on that line:
 +
** '''Equals (=)''' - The data in the selected field must equal the 'Selection Value'
 +
** '''Not Equals (<>)''' - The data in the selected field must not equal the 'Selection Value'
 +
** '''Greater Than (>)''' - The data in the selected field must be greater than the 'Selection Value', which would be appropriate for values and dates.
 +
** '''Greater Than or Equal To (>=)''' - The data in the selected field must be greater than or equal to the 'Selection Value', which would be appropriate for values and dates.
 +
** '''Less Than (<)''' - The data in the selected field must be less than the 'Selection Value', which would be appropriate for values and dates.
 +
** '''Less Than or Equal To (<=)''' - The data in the selected field must be less than or equal to the 'Selection Value', which would be appropriate for values and dates.
 +
** '''Text Begins With''' - The data in the selected field must begin with the 'Selection Value', often used with text (postcodes, address line or notes).
 +
** '''Text Contains''' - The data in the selected field must contain the 'Selection Value', often used with text (postcodes, address line, notes or email address).
 +
** '''Text Does Not Contain''' - The data in the selected field must not contain the 'Selection Value', often used with text (postcodes, address line, notes).
 +
** '''IN (this,this,this)''' - a set of any required values, separated by commas, for example, Smith,Jones,Williams would find everyone whose surname was any one of these three.
 +
* '''Selection Value''' - Where you enter the values to be used in the argument/query.  This may have been auto completed by the selection of the 'Field Name', which you can change by clicking the 'Change Selection' button.
  
[[File:cl_DE_4.png|790px|border]]
+
Once you have created a line you can add a new line by clicking the 'Save Details' button.  If you wish to remove a line then set the 'Field Name' to '(No Field Selected)', when saving the line will be deleted.  Once you have finished specifying the criteria click on the 'Save and Close' Button.
  
 +
=Existing Definitions=
  
You can now set up your filters. First choose the field you want to filter by, and once you have chosen it, you will see a popup where you can pick the options to use for your filter. (options for "Postal District" shown)
+
To edit click on the name of the definition you wish to change.
  
 +
To change a [[Field Sets| Field Set]] linked to the definition (export to spreadsheet only) click on the name of the displayed Field Set.
  
[[File:cl_DE_5.png|border]]
+
To run an existing definition click on the button on the right hand side:
 +
* '''Export to Spreadsheet'''
 +
* '''Send to Blue Folder Lookup'''
  
 
+
=Orgs & People Extract for Assessment Documents=
The filters default to "equals" (or "in (this,this,this)" which is the multiple version of "equals"), but you can change the criteria. The options are:
+
It is also possible to use Microsoft Orgs & People Extract to create assessment documents.  
 
+
The following video explains how this can be done.  
''Options intended for use on any type of field''
+
{{#ev:youtube|aB112rjgXi8|790}}
* Equals
 
* Not Equals ''(does not equal)''
 
''Options intended for use on number fields''
 
* Greater than
 
* Greater than or equal to
 
* Less than
 
* Less than or equal to
 
''Options intended for use on text fields''
 
* Text begins with
 
* Text contains
 
* Text does not contain
 
''Only for use with drop-down lists:''
 
* In (this, this, this)
 
 
 
After you "Save Details", another line will appear. You can add as many criteria as required, and each subsequent line can be an OR line (which would widen the results) or an AND line (which would narrow the results).
 
 
 
The screenshot below shows rules that will extract data about people who are male, 50 or over, and live in the North or West postal districts.
 
 
 
 
 
[[File:cl_DE_6.png|790px|border]]
 
 
 
 
 
===2. Choose what to print===
 
 
 
You need to choose some data to print. This step is not optional - even if you are producing a mail merge, which doesn't take fields directly from whatever you specify here, you will need to choose at each one field.
 
 
 
If you are producing a data extraction on a spreadsheet, each option you choose here will be displayed in a column on the spreadsheet.
 
 
 
 
 
[[File:cl_DE_7.png|border]]
 
 
 
 
 
Clicking the button will show you a list of standard fields that you can choose to display. You can order them in the default order, or alphabetically, by clicking the buttons in the top right hand corner.
 
 
 
 
 
[[File:cl_DE_8.png|border]]
 
 
 
 
 
===3. Export The Results===
 
 
 
Clicking this button will run the data extraction. Different things will happen, depending on which option is selected in the "Export to a Spreadsheet or Create Mail-merged Letters/Emails?" field.
 
 
 
 
 
[[File:cl_DE_11.png|border]]
 
 
 
 
 
----
 
[[File:helpheader_small.png|right]]
 
[[User:Rob Kay|Rob Kay - manual author]] ([[User talk:Rob Kay|talk]]) 13:48, 12 January 2017 (GMT)
 
[[Category:Reporting]]
 

Latest revision as of 11:50, 3 September 2024

Helpheader small.png

Orgs & People Extract Menu.png


Introduction

The Orgs & People Extract function is used to create definitions for reporting or as a data source for mail merging in another application, e.g. Microsoft Excel/Word. They can be used in 2 ways:

1) Directly from the Contact Management menu, for exporting people regardless of the kind of work you've done with them.

2) Via a blue folder in a report, for instance to extract everyone who's had a contact in a particular project.

For saved Extracts, you must set a set a Lawful basis for this to function. The June 2024 version made specifying a lawful basis compulsory for using this feature. An audit of the basis, e.g. consent or legitimate interests, may be logged against each person whose details are extracted.

The below video gives an overview of how they can be used.

Running an Extract

To run an existing extract, ensure the Lawful basis has been selected, then choose from either Send to Blue Folder or Extract to Spreadsheet. You can always click on an extract to check or edit the options.

Neworgextract.png

Creating a Orgs & People Extract

To edit an existing definition, click on the name on the left or to create a new one click the 'Create New Orgs & People Extract' button. On the following screen you will be able to configure:

Branch Reporting Section (requires Branch Module)

In this section you can specify to report on records in;

  • All Branches
  • Selected Branches
  • Selected Branch Groups (views)

Report Type Filters

Organdpeopleextract.png

  • Definition Name - If you are saving the extract it would make sense to give it a specific name so you can easily distinguish it from the others.
  • Description -optional, but allows you to add a detailed description of the filters etc.
  • Export Function - You can either send to Blue Folder Look up for additional options, or extract direct to Spreadsheet
  • Spreadsheet Name - if you are exporting to spreadsheet, you can preset the name
  • Field Set - This allows you to choose which fields will be included in the extract. Note you must set up the field set first.
  • Exclude anonymous and anonymised people/organisation records - will be set to Yes as a default. You could choose to include anonymous records for the extract.
  • Export active people/organisation records - Will default to Yes, but you could decide to export inactive records also.
  • Include this extract in mail-merge merge audit? - will default to Yes. but you could opt to not include in the mail merge audit. Especially if this extract is for reporting purposes and not mail merge.
  • Apply contact preferences for emails? -
  • Apply contact preferences for letters? -
  • Lawful Basis - You will be required to specify the basis by which this extract is being run. You will need to choose from one of the following: Consent, Contract, Legal Obligation, Legitimate Interests, Public Task, Vital Interests. Lawful basis still needs to be applied as the extract may be used for mail merge and/or other communication systems.

Consent Based Extracts

If you choose Consent as the lawful basis, you will see the below option. You will then be required to specify which consent rule should apply. This means it will only extract records where there is a valid response to that consent rule. It will also ignore records where that consent has expired.

Consentbasedextract.png

Who to Include/Exclude

Mail Merge Orgs Types.png

In this section you will find the list of People and Organisation types you are using on your system. The names displayed are the ones specified in Customise Orgs & People. For each record type you can choose one of the following options:

  • Who to Include - This will include this record type when running the definition.
  • Who to Exclude - This is to exclude a record type when running the definition. If a person/organisation is set to more than one record type, the exclude will not use this record for the include.
Example
You wish to send a mail out to staff and a second mail out to volunteers.  You do not want to send anyone two mail outs.  The staff mail out is only set to include staff, the second mail out would be set to include volunteers and exclude staff.  Anyone that is both a staff member and volunteer would only be included in the staff mail out.

Organisation & People Filters

Note: The Orgs & People Extract excludes deceased people by default.

If you require to report on deceased clients, you will need to specify this using the Extra Criteria to set "Deceased = Y OR Deceased = N"


Mail Merge Extra Criteria.png

The extra criteria section allows you to add conditions (arguments/queries) to the records being exported. These arguments/queries can include items, such as "only people in a certain location or a certain client type based on demographic data". This section will display the number of arguments/queries setup in the criteria.

To add or edit the criteria click on the 'Add Extra Criteria' button.

The criteria page will always start with a line with the condition set to 'WHERE'.

  • Condition - The first condition will always be set to where, this is the start of the argument/query. You can add multiple lines to the argument/query using the following conditions:
    • AND - This is in addition to the first line. The system will find data based on the first line and the second line.
    • OR - This will look at the first line to match data or match data based on the second line.
Example for AND
Looking for people in specific town that have any disability.
Where 'Town' = 'Town Name' AND 'Has Disability' = 'Yes'

Example for OR
Looking for people in two postcode areas, L1 and L2.
Where 'Postcode' text begins with 'L1 ' or 'Postcode' text begins with 'L2 '
  • Field Name - This is a dropdown list of people/org fields that can be used. If you select a field that is populated from a drop down menu you will be display a selection screen where you can select the required values (will imply 'or' for each option), this will populate the 'Selection Value' for you when you click the 'Save Details' button on the pop up screen.
  • Selection Criteria - This is specify the argument/query that is to be used on that line:
    • Equals (=) - The data in the selected field must equal the 'Selection Value'
    • Not Equals (<>) - The data in the selected field must not equal the 'Selection Value'
    • Greater Than (>) - The data in the selected field must be greater than the 'Selection Value', which would be appropriate for values and dates.
    • Greater Than or Equal To (>=) - The data in the selected field must be greater than or equal to the 'Selection Value', which would be appropriate for values and dates.
    • Less Than (<) - The data in the selected field must be less than the 'Selection Value', which would be appropriate for values and dates.
    • Less Than or Equal To (<=) - The data in the selected field must be less than or equal to the 'Selection Value', which would be appropriate for values and dates.
    • Text Begins With - The data in the selected field must begin with the 'Selection Value', often used with text (postcodes, address line or notes).
    • Text Contains - The data in the selected field must contain the 'Selection Value', often used with text (postcodes, address line, notes or email address).
    • Text Does Not Contain - The data in the selected field must not contain the 'Selection Value', often used with text (postcodes, address line, notes).
    • IN (this,this,this) - a set of any required values, separated by commas, for example, Smith,Jones,Williams would find everyone whose surname was any one of these three.
  • Selection Value - Where you enter the values to be used in the argument/query. This may have been auto completed by the selection of the 'Field Name', which you can change by clicking the 'Change Selection' button.

Once you have created a line you can add a new line by clicking the 'Save Details' button. If you wish to remove a line then set the 'Field Name' to '(No Field Selected)', when saving the line will be deleted. Once you have finished specifying the criteria click on the 'Save and Close' Button.

Existing Definitions

To edit click on the name of the definition you wish to change.

To change a Field Set linked to the definition (export to spreadsheet only) click on the name of the displayed Field Set.

To run an existing definition click on the button on the right hand side:

  • Export to Spreadsheet
  • Send to Blue Folder Lookup

Orgs & People Extract for Assessment Documents

It is also possible to use Microsoft Orgs & People Extract to create assessment documents. The following video explains how this can be done.