Re: Adding attributes to Report Wizard (and a dictionary problem too)
I know nothing about micro-moths, or their taxonomy, but I think that I have found an error in the dictionary. Of course I could be wrong because the dictionary is so complicated that I don't know what to do to check it.
I have just added a new attribute to the report wizard to return Bradley & Fletcher codes for micro moths. These codes are held in TAXON_LIST_ITEM.LST_ITM_CODE so should be straightforward to extract. Before anyone points it out, I am aware that this field also contains various other types of codes for other taxa, but at present I am not interestedin them).
My JOIN SQL looks like this
FROM #REPORT_OUTPUT INNER JOIN INDEX_TAXON_NAME
ON #REPORT_OUTPUT.LIST_ITEM_KEY = INDEX_TAXON_NAME.TAXON_LIST_ITEM_KEY
INNER JOIN TAXON_LIST_ITEM
ON INDEX_TAXON_NAME.RECOMMENDED_TAXON_LIST_ITEM_KEY = TAXON_LIST_ITEM.TAXON_LIST_ITEM_KEY
And the ATTRIBUTE_SQL is
#REPORT_OUTPUT.[Bradley and Fletcher codes] = TAXON_LIST_ITEM.LST_ITM_CODE
I realise that it is probably desirable to also filter for system supplied data on both tables and also to check that #REPORT_OUTPUT.TYPE = 'T', but I am not quite sure about the syntax for this so I will keep checking, any advice on this will be gratefully received.
I ran the wizard & selected all taxa from the LEPIDOPTERA (micro) list and got nearly 17000 records back, then repeated the process with the new attribute included and got the same number, so far so good.
However on closer inspection 56 records returned no value for my new attribute. All of those were either higher taxa (mostly family level), or records of a single species for which the preferred name was returned as Blastobasis adustella which is in the list of additional names.
Closer inspection of the offending records in the observations hierarchy revals that they are reported there as Blastobasis lignea. The LEPIDOPTERA list gives these as synonyms with lignea preferred.
What puzzles me is this. If I inspect a single occurrence I find the TLIK used is NHMSYS0000495791. If I put this into the INDEX_TAXON_NAME table it gives both the actual and preferred names as B. lignea and the Recommended TLIK as NHMSYS0020327125. However if I put this RTLIK backinto ITN as the TLIK it returns both actual and preferred as B. adustella and the RTLIK as NHMSYS0020327125 again.
Surely if they are synonyms then the preferred name and the recommended TLIK should be the same in both cases no?
Also it would appear that the original determination was as B. lignea (as reported by the observation heirarchy), the preferred name according to the preferred checklist. As far as I can tell all 30 or so records of this taxon have been recorded against this TLIK.
I have chosen to use the ITN table for the attribute because without it the report returns about 1500 records without a B&F code instead of 56.
So ultimately there are two questions 1. Can someone suggest any ways I might improve the join sql above? and 2. am I correct that this is a dictionary error (in which case I will post it over there instead?
Wildlife Sites Officer
Wiltshire & Swindon Biological Records Centre