Archiving! [Slippery Script]

[allan_2009]

Hi All,

I ve been searching and figuring out how to archive a file which remains in the same file but can be summoned anytime to create;

1) duplicate record with different data set or independent of each other without changing the archive file.

I can duplicate and had flagged the archived "inactive" and saved the found set and set privileges to be accessed only by [Full Access] accounts but;

1) PROBLEM IS - It is not independent of each other. Any changes from ... will occur to the other.

' i thought of exporting to an archived file but I am very sure there is an answer from our experts!

Thank you sincerely... :

[bcooney]

Hello Allan. You say "archive a file," do you mean archive a record?

[allan_2009]

Hi Miss Cooney,

Yes, I am creating a database that will contain a medical record in the office. At one point, we have to discharge them from our care (similar concept to being hospitalized). All the files that was created during that time frame must be protected from any alteration as part of regulatory (fed and state) compliance.

I have tables PATIENTS (A), MEDICATIONS (:, DIAGNOSIS©, INSURANCES(D),VISITS(E) and CHARTS(F). "A" has one to many relationships to the rest (B,C,D,E,F).

...What I can do so far..

1) I can flag B,C,D,E,F to "Archived" or "Discharged"

2) I can create New B,C,D,E,F files and relate it to A.

....Here's my PROBLEM <....(Supposed I have to readmit a patient)

1) On PATIENT's table layout (I have fields called from B,C,D,E,F) the previous "archived " files are the ones showing on the layout although I have created new CHARTS or VISITS....

My INTENTION is

1) To "archive" discharged patient files.

2) The archived files must be viewed anytime but as a"Read Only" to uphold compliance and security.

3) To have the newly created files ( B,C,D,E,F) be the default view on readmission.

I hope that I explained my intention well enough and pardon me if I confused anybody.

Thanks again for the reply, Miss Cooney.

Allan :

[bcooney]

Allan,

All you need to do is change the status of the patient record and add a record level access rule to only allow edit if the patient's status is "Active." Likewise, add an edit rule for the child tables that looks to the parent table's status.

I am assuming that from the Patient form, you have portals to the child tables.

