Difference between revisions of "Extension Database Setup"

From Charitylog Manual
Jump to: navigation, search
(→‎Filter by Field)
(→‎Spreadsheet Calculations Result)
 
(166 intermediate revisions by 8 users not shown)
Line 2: Line 2:
  
  
−
[[File:cl_EDBS_1.png|border]]
+
[[File:cl_EDBS_1.png]]
  
  
 
__TOC__
 
__TOC__
  
 +
=Introduction to Extension Databases=
  
−
Extension Databases provide a way for you to add fields, or sets of fields, to the system. They can be used to capture any information which is not catered for in the standard setup. See [[Extension Databases]] for an overview.
+
Extension Databases provide a way for you to add fields, or sets of fields, to the system. They can be used to capture any information which is not catered for in the standard setup.  
  
 
The Extension Database Setup page lists the Extension Databases that are already set up on your system (if there are any). You can amend existing Extension Databases and create new ones.
 
The Extension Database Setup page lists the Extension Databases that are already set up on your system (if there are any). You can amend existing Extension Databases and create new ones.
  
 +
To create a new Extension Database click "New Extension Database" or click on the name of an existing Extension Database to edit it. Its important you understand the different locations available for an Extension Database. The below webinar explains this.
 +
{{#ev:youtube|FAB52WalfDw|790}}
  
−
==Setting up new Extension Databases==
+
=Extension Database Settings=
  
−
Click "New Extension Database".
+
On an existing Extension Database or when saving the Extension Database settings for a new one a navigation bar will appear at the top of the section:
  
−
[[File:cl_EDBS_2.png|border]]
+
[[File:ExtDB_navbar.PNG]]
  
 +
This allows you to move between the following sections of the setup:
 +
* '''The name of the Extension Database is underlined ''' - This is the general details of where the extension data base will appear and which security groups can access it.
 +
* '''All Sections''' - Sections are used to group questions together to make data entry easier and more logical.
 +
* '''New Field''' - allows you to add new fields
  
−
===Basic settings for the Extension Database===
+
To create a new Extension Database, click on New '''Extension Database'''
  
−
[[File:cl_EDBS_3.png|500px|border]]
+
[[File:New_Extension_database.PNG]]
  
 +
* '''Extension Database Name'''- This is name of the Extension Database and will also be the button label that appears for the user.
 +
* '''Description''' - This is information about the Extension Database which can only be viewed on this page
 +
* '''One record or multiple records for this entry?'''
 +
** '''One Record''' - This displays a single set of fields, if the data is changed the previous entry will be simply over written.  This would be used for questions like 'Do you drive?', if circumstances changed you would simply change the answer to the question.
 +
** '''One or More Records''' - This is used if multiple sets of data is required.  As an example you could have an extension database to record donations given, as you may get more than one donation from the some person.
 +
* '''Linked to which main type of record?''':
 +
** '''Organisations and People''' - This will be able to be displayed on a record tab or at the footer of the record.
 +
** '''Referrals''' - This allows the Extension Database to be displayed on the history tab, referral edit screen or appear when recording a contact.  See [[Project_Set_Up#Additional_Data_Collection_tab|Project Setup]] for further details.
 +
** '''Clubs and Clinics''' - This will appear on the footer of a Club, Clinic or Group details page, for those specified.
 +
** '''Clubs and Clinics Meetings''' - This will appear on the individual occurrence of the Club, Clinic or Group on the incomplete/complete meeting tabs and when a meeting is completed via the wizard or complete button.
 +
* '''Show Fields on Section''' - This option appears when the '''linked to''' option is set to 'Organisations and People'Specify weather the Extension Database is to be displayed on the required section  Additional sections can be setup, see [[Customise_Orgs_%26_People#Additional_Tabs_for_Records|Customise Orgs & People]] for details.
 +
* '''Available to users in these groups''' - Select which [[Group Access|Groups]] of users can access the Extension Database.
 +
* '''Maximum number of column Headers to be viewable when selecting from multiple records?''' - When selecting 'One or More' you can specify the number of columns displayed for the Extension Database.
 +
* '''Active?''' - When set to 'Yes' the Extension Database will be dis[played and can be reported on.
  
−
You can now enter:
+
Once the above options have been completed you can then select one of the following save buttons:
 +
* '''Save and edit Sections''' - Saves any changes and moves to the page to create sections, if required.
 +
* '''Save and edit Fields''' - Saves any changes and moves to the page to create the fields to record the data.
  
−
* '''Name for additional data entry option''' - the name of the extension database
+
=Extension Database Sections=
−
* '''Description''' - this will appear on the "Extension Database Reports" screen.
 
−
* '''Linked to which main type of record?''' - controls whether the extension database will be linked to someone's [[Details Screen]], to Referrals, or to Clubs/Clinics.
 
−
* '''Select specific type of record(s)''' -
 
−
** in the case of extension databases on org/people records, this controls which type(s) of org/people the extension database displays for.
 
−
** in the case of extension databases on referral entries, this controls which Projects the extension database is available to.
 
−
** in the case of extension databases on clubs/clinics, this controls which club(s) the extension database displays for.
 
−
* '''Available to users in these groups''' - controls which user groups can see this extension database.
 
−
* '''One record or multiple records for this entry?''' - this controls whether the database can be filled in multiple times. Again, this option is not available for extension databases on the Client Personal tab.
 
−
* '''Active?''' - controls whether this extension database is currently in use or not. This is mainly used for switching off historical extension databases.
 
  
−
'''Remember that when selecting Projects/people types etc, you can select multiples from the list by holding "ctrl" (PC) or "Command" (Mac) and clicking the items in the list that you want.'''
+
Section Headings will be displayed if you add an Extension Database to display One or More. They won't appear if you have just added fields to an existing section such as the Personal Details page. The following webinar gives an example of how section headings can be used for things like assessments.  
  
−
Once these fields have been filled in, click "Save and Edit Fields".
+
{{#ev:youtube|bAHllGk4i-k|790}}
  
−
[[File:cl_EDBS_4.png|border]]
+
[[File:ExtDB_sections.PNG]]
  
 +
Sections allow you to break up fields into logical sections.  Example:
 +
Section 1: Assessment one
 +
Date:
 +
Range of questions
 +
 +
Section 2: Assessment two
 +
Date:
 +
Range of questions
  
−
==Adding and editing fields==
+
To create a new section click on the lick 'Create a new Extension Database Section...'.  Type the required label for the section and click go to create it.
  
−
Click "New Field" to enter a new one.
+
The section will be created and added to the list of existing sections (if any).
  
−
[[File:cl_EDBS_5.png|border]]
+
To edit an existing section click on the sections name.  From here you can specify if the heading;
 +
*Displays as a title, the name.
 +
*Displays as just a dividing line.
 +
*Displays as a blank line.
  
 +
To delete a section click the Bin Icon [[File:Icon_Bin.PNG]].
  
−
Enter the following information;
+
==Reordering Sections==
 +
Click on the 'Enable Reorder' button to Enable reordering, from here you can drag and drop the sections to the required position, or use the A-Z or Z-A option.  Once complete you can then click 'Disable Reorder'.
  
−
* Screen Label for Data Entry - The Label is the name given to the field on screens and reports. This is what the user will see.
+
[[File:ExtDB_reorder.PNG]]
−
* Description - further description of the field if required. '''This will display as help text, shown when the user hovers over/clicks on the green help icon for this field, so it's best to always fill in a description, even if it's only brief.'''
 
−
* Field Type - Choose the relevant field type from the list. Note that this cannot be changed once data has been entered into the Extension Database as this could potentially corrupt existing data.
 
−
* Filter by Field - see [[#Filter_by_Field|the bottom of this page]].
 
−
* '''Various other options will follow depending on which field type you have chosen - see below.'''
 
  
−
[[File:cl_EDBS_6.png|border]]
+
=Extension Database Fields=
  
 +
An Extension Data base can contain a series of different field types.  After you have completed the field options click 'Save Details' to create the field. As you click New Field, you will notice the field type is divided into categories as seen below.
  
−
* Field Entry Requirement - choose whether the field is not required, forced (user will not be able to save the page without something in this field, this field will be red), or "warning" (user will be warned if they do not fill in the field, but will be allowed to save the page, this field will be yellow).
+
[[File:edbfieldcategories.png]]
−
* Shortcut Key - This is an accessibility feature. The Shortcut is the character, shown as underlined in the field label, which is used as a keyboard shortcut to the entry of the field (Alt and a character on PC).
 
−
* Display Order - This will define the order in which fields appear on the screen when the data entry page is shown. It is worth leaving gaps to allow additional fields to be easily added later - numbering your fields as 10, 20, 30 etc.
 
−
* Active Field? - If you make the field non active, it will not be displayed on the data entry screen.
 
  
 +
The following webinar explains the different types of fields available.
  
−
===Field Types and associated options===
+
{{#ev:youtube|bbTWspcJu1U|790}}
  
−
====Single Row Alphanumeric String====
 
  
−
A simple field where the user can enter any text or numbers. When this type is selected, you can choose;
+
==Date and Time==
 +
Here you can add both date fields and Time fields
  
−
* '''Field Length''' in number of characters (including spaces)
+
===Date===
−
* '''Default Value''' to be shown in the field when the user first sees it. Leave this field blank if there is no default value.
+
Allows entry of a date, useful when reporting on certain periods.
  
−
[[File:EDBEF_1.png|border]]
+
[[File:EDBEF_4.png]]
  
 +
* '''Field Label''' - This is the name/question that is displayed to the user.
 +
* '''Description''' - This is displayed as help text via a question mark icon [[File:Icon_greenquestionmark.PNG]].  This is note displayed when the fields are displayed on a tab.
 +
* '''Field Type''' - Option to select which of these field types you wish to use.  Once a field has been created the field type cannot be changed.  Set to 'Date'.
 +
* '''Include in Reports''' - allows you to report on data entered
 +
* '''Section''' - If you are using sections you can specify which section this option appears in.
 +
* '''Allow API access?''' - Used for API connections if your system links to a third party system.  When set to yes you can then specify the API name to be used.
 +
* '''Link to Spreadsheet''' - If the extension database is using excel to produce calculations and the field is as part of the calculations, specify the spreadsheet template that field is used to input in to.  See  [[Extension_Database_Setup#Spreadsheet_Calculations_Result|Spreadsheet Calculations Result]] for further details.
 +
* '''Spreadsheet cell''' - Specify the cell in the spreadsheet that the field feeds into.
 +
* '''Default Date Value''' - You can populate the field with the date the record is entered or left empty.
 +
* '''Field Entry Requirement'''
 +
** '''Not Required - No Rules''' - The field will be displayed but can be left blank.
 +
** '''Warning - Warn if not filled in''' - The field will be displayed with a yellow background (not when set as radio buttons) and a pop up will be displayed if left blank when saving.
 +
* '''Access Key''' - Specify a shortcut key so the user can jump directly to this field when entering data.  Please note that different operating systems and browser also use shortcut keys, this will override this option.
 +
* '''[[Display Order]]''' - The order that this field will be displayed on the page or section.
 +
* '''Active''' - When set to 'Yes' this field will be displayed to the user.
  
−
====Numeric Only====
+
===Time===
 +
Allows entry of a time.
  
−
A field where the user must enter a number (no text allowed). When this type is selected, you can choose;
+
[[File:timefield_edb.png]]
  
−
* '''Field Length''' in characters including the decimal point if there is to be one
+
* '''Field Label''' - This is the name/question that is displayed to the user.
−
* '''Decimal Places'''
+
* '''Description''' - This is displayed as help text via a question mark icon [[File:Icon_greenquestionmark.PNG]].  This is note displayed when the fields are displayed on a tab.
−
* '''Default Value''' to be shown in the field when the user first sees it. Leave this field blank if there is no default value.
+
* '''Field Type''' - Option to select which of these field types you wish to use.  Once a field has been created the field type cannot be changed.  Set to 'Date'.
 +
* '''Include in Reports''' - allows you to report on data entered
 +
* '''Section''' - If you are using sections you can specify which section this option appears in.
 +
* '''Allow API access?''' - Used for API connections if your system links to a third party system.  When set to yes you can then specify the API name to be used.
 +
* '''Link to Spreadsheet''' - If the extension database is using excel to produce calculations and the field is as part of the calculations, specify the spreadsheet template that field is used to input in to.  See  [[Extension_Database_Setup#Spreadsheet_Calculations_Result|Spreadsheet Calculations Result]] for further details.
 +
* '''Default Time Value''' - allows you to specify a default value which will displayed when the extension database is created.
 +
* '''Field Entry Requirement'''
 +
** '''Not Required - No Rules''' - The field will be displayed but can be left blank.
 +
* '''Access Key''' - Specify a shortcut key so the user can jump directly to this field when entering data.  Please note that different operating systems and browser also use shortcut keys, this will override this option.
 +
* '''[[Display Order]]''' - The order that this field will be displayed on the page or section.
 +
* '''Active''' - When set to 'Yes' this field will be displayed to the user.
  
−
[[File:EDBEF_2.png|border]]
+
==Lists==
  
 +
===Drop-Down===
  
−
====Money====
+
This will display a dropdown list that the user can select one option from.
  
−
As "Numeric Only", but formatted for currency.
+
[[File:EDBEF_6.png]]
  
−
[[File:EDBEF_3.png|border]]
+
* '''Field Label''' - This is the name/question that is displayed to the user.
 +
* '''Description''' - This is displayed as help text via a question mark icon [[File:Icon_greenquestionmark.PNG]].  This is note displayed when the fields are displayed on a tab.
 +
* '''Field Type''' - Option to select which of these field types you wish to use.  Once a field has been created the field type cannot be changed.  Set to 'Drop down'.
 +
* '''Include in Reports''' - allows you to report on data entered
 +
* '''Section''' - If you are using sections you can specify which section this option appears in.
 +
* '''Allow API access?''' - Used for API connections if your system links to a third party system. When set to yes you can then specify the API name to be used.
 +
* '''Link to Spreadsheet''' - If the extension database is using excel to produce calculations and the field is as part of the calculations, specify the spreadsheet template that field is used to input in to.  See  [[Extension_Database_Setup#Spreadsheet_Calculations_Result|Spreadsheet Calculations Result]] for further details.
 +
* '''Spreadsheet cell''' - Specify the cell in the spreadsheet that the field feeds into.
 +
* '''Option Details''' - This is the items that will appear in the drop down list.
 +
** '''Default''' - When ticked this option will automatically be selected when entering a new record. It is worth considering if you want this value saved if the extension database is not manually changed before saving a client record.
 +
** '''Option Text''' - This is the name displayed in the dropdown list.
 +
* '''Filter By Field''' - This allows the dropdown list to be filtered by another drop down list (does not work with radio buttons).  The options that have been selected on a previous question can filter what options can be selected on this question.  From the list select the dropdown list that is to be used to filter this question.
 +
* '''Inactive Option Display'''
 +
** '''Do not display inactive options''' - Options that have been set as inactive will not be displayed in the dropdown list.
 +
** '''Display inactive options as not selectable''' - This will display inactive options, which cannot be selected by the user.
 +
* '''Field Entry Requirement'''
 +
** '''Not Required - No Rules''' - The field will be displayed but can be left blank.
 +
** '''Warning - Warn if not filled in''' - The field will be displayed with a yellow background (not when set as radio buttons) and a pop up will be displayed if left blank when saving.
 +
* '''Access Key''' - Specify a shortcut key so the user can jump directly to this field when entering data.  Please note that different operating systems and browser also use shortcut keys, this will override this option.
 +
* '''[[Display Order]]''' - The order that this field will be displayed on the page or section.
 +
* '''Active''' - When set to 'Yes' this field will be displayed to the user.
  
 +
===Radio Button===
  
−
====Date====
+
As "Drop down lists", but options will be displayed as clickable buttons. Only one button can be selected at a time.
  
−
A date field, with the standard date formatting/date picker applied. You can choose;
+
* You can choose whether the buttons display "On one row" (side by side), or "Multi Rows/Stacked" (one above the other).
 +
 
 +
[[File:EDBEF_7.png|border]]
 +
 
 +
* '''Field Label''' - This is the name/question that is displayed to the user.
 +
* '''Description''' - This is displayed as help text via a question mark icon [[File:Icon_greenquestionmark.PNG]].  This is note displayed when the fields are displayed on a tab.
 +
* '''Field Type''' - Option to select which of these field types you wish to use.  Once a field has been created the field type cannot be changed.  Set to 'Radio Button'.
 +
* '''Include in Reports''' - allows you to report on data entered
 +
* '''Section''' - If you are using sections you can specify which section this option appears in.
 +
* '''Allow API access?''' - Used for API connections if your system links to a third party system.  When set to yes you can then specify the API name to be used.
 +
* '''Link to Spreadsheet''' - If the extension database is using excel to produce calculations and the field is as part of the calculations, specify the spreadsheet template that field is used to input in to.  See  [[Extension_Database_Setup#Spreadsheet_Calculations_Result|Spreadsheet Calculations Result]] for further details.
 +
* '''Spreadsheet cell''' - Specify the cell in the spreadsheet that the field feeds into.
 +
** '''Option Text''' - This is the name displayed in the dropdown list.
 +
* '''Option Details''' - This is the items that will appear in the drop down list.
 +
** '''Default''' - When ticked this option will automatically be selected when entering a new record.
 +
** '''[[Display Order]]''' - The order the option will be displayed in the dropdown list.
 +
** '''Selection list for the filter''' - Select which answers of the previously selected question that the current option is available to be selected.  If you are using the filter and nothing is selected then the option cannot be selected.  If the option is to be available to all then all answers to the previous question must be selected.
 +
** '''Active''' - If set to yes then this option can be selected from the drop down list.
 +
** '''Plus Icon [[File:Icon_plus.PNG]]''' - Creates a new line for the next option.
 +
* '''Radio Button Display''' - Select the required display option.
 +
* '''Filter By Field''' - This allows the dropdown list to be filtered by another radio button field (does not work with dropdown lists).  The options that have been selected on a previous question can filter what options can be selected on this question.  From the list select the radio button field that is to be used to filter this question.
 +
** '''On one row''' - Ideal for less options and is displayed across the screen.
 +
** '''Multi rows/Stacked''' - Ideal for options with a long names or larger quantity of options.
 +
* '''Inactive Option Display'''
 +
** '''Do not display inactive options''' - Options that have been set as inactive will not be displayed in the dropdown list.
 +
** '''Display inactive options as not selectable''' - This will display inactive options, which cannot be selected by the user.
 +
* '''Field Entry Requirement'''
 +
** '''Not Required - No Rules''' - The field will be displayed but can be left blank.
 +
** '''Warning - Warn if not filled in''' - The field will be displayed with a yellow background (not when set as radio buttons) and a pop up will be displayed if left blank when saving.
 +
* '''Shortcut Key''' - Specify a shortcut key so the user can jump directly to this field when entering data.  Please note that different operating systems and browser also use shortcut keys, this will override this option.
 +
* '''[[Display Order]]''' - The order that this field will be displayed on the page or section.
 +
* '''Active''' - When set to 'Yes' this field will be displayed to the user.
 +
 
 +
===Organisations or People List===
 +
 
 +
This field displays a look up list of Organisations and People on the system.
 +
 
 +
[[File:extdb_orgslist.png]]
 +
 
 +
* '''Field Label''' - This is the name/question that is displayed to the user.
 +
* '''Description''' - This is displayed as help text via a question mark icon [[File:Icon_greenquestionmark.PNG]].  This is note displayed when the fields are displayed on a tab.
 +
* '''Field Type''' - Option to select which of these field types you wish to use.  Once a field has been created the field type cannot be changed.  Set to 'Organisations and People List'.
 +
* '''Include in Reports''' - allows you to report on data entered
 +
* '''Types''' - Selected the required record types that will be available to be selected. Note you can select more than one.
 +
* '''Section''' - If you are using sections you can specify which section this option appears in.
 +
* '''Allow API access?''' - Used for API connections if your system links to a third party system.  When set to yes you can then specify the API name to be used.
 +
* '''Field Entry Requirement'''
 +
** '''Not Required - No Rules''' - The field will be displayed but can be left blank.
 +
** '''Warning - Warn if not filled in''' - The field will be displayed with a yellow background (not when set as radio buttons) and a pop up will be displayed if left blank when saving.
 +
* '''Access Key''' - Specify a shortcut key so the user can jump directly to this field when entering data.  Please note that different operating systems and browser also use shortcut keys, this will override this option.
 +
* '''[[Display Order]]''' - The order that this field will be displayed on the page or section.
 +
* '''Active''' - When set to 'Yes' this field will be displayed to the user.
 +
 
 +
==Text and Numbers==
 +
 
 +
===URL===
 +
 
 +
This will allow you to either, add a URL which is required as part of the extension database entry.
 +
 
 +
[[File:urlfield_edb.png]]
 +
 
 +
* '''Field Label''' - This is the name/question that is displayed to the user.
 +
* '''Description''' - This is displayed as help text via a question mark icon [[File:Icon_greenquestionmark.PNG]].  This is note displayed when the fields are displayed on a tab.
 +
* '''Field Type''' - Option to select which of these field types you wish to use.  Once a field has been created the field type cannot be changed.  Set to 'URL'.
 +
* '''Include in Reports''' - allows you to choose if this data will appear in reports. 
 +
* '''Section''' - If you are using sections you can specify which section this option appears in.
 +
* '''Allow API access?''' - Used for API connections if your system links to a third party system.  When set to yes you can then specify the API name to be used.
 +
* '''Link to Spreadsheet''' - If the extension database is using excel to produce calculations and the field is as part of the calculations, specify the spreadsheet template that field is used to input in to.  See  [[Extension_Database_Setup#Spreadsheet_Calculations_Result|Spreadsheet Calculations Result]] for further details.
 +
* '''Spreadsheet cell''' - Specify the cell in the spreadsheet that the field feeds into.
 +
* '''Field length''' - This determines maximum number of characters allowed.
 +
* '''Default Value''' - Specify a default value that will be prepopulated.
 +
* '''Field Entry Requirement'''
 +
** '''Not Required - No Rules''' - The field will be displayed but can be left blank.
 +
** '''Warning - Warn if not filled in''' - The field will be displayed with a yellow background (not when set as radio buttons) and a pop up will be displayed if left blank when saving.
 +
* '''Access Key''' - Specify a shortcut key so the user can jump directly to this field when entering data.  Please note that different operating systems and browser also use shortcut keys, this will override this option.
 +
* '''[[Display Order]]''' - The order that this field will be displayed on the page or section.
 +
* '''Active''' - When set to 'Yes' this field will be displayed to the user.
 +
 
 +
===Email===
 +
 
 +
Allows you to add an email address to the extension database.
 +
 
 +
[[File:emailfieldv2.png]]
 +
 
 +
* '''Field Label''' - This is the name/question that is displayed to the user.
 +
* '''Description''' - This is displayed as help text via a question mark icon [[File:Icon_greenquestionmark.PNG]].  This is note displayed when the fields are displayed on a tab.
 +
* '''Field Type''' - Option to select which of these field types you wish to use.  Once a field has been created the field type cannot be changed.  Set to 'Email'.
 +
* '''Include in Reports''' - allows you to choose if this data will appear in reports. 
 +
* '''Section''' - If you are using sections you can specify which section this option appears in.
 +
* '''Allow API access?''' - Used for API connections if your system links to a third party system.  When set to yes you can then specify the API name to be used.
 +
* '''Link to Spreadsheet''' - If the extension database is using excel to produce calculations and the field is as part of the calculations, specify the spreadsheet template that field is used to input in to.  See  [[Extension_Database_Setup#Spreadsheet_Calculations_Result|Spreadsheet Calculations Result]] for further details.
 +
* '''Spreadsheet cell''' - Specify the cell in the spreadsheet that the field feeds into.
 +
* '''Default Value''' - Specify a default value that will be prepopulated.
 +
* '''Field Entry Requirement'''
 +
** '''Not Required - No Rules''' - The field will be displayed but can be left blank.
 +
** '''Warning - Warn if not filled in''' - The field will be displayed with a yellow background (not when set as radio buttons) and a pop up will be displayed if left blank when saving.
 +
* '''Access Key''' - Specify a shortcut key so the user can jump directly to this field when entering data.  Please note that different operating systems and browser also use shortcut keys, this will override this option.
 +
* '''[[Display Order]]''' - The order that this field will be displayed on the page or section.
 +
* '''Active''' - When set to 'Yes' this field will be displayed to the user.
 +
 
 +
===Phone Number===
 +
 
 +
Allows you to add a phone number to the extension database entry.
 +
 
 +
[[File:phonenumber_edb.png]]
 +
 
 +
* '''Field Label''' - This is the name/question that is displayed to the user.
 +
* '''Description''' - This is displayed as help text via a question mark icon [[File:Icon_greenquestionmark.PNG]].  This is note displayed when the fields are displayed on a tab.
 +
* '''Field Type''' - Option to select which of these field types you wish to use.  Once a field has been created the field type cannot be changed.  Set to 'Phone Number'.
 +
* '''Include in Reports''' - allows you to choose if this data will appear in reports. 
 +
* '''Section''' - If you are using sections you can specify which section this option appears in.
 +
* '''Allow API access?''' - Used for API connections if your system links to a third party system.  When set to yes you can then specify the API name to be used.
 +
* '''Link to Spreadsheet''' - If the extension database is using excel to produce calculations and the field is as part of the calculations, specify the spreadsheet template that field is used to input in to.  See  [[Extension_Database_Setup#Spreadsheet_Calculations_Result|Spreadsheet Calculations Result]] for further details.
 +
* '''Spreadsheet cell''' - Specify the cell in the spreadsheet that the field feeds into.
 +
* '''Field Lenght''' - specify the length of characters that will be allowed in this field
 +
* '''Default Value''' - Specify a default value that will be prepopulated.
 +
* '''Field Entry Requirement'''
 +
** '''Not Required - No Rules''' - The field will be displayed but can be left blank.
 +
** '''Warning - Warn if not filled in''' - The field will be displayed with a yellow background (not when set as radio buttons) and a pop up will be displayed if left blank when saving.
 +
* '''Access Key''' - Specify a shortcut key so the user can jump directly to this field when entering data.  Please note that different operating systems and browser also use shortcut keys, this will override this option.
 +
* '''[[Display Order]]''' - The order that this field will be displayed on the page or section.
 +
* '''Active''' - When set to 'Yes' this field will be displayed to the user.
 +
 
 +
===Money===
 +
 
 +
Allows only numbers to be used and totals on displayed reports.
 +
 
 +
[[File:EDBEF_3.png]]
 +
 
 +
* '''Field Label''' - This is the name/question that is displayed to the user.
 +
* '''Description''' - This is displayed as help text via a question mark icon [[File:Icon_greenquestionmark.PNG]].  This is note displayed when the fields are displayed on a tab.
 +
* '''Field Type''' - Option to select which of these field types you wish to use.  Once a field has been created the field type cannot be changed.  Set to 'Money'.
 +
* '''Include in Reports''' - allows you to decide if you would like to include this data in reports.
 +
* '''Section''' - If you are using sections you can specify which section this option appears in.
 +
* '''API access?''' - Used for API connections if your system links to a third party system.  When set to yes you can then specify the API name to be used.
 +
* '''Link to Spreadsheet''' - If the extension database is using excel to produce calculations and the field is as part of the calculations, specify the spreadsheet template that field is used to input in to.  See  [[Extension_Database_Setup#Spreadsheet_Calculations_Result|Spreadsheet Calculations Result]] for further details.
 +
* '''Spreadsheet cell''' - Specify the cell in the spreadsheet that the field feeds into.
 +
* '''Field length''' - This determines maximum number of digits (including decimal point), this works alongside the numeric maximum.
 +
* '''Decimal Places''' - Specify the number of decimal places that are required, please keep in mind the field length.
 +
* '''Default Value''' - Specify a default value that will be prepopulated.
 +
* '''Field Entry Requirement'''
 +
** '''Not Required - No Rules''' - The field will be displayed but can be left blank.
 +
** '''Warning - Warn if not filled in''' - The field will be displayed with a yellow background (not when set as radio buttons) and a pop up will be displayed if left blank when saving.
 +
* '''Numeric Minimum''' - The minimum value a user can enter.
 +
* '''Numeric Maximum''' - The maximum value a user can enter, this must comply with the field length.
 +
* '''Access Key''' - Specify a shortcut key so the user can jump directly to this field when entering data.  Please note that different operating systems and browser also use shortcut keys, this will override this option.
 +
* '''[[Display Order]]''' - The order that this field will be displayed on the page or section.
 +
* '''Active''' - When set to 'Yes' this field will be displayed to the user.
 +
 
 +
===Number===
 +
 
 +
Allows only numbers to be used and totals on displayed reports.
 +
 
 +
[[File:EDBEF_2.png]]
 +
 
 +
* '''Field Label''' - This is the name/question that is displayed to the user.
 +
* '''Description''' - This is displayed as help text via a question mark icon [[File:Icon_greenquestionmark.PNG]].  This is note displayed when the fields are displayed on a tab.
 +
* '''Field Type''' - Option to select which of these field types you wish to use.  Once a field has been created the field type cannot be changed.  Set to 'Number'.
 +
* '''Include in Reports''' - allows you to choose if this data will appear in reports. 
 +
* '''Section''' - If you are using sections you can specify which section this option appears in.
 +
* '''Allow API access?''' - Used for API connections if your system links to a third party system.  When set to yes you can then specify the API name to be used.
 +
* '''Link to Spreadsheet''' - If the extension database is using excel to produce calculations and the field is as part of the calculations, specify the spreadsheet template that field is used to input in to.  See  [[Extension_Database_Setup#Spreadsheet_Calculations_Result|Spreadsheet Calculations Result]] for further details.
 +
* '''Spreadsheet cell''' - Specify the cell in the spreadsheet that the field feeds into.
 +
* '''Field length''' - This determines maximum number of digits (including decimal point), this works alongside the numeric maximum.
 +
* '''Decimal Places''' - Specify the number of decimal places that are required, please keep in mind the field length.
 +
* '''Default Value''' - Specify a default value that will be prepopulated.
 +
* '''Field Entry Requirement'''
 +
** '''Not Required - No Rules''' - The field will be displayed but can be left blank.
 +
** '''Warning - Warn if not filled in''' - The field will be displayed with a yellow background (not when set as radio buttons) and a pop up will be displayed if left blank when saving.
 +
* '''Numeric Minimum''' - The minimum value a user can enter.
 +
* '''Numeric Maximum''' - The maximum value a user can enter, this must comply with the field length.
 +
* '''Access Key''' - Specify a shortcut key so the user can jump directly to this field when entering data.  Please note that different operating systems and browser also use shortcut keys, this will override this option.
 +
* '''[[Display Order]]''' - The order that this field will be displayed on the page or section.
 +
* '''Active''' - When set to 'Yes' this field will be displayed to the user.
 +
 
 +
===Text (Single-Row)===
 +
This will display a single line text box based on the field length.
  
−
* '''Default Value''' - choose from "Show blank date" or "Default to current date".
+
[[File:EDBEF_1.png]]
  
−
[[File:EDBEF_4.png|border]]
+
* '''Field Label''' - This is the name/question that is displayed to the user.
 +
* '''Description''' - This is displayed as help text via a question mark icon [[File:Icon_greenquestionmark.PNG]].  This is note displayed when the fields are displayed on a tab.
 +
* '''Field Type''' - Option to select which of these field types you wish to use.  '''Once a field has been created the field type cannot be changed.'''  Set to 'Text (single-row)'.
 +
* '''Appear in Reports''' - allows you to specify if data entered will appear in your reports. Useful if you want to add a free text box, but don't need that data for reporting purposes.
 +
* '''Section''' - If you are using sections you can specify which section this option appears in.
 +
* '''Allow API access?''' - Used for API connections if your system links to a third party system.  When set to yes you can then specify the API name to be used.
 +
* '''Link to Spreadsheet''' - If the extension database is using excel to produce calculations and the field is as part of the calculations, specify the spreadsheet template that field is used to input in to.  See  [[Extension_Database_Setup#Spreadsheet_Calculations_Result|Spreadsheet Calculations Result]] for further details.
 +
* '''Spreadsheet cell''' - Specify the cell in the spreadsheet that the field feeds into.
 +
* '''Field length''' - This determines the length of the field and the maximum number of characters that can be entered.
 +
* '''Default Value''' - You can populate the field with the details specified.
 +
* '''Field Entry Requirement'''
 +
** '''Not Required - No Rules''' - The field will be displayed but can be left blank.
 +
** '''Warning - Warn if not filled in''' - The field will be displayed with a yellow background (not when set as radio buttons) and a pop up will be displayed if left blank when saving.
 +
* '''Access Key''' - Specify a shortcut key so the user can jump directly to this field when entering data.  Please note that different operating systems and browser also use shortcut keys, this will override this option.
 +
* '''[[Display Order]]''' - The order that this field will be displayed on the page or section.
 +
* '''Active''' - When set to 'Yes' this field will be displayed to the user.
  
 +
===Text (Multi-row)===
  
−
====Multi Row Text Input====
+
Displays a text box.
  
−
As "Single Row Alphanumeric String" but with no limitation on the field length.
+
[[File:EDBEF_5.png]]
  
−
* If required, choose a ''Default Value''' to be shown in the field when the user first sees it. Leave this field blank if there is no default value.
+
* '''Field Label''' - This is the name/question that is displayed to the user.
 +
* '''Description''' - This is displayed as help text via a question mark icon [[File:Icon_greenquestionmark.PNG]].  This is note displayed when the fields are displayed on a tab.
 +
* '''Field Type''' - Option to select which of these field types you wish to use.  Once a field has been created the field type cannot be changed.  Set to 'Text (multi-row)'.
 +
* '''Include in Reports''' - you can decide to include this data in reports.
 +
* '''Section''' - If you are using sections you can specify which section this option appears in.
 +
* '''Allow API access?''' - Used for API connections if your system links to a third party system.  When set to yes you can then specify the API name to be used.
 +
* '''Link to Spreadsheet''' - If the extension database is using excel to produce calculations and the field is as part of the calculations, specify the spreadsheet template that field is used to input in to.  See  [[Extension_Database_Setup#Spreadsheet_Calculations_Result|Spreadsheet Calculations Result]] for further details.
 +
* '''Spreadsheet cell''' - Specify the cell in the spreadsheet that the field feeds into.
 +
* '''Default Value''' - can be used if you want a value to be automatically entered into this field. Use with caution as people may have to delete any text before entering their own.
 +
* '''Field Entry Requirement'''
 +
** '''Not Required - No Rules''' - The field will be displayed but can be left blank.
 +
** '''Warning - Warn if not filled in''' - The field will be displayed with a yellow background (not when set as radio buttons) and a pop up will be displayed if left blank when saving.
 +
* '''Access Key''' - Specify a shortcut key so the user can jump directly to this field when entering data.  Please note that different operating systems and browser also use shortcut keys, this will override this option.
 +
* '''[[Display Order]]''' - The order that this field will be displayed on the page or section.
 +
* '''Active''' - When set to 'Yes' this field will be displayed to the user.
  
−
[[File:EDBEF_5.png|border]]
+
=Extension Database for Outcomes=
 +
Extension databases are widely used for things like outcomes or impact measurement tools. They are custom to your requirements, easy to input and provide useful data for reporting on outcomes/impact. The following webinar explains how they can be set up.  
  
 +
{{#ev:youtube|3o6codfk6Hk|790}}
  
−
====Select from Option Dropdown List====
+
=Extension Databases for Record Types=
 +
You can add custom fields to any of your record types such as Client, Staff, Volunteer etc. These are great to extend the reporting information or to simply add additional useful information to that record. The following webinar illustrates how this can be set up.
  
−
Lists set up here will display as a single dropdown list for the user to pick an option from. Add as many options as you need with the red "+" button and name them appropriately. They will automatically be created with display order of 10, 20, 30 etc.
+
{{#ev:youtube|_m9C5ET-b2Y|790}}
  
−
[[File:EDBEF_6.png|border]]
+
=Spreadsheet Calculations Result=
 +
The Spreadsheet Calculations Result field uses the fields linked to a spreadsheet template and adds a button for the user to calculate results based on the data entered.  The template needs to be upload via [[System Uploads]] and set to be used with extension databases.  When the user clicks the calculate button when entering data the system reads the formulas in the corresponding spreadsheet to return the result.  This allows administrators to add templates containing a wide range of formulas, including standard additions, subtractions, multiplications, IF Formulas, VLOOKUP formulas and more.  It is not possible to use conditional formatting, tables (including pivot) and array based formulas that require curly brackets {}.  The results that are produced from the calculations will be save when the record is saved. 
  
 +
{{#ev:youtube|7O3WNkUt_To|790}}
  
−
====Radio Button List====
+
Complete the following details:
  
−
As "Select from Option List", but options will be displayed as clickable buttons. Only one button can be selected at a time.
+
[[File:EDBEF_8ssc.PNG]]
  
−
* You can choose whether the buttons display "On one row" (side by side), or "Multi Rows/Stacked" (one above the other).
+
* '''Field Label''' - This is the name/question that is displayed to the user.
 +
* '''Description''' - This is displayed as help text via a question mark icon [[File:Icon_greenquestionmark.PNG]].  This is note displayed when the fields are displayed on a tab.
 +
* '''Appear in Section''' - If you are using sections you can specify which section this option appears in.
 +
* '''Allow collaborator API access?''' - Used for API connections if your system links to a third party system.  When set to yes you can then specify the API name to be used.
 +
* '''Field Type''' - Option to select which of these field types you wish to use.  Once a field has been created the field type cannot be changed.  Set to 'Numeric Only'.
 +
* '''Link to Spreadsheet''' - If the extension database is using excel to produce calculations and the field is as part of the calculation, specify the spreadsheet template that field is used to input in to.  See .
 +
* '''Spreadsheet cell''' - Specify the cell in the spreadsheet that contains the formula for the calculation.
 +
* '''Required input fields''' - Select the fields that are used with this calculation (these are determined by the formula in the spreadsheet).
 +
* '''Field length''' - This determines maximum number of digits (including decimal point), this works alongside the numeric maximum.
 +
* '''Decimal Places''' - Specify the number of decimal places that are required, please keep in mind the field length.
 +
* '''Default Value''' - Specify a default value that will be prepopulated.
 +
* '''Field Entry Requirement'''
 +
** '''Not Required - No Rules''' - The field will be displayed but can be left blank.
 +
** '''Warning - Warn if not filled in''' - The field will be displayed with a yellow background (not when set as radio buttons) and a pop up will be displayed if left blank when saving.
 +
* '''Numeric Minimum''' - The minimum value a user can enter.
 +
* '''Numeric Maximum''' - The maximum value a user can enter, this must comply with the field length.
 +
* '''Shortcut Key''' - Specify a shortcut key so the user can jump directly to this field when entering data.  Please note that different operating systems and browser also use shortcut keys, this will override this option.
 +
* '''[[Display Order]]''' - The order that this field will be displayed on the page or section.
 +
* '''Active''' - When set to 'Yes' this field will be displayed to the user.
  
−
[[File:EDBEF_7.png|border]]
+
===Example===
 +
In this example we are going to use a basic calculation by adding up some input fields to calculate a persons expenditure.  There are three fields, Rent, Food and Bills, and the calculator will add these up.  I an spreadsheet A1, A2 &a3 would be left blank and A4 will have the formula '''=A1+A2+A3''' or '''=SUM(A1:A3)'''.  The spreadsheet is the uploaded called expenditure. The fields are then set up as follows;
  
 +
'''Field 1'''
  
−
===Save Details===
+
[[File:CalcExtDBCalcF1.PNG]]
  
−
Click the "Save Details" button to save the field configuration.
 
  
−
===Filter by Field===
+
'''Field 2'''
  
−
Option Dropdown List fields and Radio Button fields have an extra feature; they can be filtered by another field within the extension database. This is how it works.
+
[[File:CalcExtDBCalcF2.PNG]]
  
−
Suppose you set up a field in the extension database which is to ask if there is a fire risk, as shown:
 
  
−
[[File:EDBEF_9.png|border]]
+
'''Field 4'''
  
 +
[[File:CalcExtDBCalcF3.PNG]]
  
−
You can then set up another field, where the options that are shown depend on what the user picks in the previous field. For example, if you said there was a risk, you can then ask if the person was awre.
 
  
−
Name the field, and in "Filter By Field", select the field you want to filter by.
+
'''Field 4'''
  
−
[[File:EDBEF_10.png|border]]
+
[[File:CalcExtDBCalcF4.PNG]]
  
  
−
Now, for each option added to the list, you can select when that option will show, based on what's in the other field.
+
'''User input field'''
  
−
We can have a list of three options, two of which show if "Yes" has been selected and one of which shows if "No" has been selected.
+
The user will be displayed the screen below, on clicking 'Calculate' the result will be display and saved when the save button is pressed.
  
−
[[File:EDBEF_11.png|border]]
+
[[File:CalcExtDBCalcResults.PNG]]
  
 +
=Filter Fields=
  
−
The user will see both fields when filling in the information, but the second field will not show anything (apart from "Unknown") until the first field has been selected.
+
For dropdown lists and radio button you can filter the options displayed from fields, depending what was selected in a previous field.  The diagram below shows two question.  If on question one the answer one is selected the for question two answers one and two will be available for selection.  If answer two was selected on question one then answers three and for will be selectable on question two.
  
−
[[File:EDBEF_12.png|border]]
+
[[File:ExtDB_filterdiag.png]]
  
 +
=Export/Import=
  
−
However, if the user selects "Yes", the second box will display the yes-related options:
+
You can also import an existing Extension Database into your system, saving you from creating it from scratch. This could be because you are working as part of a consortium and you all have to use the same outcome/assessment tool, or it could be because you have already set it up on your training/test site. Setting an extension database up on your [https://wiki.dizions.co.uk/index.php/Update_Replica_Databases Replica Database] is a great way to test if it functions correctly. You can do a trial run with some entries and ensure it reports correctly. Once its set up you can simply export the extension database from your replica site and import it into your live site.
  
−
[[File:EDBEF_13.png|border]]
+
The following video explains how this can be done.  
  
 +
{{#ev:youtube|CvpXlcRxeAU|790}}
  
−
...and if they select "dogs", the second box will display the dog-related options.
+
To start, click on the extension database you wish to export.  
  
−
[[File:EDBEF_14.png|border]]
+
[[File:EDBExport1.png]]
  
 +
You will then see a preview and you can Export.
  
−
It works the same way for Radio Button Lists. When nothing is selected in the first field, no options display in the second.
+
[[File:EDBExport2.png]]
  
−
[[File:EDBEF_15.png|border]]
+
Once exported, you can save the Excel file in a safe location for import later. To import, log into the system you wish to import the extension database to. Ensuring they are both on the same version. Click on the Import button at the top of the Extension Database Menu.  
  
 +
[[File:EDBExport3.png]]
  
−
If the user selects "cats", the second field displays the cat names:
+
You can then browse for the file, and select it for import.
  
−
[[File:EDBEF_16.png|border]]
+
[[File:EDBExport4.png]]
  
 +
You can now preview the fields which will be imported but you can go ahead and click on Import and it will be ready to use.
  
−
...and if they select "dogs", the second field will display the dog names.
+
[[File:EDBExport5.png]]
  
−
[[File:EDBEF_17.png|border]]
+
Once imported, you may find you need to amend relevant settings for Group Access along with which section you wish the extension database to appear.  
  
 +
[[File:EDBExport6.png]]
  
 
----
 
----
 
[[File:helpheader_small.png|right]]
 
[[File:helpheader_small.png|right]]
−
[[Category: Configuration]]
 

Latest revision as of 14:18, 9 May 2024

Helpheader small.png


Cl EDBS 1.png


Introduction to Extension Databases

Extension Databases provide a way for you to add fields, or sets of fields, to the system. They can be used to capture any information which is not catered for in the standard setup.

The Extension Database Setup page lists the Extension Databases that are already set up on your system (if there are any). You can amend existing Extension Databases and create new ones.

To create a new Extension Database click "New Extension Database" or click on the name of an existing Extension Database to edit it. Its important you understand the different locations available for an Extension Database. The below webinar explains this.

Extension Database Settings

On an existing Extension Database or when saving the Extension Database settings for a new one a navigation bar will appear at the top of the section:

ExtDB navbar.PNG

This allows you to move between the following sections of the setup:

  • The name of the Extension Database is underlined - This is the general details of where the extension data base will appear and which security groups can access it.
  • All Sections - Sections are used to group questions together to make data entry easier and more logical.
  • New Field - allows you to add new fields

To create a new Extension Database, click on New Extension Database

New Extension database.PNG

  • Extension Database Name- This is name of the Extension Database and will also be the button label that appears for the user.
  • Description - This is information about the Extension Database which can only be viewed on this page
  • One record or multiple records for this entry?
    • One Record - This displays a single set of fields, if the data is changed the previous entry will be simply over written. This would be used for questions like 'Do you drive?', if circumstances changed you would simply change the answer to the question.
    • One or More Records - This is used if multiple sets of data is required. As an example you could have an extension database to record donations given, as you may get more than one donation from the some person.
  • Linked to which main type of record?:
    • Organisations and People - This will be able to be displayed on a record tab or at the footer of the record.
    • Referrals - This allows the Extension Database to be displayed on the history tab, referral edit screen or appear when recording a contact. See Project Setup for further details.
    • Clubs and Clinics - This will appear on the footer of a Club, Clinic or Group details page, for those specified.
    • Clubs and Clinics Meetings - This will appear on the individual occurrence of the Club, Clinic or Group on the incomplete/complete meeting tabs and when a meeting is completed via the wizard or complete button.
  • Show Fields on Section - This option appears when the linked to option is set to 'Organisations and People'Specify weather the Extension Database is to be displayed on the required section Additional sections can be setup, see Customise Orgs & People for details.
  • Available to users in these groups - Select which Groups of users can access the Extension Database.
  • Maximum number of column Headers to be viewable when selecting from multiple records? - When selecting 'One or More' you can specify the number of columns displayed for the Extension Database.
  • Active? - When set to 'Yes' the Extension Database will be dis[played and can be reported on.

Once the above options have been completed you can then select one of the following save buttons:

  • Save and edit Sections - Saves any changes and moves to the page to create sections, if required.
  • Save and edit Fields - Saves any changes and moves to the page to create the fields to record the data.

Extension Database Sections

Section Headings will be displayed if you add an Extension Database to display One or More. They won't appear if you have just added fields to an existing section such as the Personal Details page. The following webinar gives an example of how section headings can be used for things like assessments.

ExtDB sections.PNG

Sections allow you to break up fields into logical sections. Example: 

Section 1: Assessment one
Date:
Range of questions

Section 2: Assessment two
Date:
Range of questions

To create a new section click on the lick 'Create a new Extension Database Section...'. Type the required label for the section and click go to create it.

The section will be created and added to the list of existing sections (if any).

To edit an existing section click on the sections name. From here you can specify if the heading;

  • Displays as a title, the name.
  • Displays as just a dividing line.
  • Displays as a blank line.

To delete a section click the Bin Icon Icon Bin.PNG.

Reordering Sections

Click on the 'Enable Reorder' button to Enable reordering, from here you can drag and drop the sections to the required position, or use the A-Z or Z-A option. Once complete you can then click 'Disable Reorder'.

ExtDB reorder.PNG

Extension Database Fields

An Extension Data base can contain a series of different field types. After you have completed the field options click 'Save Details' to create the field. As you click New Field, you will notice the field type is divided into categories as seen below.

Edbfieldcategories.png

The following webinar explains the different types of fields available.


Date and Time

Here you can add both date fields and Time fields

Date

Allows entry of a date, useful when reporting on certain periods.

EDBEF 4.png

  • Field Label - This is the name/question that is displayed to the user.
  • Description - This is displayed as help text via a question mark icon Icon greenquestionmark.PNG. This is note displayed when the fields are displayed on a tab.
  • Field Type - Option to select which of these field types you wish to use. Once a field has been created the field type cannot be changed. Set to 'Date'.
  • Include in Reports - allows you to report on data entered
  • Section - If you are using sections you can specify which section this option appears in.
  • Allow API access? - Used for API connections if your system links to a third party system. When set to yes you can then specify the API name to be used.
  • Link to Spreadsheet - If the extension database is using excel to produce calculations and the field is as part of the calculations, specify the spreadsheet template that field is used to input in to. See Spreadsheet Calculations Result for further details.
  • Spreadsheet cell - Specify the cell in the spreadsheet that the field feeds into.
  • Default Date Value - You can populate the field with the date the record is entered or left empty.
  • Field Entry Requirement
    • Not Required - No Rules - The field will be displayed but can be left blank.
    • Warning - Warn if not filled in - The field will be displayed with a yellow background (not when set as radio buttons) and a pop up will be displayed if left blank when saving.
  • Access Key - Specify a shortcut key so the user can jump directly to this field when entering data. Please note that different operating systems and browser also use shortcut keys, this will override this option.
  • Display Order - The order that this field will be displayed on the page or section.
  • Active - When set to 'Yes' this field will be displayed to the user.

Time

Allows entry of a time.

Timefield edb.png

  • Field Label - This is the name/question that is displayed to the user.
  • Description - This is displayed as help text via a question mark icon Icon greenquestionmark.PNG. This is note displayed when the fields are displayed on a tab.
  • Field Type - Option to select which of these field types you wish to use. Once a field has been created the field type cannot be changed. Set to 'Date'.
  • Include in Reports - allows you to report on data entered
  • Section - If you are using sections you can specify which section this option appears in.
  • Allow API access? - Used for API connections if your system links to a third party system. When set to yes you can then specify the API name to be used.
  • Link to Spreadsheet - If the extension database is using excel to produce calculations and the field is as part of the calculations, specify the spreadsheet template that field is used to input in to. See Spreadsheet Calculations Result for further details.
  • Default Time Value - allows you to specify a default value which will displayed when the extension database is created.
  • Field Entry Requirement
    • Not Required - No Rules - The field will be displayed but can be left blank.
  • Access Key - Specify a shortcut key so the user can jump directly to this field when entering data. Please note that different operating systems and browser also use shortcut keys, this will override this option.
  • Display Order - The order that this field will be displayed on the page or section.
  • Active - When set to 'Yes' this field will be displayed to the user.

Lists

Drop-Down

This will display a dropdown list that the user can select one option from.

EDBEF 6.png

  • Field Label - This is the name/question that is displayed to the user.
  • Description - This is displayed as help text via a question mark icon Icon greenquestionmark.PNG. This is note displayed when the fields are displayed on a tab.
  • Field Type - Option to select which of these field types you wish to use. Once a field has been created the field type cannot be changed. Set to 'Drop down'.
  • Include in Reports - allows you to report on data entered
  • Section - If you are using sections you can specify which section this option appears in.
  • Allow API access? - Used for API connections if your system links to a third party system. When set to yes you can then specify the API name to be used.
  • Link to Spreadsheet - If the extension database is using excel to produce calculations and the field is as part of the calculations, specify the spreadsheet template that field is used to input in to. See Spreadsheet Calculations Result for further details.
  • Spreadsheet cell - Specify the cell in the spreadsheet that the field feeds into.
  • Option Details - This is the items that will appear in the drop down list.
    • Default - When ticked this option will automatically be selected when entering a new record. It is worth considering if you want this value saved if the extension database is not manually changed before saving a client record.
    • Option Text - This is the name displayed in the dropdown list.
  • Filter By Field - This allows the dropdown list to be filtered by another drop down list (does not work with radio buttons). The options that have been selected on a previous question can filter what options can be selected on this question. From the list select the dropdown list that is to be used to filter this question.
  • Inactive Option Display
    • Do not display inactive options - Options that have been set as inactive will not be displayed in the dropdown list.
    • Display inactive options as not selectable - This will display inactive options, which cannot be selected by the user.
  • Field Entry Requirement
    • Not Required - No Rules - The field will be displayed but can be left blank.
    • Warning - Warn if not filled in - The field will be displayed with a yellow background (not when set as radio buttons) and a pop up will be displayed if left blank when saving.
  • Access Key - Specify a shortcut key so the user can jump directly to this field when entering data. Please note that different operating systems and browser also use shortcut keys, this will override this option.
  • Display Order - The order that this field will be displayed on the page or section.
  • Active - When set to 'Yes' this field will be displayed to the user.

Radio Button

As "Drop down lists", but options will be displayed as clickable buttons. Only one button can be selected at a time.

  • You can choose whether the buttons display "On one row" (side by side), or "Multi Rows/Stacked" (one above the other).

EDBEF 7.png

  • Field Label - This is the name/question that is displayed to the user.
  • Description - This is displayed as help text via a question mark icon Icon greenquestionmark.PNG. This is note displayed when the fields are displayed on a tab.
  • Field Type - Option to select which of these field types you wish to use. Once a field has been created the field type cannot be changed. Set to 'Radio Button'.
  • Include in Reports - allows you to report on data entered
  • Section - If you are using sections you can specify which section this option appears in.
  • Allow API access? - Used for API connections if your system links to a third party system. When set to yes you can then specify the API name to be used.
  • Link to Spreadsheet - If the extension database is using excel to produce calculations and the field is as part of the calculations, specify the spreadsheet template that field is used to input in to. See Spreadsheet Calculations Result for further details.
  • Spreadsheet cell - Specify the cell in the spreadsheet that the field feeds into.
    • Option Text - This is the name displayed in the dropdown list.
  • Option Details - This is the items that will appear in the drop down list.
    • Default - When ticked this option will automatically be selected when entering a new record.
    • Display Order - The order the option will be displayed in the dropdown list.
    • Selection list for the filter - Select which answers of the previously selected question that the current option is available to be selected. If you are using the filter and nothing is selected then the option cannot be selected. If the option is to be available to all then all answers to the previous question must be selected.
    • Active - If set to yes then this option can be selected from the drop down list.
    • Plus Icon Icon plus.PNG - Creates a new line for the next option.
  • Radio Button Display - Select the required display option.
  • Filter By Field - This allows the dropdown list to be filtered by another radio button field (does not work with dropdown lists). The options that have been selected on a previous question can filter what options can be selected on this question. From the list select the radio button field that is to be used to filter this question.
    • On one row - Ideal for less options and is displayed across the screen.
    • Multi rows/Stacked - Ideal for options with a long names or larger quantity of options.
  • Inactive Option Display
    • Do not display inactive options - Options that have been set as inactive will not be displayed in the dropdown list.
    • Display inactive options as not selectable - This will display inactive options, which cannot be selected by the user.
  • Field Entry Requirement
    • Not Required - No Rules - The field will be displayed but can be left blank.
    • Warning - Warn if not filled in - The field will be displayed with a yellow background (not when set as radio buttons) and a pop up will be displayed if left blank when saving.
  • Shortcut Key - Specify a shortcut key so the user can jump directly to this field when entering data. Please note that different operating systems and browser also use shortcut keys, this will override this option.
  • Display Order - The order that this field will be displayed on the page or section.
  • Active - When set to 'Yes' this field will be displayed to the user.

Organisations or People List

This field displays a look up list of Organisations and People on the system.

Extdb orgslist.png

  • Field Label - This is the name/question that is displayed to the user.
  • Description - This is displayed as help text via a question mark icon Icon greenquestionmark.PNG. This is note displayed when the fields are displayed on a tab.
  • Field Type - Option to select which of these field types you wish to use. Once a field has been created the field type cannot be changed. Set to 'Organisations and People List'.
  • Include in Reports - allows you to report on data entered
  • Types - Selected the required record types that will be available to be selected. Note you can select more than one.
  • Section - If you are using sections you can specify which section this option appears in.
  • Allow API access? - Used for API connections if your system links to a third party system. When set to yes you can then specify the API name to be used.
  • Field Entry Requirement
    • Not Required - No Rules - The field will be displayed but can be left blank.
    • Warning - Warn if not filled in - The field will be displayed with a yellow background (not when set as radio buttons) and a pop up will be displayed if left blank when saving.
  • Access Key - Specify a shortcut key so the user can jump directly to this field when entering data. Please note that different operating systems and browser also use shortcut keys, this will override this option.
  • Display Order - The order that this field will be displayed on the page or section.
  • Active - When set to 'Yes' this field will be displayed to the user.

Text and Numbers

URL

This will allow you to either, add a URL which is required as part of the extension database entry.

Urlfield edb.png

  • Field Label - This is the name/question that is displayed to the user.
  • Description - This is displayed as help text via a question mark icon Icon greenquestionmark.PNG. This is note displayed when the fields are displayed on a tab.
  • Field Type - Option to select which of these field types you wish to use. Once a field has been created the field type cannot be changed. Set to 'URL'.
  • Include in Reports - allows you to choose if this data will appear in reports.
  • Section - If you are using sections you can specify which section this option appears in.
  • Allow API access? - Used for API connections if your system links to a third party system. When set to yes you can then specify the API name to be used.
  • Link to Spreadsheet - If the extension database is using excel to produce calculations and the field is as part of the calculations, specify the spreadsheet template that field is used to input in to. See Spreadsheet Calculations Result for further details.
  • Spreadsheet cell - Specify the cell in the spreadsheet that the field feeds into.
  • Field length - This determines maximum number of characters allowed.
  • Default Value - Specify a default value that will be prepopulated.
  • Field Entry Requirement
    • Not Required - No Rules - The field will be displayed but can be left blank.
    • Warning - Warn if not filled in - The field will be displayed with a yellow background (not when set as radio buttons) and a pop up will be displayed if left blank when saving.
  • Access Key - Specify a shortcut key so the user can jump directly to this field when entering data. Please note that different operating systems and browser also use shortcut keys, this will override this option.
  • Display Order - The order that this field will be displayed on the page or section.
  • Active - When set to 'Yes' this field will be displayed to the user.

Email

Allows you to add an email address to the extension database.

Emailfieldv2.png

  • Field Label - This is the name/question that is displayed to the user.
  • Description - This is displayed as help text via a question mark icon Icon greenquestionmark.PNG. This is note displayed when the fields are displayed on a tab.
  • Field Type - Option to select which of these field types you wish to use. Once a field has been created the field type cannot be changed. Set to 'Email'.
  • Include in Reports - allows you to choose if this data will appear in reports.
  • Section - If you are using sections you can specify which section this option appears in.
  • Allow API access? - Used for API connections if your system links to a third party system. When set to yes you can then specify the API name to be used.
  • Link to Spreadsheet - If the extension database is using excel to produce calculations and the field is as part of the calculations, specify the spreadsheet template that field is used to input in to. See Spreadsheet Calculations Result for further details.
  • Spreadsheet cell - Specify the cell in the spreadsheet that the field feeds into.
  • Default Value - Specify a default value that will be prepopulated.
  • Field Entry Requirement
    • Not Required - No Rules - The field will be displayed but can be left blank.
    • Warning - Warn if not filled in - The field will be displayed with a yellow background (not when set as radio buttons) and a pop up will be displayed if left blank when saving.
  • Access Key - Specify a shortcut key so the user can jump directly to this field when entering data. Please note that different operating systems and browser also use shortcut keys, this will override this option.
  • Display Order - The order that this field will be displayed on the page or section.
  • Active - When set to 'Yes' this field will be displayed to the user.

Phone Number

Allows you to add a phone number to the extension database entry.

Phonenumber edb.png

  • Field Label - This is the name/question that is displayed to the user.
  • Description - This is displayed as help text via a question mark icon Icon greenquestionmark.PNG. This is note displayed when the fields are displayed on a tab.
  • Field Type - Option to select which of these field types you wish to use. Once a field has been created the field type cannot be changed. Set to 'Phone Number'.
  • Include in Reports - allows you to choose if this data will appear in reports.
  • Section - If you are using sections you can specify which section this option appears in.
  • Allow API access? - Used for API connections if your system links to a third party system. When set to yes you can then specify the API name to be used.
  • Link to Spreadsheet - If the extension database is using excel to produce calculations and the field is as part of the calculations, specify the spreadsheet template that field is used to input in to. See Spreadsheet Calculations Result for further details.
  • Spreadsheet cell - Specify the cell in the spreadsheet that the field feeds into.
  • Field Lenght - specify the length of characters that will be allowed in this field
  • Default Value - Specify a default value that will be prepopulated.
  • Field Entry Requirement
    • Not Required - No Rules - The field will be displayed but can be left blank.
    • Warning - Warn if not filled in - The field will be displayed with a yellow background (not when set as radio buttons) and a pop up will be displayed if left blank when saving.
  • Access Key - Specify a shortcut key so the user can jump directly to this field when entering data. Please note that different operating systems and browser also use shortcut keys, this will override this option.
  • Display Order - The order that this field will be displayed on the page or section.
  • Active - When set to 'Yes' this field will be displayed to the user.

Money

Allows only numbers to be used and totals on displayed reports.

EDBEF 3.png

  • Field Label - This is the name/question that is displayed to the user.
  • Description - This is displayed as help text via a question mark icon Icon greenquestionmark.PNG. This is note displayed when the fields are displayed on a tab.
  • Field Type - Option to select which of these field types you wish to use. Once a field has been created the field type cannot be changed. Set to 'Money'.
  • Include in Reports - allows you to decide if you would like to include this data in reports.
  • Section - If you are using sections you can specify which section this option appears in.
  • API access? - Used for API connections if your system links to a third party system. When set to yes you can then specify the API name to be used.
  • Link to Spreadsheet - If the extension database is using excel to produce calculations and the field is as part of the calculations, specify the spreadsheet template that field is used to input in to. See Spreadsheet Calculations Result for further details.
  • Spreadsheet cell - Specify the cell in the spreadsheet that the field feeds into.
  • Field length - This determines maximum number of digits (including decimal point), this works alongside the numeric maximum.
  • Decimal Places - Specify the number of decimal places that are required, please keep in mind the field length.
  • Default Value - Specify a default value that will be prepopulated.
  • Field Entry Requirement
    • Not Required - No Rules - The field will be displayed but can be left blank.
    • Warning - Warn if not filled in - The field will be displayed with a yellow background (not when set as radio buttons) and a pop up will be displayed if left blank when saving.
  • Numeric Minimum - The minimum value a user can enter.
  • Numeric Maximum - The maximum value a user can enter, this must comply with the field length.
  • Access Key - Specify a shortcut key so the user can jump directly to this field when entering data. Please note that different operating systems and browser also use shortcut keys, this will override this option.
  • Display Order - The order that this field will be displayed on the page or section.
  • Active - When set to 'Yes' this field will be displayed to the user.

Number

Allows only numbers to be used and totals on displayed reports.

EDBEF 2.png

  • Field Label - This is the name/question that is displayed to the user.
  • Description - This is displayed as help text via a question mark icon Icon greenquestionmark.PNG. This is note displayed when the fields are displayed on a tab.
  • Field Type - Option to select which of these field types you wish to use. Once a field has been created the field type cannot be changed. Set to 'Number'.
  • Include in Reports - allows you to choose if this data will appear in reports.
  • Section - If you are using sections you can specify which section this option appears in.
  • Allow API access? - Used for API connections if your system links to a third party system. When set to yes you can then specify the API name to be used.
  • Link to Spreadsheet - If the extension database is using excel to produce calculations and the field is as part of the calculations, specify the spreadsheet template that field is used to input in to. See Spreadsheet Calculations Result for further details.
  • Spreadsheet cell - Specify the cell in the spreadsheet that the field feeds into.
  • Field length - This determines maximum number of digits (including decimal point), this works alongside the numeric maximum.
  • Decimal Places - Specify the number of decimal places that are required, please keep in mind the field length.
  • Default Value - Specify a default value that will be prepopulated.
  • Field Entry Requirement
    • Not Required - No Rules - The field will be displayed but can be left blank.
    • Warning - Warn if not filled in - The field will be displayed with a yellow background (not when set as radio buttons) and a pop up will be displayed if left blank when saving.
  • Numeric Minimum - The minimum value a user can enter.
  • Numeric Maximum - The maximum value a user can enter, this must comply with the field length.
  • Access Key - Specify a shortcut key so the user can jump directly to this field when entering data. Please note that different operating systems and browser also use shortcut keys, this will override this option.
  • Display Order - The order that this field will be displayed on the page or section.
  • Active - When set to 'Yes' this field will be displayed to the user.

Text (Single-Row)

This will display a single line text box based on the field length.

EDBEF 1.png

  • Field Label - This is the name/question that is displayed to the user.
  • Description - This is displayed as help text via a question mark icon Icon greenquestionmark.PNG. This is note displayed when the fields are displayed on a tab.
  • Field Type - Option to select which of these field types you wish to use. Once a field has been created the field type cannot be changed. Set to 'Text (single-row)'.
  • Appear in Reports - allows you to specify if data entered will appear in your reports. Useful if you want to add a free text box, but don't need that data for reporting purposes.
  • Section - If you are using sections you can specify which section this option appears in.
  • Allow API access? - Used for API connections if your system links to a third party system. When set to yes you can then specify the API name to be used.
  • Link to Spreadsheet - If the extension database is using excel to produce calculations and the field is as part of the calculations, specify the spreadsheet template that field is used to input in to. See Spreadsheet Calculations Result for further details.
  • Spreadsheet cell - Specify the cell in the spreadsheet that the field feeds into.
  • Field length - This determines the length of the field and the maximum number of characters that can be entered.
  • Default Value - You can populate the field with the details specified.
  • Field Entry Requirement
    • Not Required - No Rules - The field will be displayed but can be left blank.
    • Warning - Warn if not filled in - The field will be displayed with a yellow background (not when set as radio buttons) and a pop up will be displayed if left blank when saving.
  • Access Key - Specify a shortcut key so the user can jump directly to this field when entering data. Please note that different operating systems and browser also use shortcut keys, this will override this option.
  • Display Order - The order that this field will be displayed on the page or section.
  • Active - When set to 'Yes' this field will be displayed to the user.

Text (Multi-row)

Displays a text box.

EDBEF 5.png

  • Field Label - This is the name/question that is displayed to the user.
  • Description - This is displayed as help text via a question mark icon Icon greenquestionmark.PNG. This is note displayed when the fields are displayed on a tab.
  • Field Type - Option to select which of these field types you wish to use. Once a field has been created the field type cannot be changed. Set to 'Text (multi-row)'.
  • Include in Reports - you can decide to include this data in reports.
  • Section - If you are using sections you can specify which section this option appears in.
  • Allow API access? - Used for API connections if your system links to a third party system. When set to yes you can then specify the API name to be used.
  • Link to Spreadsheet - If the extension database is using excel to produce calculations and the field is as part of the calculations, specify the spreadsheet template that field is used to input in to. See Spreadsheet Calculations Result for further details.
  • Spreadsheet cell - Specify the cell in the spreadsheet that the field feeds into.
  • Default Value - can be used if you want a value to be automatically entered into this field. Use with caution as people may have to delete any text before entering their own.
  • Field Entry Requirement
    • Not Required - No Rules - The field will be displayed but can be left blank.
    • Warning - Warn if not filled in - The field will be displayed with a yellow background (not when set as radio buttons) and a pop up will be displayed if left blank when saving.
  • Access Key - Specify a shortcut key so the user can jump directly to this field when entering data. Please note that different operating systems and browser also use shortcut keys, this will override this option.
  • Display Order - The order that this field will be displayed on the page or section.
  • Active - When set to 'Yes' this field will be displayed to the user.

Extension Database for Outcomes

Extension databases are widely used for things like outcomes or impact measurement tools. They are custom to your requirements, easy to input and provide useful data for reporting on outcomes/impact. The following webinar explains how they can be set up.

Extension Databases for Record Types

You can add custom fields to any of your record types such as Client, Staff, Volunteer etc. These are great to extend the reporting information or to simply add additional useful information to that record. The following webinar illustrates how this can be set up.

Spreadsheet Calculations Result

The Spreadsheet Calculations Result field uses the fields linked to a spreadsheet template and adds a button for the user to calculate results based on the data entered. The template needs to be upload via System Uploads and set to be used with extension databases. When the user clicks the calculate button when entering data the system reads the formulas in the corresponding spreadsheet to return the result. This allows administrators to add templates containing a wide range of formulas, including standard additions, subtractions, multiplications, IF Formulas, VLOOKUP formulas and more. It is not possible to use conditional formatting, tables (including pivot) and array based formulas that require curly brackets {}. The results that are produced from the calculations will be save when the record is saved.

Complete the following details:

EDBEF 8ssc.PNG

  • Field Label - This is the name/question that is displayed to the user.
  • Description - This is displayed as help text via a question mark icon Icon greenquestionmark.PNG. This is note displayed when the fields are displayed on a tab.
  • Appear in Section - If you are using sections you can specify which section this option appears in.
  • Allow collaborator API access? - Used for API connections if your system links to a third party system. When set to yes you can then specify the API name to be used.
  • Field Type - Option to select which of these field types you wish to use. Once a field has been created the field type cannot be changed. Set to 'Numeric Only'.
  • Link to Spreadsheet - If the extension database is using excel to produce calculations and the field is as part of the calculation, specify the spreadsheet template that field is used to input in to. See .
  • Spreadsheet cell - Specify the cell in the spreadsheet that contains the formula for the calculation.
  • Required input fields - Select the fields that are used with this calculation (these are determined by the formula in the spreadsheet).
  • Field length - This determines maximum number of digits (including decimal point), this works alongside the numeric maximum.
  • Decimal Places - Specify the number of decimal places that are required, please keep in mind the field length.
  • Default Value - Specify a default value that will be prepopulated.
  • Field Entry Requirement
    • Not Required - No Rules - The field will be displayed but can be left blank.
    • Warning - Warn if not filled in - The field will be displayed with a yellow background (not when set as radio buttons) and a pop up will be displayed if left blank when saving.
  • Numeric Minimum - The minimum value a user can enter.
  • Numeric Maximum - The maximum value a user can enter, this must comply with the field length.
  • Shortcut Key - Specify a shortcut key so the user can jump directly to this field when entering data. Please note that different operating systems and browser also use shortcut keys, this will override this option.
  • Display Order - The order that this field will be displayed on the page or section.
  • Active - When set to 'Yes' this field will be displayed to the user.

Example

In this example we are going to use a basic calculation by adding up some input fields to calculate a persons expenditure. There are three fields, Rent, Food and Bills, and the calculator will add these up. I an spreadsheet A1, A2 &a3 would be left blank and A4 will have the formula =A1+A2+A3 or =SUM(A1:A3). The spreadsheet is the uploaded called expenditure. The fields are then set up as follows;

Field 1

CalcExtDBCalcF1.PNG


Field 2

CalcExtDBCalcF2.PNG


Field 4

CalcExtDBCalcF3.PNG


Field 4

CalcExtDBCalcF4.PNG


User input field

The user will be displayed the screen below, on clicking 'Calculate' the result will be display and saved when the save button is pressed.

CalcExtDBCalcResults.PNG

Filter Fields

For dropdown lists and radio button you can filter the options displayed from fields, depending what was selected in a previous field. The diagram below shows two question. If on question one the answer one is selected the for question two answers one and two will be available for selection. If answer two was selected on question one then answers three and for will be selectable on question two.

ExtDB filterdiag.png

Export/Import

You can also import an existing Extension Database into your system, saving you from creating it from scratch. This could be because you are working as part of a consortium and you all have to use the same outcome/assessment tool, or it could be because you have already set it up on your training/test site. Setting an extension database up on your Replica Database is a great way to test if it functions correctly. You can do a trial run with some entries and ensure it reports correctly. Once its set up you can simply export the extension database from your replica site and import it into your live site.

The following video explains how this can be done.

To start, click on the extension database you wish to export.

EDBExport1.png

You will then see a preview and you can Export.

EDBExport2.png

Once exported, you can save the Excel file in a safe location for import later. To import, log into the system you wish to import the extension database to. Ensuring they are both on the same version. Click on the Import button at the top of the Extension Database Menu.

EDBExport3.png

You can then browse for the file, and select it for import.

EDBExport4.png

You can now preview the fields which will be imported but you can go ahead and click on Import and it will be ready to use.

EDBExport5.png

Once imported, you may find you need to amend relevant settings for Group Access along with which section you wish the extension database to appear.

EDBExport6.png

Helpheader small.png
Retrieved from ‘https://wiki.dizions.co.uk/index.php?title=Extension_Database_Setup&oldid=17445’