Importing related files to Runtime Solution

[rbrouwer]

I have a runtime solution containing 4 related files (Patient, Procedure, Diagnosis and Complication), based around the master file (Episodes). I can import all the files into an updated version of my Runtime solution. I can also import a subset of Episodes by doing a Find for dates of admission. My problem is, how do I then import the data from the 4 related files just applicable to that subset of Episodes I imported into the updated file ?

I was thinking of a looping script, which goes to the first Epidsode I wish to import, then finds the related files in the 4 other files, performs import (as only the found set is imported I think), then goes to the next record in Episode and repeats....but I'm not to sure if this will work or how to implement it.

I would really appreciate any advice, it's really the only thing holding back my solution.

[Fenton]

You have the basic idea, going to the related records of Episode, based on the EpisodeID. That ID should be passed down the line as records are created in the other files. Even though it might not be necessary otherwise, it would make this import routine easier. This can be done by defining it as a lookup, on whatever the primary relationship to the parent file is (or done by script).

But you don't have to do a Loop. You can capture all the EpisodeIDs in the found set at once, then go to all their related records in another file in one step (for each file).

The trick is the Copy All Records technique. The principle is that a multi-line field key relates to matches of any of its lines.

It requires a global (text) field and a couple layouts. One layout which has ONLY the EpisodeID field. Another that has the global field (_gEpisodeIDs), enterable.

Exit Record/Request

Go to Layout ["ID Only"]

Copy All Records

Go to Layout ["gID"]

Paste [select, "_gEpisodeIDs"]

Go to Layout [Original]

Exit Record/Request

Now you've got the EpisodeIDs; but where you need them is in other files. So pass them to corresponding globals in those files; it's portable. This can be done via Set Field, using any relationship to the file.

Now you need a relationship from the _gEpisodeIDs field in each file to the EpisodeID the corresponding file, to import from.

Then:

Go To Related Record [show, "Patient__gEpisodeIDs"]

That isolates all the Patient records related to any/all of the Ids in _gEpisodeIDs.

Import.

There is a 64,000 character limit to the _gEpisodesIDs field. Copy All Records requires Export privileges.

[i'm assuming that an Episode is imported once, all at once. If it is not, then the above would still work, but you could use the Update Matching Records option in the Import dialog.]

[Fenton]

Each file should Exit Record before executing the Go To Related Record based on the global relationship.

It makes it 100% reliable. I've seen them misbehave without it; but that was long ago; I always use it.

[rbrouwer]

Thanks very much for you advice and it works like a charm. The only modification I had to make was with the Patient file, as this was related to the Episode file via PatientID, not EpisodeID, as one patient could have many Episodes. But the technique works well using _gPatientIDs. It is a great technique and so much easier than dealing with looping scripts - Thanks again.

I am just having a spot of bother with the actual importing. The script that isolates the related files runs in the old Runtime solution, but is called from the new one, yet the import scripts run in the new Runtime solution. The problem is that as the files are named the same, the scripts get confused which file (old or new) they are importing from. Would changing the extension in each new Runtime version stop this problem ?

[Fenton]

I would definitely change the extension. I think that would fix it. If you have the Developer Tool, which it sounds like you do, you could also change the file names slightly in the runtime files. Probably just the extension change is enough.

Since the imports are all going from the original files to the runtime the name change should have no negative impact on the import scripts; the original names are still the same.

Yeah, you're right. There's no EpisodeID in the Patients file. But like you say, there is a direct many-to-one relationship using the PatientID, and you adapted the multi-line global for _gPatientIDs.

The only real limitation to the Copy All Records trick is the 64,000 character limit. But that's a lot of IDs. Any more than that and they probably have the wrong found set anyway. It's fairly easy to check with the Length() function or Status(CurrentFoundCount), if these are manual Finds. They probably should be highly scripted Finds; otherwise all kinds of havoc is possible.