1

Re: NBN Exchange export addin

Hello all

Craig Slawson at Staffordshire Ecological Record is having problems with using the NBN Exchange export addin to send data to the NBN Gateway. He is currently using the latest version of the addin (version 6.13.0.14) and Recorder 6 (version 6.13.2.176), connected to SQLServer 2005 Express.

'On running the export, it goes through all the 'External Validation Checks' and then ... nothing no further messages in the bottom bar,
the pop-up window remains with the 'Cancel' button active, but no further activity - pressing cancel works and  the popup disappears'

Has anyone had a similar problem with this addin?

On the whole since the release of the addin (http://forums.nbn.org.uk/recorderWebsite/Addins.htm) most Recorder users have had no problems with using it to send me data in the NBN Exchange Format, to load onto the NBN Gateway. I would however encourage anyone with problems with this addin to post it onto this forum so that I, and JNCC, get an idea of any issues that need to be resolved. I look at the forum everyday so can quickly pick up any problems that are raised

Best wishes

Graham French
NBN Trust

2

Re: NBN Exchange export addin

I've just encountered a problem. I've extracted some data but if there is location name data then this is placed in the site name (location) column and the site name is missing.

Graham Hawker
Thames Valley Environmental Records Centre

3

Re: NBN Exchange export addin

Thanks for raising this issue. I have just tried it myself with the latest version of the NBN Exchange Addin (v6.13.0.14).

I too found that only the location name in the sample location name field  is extracted and not the site name in the location hierarchy.

Initial versions of the addin concatenated these two fields together into one 'SiteName' field, but this approach lead to duplication of the site name. The latest version of the addin takes a slightly different approach, populating the sitename field with the details of the location name in the sample location name field (STEP 1), then if this is empty, populating the sitename field with the site name in the location hierarchy (firstly tries to do this by linking from the Sample table (STEP2) and if still empty then tries it linking from the Survey Event table (STEP3)). The problem is that the addin is doing the STEP 1 ok but not STEP 2 or STEP 3.

To solve this I edited the stored procedure (nbn_exchange_basic_update) which run the T-SQL to do these steps, adding “OR LEN(SiteName) =0” to the where clauses as shown below.

-- *********
-- Site Name
-- *********
-- Get the SiteName from the LOCATION_NAME where we can
UPDATE ##nbn_exchange_obs
SET SiteName = SAMPLE.LOCATION_NAME
FROM ##nbn_exchange_obs INNER JOIN
SAMPLE ON ##nbn_exchange_obs.SampleKey = SAMPLE.SAMPLE_KEY;

-- If it is still null, try getting it from the SAMPLE.LOCATION_KEY
UPDATE ##nbn_exchange_obs
SET SiteName = LOCATION_NAME.ITEM_NAME
FROM ##nbn_exchange_obs INNER JOIN
SAMPLE ON ##nbn_exchange_obs.SampleKey = SAMPLE.SAMPLE_KEY
INNER JOIN LOCATION_NAME ON SAMPLE.LOCATION_KEY = LOCATION_NAME.LOCATION_KEY AND LOCATION_NAME.PREFERRED = 1
WHERE SiteName Is Null OR LEN(SiteName) =0;

-- Finally, try getting it from the SURVEY_EVENT.LOCATION_KEY
UPDATE ##nbn_exchange_obs
SET SiteName = LOCATION_NAME.ITEM_NAME
FROM ##nbn_exchange_obs INNER JOIN
SAMPLE ON ##nbn_exchange_obs.SampleKey = SAMPLE.SAMPLE_KEY
INNER JOIN SURVEY_EVENT ON SAMPLE.SURVEY_EVENT_KEY = SURVEY_EVENT.SURVEY_EVENT_KEY
INNER JOIN LOCATION_NAME ON SURVEY_EVENT.LOCATION_KEY = LOCATION_NAME.LOCATION_KEY AND LOCATION_NAME.PREFERRED = 1
WHERE SiteName Is Null OR LEN(SiteName) =0;

I will raise this issue with JNCC so that this is changed for the next release of Recorder. In the meantime if you can not change the stored procedure yourself then my other suggestion is to run the NBN Exchange Format addin as normal, and then additonally run an export using the wizard to extract the site name along with the taxonoccurrencekey, this can then be merged back in the NBN Exchange Format file. To do this I exported the taxonoccurencekey (=obskey), location name field (=Sample location Name or Event Location name) and sitename in location hierarchy (=Sample location or Event location). These additional columns then need to be concatenated together and put in with the other NBN Exchange fields. This can be a bit fiddly so I can help with this step if you wish.

Finally in the NBN Exchange Format the sitename is an optional field and so may be excluded altogether.

Hope this helps

Graham

4

Re: NBN Exchange export addin

Graham,

Folowing on from your email and this topic;

So - JNCC are inferring that the LOCATION NAME field is 'more relevant' than the LOCATION field /hierarchy in NBN data.

Unless I'm missing the plot  I regarded the locating of observations to be done thus;

Location - Penrith
Location Name - Westbound verge of the A66.

Location - Finglandrigg Woods
Location Name - Bat box 32

This, on the basis that identifiable sites should be built up in the hierarchy as a way of 'representing the area' of the database.
Locations that aren't sites in the strict sense, should be put in the Location Name field.
(We'll take the debate over the dropping of the 'SITE' and 'LOCATION' fields from Recorder 3 as read).

So, if this interpretation of how recorder 6 locations intended to be managed is correct - what is the reasoning for going against that philosophy when populating the NBN? This does seem to be the interpretation of the above procedure.

I am genuinely bemused.
Please de-bemuse.

MAtt

Cumbria Biodiversity Data Centre
Tullie House Museum

5

Re: NBN Exchange export addin

Yes, Matt is right to be bemused. Ideally the procedure should combine (separated with a comma) the LOCATION and LOCATION_NAME if they are both populated. To use Matt's examples, they would appear something like this:

Westbound verge of the A66, Penrith
Bat box 32, Finglandrigg Woods

You could of course swap the order the LOCATION and LOCATION_NAME appear:

Penrith, Westbound verge of the A66
Finglandrigg Woods, Bat box 32

But I've generally found the former approach to generate a more grammatically pleasing result.

Here's the code I use to do this:

    CASE
        WHEN SA.LOCATION_NAME != '' AND LN.ITEM_NAME != '' THEN
            CASE WHEN SA.LOCATION_NAME = LN.ITEM_NAME THEN
                LN.ITEM_NAME
            ELSE
                SA.LOCATION_NAME + ', ' + LN.ITEM_NAME
            END
        WHEN SA.LOCATION_NAME IS NULL AND LN.ITEM_NAME IS NOT NULL THEN
            LN.ITEM_NAME
        WHEN LN.ITEM_NAME IS NULL AND SA.LOCATION_NAME IS NOT NULL THEN
            SA.LOCATION_NAME
    END AS LOCATION
Charles Roper
Digital Development Manager | Field Studies Council
http://www.field-studies-council.org | https://twitter.com/charlesroper | https://twitter.com/fsc_digital