1

Topic: Find Taxon in Taxon Dictionary Browser

Is it possible to sort the results of a search in the Taxon Dictionary Browser so that a species appears before any of its sub species, forms, variants and before any sensu lato results?  Attached is an example where the sort order is rather confusing because the species appears after any variants but before the sensu lato entry.

Andy Foy
Systems Manager
Greenspace Information for Greater London (GiGL) CIC
www.gigl.org.uk

2

Re: Find Taxon in Taxon Dictionary Browser

Hi

The sort is being done by the list box  based only on the content. The name as listed comes from  [dbo].[ufn_FormattedSpeciesName] so it might be possible to do something in this UDF to give better results.

Try this simple change and see if it helps with the ones you have identified.

USE [NBNData]
GO
/****** Object:  UserDefinedFunction [dbo].[ufn_FormattedSpeciesName]    Script Date: 07/19/2019 20:11:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

/*===========================================================================*\
* Description:    Returns the formatted species Name.
*
* Parameters:    @ActualName         ITN.Actual_Name,
*                @Authority             ITN.Authority ,
*                @PreferredAuthority    ITN.Authority where ITN.Taxon_List_Item_Key = TLI.Preferred_Name,
*                @PreferredName        ITN.Preferred_Name,
*                @ActualItalic         ITN.Actual_Name_Italic,
*                @PreferredItalic    ITN.Preferred_Name_Italic,
*                @Attribute            ITN.Attribute,
*                @RankName            ITN.Short_Name
*              @CanExpand            BIT,
*              @CanExpandChar      VARCHAR(50)
* AUTHOR:    Qing Sun, Dorset Software
* CREATED: 25/11/2008
* Modified for Mantis 594  November 2015
" Modified for Mnatis 611  September 2016
\*===========================================================================*/
ALTER FUNCTION [dbo].[ufn_FormattedSpeciesName]
( @ActualName             VARCHAR(100),
  @Authority             VARCHAR(100),
  @PreferredAuthority    VARCHAR(100),
  @PreferredName        VARCHAR(100),
  @ActualItalic         BIT,
  @PreferredItalic        BIT,
  @Attribute            VARCHAR(100),
  @RankName                VARCHAR(100),
  @CanExpand            BIT,
  @CantExpandSuffix       VARCHAR(50))
RETURNS varchar(200)
AS

BEGIN
    DECLARE    @FormattedName     VARCHAR(200)
   
    SET  @FormattedName = ''
    SET  @ActualName = dbo.LCRemoveSubGenusText(@actualName)
    IF @CanExpand = 1  SET
       @CantExpandSuffix = NULL    
   
    IF @ActualItalic = 1
        SET @FormattedName = '<i>' + @ActualName + ' </i>'
    ELSE
        SET @FormattedName = @ActualName

    SET @FormattedName = @FormattedName +  ISNULL(' ' + @Attribute, '') + ISNULL(' ' + @Authority, '') + ISNULL(' [' + @RankName + ']', '')

    IF @ActualName <> @PreferredName
    BEGIN
       
        IF @PreferredItalic = 1
            SET @FormattedName = @FormattedName + ' (' + '<i>' + @PreferredName + '</i>' + ISNULL(' ' + @PreferredAuthority, '') + ')'
        ELSE
            SET @FormattedName = @FormattedName + ' (' + @PreferredName + ISNULL(' ' + @PreferredAuthority, '') + ')'
    END   
   
    SET @FormattedName = @FormattedName + ISNull(@CantExpandSuffix,'')
   
    RETURN @FormattedName
END

Mike Weideli
Littlefield Consultancy - IT Consultants

3

Re: Find Taxon in Taxon Dictionary Browser

Hi Mike

That's perfect.  Using "agrostis canina" as a search example, before that change it sorted the matches in the following order:

Agrostis canina agg.
Agrostis canina canina fascicularis
Agrostis canina montana arida
Agrostis canina subsp. canina
Agrostis canina subsp. fascicularis
Agrostis canina subsp. montana
Agrostis canina var. arida
Agrostis canina
Agrostis canina  sensu latu

After applying that change it sorts the matches in the following order:

Agrostis canina
Agrostis canina  sensu latu
Agrostis canina agg.
Agrostis canina canina fascicularis
Agrostis canina montana arida
Agrostis canina subsp. canina
Agrostis canina subsp. fascicularis
Agrostis canina subsp. montana
Agrostis canina var. arida

Thanks
Andy

Andy Foy
Systems Manager
Greenspace Information for Greater London (GiGL) CIC
www.gigl.org.uk

4

Re: Find Taxon in Taxon Dictionary Browser

Hi Mike

Can this fix be added to the next release to save having to manually apply it after each upgrade?

Many thanks
Andy

Andy Foy
Systems Manager
Greenspace Information for Greater London (GiGL) CIC
www.gigl.org.uk