1

Topic: Dictionary Upgrade 0000003Y to 00000040

Dictionary Upgrade 0000003Y to 00000040 it now available at http://jncc.defra.gov.uk/page-4615 . Please note that it can only be applied to Recorder v 6.26.2 or above. Applying them to other version will corrupt table Index_Taxon_Name.

Mike Weideli

2

Re: Dictionary Upgrade 0000003Y to 00000040

Thanks Mike - I missed this one.

Charlie Barnes
Information Officer
Greater Lincolnshire Nature Partnership

3 (edited by Graham Hawker 18-12-2017 13:58:37)

Re: Dictionary Upgrade 0000003Y to 00000040

This upgrade claims to include updates to the designation dictionary but the Birds of Conservation Concern are still wrong. It mentions species on version 4 but it looks like some species that changed between version 3 or 4 haven't been updated.

Graham Hawker
Thames Valley Environmental Records Centre

4

Re: Dictionary Upgrade 0000003Y to 00000040

I suggest that you email Mary Camplin at JNCC direct Mary Campling Mary.Campling@jncc.gov.uk. The dictionary in R6 will be in line with the NHM Dictionary,and the responsilbilty for keeping the deignations up to date is with JNCC. This isn't just an R6 issue as the NHM dictionary is used by the NBN Atlas and Indicia, so it should  be looked at.

Mike Weideli

5

Re: Dictionary Upgrade 0000003Y to 00000040

Hi,
our IT just did the dictionary update and according to the "About Recorder6" window it is still 0000003X. We are on Database version 6.26.2.286.
Does that mean, the update didn't work or was just the version number not updated as our IT assumes?
Is it possible to look into one of the tables to see, if the updates came through?

Thanks,
Wolfgang

6

Re: Dictionary Upgrade 0000003Y to 00000040

Hi
I think this means it hasn't worked. There is a new list 'CEH CheckList of Freshwater Life'. Try and find this on the dictionary list. If this isn't there the upgrade didn't work.  Because it appears to have worked and there were no errors, the first thing I suggest is that the Scripts folder is checked to ensure that the files needed are there. There should be six files 0000003Y.zip, 0000003Y.ini,00000003Z.zip, 0000003Z.ini and 00000040.sql. Earlier files may also be there, but not the downloaded file 0000003Yto40.zip

Mike

Mike Weideli

7

Re: Dictionary Upgrade 0000003Y to 00000040

Hi
I can see the new CEH checklist, but I have an issue with the newly added BSBI England Red List statuses:

SELECT     Index_Taxon_Designation.Taxon_List_Item_Key, Index_Taxon_Designation.Taxon_Designation_Type_Key, TAXON_DESIGNATION_TYPE.Status_Abbreviation
FROM         Index_Taxon_Designation INNER JOIN
                      TAXON_DESIGNATION_TYPE ON Index_Taxon_Designation.Taxon_Designation_Type_Key = TAXON_DESIGNATION_TYPE.TAXON_DESIGNATION_TYPE_KEY
WHERE     (Index_Taxon_Designation.Taxon_List_Item_Key = 'NHMSYS0020519614')

returns the following for Lady's Slipper Orchid:
Taxon_List_Item_Key    Taxon_Designation_Type_Key Status_Abbreviation
NHMSYS0020519614    NBNSYS0000000017                 WCA8
NHMSYS0020519614    NBNSYS0000000059                 Bern1
NHMSYS0020519614    NBNSYS0000000063                 HSD2p
NHMSYS0020519614    NBNSYS0000000064                 HSD4
NHMSYS0020519614    NBNSYS0000000089                 CITESB
NHMSYS0020519614    NBNSYS0100000012                 NR-excludes
NHMSYS0020519614    NBNSYS0100000013                 Sect.41
NHMSYS0020519614    NBNSYS0100000021                 HabRegs5
NHMSYS0020519614    NHMSYS0020443050                 UKBAP
NHMSYS0020519614    NHMSYS0020443053                 RLGB.CR
NHMSYS0020519614    NHMSYS0021177873                 RLENG.CR - here is the England Red List status

So the status info is there, the only thing that is missing is an entry for the Taxon_Designation_Type_Key NHMSYS0021177873 in the Taxon_Designation_Set_Item table as the following returns nothing:

SELECT     Taxon_Designation_Set_Item_Key, Taxon_Designation_Set_Key, Taxon_Designation_Type_Key, Entered_By, Entry_Date, Changed_By, Changed_Date,
                      System_Supplied_Data, Custodian
FROM         NBNData.dbo.Taxon_Designation_Set_Item
WHERE     (Taxon_Designation_Type_Key = 'NHMSYS0021177873')

