1 Topic by tamara (edited by tamara 21-01-2013 13:15:56)

  • tamara
  • Member
  • Offline
  • Registered: 08-01-2013
  • Posts: 28

Topic: Upload multiple verified records

I am trying to find a way to upload a spreadsheet of records which have already been verified.  In a test I tried adding a 'Det. Type' column to the spreadsheet and marking all as 'Considered Correct' as I thought this would import them and set the flag to 'Passed verification' but it showed as 'Not verified' and I had to go in and amend manually.

Does anyone know if there's a way I can get this to work?  Or point me in the direction of a thread that covers this?

Thanks in advance!

Tamara
Environmental Records Centre for Cornwall and the Isles of Scilly  (ERCCIS)

Tamara Weeks
Environmental Records Centre for Cornwall and the Isles of Scilly (ERCCIS)

2 Reply by nmrs

  • nmrs
  • Veteran
  • Offline
  • From: London
  • Registered: 22-11-2006
  • Posts: 387

Re: Upload multiple verified records

Hi Tamara,

I believe the following Batch Update will do what you require...

<?xml version="1.0" ?> 

<!--
Set verified flag by determination type
XML Batch Update by Les Hill
Version 1.0.1 - 12/10/2012
-->

<batchupdate

title="Set verified flag by determination type"
menupath="My Batch Updates"
description="Sets TAXON_OCCURRENCE.VERIFIED according to the state of TAXON_DETERMINATION.DETERMINATION_TYPE_KEY.">

<SQL>

<Where keytype="Default">

/* Set "Passed verification" where "Considered correct" and preferred determination */
UPDATE TAXON_OCCURRENCE SET VERIFIED = 2
WHERE TAXON_OCCURRENCE.VERIFIED = 0 AND TAXON_OCCURRENCE_KEY
IN (SELECT TAXON_OCCURRENCE_KEY FROM NBNData.dbo.TAXON_DETERMINATION
WHERE DETERMINATION_TYPE_KEY = 'NBNSYS0000000007' AND PREFERRED = 1)

/* Set "Failed/pending verification" where "Requires confirmation" or "Invalid" and preferred determination */
UPDATE TAXON_OCCURRENCE SET VERIFIED = 1
WHERE TAXON_OCCURRENCE.VERIFIED = 0 AND TAXON_OCCURRENCE_KEY
IN (SELECT TAXON_OCCURRENCE_KEY FROM NBNData.dbo.TAXON_DETERMINATION
WHERE (DETERMINATION_TYPE_KEY = 'NBNSYS0000000001' OR DETERMINATION_TYPE_KEY = 'NBNSYS0000000010') AND PREFERRED = 1)

</Where>

</SQL>

</batchupdate>

I use this for the NMRS database all the time for already-verified and imported data.

Les Evans-Hill
Senior Data Officer
Butterfly Conservation, Butterflies for the New Millennium and National Moth Recording Scheme

nmrs's Website

3 Reply by nmrs

  • nmrs
  • Veteran
  • Offline
  • From: London
  • Registered: 22-11-2006
  • Posts: 387

Re: Upload multiple verified records

Forgot to add, you will need to add your 'Det. Type' column to the spreadsheet and marking all as 'Considered Correct'.

Les Evans-Hill
Senior Data Officer
Butterfly Conservation, Butterflies for the New Millennium and National Moth Recording Scheme

nmrs's Website

4 Reply by MikeWeideli

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

Re: Upload multiple verified records

If the Determination types are correct then you need to run a batch update to bring the verified indicators into line.  I attach Batch Update JNCCDataCorrection1 which will do the job. Put this into the Batch Update folder of R6 and you should  then be able to run it from Tools/Run Batch Updates. It will appear in the Updates menu.

Post's attachments

JNCCDATACorrection1.xml 2.31 kb, 13 downloads since 2013-01-22 

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

MikeWeideli's Website

5 Reply by Don Stenhouse

  • Don Stenhouse
  • Member
  • Offline
  • From: Lancs & Ches beetle Recorder
  • Registered: 12-01-2013
  • Posts: 22

Re: Upload multiple verified records

I downloaded and installed this as it seems a useful addition.

6 Reply by nmrs

  • nmrs
  • Veteran
  • Offline
  • From: London
  • Registered: 22-11-2006
  • Posts: 387

Re: Upload multiple verified records

Seems to be much better than my effort! :-)

Les Evans-Hill
Senior Data Officer
Butterfly Conservation, Butterflies for the New Millennium and National Moth Recording Scheme

nmrs's Website

7 Reply by Don Stenhouse

  • Don Stenhouse
  • Member
  • Offline
  • From: Lancs & Ches beetle Recorder
  • Registered: 12-01-2013
  • Posts: 22

Re: Upload multiple verified records

Well, you may call it an 'effort' - I call it something that I can't do!
If I knew more about such matters I would have tried it, but need to gain some familiarity with SQL etc.

Thanks for posting it.

8 Reply by tamara

  • tamara
  • Member
  • Offline
  • Registered: 08-01-2013
  • Posts: 28

Re: Upload multiple verified records

Yes, thanks to all for your help.  I will put this into practice asap and hopefully have success!

Tamara Weeks
Environmental Records Centre for Cornwall and the Isles of Scilly (ERCCIS)