Import script

[MikeKD]

Hi Folks,

Every couple of weeks I'm importing data from my colleagues database into mine (which acts as a master) and then giving her a copy of mine.

(I was hoping to have MirrorSync or EasySync in place on the school server, but the management doesn't want that to happen - I'm now looking at GoZync which might be a runner, but not for a couple of months at least).

 

So far, I've just gone through and imported each table one at a time. However I usually make a mistake and have to go back to backups and beer to sort things out.  I assume that it would be safer to do it with a script - once I've got it working, it will work every time.

 

Existing records aren't often altered, so it's a case of adding the new records to my master database and not overwriting any existing data. (which I did once and didn't spot for a week that all the kids were playing the wrong instruments and were in the wrong class etc!!)

 

Here's the start of my script (it continues for other tables). Does it look like I'm on the right lines?

 

Cheers,

Mike

#Double check script should be run.
Show Custom Dialog [ Title: "Import Records"; Message: "Are you sure that you want to import records from a matching database?"; Default Button: “OK”, Commit: “No”; Button 2: “Cancel”, Commit: “No” ]
If [ Get ( LastMessageChoice ) =1 ] Else
Exit Script [ ] End If
#Make a backup copy first.
Save a Copy as [ “file:Backup before merge/PupilGradeBackup.fmp12” ] [ copy of current file ]
#Import relevant tables - I want to import new records but not overwrite existing ones. #Import Pupils
Import Records [ Source: “file:../../../Desktop/PupilGradeIMPORT.fmp12” OR “file:../../../Desktop/PupilGradeV072.fmp12”; Target: “Pupils”; Method: Update matching; Add remaining; Character Set: “Mac Roman”; Field Mapping: Source field 1 match with Pupils::PupilID
Source field 2 import to Pupils::PreferedName Source field 3 import to Pupils::Nickname
Source field 4 import to Pupils::FullName
Source field 6 import to Pupils::DateOfBirth Source field 7 import to Pupils::Y7EntryYear Source field 9 import to Pupils::Y7Notes
Source field 10 import to Pupils::Y8Notes
Source field 11 import to Pupils::Y9Notes
Source field 12 import to Pupils::Y10Notes
Source field 13 import to Pupils::Y11Notes
Source field 14 import to Pupils::Y12_13Notes Source field 15 import to Pupils::Instrument1 Source field 16 import to Pupils::Instrument2 Source field 17 import to Pupils::Instrument3 Source field 18 import to Pupils::Instrument4 Source field 19 import to Pupils::Instrument1Grade Source field 20 import to Pupils::Instrument2Grade Source field 21 import to Pupils::Instrument3Grade Source field 22 import to Pupils::Instrument4Grade Source field 24 import to Pupils::CohortID
Source field 25 import to Pupils::Gender
Source field 26 import to Pupils::Email
Source field 27 import to Pupils::Boarder? Source field 28 import to Pupils::Academic House Source field 29 import to Pupils::School
Source field 30 import to Pupils::YearJoined

[comment]

Existing records aren't often altered, so it's a case of adding the new records to my master database and not overwriting any existing data.

 

In a situation like this, it's best to ensure each record has a globally unique id - either by using UUIDs or by giving each station its own prefix for auto-entered serials. Set this id field to validate as Unique, Validate always. Then you can import the records from the other station/s with no fear of overwriting already existing records. (Note: do not enable auto-enters during the import.)

[MikeKD]

Many thanks!

Each record has a UUID already - I twigged this might be an issue when there was more than one of us creating records at the same time (and even more so if I ever manage to get syncing organised!)

 

Can I just double check that my understanding of the import actions is correct:

• Add New Records - does what it says on the tin - any new records are added; if the ID is already there, no action is taken. Probably what I want most of the time.
• Update existing records in found set - avoid at all costs - this deletes existing records and replaces them with the import. 
• Update matching records in found set - This replaces field contents of matching records; if an import field is empty it doesn't overwrite, but if it has any contents it does.
• I do want to tick the "add remaining data" box.

I didn't understand your "do not enable auto-enters during the import" comment though - where's that option?

 

Many thanks,

Mike

[comment]

A few corrections:

 

Update existing records in found set - avoid at all costs - this deletes existing records and replaces them with the import.

 

No, it updates the existing records, same as the "Update matching records" method does. But there's no matching by an id field, so practically useless for you.

 

 

Update matching records in found set - This replaces field contents of matching records; if an import field is empty it doesn't overwrite, but if it has any contents it does.

 

Empty fields in the source file do overwrite existing data in the target fields (if you have selected the fields in the import field mapping).

 

 

IMHO, you do not want to use any of these two methods because if both of you have been working in the same record, one of you is going to lose his/her work. This is where you need syncing.

 

 

I didn't understand your "do not enable auto-enters during the import" comment though - where's that option?

 

It comes up after you're done with import field mapping.

[MikeKD]

Bugger - it doesn't work / do what I was expecting it to.

 

I've got import fields all set to "Add New Records".

 

What I was expecting was for records that didn't already exist to be added; instead all records are added, so I have duplicates of most.

Obviously there's no match field option when "Adding New Records" - so is this expected behaviour?

 

How do I achieve the result I want please?

[comment]

What I was expecting was for records that didn't already exist to be added; instead all records are added, so I have duplicates of most.

 

Set this id field to validate as Unique, Validate always.

[MikeKD]

Thanks so much comment; it's working exactly how I thought it would now. - I'd got all the IDs to validate only on data entry.

Very grateful and relieved!!

:-)

[MikeKD]

 

 

IMHO, you do not want to use any of these two methods because if both of you have been working in the same record, one of you is going to lose his/her work. This is where you need syncing.

 

OK, I'm mainly getting along OK with just adding new records when they're created.

In the main Pupils table though, it would be handy for other teachers to be able to enter info into fields about existing pupils.

 

Would a possible sync workflow for this one table go something like this?:

1. Import into a new Pupil_for_Syncing holding table, related to my main Pupil table.
2. Check for new records and create a new record in Pupil table to import them into.
3. Compare each field of Pupils & Pupil_for_Syncing:

• If Pupil_for_Syncing field is blank, do nothing.
• If Pupil_for_Syncing field has contents, compare them with Pupils - if they're the same, do nothing. If they're not, add the Pupil_for_Syncing field contents to the contents of Pupils field.

​I do already have date modified fields in the table, but using that to decide which contents gets used could still cause data loss (though I'm going to mull to see if that is the case in reality..)

 

Is it possible to script a loop to go through each field, or do I just have to write a separate script stage for each one?

 

Is it necessary to use Pupil_for_Syncing table?

 

(I don't have the option of using EasySync or MirrorSync - management don't want the database on the school server at the moment :-/)

 

Cheers,

Mike

 

Cheers!