1 Reply by SteveHannah

  • SteveHannah
  • Veteran
  • Offline
  • From: Midlothian
  • Registered: 02-09-2010
  • Posts: 84

Re: Mass check records

Does anybody have a batch update that will mark all the records in a survey as checked?

-----------------------------------------------------------------------
Steve Hannah - GIS and Data Infrastructure Officer
The Wildlife Information Centre

SteveHannah's Website

2 Reply by MikeWeideli

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

Re: Mass check records

Steve

The following will do it as a quick batch update on a selected Survey. It doesn't update Checked_By or Changed_By, which does not cause any problems,  but is not strictly correct.

Mike

<?xml version="1.0" ?>
<batchupdate menupath="LC_BatchUpdates" description="Update Checked"
title="Update Checked"
>
<SQL>

<Where keytype="Survey">

UPDATE Taxon_Occurrence set checked = 1
FROM Taxon_Occurrence TOCC
INNER JOIN Sample S
ON S.Sample_key = TOCC.Sample_Key
INNER JOIN Survey_Event SE
ON SE.Survey_Event_Key = S.Survey_Event_Key
WHERE SE.Survey_Key = '%s'

</Where>

</SQL>
</batchupdate>

Mike Weideli

MikeWeideli's Website

3 Reply by sallyrankin

  • sallyrankin
  • Administrator
  • Offline
  • Registered: 07-04-2006
  • Posts: 371

Re: Mass check records

Checked_by and Checked_date should be updated in the same way as Changed_by and Changed_date in SRChangeRecorder.xml – see http://forums.nbn.org.uk/uploads.php .

Sally Rankin, JNCC Recorder Approved Expert
E-mail: s.rankin@btinternet.com
Telephone: 01491 578633
Mobile: 07941 207687

4 Reply by SteveHannah

  • SteveHannah
  • Veteran
  • Offline
  • From: Midlothian
  • Registered: 02-09-2010
  • Posts: 84

Re: Mass check records

Thanks guys, made life a lot easier :)

-----------------------------------------------------------------------
Steve Hannah - GIS and Data Infrastructure Officer
The Wildlife Information Centre

SteveHannah's Website

5 Reply by mbeard

  • mbeard
  • Veteran
  • Offline
  • From: Greater Manchester
  • Registered: 28-02-2010
  • Posts: 101

Re: Mass check records

I have inserted Sally's suggested Changed_by and Changed_date updates.  I've put the code below as it might be of use to somebody.

<?xml version="1.0" ?>
<batchupdate menupath="MB_BatchUpdates" description="Update all records in selected survey as Checked"
title="Update Checked"
>
<SQL>

<Where keytype="Survey">

DECLARE @ChangedBy char(16)
DECLARE @ChangedDate datetime

SET @ChangedBy = (SELECT Name_key FROM Name WHERE
   <Condition field="Name.Name_Key" type="CurrentUserID" name="Current User ID" operator="equal"/>)
SET @ChangedDate = GETDATE()

UPDATE Taxon_Occurrence set checked = 1, Changed_By = @ChangedBy, Changed_date = @ChangedDate
FROM Taxon_Occurrence TOCC
INNER JOIN Sample S
ON S.Sample_key = TOCC.Sample_Key
INNER JOIN Survey_Event SE
ON SE.Survey_Event_Key = S.Survey_Event_Key
WHERE SE.Survey_Key = '%s'

</Where>

</SQL>
</batchupdate>

Mike Beard
Natural Course Project Officer
Greater Manchester Local Records Centre

mbeard's Website