I am definately in agreement with Charles about adding in the ability to batch delete records. This would give me more confidence to tackle the 10-20% of duplicates there are in the DRN database. I would add to Charles list of batch operations.... one that could move a lot of records across surveys into a new survey. For example instead of deleting the duplicate records I could simply move then all into a new survey, so that they can be exported before deletion or else left there and made invalid. The ability to identify and delete empty sample and survey events, resulting from all this deleting, would also be useful.

Graham

Great idea Dave. I use a similar approach when deleting data manually, but instead of a delete column, I use the ENTERED_BY column, update it to 'delete' then run the delete queries. It's makes the whole process a lot more controlled.

Charles

Need the flag be a "delete" one. I get the impression that to carry out some of the above it would be handy to have a "multi-select" facility (which sets the flag). Following that one could perhaps carry out deletes and moves.
If the multi-select facility were also available through a filter then some further very useful operations could be performed e.g. "filter-select" all locations within a grid square (which would highlight rather than filter them) then perform some bulk operation such as moving them within the Location hierarchy.

I am all for this as a feature, whichever way it is done. However, my needs in particular require to be able to delete all records in a survey as I will need to illiminate records deleted from the other system which is MapMate. Otherwise I will still have those deleted records in my system, which would be wrong, and would take forever and a day.

On a similar line to all this, I have heard it said and seen it done in other databases, that records should not be deleted at all and instead merely flagged as so (I think this was mentioned earlier too). The reason for this, specifically when the system is the same (i.e. Recorder 6) is that when data is exported and exchanged, any 'deleted' records will be picked up as so and thus 'deleted' in the importing system. The records could be deleted and just the keys kept. Or they could be moved to a mirror database (or the like) so they could be accessed in the future if needed and the keys used as the flag and link to the main database.

Now, if there were any standards out there in the biological recording software world, then all systems could employ this 'standard' field to flag the deletions and data could be exchanged much more easily and thus get round the need for this deleting feature which is so desperately needed. However and after all we are a free market and everyone will never use one system.

Thanks

Brian

Forgot to mention regarding standards: the fact that we have a free market doesn't mean we can't have standards. The fact we have a free market is the very reason we need standards.

A little aside: the internet only really took off with the invention of the HTML standard. Once HTML came along, the proprietary online services such as AOL, Compuserv, CIX (the proprietary aspects of their platform at least) started to die. Then we had the browser wars where each browser manufaturer added their own extensions to HTML, creating a landscape excruciatingly difficult to build websites for because we had to build for the quirks of each of the different browsers. Then came the unhealthy rise to dominance of Microsoft's browser - Internet Explorer - which essentially halted progress of the web for many years until Mozilla came along with Firefox - a browser who's key feature is standards compliance.  With web developers demanding standards compliance from browser manufacturers, and by building standards compliant sites themselves, we've seen a renaissance in web applications since the crash of 2001 and the market is buyoant once more. Microsoft even relented and updated their browser (IE7) so that it has much better standards support. A web designer can now build a site that is standards compliant and be fairly confident it will run without much modification on Windows (on Firefox, IE7, Opera, etc.), Mac (on Camino, Safari, etc.) and Linux (on Firefox, Konqueror, etc.) or any other standards compliant platform.

So, open standards are fundamental in creating a free and open market. The NBN Data Model that Recorder is based on is the closest thing we have to a standard at present, which is why it's important to make use of it.

Charles

Though I agree in principle to Charles' comments above about standards, I'm still not that impressed by IE7's lack of standards compliance having just relaunched the Dorset Software website (which aims to be as standards compliant and accessible as possible). And the introduction of page zoom in IE7, which I initially thought was great, quickly turned to dismay when I realised that quite a lot of code does not work properly when zoomed and my elements were drifting all over the place! But, as rightly pointed out, properly followed standards are a great thing and would have saved a lot of work.

Enough of the moan, what I really wanted to say was that you ought to be able to write XML reports that perform batch operations. For example, someone could write an XML report that takes a survey as a parameter, then run a series of DELETE (or UPDATE etc) operations on the survey to obliterate the entire survey. At the end, the XML report code could just select 'Operation Completed' or similar.

This is a great idea because its flexible and powerful. However, there are 2 caveats. First you need to make sure you don't import and run a malicious XML report! Secondly, there are no facilities for controlling the required access level for running an XML report. However, adding a minimum access level to the attributes of a report would be a simple change, so Sarah, perhaps it could be considered for the next version if there is a consensus of opinion that this is a good approach to take.

John,

Yes, sadly, IE7 is still probably the worst of the bunch when it comes to standards compliance, but at least Microsoft have acknowledged the need for standards and responded with a product that, while still flawed, has come a long way since IE6. Unfortunately, even with the improvements in IE7, we still need to test our sites in IE6 and IE5 as so many people are still using them.

Regarding XML Reports, I too had no idea that they could do that. For some reason I had assumed UPDATES and DELETEs were disallowed. This opens up some really powerful possibilities!

In light of this, I definitely think access control on XML Report is a requirement.

Cheers,

Charles

John, that sounds great. Seems like a workable solution to me.

I've had a play with stringing batches of queries together using XML Reports in its current guise, but I'm afraid I've stumbled at the first hurdle. When doing something as simple as this:

<?xml version="1.0" ?>
<CustomReport title="Repair Last Keys" menupath="Utilities" description="Repairs the last key of each table.">
<SQL>
SELECT 'Last Key Repair has completed successfully'
<Where keytype="Default">
</Where>
</SQL>
<Columns></Columns>
</CustomReport>

When I run this using XML Reports, I get an error saying "there is an error in the SQL code. The error message is: 'Invalid object name '#REPORT_OUTPUT'". Likewise, if I try the following:

<?xml version="1.0" ?>
<CustomReport title="Repair Last Keys" menupath="Utilities" description="Repairs the last key of each table.">
<SQL>
DECLARE @TableName sysname
DECLARE cur_lastkey CURSOR FOR
SELECT TABLE_NAME FROM LAST_KEY
OPEN cur_lastkey
FETCH NEXT FROM cur_lastkey INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
  PRINT 'Repairing Lastkey for ' + @TableName
  EXEC dbo.spRepairLastKey @TableName
  FETCH NEXT FROM cur_lastkey INTO @TableName
END
CLOSE cur_lastkey
DEALLOCATE cur_lastkey
GO

SELECT 'Last Key Repair has completed successfully'
<Where keytype="Default">
</Where>
</SQL>
<Columns></Columns>
</CustomReport>

I get the same error. Why doesn't this work, and how exactly does one string a series of operations together?

In response to the testing on different versions of IE, take a look at this: http://tredosoft.com/Multiple_IE

Cheers,

Charles

Charles

The report parser looks for the last query in your statement, and inserts 'INTO #Report_Output' before the FROM clause. As your last query doesn't use a FROM clause it doesn't insert the result into the output table.

You could dummy up a query which will work like the following:
SELECT TOP 1 'Report completed'
FROM Setting

Let us know how you get on.