Difference between revisions of "Orgs & People Extract"

From Charitylog Manual
Jump to: navigation, search
(Merging with a letter/email)
(Introduction)
 
(40 intermediate revisions by 2 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 spreadsheet.
 
# Generate a mail merge with [[Standard Letters| Letters or Email]]
 
# Generate a Batch SMS message
 
  
[[File:Data_Extract_Page.png|1050px]]
+
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.
  
=Creating a data extraction=
+
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.
  
To edit an existing definition, click on the name on the left or to create a new one click the 'Create New Data Extraction' button. On the following screen you will be able to configure:
+
The below video gives an overview of how they can be used.  
  
==General Details Tab==
+
{{#ev:youtube|VskxjTHNY4Y|790}}
  
[[File:General_Settings_Data.PNG|1050px]]
+
=Running an Extract=
  
* '''Definition Name''' - The name of the definition.
+
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.  
* '''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'.
 
* '''Where to export the data''':
 
** '''Export to Spreadsheet''' - Exports the results directly to a spreadsheet with the fields specified by the [[Field Sets| Field Set]].
 
** '''Send to [[Blue Folder Lookup]]''' - Send the person/org record id's to the [[Blue Folder Lookup]]. To check/change this, 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>
 
* '''Spreadsheet Name''' - If exporting to a spreadsheet you can specify a 'Save as' name for the spreadsheet.  If the results are large, the export is converted to a CSV spreadsheet and this name will not be used.
 
* '''Field Set''' - Select the Field Set that contains the fields that you require.
 
* '''Include this extract in mail-merge audit?''' - This logs against each record that they have been included in this spreadsheet, which is typically used if the spreadsheet is to be used by another application, e.g. using word processing software used to generate a mail merge
 
* '''Suppress people/organisations based on contact consent rules for letters''' - This will exclude people/orgs that have 'Suppress from mail-merge extract by default' set to 'No' in the Contact Rules of the persons/org record.
 
* '''Suppress people/organisations based on contact consent rules for emails''' - This will exclude people/orgs that have 'Suppress or Block Emails' set to 'Yes' in the Contact Rules of the persons/org record.
 
* '''Suppress anonymous and anonymised people/organisation records''' - This will exclude people that have been recorded using the record an anonymous record (whom have not been changed to a named person) and records that have been '''anonymised''' using the [[Anonymise_People|Cleanse/Anonymise Tool]].
 
* '''Export active people/organisation records''' - This allows you to only use people/org records that are set to active.
 
  
==Types of Orgs & People Tab==
+
[[File:neworgextract.png]]
  
[[File:Types_of_Orgs.png|1050px]]
+
=Creating a Orgs & People Extract=
  
On this tab 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:
+
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 [[Modules| Branch Module]])==
 +
 
 +
In this section you can specify to report on records in;
 +
*All Branches
 +
*Selected Branches
 +
*Selected Branch Groups (views)
 +
 
 +
==Report Type Filters==
 +
 
 +
[[File: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 [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 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==
 +
 
 +
[[File:Mail_Merge_Orgs_Types.png|1050px]]
 +
 
 +
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 Include''' - This will include this record type when running the definition.
Line 46: Line 68:
 
  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.
 
  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.
  
==Extra Criteria Tab==
+
==Organisation & People Filters==
  
'''Note: The data extraction report excludes deceased people by default.  
+
'''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" '''
 
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:Extra_Criteria.png|1050px]]
+
[[File:Mail_Merge_Extra_Criteria.png|1050px]]
  
The extra criteria section allows you to add extraction conditions (arguments/queries) to the records being used/extracted.  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.  The specified criteria will only function when running the definition and does not function with a [[Blue Folder Lookup]].
+
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.
 
To add or edit the criteria click on the 'Add Extra Criteria' button.
 
[[File:DataExt_AddCriteria.png]]
 
  
 
The criteria page will always start with a line with the condition set to 'WHERE'.
 
The criteria page will always start with a line with the condition set to 'WHERE'.
Line 88: Line 108:
  
 
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.
 
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.
 
==Branch Reporting Tab (requires [[Modules| Branch Module]])==
 
 
On this tab you can specify to report on records in;
 
*All Branches
 
*Selected Branches
 
*Selected Branch Groups (views)
 
 
==Saving the definition==
 
 
* '''Save and Close''' - Clicking the 'Save and Close' button will save the changes to the definition and return to the list of definitions.
 
  
 
=Existing Definitions=
 
=Existing Definitions=
Line 110: Line 119:
 
* '''Send to Blue Folder Lookup'''
 
* '''Send to Blue Folder Lookup'''
  
To delete a definition click on the 'Delete' button on the right hand side, Click 'OK' to delete or 'Cancel' to keep it.
+
=Orgs & People Extract for Assessment Documents=
 
+
It is also possible to use Microsoft Orgs & People Extract to create assessment documents.  
==Exporting to a spreadsheet==
+
The following video explains how this can be done.  
 
+
{{#ev:youtube|aB112rjgXi8|790}}
When clicking the 'Export To Spreadsheet' button the system will gather the data and supply a Spreadsheet to the browser (the program on your computer that you access the system with).  You can then Open/Save the Spreadsheet.
 
 
 
==Sending to the Blue Folder lookup==
 
 
 
Clicking the 'Send to Blue Folder' button the definition will run and pass the person/org record ID's to the [[Blue Folder Lookup]] tool.
 
 
 
==Mail Merge Function==
 
 
 
 
 
The Data Extraction/Mail Merge function can also be used to create definitions for producing letters or emails to be sent in bulk. To do this, select the Mail Merge section on the left hand side.
 
 
 
[[File:Mail_Merge.PNG|1050px]]
 
 
 
[[File:Mail_Merge_Paint.PNG|1050px]]
 
 
 
*'''Definition Name''' - Name of the definition.
 
*'''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'.
 
*'''Standard Letter Template''' - Select the letter template you would like to use for this mail merge. This letter or email will be generated as a result of this mail merge, so make sure you select the one that you want for this definition. The standard letter templates are set up under general settings in the admin menu.
 
*'''Send Letters or Emails (based on 'preferred method of communication')?''' - If you use the 'preferred method of communication' field to capture information, you can generate either a letter or email for records, based off the value in this field on their record.
 
*'''Suppress people/organisations based on contact consent rules for letters''' - This will exclude people/orgs that have 'Suppress from mail-merge extract by default' set to 'No' in the Contact Rules of the persons/org record.
 
*'''Suppress people/organisations based on contact consent rules for emails''' - This will exclude people/orgs that have 'Suppress or Block Emails' set to 'Yes' in the Contact Rules of the persons/org record.
 
*'''Suppress anonymous and anonymised people/organisation records''' - This will exclude people that have been recorded using the record an anonymous record (whom have not been changed to a named person) and records that have been anonymised using the Cleanse/Anonymise Tool.
 
*'''Send only one letter/email to both "related and partnered" people, when both are in mail merge (and marked as such)''' - Using the Relationships Screen you can specify that two people only receive one letter in mail merges (you must also specify the joint salutation). If both of these records are part of the data set then they will be merged using the joint salutation on exports to spreadsheet and mail merges.
 
*'''Export active people/organisation records''' - This allows you to only use people/org records that are set to active.
 
 
 
==Batch sending SMS messages==
 
 
 
When you click on the 'Batch send to SMS message' button you will be displayed with a list of existing Batch SMS definitions.
 
 
 
[[File:Batch_SMS_Message.PNG|1050px]]
 
 
 
 
 
*'''Create New Batch SMS Message''' This will require you to create a new definition if you need to create a new Batch SMS Message.
 
*'''Select the name of an existing definition if you need to create a new one.'''
 
 
 
[[File:Batch_SMS_General.PNG|1050px]]
 
 
 
*'''Definition Name''' - The name of the definition.
 
*'''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'.
 
*'''Suppress people/organisations based on contact consent rules for SMS''' - This will exclude people/orgs that have 'Suppress or Block SMS' set to 'Yes' in the Contact Rules of the persons/org record.
 
*'''Suppress anonymous and anonymised people/organisation records''' - This will exclude people that have been recorded using the record an anonymous record (whom have not been changed to a named person) and records that have been anonymised using the Cleanse/Anonymise Tool.
 
*'''Export active people/organisation records''' - This allows you to only use people/org records that are set to active.
 
 
 
 
 
== Send SMS Message ==
 
 
 
'''Select the Send SMS button to see the results of your criteria. This will display a list of records captured by your criteria for the Batch SMS. For the records with no number the name will appear crossed out.
 
 
 
Those with a mobile number will show the number displayed in the drop down list. Those without will display an empty value in the drop down, and you will need to manually select the number you wish to send to. This is likely because the record is missing a mobile number, but does have a telephone number.
 
 
 
In the message box type your message, this may be split into more than one SMS message. A single SMS message supports up to 159 characters (including spaces) but can send multiple messages to a limit of 459 characters across all SMS messages. You will see the counters below the message box.
 
 
 
[[File:MM_SMSmessage.png]]
 
 
 
When you are ready to continue click on the 'Send SMS' button.
 
 
 
You will then be shown a confirmation message telling you how many credits this will use.  Click 'OK' to send or 'Cancel' to return without sending.
 
 
 
You will then be displayed a summary.
 

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.