This feature is only available on the Professional Plan to those organisation who also have an OwnAzure subscription.

Why create a custom list view?

Donorfy comes with standard list views, so that when you create a new list you can base it on those views - eg Constituent, Activity, Transaction and so on.

If you use Donorfy with your own Azure database you can add your own list views to Donorfy, specialised for the way you want to query your Donorfy.

Another reason for creating custom list views is to optimise performance by streamlining them to contain just the data that you want, and not the information that you don't want. This enables you to query your data at scale.

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 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 - then open the custom list definition in Settings and 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.