1 Topic by Purgle Linham

  • Purgle Linham
  • Veteran
  • Offline
  • From: Wiltshire
  • Registered: 30-03-2009
  • Posts: 99

Topic: Adding measurement type values to imported data

Hi
In our R6 we have set up some Observation types within the measurement type term list to allows us to add terms for bird breeding values and restricted sites for certain records or species, e.g. bat roosts. This was set up and managed by our R6 expert, Rob Large (no longer with us at WSBRC), several years ago.

We have discovered that some of our bat data has erroneously been imported without a bat site type and so I was looking at a way of adding this to the 1,200 or so records which need it as a backend query in Access. I haven't worked out a way to do this as my skills are limited but as I thought more about it then I thought perhaps I was going about it the wrong way and it would be better as a batch update?

I haven't noticed anything that would do this job and I have no idea how to create one. Is this something that anyone could help me with please? I assume a query that  adds a chosen value from a specific measurement type list to a set of records (selected by TOCC) would work on our Observation list even though its not a system list?

Attached is a screen shot of our MT list so you can see what it looks like. I want to add 'Not roost' to the remaining records, some of which have other measurements such as abundance values, so these need to be additional.

Yours hopefully
Purgle

Post's attachments

Bat Site MT.JPG 79.52 kb, file has never been downloaded. 

You don't have the permssions to download the attachments of this post.
Purgle Linham
Wiltshire & Swindon Biological Records Centre

Purgle Linham's Website

2 Reply by MikeWeideli

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

Re: Adding measurement type values to imported data

The Batch Update needs to add records to Taxon_Occurrence_Data table. To do  batch update you will need a csv file with the Taxon_Occurence keys which require new entries. Either one table with a field to indicate if it is a roost or not, or two separate files. You should be able to generate a Excel file through the Report Wizard and edit this.  It will be easier with the keys for Measurement Qualifier and Measurement Unit.  The quickest way to get these is via the System Supplied Reports/Information - Report  H5. If you can provide the file(s) plus the Measurement Unit and Measurement qualifier keys you want to use I will do the Batch Update for you.

Mike Weideli

MikeWeideli's Website

3 Reply by Purgle Linham (edited by Purgle Linham 23-08-2016 09:51:57)

  • Purgle Linham
  • Veteran
  • Offline
  • From: Wiltshire
  • Registered: 30-03-2009
  • Posts: 99

Re: Adding measurement type values to imported data

Hi Mike
Thanks very much for your help. Hopefully I've done this right and below are all the relevant keys for the Observation hierarchy that the bat site measurement unit sites within. Attached is a file with the Obs Keys/Taxon Occurrence Keys for all records needing a  value added. The input value is the same for all of these: Not Roost. I've added this as an observation value in the file (in R6 this actually returns as Bat Sites: Not Roost).

Thanks!

Measurement Type Key
DSS0062800000005
Measurement Type - 'observation'

Measurement Qualifer Key
DSS00628000000AK
Measurement Qualifier - 'evidence'

Measurement Unit Key
DSS006280000000F
Measurement Unit - 'Bat Site:'

Post's attachments

Bats_NotRoost.csv 35.35 kb, 12 downloads since 2016-08-23 

You don't have the permssions to download the attachments of this post.
Purgle Linham
Wiltshire & Swindon Biological Records Centre

Purgle Linham's Website

4 Reply by MikeWeideli

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

Re: Adding measurement type values to imported data

Hi

I have had a go at the Batch Update designed to do this (attached). To test it fully I would need to set up the keys etc, so my testing has been a bit limited. Please give a try and let ne know of any problems. Also check the output carefully. You could run it as a test with just one  entry in the csv file.

Post's attachments

Temp1.xml 1.85 kb, 5 downloads since 2016-08-24 

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

MikeWeideli's Website

5 Reply by Purgle Linham

  • Purgle Linham
  • Veteran
  • Offline
  • From: Wiltshire
  • Registered: 30-03-2009
  • Posts: 99

Re: Adding measurement type values to imported data

Hi Mike
Thanks for this. I tried it with a CSV with just one entry in but I'm getting an error when I select the file. It says there is an error in the SQL code. The error message is: 'Incorrect syntax near the keyword 'Key''.

Thanks

Purgle Linham
Wiltshire & Swindon Biological Records Centre

Purgle Linham's Website

6 Reply by Purgle Linham

  • Purgle Linham
  • Veteran
  • Offline
  • From: Wiltshire
  • Registered: 30-03-2009
  • Posts: 99

Re: Adding measurement type values to imported data

Hi Mike
Any more thoughts on this?

Purgle Linham
Wiltshire & Swindon Biological Records Centre

Purgle Linham's Website

7 Reply by MikeWeideli

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

Re: Adding measurement type values to imported data

Hi

I think it is the space in 'Obs Key' in the csv file. Open the Temp1.xml in notepad and find the two 'Obs key' entries. Change these to 'Obs_Key' and and save the file . Also in your spreadsheet change the  Obs Key column heading to Obs_Key.     

Mike

Mike Weideli

MikeWeideli's Website

8 Reply by Purgle Linham

  • Purgle Linham
  • Veteran
  • Offline
  • From: Wiltshire
  • Registered: 30-03-2009
  • Posts: 99

Re: Adding measurement type values to imported data

Thanks. I think that helped but now I get a different error message of:
'Incorrect syntax near ',''

Purgle Linham
Wiltshire & Swindon Biological Records Centre

Purgle Linham's Website

9 Reply by MikeWeideli

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

Re: Adding measurement type values to imported data

I haven't been able to work out why you are getting this message, but I enclose a slightly revised version which works for me.

Post's attachments

Temp1.xml 1.85 kb, 4 downloads since 2016-11-17 

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

MikeWeideli's Website

10 Reply by Purgle Linham

  • Purgle Linham
  • Veteran
  • Offline
  • From: Wiltshire
  • Registered: 30-03-2009
  • Posts: 99

Re: Adding measurement type values to imported data

I'm still getting the same error message.

Purgle Linham
Wiltshire & Swindon Biological Records Centre

Purgle Linham's Website

11 Reply by DGERC

  • DGERC
  • DGERC
  • Veteran
  • Offline
  • From: SWSEIC
  • Registered: 31-05-2006
  • Posts: 232

Re: Adding measurement type values to imported data

Hi Purgle/Mike

I have a similar problem to Purgle and wanted to add in some measurements to existing Taxon Occurrences so tried Mike batch update posted above with necessary amendments. I got the same message of:

'Incorrect syntax near ',''

Did you find a fix for this?

MARK

Mark Pollitt
SWSEIC (formerly DGERC)

DGERC's Website

12 Reply by MikeWeideli

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

Re: Adding measurement type values to imported data

I was hoping to visit Purgle to sort it out, but have been a little under the weather since the begining of the year.  I will look again here to see if I can sort it out.

Mike Weideli

MikeWeideli's Website

13 Reply by Purgle Linham

  • Purgle Linham
  • Veteran
  • Offline
  • From: Wiltshire
  • Registered: 30-03-2009
  • Posts: 99

Re: Adding measurement type values to imported data

Sorry to hear that Mike.

If there's anything else you need from me just ask.

Purgle

Purgle Linham
Wiltshire & Swindon Biological Records Centre

Purgle Linham's Website