You may wish to give the user a list view that does not have "Archived" patients listed. You can do a number of ways. One is to have a base Patient table, and two "satellites" for Active and Inactive Patients. Search for subtype/supertype threads. (Here's the link).

Barbara

Edited September 20, 2009 by Guest

[allan_2009]

Hi Barbara,

// You could also consider having three tables for Patients. One is the "base" table in which all the fields reside. Then, two table "subtypes" for "Active" and "Discharged" patients. Here is a link discussing this technique.//

----"I Didnt get the link"--------

//3) To have the newly created files ( B,C,D,E,F) be the default view on readmission.

[bcooney]

The link works for me. It's a thread that discusses the use of subtypes and supertypes (your two status of a patient) and a method to list them separately, but not move the data from an active table to an inactive table.

You need to filter the relationship to the children to see only one of the child records (it would be like "finding" only the child records that match a given criteria). Search for filtered portals on the forum.

[allan_2009]

Hi Barbara,

Attached is the sample snapshot of the solution's issue.

I can pull filter the portal just fine but pulling the report from the list view to show just "Active" patients and using GTRR function to go to the PATIENT Layout and show the "Active" REGISTRATION is a mere challenge.

Just for clarification " I am filtering the list view and the portal" based on the patient_status field that resides in the REGISTRATION TABLE (child table of PATIENT TABLE).

I created an Find [ActiveList] Script that should filter the "Active"Patients as shown;

Help! ;(

Thanks again,

Allan

PS. I was able to go to the link!

#This will filter all ACTIVE patient list.

Perform Script [ “Allow User Abort” ]

Perform Script [ “Set Error Capture” ]

Go to Layout [ “PATIENTS_List” (PATIENTS) ]

Enter Find Mode [ ]

Set Field [ REGISTRATIONS::status_patient; "Active" ]

Perform Find [ ]

and a [show All]script which is limited only to [Full Access] Account.

[allan_2009]

Sorry if i screwed up on posting the message.

[Fenton]

There are 2 tables, Patients and Registrations; the latter being a kind of "history" table, as well as containing the latest registration. Is that correct?

So the information about whether a patient is currently "active" is in the Registrations table. In order to see the latest related registration's record's data directly from patient, there are 2 choices I see (actually 3). You can use one.

1. Use the Last (relationship::field) function, in a calculation field in Patients, looking at Registrations. It would be an unstored field, but would work for Finds.

(A Find in the portal would not work, as a patient could have both "active" and "discharged" in registrations.)

The function is, strangely enough, in the Repeating functions, as Last (repeating field); but this is only because it was part of FileMaker before it even had relationships. A non-sorted relationship by default sees its first matching record. Last (relationship::field) sees the last non-empty value of the related field.

2. Script the change from active to discharged. This way you can create the Registration record, but also set the data into the Patient record itself (it would auto-enter "Active" for a new record). This is "redundant" data, but in this case I think it might be justified. Having the current state (active/discharged) in the Patient table would make using the data much faster for a Find.

Because you're using a single "status" field for this value, you would need a mechanism to trigger the script when the value is changed, a "script trigger," which would run in Browse mode (not Find mode). It would create the Registration record for "discharged" patients.

Personally I think I'd use a Boolean number field for Discharged_flag instead, formatted as a checkbox, with a Value List of 1. But that's just my preference. Then you wouldn't need a script trigger, as a Boolean checkbox can easily have a script attached. But either way would work fine.

As far as a "filtered" list view, you have probably discovered that a relational "filter" does not exclude other records. You need to do a Find, or Go To Related Record [ Active relationship ] (if you have one).

3. Or, as bcooney suggested, look into the method of separation of Active and Discharged into 2 separate tables, with a 3rd "all patients" central table, which would have almost all the data for either. This method is a bit more to set up, but really solves the separate list views (of active and discharged) problem; which a single Patient table can only do via a Find.

The idea is not much different from your registrations table; it is like splitting it in 2, a table for Active, a table for Discharged. These tables would have very few fields, the PatientID, the Date, and a few developer type fields, creation timestamp, creator, modification timestamp, modifier, and perhaps a primary id. I would consider a UUID for that, to ease imports (in case of crash/burn data recovery import, which should be planned for).

If someone must then go to the Discharged table layout to see its data, you can make the fields non-enterable in Browse mode. Which solves (on the surface) data editing requirements. You would still need to use a record-level access Edit restriction in the "central" table to really satisfy the "cannot edit" requirement. But users would not go to the central table, nor normally need to know it exists; its layouts could be protected.

I'd still script a change so that the current active/discharged status was in the central patient table, as it would be useful, for speed if nothing else, reports, etc..

[P.S. Darn. I just thought of a slight glitch with the separation into 3 tables structure. Which is that it would be difficult to see a history of "activations" and "discharges" for a patient in one portal; it's more one or the other. Easy to see in 2 portals, but not in the same. Unless you had a 4th simple table with just the IDs, status and date, for this purpose only. Not that hard to do, but more redundant data, requiring scripting. But it would give maximum flexibility. The real problem we're trying to solve is, "How to show only active Patients in a List view, without having to resort to a Find (every time, every Find)." Only the separation method solves that.]

[allan_2009]

Hi Fenton,

You are definitely right! Have been trying to tweak around my solution and Ive resolved some issue such as separating active vs. discharged by using REGISTRATION Table as my parent table and tunneled most of the data from it ( dont know if i'm creating a monster here! please advise!).

Drawbacks are:

1. have to declare mostly everytime if the registration is active or discharged.

2. Using find a lot.

The SOLUTIONS ISSUE now is on the portal in which i am using to register all the medications. Problem is;

1) I cant filter medications of an "active" registration vs "discharged" registration without altering the status of the medication ( listed as a value list - "active", discontinued", "new", "changed"). Again I have to leave any 'discharged" information "AS IS" as soon as outcome the registration "discharged".

Here's my relationship table and graph;

REGISTRATIONS <==> PATIENTS ==> MEDICATION PROFILES ( Joining Table) <== MEDICATIONS(Medication Database).

Again this is assuming that a patient has two registrations --- first was "Discharged" and second was "Active"(readmitted).

The portal is nested under a layout based on REGISTRATION and based on the table of MEDICATION PROFILES pulling records from MEDICATIONS.

Thanks for your help!

Allan

[allan_2009]

Hi Fenton,

Here's the sample photo of the medication profile with both registrations inside the portal.

Allan