There isn't any mechanism for checking on external keys on import to prevent duplication. It was never intended for this pupose. The options I suggest are :
1. Put the first eight characters of the R6 key in the Site Id column and the last six (not 8) in the Record ID column. This will pick up any duplicates and use the Site plus the Record Id as the R6 key. You could put the NBN key in the External Ref column. Theoretically, NBN data is not source data and should be imported as temporary data. That is into a Survey which has the Media Type of 'Temporary'. This stops the data from being exported. You can then delete the whole Survey and do a new import when the dataset changes. Using the R6 key as the Record ID will stop duplicates records, but it will not pick up deletions and changes to record dates grid refs etc. which could result in Samples with no records attached. You should keep track of the source of the data as you will probably need to acknowldege this when the data is used. You will not be the Custodian of these records and will not be able to edit them.
2. Create 'Temporary' surveys for the NBN data (probably by source) and include in these the detauils of the sourcea and any licence restrictions. Import the data into these as new data, giving it new keys and putting either the R6 key or NBN key in external key. This will allow you to find the records again if you need to check anything, but it will not stop duplicates. As with approach 1 the idea would be to delete the whole survey and replace it periodically to make sure you have the latest dataset.
Option 1 will take a bit of manipulation of the source data, before it goes in. Option 2 should not require this and has the advantage that you will be able to change the data.
Below is an extract from help regarding Temporary Data
Importing Temporary Data
Users have identified a need to be able to incorporate datasets in Recorder 6 and then to delete and replace them when a new version becomes available. In principle this is acceptable, as long as the data is never exported. This approach will be useful where a dataset is to be imported for reporting purposes. It could also be used by importing a dataset and keeping it updated with new records, but then deleting it and replacing it occasionally to take account of changed records and deletions,
It could be used in a number of different situations, for example:
● Importing MapMate datasets, where it isn’t thought appropriate to permanently import the data
● To import a NBN Gateway dataset
● To import Recorder 6 data in a consolidated format, say from an adjoining LRC
● To import Recorder 6 data, but into an existing structure (locations, abundances etc.)
● Importing data which is in the process of being reviewed or changed
● Importing data from any other system where new datasets will be provided rather than additions and changes
The normal rules which apply to changing data which belongs to others do not apply to temporary data as this is never going to be exported, i.e. the imported data will be in the custody of the importing system, so it can be edited.
The new features in the import wizard aim to make the import of temporary data as simple as possible and provide some controls to prevent the data accidentally being exported.
● One of the major problems with importing data of this nature into Recorder 6 is the need to parse names and to match these or create new entries for them in the Recorder 6 database. With temporary data the namecan optionally be imported without parsing and held in the Recorders column in the Sample table. The name which will appear in observation hierarchy will default to ‘Withheld’. It is important to note that this feature is designed only for use with ‘temporary data ’ and cannot be used with data imported in the normal way. Data with unparsed names must NEVER be exported. Note that if you want to parse and match names in the normal way this can still be done, but it will be necessary to temporarily change the media type on the Survey to 'None' while doing the import and change it back again to 'Temporary afterwards.
● The import of Taxon Version keys is now possible. This saves having to match taxon names in cases where the system supplying the data can supply the key. The TV keys need to be allocated to the ‘species name’ in the import wizard. This facility can also be used with normal imports.
● Although the import of name keys as recorders is now possible, this is not appropriate for use with temporary data imports as the keys will be used as the name of the recorder.
● The import of an ‘External’ key is possible. This is not for the purposes of updating records, but to allow the original source record to be identified in case of a query. This facility can also be used with normal imports.
● If there are spurious taxa in the import file or TV keys which do not match, then these can be ignored just by not matching the keys. TV keys which do not match can be ignored or suitable taxa found. It may be necessary to ask the data supplier for the species name.
Suggested Approach for Importing Temporary Data
● Set up a Survey for the data to be imported. Use the media type of ‘Temporary’ or one of the other options for importing temporary data. This is a new system supplied media type which, when used, allows the import of unparsed names.
● When importing data into a survey with a temporary media type, the ‘Observer(s)’ column will not be available for matching, but there will be a new column ‘TempObserver(s)’. Use this column for the observers names. Note that, if possible, for consistency in output, the names should be in the normal Recorder 6 format, e.g. Mr. Mike D. Smith, but this isn’t essential. See Supported columns in Import Wizard - data format. Note - If you do want to import the names as Observers then change the media type on the Survey to 'None' while doing the import then change it back to Temporary after the import.
● Determiner column can be used, but it is suggested that this is only used if all the determiners are already in the system. The determiner name will default to ‘Withheld’.
● Locations may either be processed as Location Names, or match them to existing Locations within the system. Do not let the system create new Locations for the temporary data unless you need them for other purposes. See Supported columns in Import Wizard - data format.
● If Taxon Version Keys are provided use the Species Names column for these. See Supported columns in Import Wizard - data format.
● If required, match any other fields, e.g. abundance, to existing fields within the system. Avoid creating new term list items, unless they will be of use for other purposes.
● The above approach will minimise the time needed for matching and only create new keys within the Observation hierarchy.
● Data imported in this way may be changed if required.
● When a new version of the dataset is available. Use the delete Batch update to remove the entire Survey. Re-import the new dataset.
NEVER export the ‘temporary data’ to any other user. Recorder 6 will not export any records in Surveys which have a media type of ‘Temporary’ or one of the other options for importing temporary data. Neither will Recorder 6 export records in Samples which have data in the Recorders field of the sample (i.e. unparsed names). These checks are there to avoid accidental export of the data, but it is down to users to take care not to export these records. When using the Report Wizard ‘Additional Filters’ can be used to filter out any entries for surveys with media set to ‘Temporary’. Do not move the records from the Survey into other Surveys and do not change the media type on the Survey on a permanent basis. (It can be done just while importing if you want to parse the observers field in the normal way. Always change it back afterwards.
The survey media type of ‘Temporary’ is the system supplied entry for defining surveys to which temporary data can be imported. Other media types can also be used by adding them to the survey media term list using Tools - Term List - Survey Media, then adding them to the comma-separated list of media type keys for temporary data listed for 'TempMedia' in the Setting table.
'TempName' in the Setting table contains the name key to be used when processing imports into the ‘Temporary’ Surveys. The system supplied entry is ‘Withheld’.
Viewing and Reporting In Recorder
The Recorders name will always appear in the Observation hierarchy as ‘Withheld’. The unparsed observers name will not be visible, but will be held in the ‘Recorders’ column in the Sample table. It is only possible to view this data in the observation hierarchy using the Extra Info. button. This is added to your system when the Observations_Extra_Info_Button_Addin is installed. It is available from the Addins section of the Recorder web site, http://jncc.defra.gov.uk/page-4597. This will also show the external key for a record.
If Sample Recorders is selected in the report wizard this will return ‘Withheld’ as the Recorders name. However, the wizard has a new output option ‘Consolidated Recorders’ which will return the unparsed names where this has been used or the normal name if not. This is done using a UDF which can be used for XML reports. The report wizard is also able to return the External Key.
If the default name ‘Withheld’ for unparsed records is not considered appropriate then this can be changed by editing the Individuals table in Access or SQL Server tools. If this isn’t possible then please ask on the forum for a batch update to make the change.
Frequent use of this approach over a very long period could potentially result in all the available keys being used up in the Taxon_Occurrence and Taxon Determination tables, but it is considered that this extremely unlikely to happen in the lifetime of most systems, as the recorder key has a capacity of nearly 3 billion
Littlefield Consultancy - IT Consultants