KCL Software Solutions

Sharing Records to Other CIOC Databases

How does sharing between CIOC databases work?

Records can be shared between CIOC databases using the CIOC Sharing Format. This special format ensures that the information exchanged conforms to the data structure and content rules within CIOC, so that the information can be loaded into the application without any explicit data conversion or manipulation. The CIOC Sharing format is an XML format that conforms to a particular specification as outlined in the XML Schema Document or XSD describing the format. The current XSD for the sharing format is always available by going to the file import/cioc_schema.xsd in your database; for example, http://test.cioc.ca/import/cioc_schema.xsd. You may share this schema with external agencies with whom you wish to interchange data, however, they should be made aware that this schema may change following a software upgrade. If you partner with a 3rd party organization, the organization or their developers should sign up for CIOC Alerts to be informed with the format is scheduled to change.

There are two prerequisites to exporting records in the sharing format:

  1. Ensure that an Export Profile has been created and configured with the desired options
  2. Ensure that the user who will be performing the export has permission to export set up correctly in their User Type.

Remember that the various Import and Export pages have Page Help available, which may have more detail than specified here.

Creating Export Profiles

To export using the CIOC Sharing format, you must first configure an Export Profile. This is done from the Setup menu, by selecting the Export Profile link. The Export Profile will indicate:

  • Which fields to share (Manage Fields). Only the fields that you share will be updated in the target database. So for example, if you did not share the Internal Memo field, the target database would be free to maintain their own Internal Memo field which would remain intact between updates.
  • Which distribution and publication codes to share (Manage Distributions and Manage Publications). If the distribution or publication codes you include do not exist in the target database, they will be ignored. If they do exist in the target database, your classification of the records will override the classification in the target database for the selected Distribution and Publication codes (and in the case of publications, the general headings and publication description as well). Any Publication or Distribution codes not included in the Export Profile will remain untouched in the target database.
  • Whether to share information marked as private (records using a privacy profile). If sending to another CIOC database, you may choose to Export Privacy Profiles along with your information, so they can continue to have the private information in their database; or, you can choose Do not export private fields so that you can be sure that the private information does not leave your database.
  • How you wish the recipient to credit you for the information and/or link back to your database or website (the Source Database Information)
  • Which View in your database the recipient should use to submit changes to the selected records (Submit Record Changes to). A feedback link for each record will be generated during the export process using the View you have specified.

Exporting Data

Once you have created an Export Profile, you now have the ability to export records. This can be done in two ways:

  • If you have marked the records for export using a Distribution or Publication Code, or you want to export all the records in your database that you have permission to export, then you can choose Export from the Main Menu
  • If you want to select specific records to export, you can do so by performing a search and using the Select Checkbox tool. For assistance on using this tool, see the Page Help on any search results page and review the section on Record List Display Options.

Once you have entered the Export menu, you need to select the format; you will select CIOC Sharing Format (XML) and the appropriate Export Profile. Depending on the method of export, you may have the option of confining the export to one or more Publication or Distribution codes. Once you have filled out the export form, click Next >> to proceed with the export.

