1 Reply by burkmarr

  • burkmarr
  • burkmarr
  • FSC BioLinks Digital Development Officer
  • Offline
  • From: Preston Montford
  • Registered: 14-05-2006
  • Posts: 105

Re: Reporting common names consistently

When running a report in R6 I can report scientific names for records consistently by making use of the 'Recommended Taxon Name' option in the wizard. This always returns the same scientific name for a taxon regardless of how the individual records were recorded.

I'd like to do the same for common names, but the 'Taxon Common Name' options in the report wizard returns various names for the same taxon - I suppose depending upon how the record was originally recorded.

Has anyone overcome this problem?

Rich

Richard Burkmar
Biodiversity Project Officer
Field Studies Council

burkmarr's Website

2 Reply by charliebarnes

  • charliebarnes
  • Veteran
  • Offline
  • From: Greater Lincolnshire
  • Registered: 07-09-2008
  • Posts: 1,396

Re: Reporting common names consistently

The only way I know of doing this is if you pull out the common name associated with the recommended taxon name via SQL. I don't think it can be done via the Report Wizard?

Charlie Barnes
Information Officer
Greater Lincolnshire Nature Partnership

charliebarnes's Website

3 Reply by MikeWeideli

  • MikeWeideli
  • Administrator
  • Offline
  • From: Littlefield Consultancy
  • Registered: 05-07-2006
  • Posts: 2,645

Re: Reporting common names consistently

The following SQL will set up a Report Wizard entry for Recommended Common Name. Dictionary issues may mean that on occasions it may be the scientific name, but it will eliminate duplicates. . It only adds entries so risk is minimal but,  as with all change of this type please take a backup before running and then make sure your report wizard works.

USE NBNData
INSERT INTO REPORT_ATTRIBUTE (REPORT_ATTRIBUTE_KEY,    ITEM_GROUP,    SOURCE_TABLE,    ITEM_NAME,    ATTRIBUTE_SQL,    REPORT_JOIN_KEY, ENTERED_BY,    ENTRY_DATE,    SYSTEM_SUPPLIED_DATA)
VALUES ('LC00000100000101',    'Taxon',    'TAXON_LIST',    'Recommended Common Name',    '#REPORT_OUTPUT.[Recommended Common Name] = Index_Taxon_Name.Common_name',    'NBNSYS0000000039', 'LC00000100000001' , Getdate(),0)
INSERT INTO REPORT_FIELD (REPORT_FIELD_KEY,    REPORT_ATTRIBUTE_KEY,    FIELD_ITEM_NAME,    FIELD_TYPE,    FIELD_SIZE,    ENTERED_BY,    ENTRY_DATE,SYSTEM_SUPPLIED_DATA)
VALUES ('LC00000100000101',    'LC00000100000101',    'Recommended Common Name',    'varchar', 100, 'LC00000100000001',getdate()    ,0)

Mike

Mike Weideli

MikeWeideli's Website

4 Reply by burkmarr

  • burkmarr
  • burkmarr
  • FSC BioLinks Digital Development Officer
  • Offline
  • From: Preston Montford
  • Registered: 14-05-2006
  • Posts: 105

Re: Reporting common names consistently

Thanks Charlie and Mike. Mike I must owe you a lot of pints of beer by now, but I supose that I am not the only one in your debt. It worked a treat.

The only fly in the ointment is that some of the common names associated with the recommended taxon records aren't really to be recommended (!) - e.g. 'Sky Lark' - but hey, you can't have everything. Consistency is probably more important for reporting purposes.

(I didn't indicate in the original post why I wanted to do this: it is because I'm generating summary tables in reports which can mean summarising several records for one taxon with a single line - so a choice has to be made if the records are represented by different names.)

Richard Burkmar
Biodiversity Project Officer
Field Studies Council

burkmarr's Website