As an additon to this - I can't use the export back to source as I do not want to add the changes we made to the source DB. Also I tried to create a snap shot and use this as my new source - but then when my original DB updates I have no way of adding int he new data. :(

Peter - This is a common issue, and exists in spreadsheets as well, not just Omniscope. The solution involves 'shadowing' the remote data source to avoid importing data you know has already been locally edited and not changed, and using Merge/Join blocks to detect and channel the already-edited valid fields, and the new/changed raw incoming fields to different files for editing/saving, then finally appending the edited versions of the new/changed records/rows to the master 'Catalogue' file of corrected data.

If you have some control over your database source, try to write the import query loading the data so as to avoid re-importing 'old' records that you have certainly already corrected or enriched and that have not changed. If you have no control over the query, and are being given an entire 'dump' each time, you can use a 'timeslicing' Source IOK file that 'shadows' the remote data source, imports it periodically, but applies a Validate data block which you can manage to avoid importing data you know has not changed since you last edited the files.

You also need to have some kind of unique identifier in each row to enable you to use a Merge/Join block on both the Edited and Unedited IOK files, channelling the matches to use only the edited fields for the master file, ignoring the incoming 'raw' fields from the Unedited file.

The next step, similar to what you have done, is add a [Flag] field in the incoming data, essentially with at least two values, 'Edited' and 'Unedited'. You can automatically populate all incoming new rows of raw data with the Flag value 'Unedited' by default.

If you repeat the multi-file workflow sequence illustrated below every day, only recent likely-unedited records should come in from the source database, and only records you have not yet edited will be channelled off to the manual editing file, and once you open, edit and save that file, and re-open the EditedMasterCatalogue file, the changed records you have just edited will be automatically imported and appended to the bottom of the EditedMasterCatalogue file, ready for the next cycle.

As so often in Omniscope, a multi-IOK file 'workflow' solution is the answer...

Hi there...Many thanks for the text above - seems what I wanted is not so simple at all. Is it possible to get an example of this so I can see the structure as I unfortunately got a bit lost in trying to work out the flow of information from the above.

Many thanks again

Peter - Attached is a multi-file workflow providing a general solution to this common problem. Download and unzip this folder with all files in the same directory. There are 4 files:
1.) IncomingRawDataSource.xlsx - a spreadsheet which simulates a remote database where new records with raw values are being added every day, and 'Ancient' records can become new records at any time when the Last Changed timestamp changes due to a changed value...you simulate these changes by making manual changes to the spreadsheet following the existing pattern, then saving the spreadsheet.
2.) IncomingRawDataValidated.iok - an Omniscope file that applies validation rules to the remote data source, to avoid always importing rows/records that do not need to be manually reviewed or edited, because they have not changed and the valid, edited values are already in the local Omniscope Master Catalogue file.
3.) EditedMasterCatalogueFile.iok - an Omniscope file used to both check incoming data to see if it has already been edited, and later to append manually edited data coming from another file to the updated master copy of this file.
4.) ChangedRowstoEditandSave.iok - an Omniscope working file which should contain only new and changed rows for the remote data source, which may need to be manually edited. If empty, it is because you have not added any new rows and/or changed any timestamps and flags in the spreadsheet that is simulating activity in the remote database.

In the workflow, these files are opened, refreshed and saved in the following sequence:

1 (make edits and save) > 2 (save)> 3 (save)> 4 (make edits/change flags, save) > then open 3 again...save...then repeat as necessary

Each time you run the sequence, archive 'timeslice' files are also saved...the contents of the EditedMasterCatalogueFile.iok can also be output as a database table for creating an internal relational data warehouse accessible by other applications, not just Omniscope.