It may take some time to produce the file; please be patient and do not close the window. When the export is complete, you will be presented with a link to your export file. Right-click the link and choose Save Target As  (or the equivalent in your browser), and save it to a memorable location on your computer. The name of the file uses the date and time that the file was produced; you may wish to rename it to something more meaningful. Note that export files are removed from the servers regularly; please download your export file immediately.You may then send it to the recipient (by e-mail, FTP, etc) to load into the target database. Because export files are very large, it is strongly recommended that you zip your files prior to sending them (a free utility is available at http://www.7-zip.org/).

Importing Data

If you have received a CIOC sharing format file for import, your first step is to load the file into CIOC. If the file was sent as a zip archive, you must extract the file from the archive before loading it.

  • To load the file, select Import from the Main Menu and click the Load New Dataset link.
  • A pop-up window will appear. Click the Browse button to locate the file on your computer. You can optionally specify a value for Import As to assign a "friendly" name to the dataset after it is loaded, to remind you who provided the data and/or when it was sent. Click Upload File.
  • At this point, the file will be sent to the server and validated against the schema (XSD) to ensure that the format of the data is correct. It may take some time to upload the file. When the file is uploaded and validated, a report will be generated. Any records that did not conform to the schema will not be imported, so you should take not of any failure messages in the report and send them to your contributing partner, so that they can make corrections to their data.If you wish to proceed with importing the records that were valid, you may want to ask your contributing partner to resend only the records that failed.
  • Once you are finished reviewing the report, you may close the pop-up window. Hit Refresh/Reload (F5) in your browser to refresh the import page and see your file in the list.

After loading the file, you may now review its contents, including how many records were sent, which records will be added and/or updated, which fields and codes are going to be updated, and so on. You should review this information carefully, particularly if this is the first time you are receiving the file or if you have been notified that the export profile used to generate the data has been recently updated. The next step is to perform the actual update; there are several ways to proceed:

  • You can individually process records to be added or updated by clicking the Add or Update links , or...
  • You can add the entire file into the import queue (the queue may contain one or many files to be imported)

In each case you will be prompted to make various choices about how to handle the incoming information:

  • If you are performing a regular Add or Update (not a Queue All), you will have the option of choosing specific record owners to import (Select Record Owners) , or limiting the Number of Records to Import at one time.If you limit the number of records, you can return to later to finish importing the rest.
  • You must specify what to do if you are updating an existing record (matched based on record number) and the incoming record has a different record owner than the owner specified in your database (a Record Owner Conflict) a different public stats (Public Status Conflict) or different deletion status (Deletion Status Conflict). By default, records with an ownership conflict will not be imported and will remain in the list waiting to be updated; non-public and deletion status default to taking what is available in the incoming file.
  • If the incoming file contains Privacy Profiles (indicating some private data within normally public fields) you must decide how to handle the private information. The system will try to automatically find an appropriate match to a Privacy Profile in your database, or you can manually select a Privacy Profile. The list of private fields in the incoming Privacy Profile is listed on the screen; select a Privacy Profile from your database from the drop-down list and click the fields link to view the private fields in the selected Profile. If you do not provide a Privacy Profile mapping, you must select whether you wish to not import the record (the default) or skip only the private fields, while importing the rest of the record. If you have already assigned a Privacy Profile to the record in your database and it is different than the Privacy Profile being assigned during the import process, you must decide whether you will keep the existing Profile, the new Profile, or not import the record (the default).

Things you need to know about Importing records...

Please read this important information - whether you are the exporter or the importer, you need to know!

  • Importing records causes a great deal of intensive work on the server, which can slow down the application. Although you can load a dataset at any time, the actual import process is best run after normal business hours if possible.
  • You should regularly review the configuration of the files being sent, to ensure that the fields, publication and distribution codes are what has been agreed. If you are being sent fields or codes that you do not want controlled by your partner sending you the information, it will overwrite your information if you run the import. Ensure your contributing partners inform you each time they make any change to their export profile, so that you can review the incoming data carefully.
  • Records that have been renumbered in the source database will not match up properly with their former record number during an import, and therefore will appear to be new records. Please review the Quick Reference on changing record numbers.

Best Practices

The following section outlines a sample step-by-step procedure for effectively sharing information between CIOC databases. If you will be importing data with sufficient regularity, you can contact the technical admins to discuss the cost to configure full import/export automation.

Confirm the Protocol for Exchange

  • Agree on and document the specific records, fields, publication and distribution codes to be exchanged.
  • Have a schedule for both Full and Partial exports of information; for example, send modified records weekly, but monthly or bi-monthly send the full list of records marked for exchange. It is strongly recommended that a full exchange of information occur on a regularly scheduled basis to ensure data quality.
  • Discuss policies for the renumbering records.
  • Decide on a policy for dealing with conflicts in deletion or public status.

When you want to perform a partial export of data you should...

Search for records that have been marked for exchange (such as by Distribution code) AND have been modified on or after the date that the last export was created. If you share Publication information, are re-exporting records that have been imported into your database, or share Taxonomy indexing information, it is often not sufficient to use the regular Modified Date field to determine if a record has changed.The following is an example of a search you can enter into Add SQL to more accurately capture the list of changed records:
-- Main record modification date
bt.MODIFIED_DATE >= '10-Jun-2009'

-- If you are using the Taxonomy
OR cbt.TAX_MODIFIED_DATE >= '10-Jun-2009'

-- If any of the records were imported from another system
OR btd.IMPORT_DATE >= '10-Jun-2009'

-- If you are exchanging Publication Codes
OR EXISTS(SELECT * FROM CIC_BT_PB pr WHERE pr.MODIFIED_DATE >= '10-Jun-2009'
     AND EXISTS(SELECT * FROM CIC_Publication pb WHERE pb.PB_ID=pr.PB_ID
          AND pb.PubCode IN ('PUBCODE','PUBCODE','PUBCODE')
     )
)

If using the example above, you should change the dates to the date of your last export and replace each 'PUBCODE' with a Publication Code you are sharing with your partner (this list can have as many items as required).

To create a search you can save, you can replace the date statement '10-Jun-2009' above with a statement that is equivalent to a certain number of days ago. For example, to find records modified 7 days ago or later:

bt.MODIFIED_DATE >= DATEADD(d,-7,CONVERT(varchar(11),GETDATE(),106))

When you want to perform a full export of data you should...

  • Retrieve and review all records that are part of the set to be exchanged. The preferred way to mark a set of records for exchange is with a Distribution Code appropriately named to distinguish it as unique for this purpose.
  • Review the list and remove the code from records that no longer belong in the set. If appropriate, review the list of records not coded for exchange to identify any records that should be in the set.
  • From the Main Menu, select Export, select the code representing the list of records for exchange, and proceed as outlined in the previous section on Exporting Records.
  • Notify your partner that you are including a full update and that a full update protocol should be used when importing the records.

When you want to perform a partial import of data you should...

  • Load and import your data as specified in the above section on Importing Records

When you want to perform a full import of data you should...

  • Load and import your data as specified in the above section on Importing Records
  • Search for records in the set (identified by Record Owner, for example) that have an Import Date prior to today. Any records returned by this search are no longer part of the set being exchanged; you should contact your partner to find out the status of these records (for example, are they deleted or no longer appropriate for exchange?).

Did you find this article helpful?