I'm on R6.26.2.286 which is supposed to be compatible with the latest dictionary update but it looks as though it didn't populate the Taxon_Designation_Set_Item table with the new BSBI statuses, making them impossible (without editing a table in SQL Server) to use from the R6 report wizard and dbo.ufn_GetDesignations SQL function.

Thanks

Ian

8

Re: Dictionary Upgrade 0000003Y to 00000040

(in case it's of any use, I get the same results as Ian from those SQL queries; we're on 6.26.2.286 as well)

Charlie Barnes
Information Officer
Greater Lincolnshire Nature Partnership

9

Re: Dictionary Upgrade 0000003Y to 00000040

As it stands  JNCC are responssible for initiating changes to the system supplied Taxon_designation_Set table, but there is no process in place for this. I will obtain clearance from JNCC to make the change and issue a dictionary upgrade. Can someone confirm which of the system supplied  set(s) it should  be in please.

Mike Weideli

10

Re: Dictionary Upgrade 0000003Y to 00000040

Thanks Mike, my guess is that they should be added to the IUCN and All Designated Species (excl Northern Ireland) designation sets unless anyone else has other ideas?

11

Re: Dictionary Upgrade 0000003Y to 00000040

Hi folks, we've (thanks Alex!) done some checking of the England Red List Statuses and there are some discrepancies between the R6 interpretation and the original BSBI list. I've listed a couple of examples below.

Hieracium argutifolium in R6 as a synonym for Hieracium sabaudum but both listed as separate taxa in BSBI England Red List as Near Threatened and LC respectively.

Both Valeriana officinalis subsp. collina and Valeriana officinalis subsp. sambucifolia are in R6 as Near Threatened, but listed as WL in BSBI England Red List - is the R6 status being inherited from Valeriana officinalis which is Near Threatened in both R6 and BSBI list?

Gentianella amarella subsp. septentrionalis has two England Red List statuses in R6 RLENG.VU and RLENG.Lr(NT) the BSBI document lists it as Vulnerable.

Should we send the rest of the discrepancies to Mary?

Thanks

Ian

12

Re: Dictionary Upgrade 0000003Y to 00000040

Attached is a dictionary upgrade which will add the designation type to the desgnation set. Copy this into the Scripts folder and run a normal dictionary upgrade Afterwards rebuild  just the Taxon Designation Index. There is no need to update the other indexes. If someone can let me know if it does what is required I will make it generally available.

Problems with Designations in general should be reported to JNCC as these affect all dictionary users. If the problem appears to be related only to the way R6 uses the data then these issues will need to be picked  up as part of R6 support.  Taxon designation sets relate only to R6 so they will also  have to be picked up as part of R6 dictionary support.

Post's attachments

00000041.sql 830 b, 2 downloads since 2018-02-05 

You don't have the permssions to download the attachments of this post.
Mike Weideli

13

Re: Dictionary Upgrade 0000003Y to 00000040

Thanks Mike, I've run it on our copy. It enables RedList_ENG_post2001-CR to be reported against using the R6 wizard and shows up with the sql query I posted earlier for Lady's Slipper Orchid. Can you add the following to the script so that the other categories of conservation concern can be reported against please?

TAXON_DESIGNATION_TYPE_KEY    SHORT_NAME
NHMSYS0021177874                            RedList_ENG_post2001-EN
NHMSYS0021177875                            RedList_ENG_post2001-VU
NHMSYS0021177878                            RedList_ENG_post2001-NT

I've left off the Data Deficient, Least Concern and Regionally Extinct categories as we tend not to use them in our reporting, but others my wish them to be included too. Please speak up if you want them in!

14

Re: Dictionary Upgrade 0000003Y to 00000040

Hi

The attached should implement the three types requested.  Put the file in the Scripts folder and run as a normal dictionary upgrade. Only the Taxon Designation Index needs to be rebuilt. If you havn't run 0000041 then this must be in the scripts folder with 0000042.

Having investigated this a bit further it could be that some other more recently added types are not reflected  correctly in the Taxon Designation Sets. This could be why some designations are not coming out as expected.

JNCC  set these up and would currently be responsible for updating them and I have asked them to look at this, but this may not happen. I will make the changes if someone with an understanding of the situation could advise what needs to be done. I attach two speadsheets to assist with this. The first lists the available Taxon Designation Types and shows those not used in sets. The second shows all the current system supplied designation sets and the Taxon Designation Types allocated to them

Post's attachments

00000042.sql 2.36 kb, 2 downloads since 2018-02-07 

QryTaxonDesignationTypes.xlsx 22.27 kb, file has never been downloaded. 

SystemSuppliedTDSets.xlsx 32.82 kb, file has never been downloaded. 

You don't have the permssions to download the attachments of this post.
Mike Weideli