1 (edited by RobLarge 12-11-2012 16:30:30)

Topic: Odd wizard behaviour problem solved, but anyway...

OK I realise that tinkering around under the bonnet of Recorder is asking for trouble, especially if one is trying to make it do things it has not been asked to do before, but I thought this was worthy of posting.

At the request of another member of staff I have added an item to the USEABLE_FIELD table to enable the wizard to filter on the TAXON_OCCURRENCE.ENTRY_DATE field. It took a bit of trial and error, but seemed to work correctly. For the sake of clarity here is the record I added:

USEABLE_FIELD_KEY = DSS006280000003                 (obviously amend as appropriate if using this elsewhere)
TABLE_NAME = TAXON_OCCURRENCE
FIELD_NAME = ENTRY_DATE
FIELD_DESCRIPTION = Entry Date
FIELD_TYPE = DATE                                                      No other record in the table has this entry so I had to guess
APPLY_TO = T
SELECTABLE = False
SORTABLE = False
FILTERABLE = True
CALCULATION_SQL = TAXON_OCCURRENCE.ENTRY_DATE

In order to test this I told the wizard to select all taxa and under Additional filters I selected Entry Date is greater that '01/01/2012'

After about 4 minutes the report returned about 126,000 records, from our total dataset of just over 1,000,000. This is a believable figure (we have been busy). So I reported to my colleague that I have cracked it.

Ten minutes later she came back to me with a problem. She had attempted essentially the same query but filtering using the date '31/03/2012', this time the wizard crashed immediately with an error report containing this:

"The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value."

This struck me as odd, since the second query should be a subset of the first, so any erroneous records selected by the second query, would also be selected by the first.

Had I had a clearer understanding of what is going on inside Recorder at this point I would probably have spotted the solution straight away, but I had to go through and test a few different scenarios as follows.

Any date between 13/03/2012 and 31/03/2012 causes the same error as above
Any date from 01/03/2012 to 10/03/2012 returns data (although not, as it turns out, quite as expected)
11/03/2012 and 12/03/2012 run through correctly but return no data

As Rolf would say "Have you guessed what it is yet?"

Of course SQL Server defaults to US style dates, so 31/03/2012 is meaningless and as yet we have entered no data in November or December 2012. When I entered the date as 03/31/2012 the query worked perfectly.

So I am feeling both a little silly and rather smug now.

The purpose of this posting has changed since I started writing it (before I found the solution). I was going to ask for help identifying the problem. No need now.

Instead I am asking if there is any way a change to the Recorder UI could fix the problem and allow me to enter the cut-off dates in UK format, because we are bound to forget when we are in a hurry to get statistics out of the database?

Even if no easy solution is available I am posting this to enable others to do what I have done, because I think a lot of people will find it useful. Also if I post it here I know I can find it next time I need to know how its done...

Rob Large
Wildlife Sites Officer
Wiltshire & Swindon Biological Records Centre

2

Re: Odd wizard behaviour problem solved, but anyway...

Apologies if this comes a bit late, after all your hard work, but I have posted a report here http://forums.nbn.org.uk/viewtopic.php?id=4212 that allows you to select records entered after a date. Might need a bit more tweaking to do a date range. Original version was Mike's which may be up somewhere here as well.

Gordon Barker
Biological Survey Data Manager
National Trust

3

Re: Odd wizard behaviour problem solved, but anyway...

I thought I had heard of someone else doing that recently, thanks Gordon.

However, since I am the only person here capable of tweaking xml reports, and since my plate already has enough on it, I was looking for a solution which takes advantage of the huge flexibility of the wizard.

I have written and amended enough xml reports, just because someone wanted a different arrangement of fields for a particular purpose, or because they wanted different filtering or exclusions, or inclusions. Almost every time I have found that the wizard runs faster than my perhaps rather clumsy coding.

Rob Large
Wildlife Sites Officer
Wiltshire & Swindon Biological Records Centre

4

Re: Odd wizard behaviour problem solved, but anyway...

We intend to include something which will make this easier in 6.18 and to look at a solution which will handle report wizard dates properly in 6.19.  Thanks for experimenting with this I hadn't been able to get date to work in any form and had gone for a different solution.  There is nothing we can do without a program change to alter the criteria input by the user so what I did  was to use a sql View to convert the entry date on Taxon_Occurrence  into a yyyymmdd format and then used this view in the report wizard to compare against user criteria entered in  yyyymmdd format. It still means that you have to enter the date in an unusual format to do the comparison. It is perhaps slightly more intuative and will work with equals which I don't think the date based solution will, but it may be slower.  The full code covering Taxon_Occurrence entry date is given below. The same principle can also be applied if the criteria is to include both the Entry Date and Changed Date. 


CREATE VIEW [dbo].[LC_DATE_FILTER]
AS
SELECT     TAXON_OCCURRENCE_KEY, CAST(CONVERT(char(10), ENTRY_DATE, 112) AS INT) AS DATESTRING
FROM         dbo.TAXON_OCCURRENCE

GO

GRANT SELECT ON [dbo].[LC_DATE_FILTER] TO PUBLIC

GO


INSERT INTO USABLE_TABLE(USABLE_TABLE_KEY,TABLE_NAME,LINK_TABLE,LINK,APPLY_TO,JOIN_ORDER)
VALUES ('LCA0002100000002', 'LC_DATE_FILTER','Taxon_Occurrence',
'LC_DATE_FILTER.Taxon_Occurrence_Key = Taxon_Occurrence.Taxon_Occurrence_Key',
'T',8)
GO

INSERT INTO USABLE_FIELD(USABLE_FIELD_KEY,TABLE_NAME,FIELD_NAME,FIELD_DESCRIPTION,
FIELD_TYPE,APPLY_TO,SELECTABLE,SORTABLE,FILTERABLE,CALCULATION_SQL)
VALUES ('LCA0002100000002','LC_DATE_FILTER','DATESTRING','Occ_Entry_Date(yyyymmdd)',
'NUMBER','T',0,0,1,'LC_DATE_FILTER.DATESTRING')

Mike Weideli

5

Re: Odd wizard behaviour problem solved, but anyway...

Thanks for that Mike

That's an interesting approach and not one I would have come up with, but it tells me more about what is going on than I knew yesterday. It would appear that my solution only works because TAXON_OCCURRENCE already exists in USEABLE_TABLE. I did check this before I started on USEABLE_FIELD, but I must admit I had no idea what the relationship between the two tables is so I was just guessing.

Amazing what one can achieve by trial and error.

Is there a list anywhere of acceptable entries for USEABLE_FIELD.FIELD_TYPE? Cos this is where I really had to guess.

Rob Large
Wildlife Sites Officer
Wiltshire & Swindon Biological Records Centre

6

Re: Odd wizard behaviour problem solved, but anyway...

There is no documentation on this. I do the same as you which is to look for silimlar existing entries and to experiment.

Mike Weideli

7

Re: Odd wizard behaviour problem solved, but anyway...

Tested both and you are correct that mine doesn't work with Is equal to. Yours was not appreciably slower using the condtions I tested and is, as you say slightly more intuitive and doesn't need the inverted commas. I'd say yours is the better solution, thanks again.

Rob Large
Wildlife Sites Officer
Wiltshire & Swindon Biological Records Centre