Donorfy comes with standard list views, so that when you create a new list the data within that List is based on that single view, e.g. Constituent, Activity, Transaction and so on.
With Custom List Views, you can create your own specialised views for the way you want to query the data within your Donorfy.
Custom list views enable you to query your data at scale by optimising the performance of that List by streamlining what data is being recalled, i.e your specified data fields only, and not peripheral information that you do require.
Custom Lists view is a Professional-only feature available to those organisations who also have an OwnAzure subscription. If, after reading through this documentation you feel that Custom List Views is right for your charity or organisation please contact us to find out more about upgrading.
Custom List view creation steps
To add a custom list view you need to:
- Create a SQL View to provide the data for the list - this is a technical task so you might need the help of a Donorfy partner - more information is provided below
- Add the details of the View in Donorfy in Settings > Configuration > Custom Lists
Creating your SQL View
Use SQL Management Studio to create a view. The view must:
- Be created in the schema called [custom]
- Contain the following standard columns (making sure the data types and lengths are correct)
- [FK_TenantId] - Guid - this Id represents the organisation using Donorfy
- [EntityId] - Guid - this is the Id of an existing item in Donorfy you can link to
- [Entity] - nvarchar(20) e.g. Constituent, Campaign, Opportunity
- [EntitySubType] - nvarchar(50) - can be a subtype of the main entity - e.g. a Constituent Type - or can be empty
- [EntityNumber] - bigint - e.g. Constituent Number
- [Status] - nvarchar(10) - whether the item is active or not - usually based on the IsActive column
- [Description] - nvarchar(100) - a description of the item returned by the List
- [Id] - Guid - the id of the item returned by the List - often the same as[EntityId]
- [Type] - nvarchar(50) - the type - e.g. an Opportunity Type - can empty or the same as [Entity] or [EntitySubType]
When the List results are displayed you will need a column in your view which contains the Id of the Donorfy entity which should be opened when an item in the list is clicked, you can open a Constituent, Campaign or Opportunity, this column must:
- Be a Guid
- Should not end in Id
- Should not contain _FK_
- Suggested name for this column is [LinksTo]
Add other columns which you want to make available in the list - do not use column names ending Id or containing _FK_
You can use standard Donorfy views (e.g. [dbo].[vConstituentBase]) in your view. You can also look at the standard views used by lists to better understand how to write your view - the standard views are named v<ListType>Filter e.g. vConstituentFilter
Allowing Dropdowns in List Filters
If you name the columns in your view using the same names as the standard Donorfy views then a dropdown will be made available for them when building a filter, e.g. if you include the Product column in your views you should name the column Transaction_Product
Add the details of the View to the Custom Lists
In Settings > Configuration > Custom Lists add a new entry, as per the example below:
- Description - this will appear on the Add menu under the Lists column.
Your Custom List descriptions should not be the same as the standard Donorfy List Types
- Custom List Type - indicates what type of entity should be opened when an item in the list is clicked
- View Name - is the name of your SQL View - including the schema, with square brackets as per the example.
- Default Sort Column - is the column that the Lists based on this view will be sorted by. With or without square brackets.
- Link EntityId Column - is the name of the column that contains the Id of the item to open when an item in the List is clicked e.g. LinksTo. Note: square brackets are not required on this field.
Changing the underlying view
If you make changes to your SQL View, e.g. adding or removing columns:
- Open the Custom List definition in Settings > Configuration > Custom Lists
- Press the Refresh button to update Donorfy with your changes
Your new List will appear on the Add menu as shown in the example below.
You may need to sign out and back in again before you can see them.