Fixing unequal record numbers in related tables

[bcolburn]

I am restructuring the data in an existing database and have come across a problem. The database I inherited had several layouts that were all supposed to have the same number of records, each record representing a single patient's medical data and each table housing data on a specific medical area of interest. All of the tables are linked by a unique patient identifier. However, not all of the tables have the same number of records in them. I need a way to find out which patients do not have records in each table and then create records for them to equalize the numbers.

My ultimate goal is to export data from the fields that currently exist (primarily one HUMONGOUS table) and import them into more appropriately distributed tables so I can delete the original tables and move forward with a more logically structured database. I'm afraid that since some of the new tables contain variables from several of the original ones, if the record numbers are unequal, I will get incomplete new records. It will take some time to reassign variables in existing calculations and scripts, but it will definitely be worth it in the long run, if I can do this right.

Can anyone tell me how to accomplish this most efficiently? Any help is much appreciated!

[bcolburn]

So I had an idea. If I export all records from the main table with the unique identifier field and import using the (match by field, create new record if no match) settings, I would have all the main table records reflected in all related tables. However, I realized that because relationships were faulty for part of the study, some tables may have individual records that never should have been created in the first place and are not matched in any other table. These outliers would be unique in each specific table. Is there a way, after I export and import as just described, to isolate these records so I can delete them?

Also, is this the best way to go about solving my issue?

[Søren Dyhr]

I have a hard time to realize, why this "equalized spread" amongst tables should be nessersary? I deeply apologize if i'm wrong here, but to me that you have way too many fields in one table ... and perhaps would benefit from watching this movie:

http://www.filemakermagazine.com/videos/data-tagging-classification-vs-organization.html

Since importing between tables inside a solution, only should be considered as a sort of nifty trick - when you can't pull anything off structurally more correct fast enough ... in situations with multiple users stumbling upon each other to massage the same set of data.

I know nothing about your age but the methods you seem to subscipe to a past in the 1985'ies where dBase where king of the smaller environment, it could also be the case that you come from a different tool, with it's way of doing things?

First off do I need to know what you actually consider logical? Try to attach a screendump of what you would consider the appropriate relational structure we are dealing with there? Perhaps you should post what the present structure exhibit of inadequacies as well ... and let the forum here comment on it, since it's a little difficult to find the mission statement in your present description.

--sd

[comment]

not all of the tables have the same number of records in them. I need a way to find out which patients do not have records in each table and then create records for them to equalize the numbers.

I don't think you need to actually create the "missing" records, as you don't have any information for them anyway. If you designate one of the tables as "the" table, and define relationships from this table to all the other tables, you can then export ALL the fields at once by exporting from "the" table and including fields from the related tables in the export order.

[bcolburn]

Great points, both of you. I am incorporating more link tables to make records only when necessary. However, I have come across an interesting situation I don't know how to handle. I am currently capturing whether or not each individual patient has a history of any of 8 cardiac tests in their case file. At present, we have as many records in each of those tables (one table per report type) as we do patients (a lot). I thought of creating a link table like this:

Patient Information >---- LINK Patient Cardiac Reports On File ----< Cardiac Test Information

Parts of this work for all data currently being collected (e.g. date, location, diagnosis), but since each test measures specific things, I'm not sure how to manage those relationships. I could put all of those fields in the Cardiac Test Information table and then place the fields on my layout appropriately so I only see relevant information, but I feel like there might be a more elegant way that still parses out unique test information into separate tables. For example, I would have a table for EKG measurements (rhythm, abnormalities, etc), a table for Cath report information (e.g. stenosis location, percentages). Do you have any ideas? how to elegantly do this?

[comment]

I thought of creating a link table like this:

Patient Information >---- LINK Patient Cardiac Reports On File ----< Cardiac Test Information

Surely you mean:

Patient Information ----< LINK Patient Cardiac Reports On File >---- Cardiac Test Information

each test measures specific things

Have you watched the video Søren linked to? Because it could really be useful here, in the form of:

Patients -< PatientsTests >- Tests -< TestParameters

                  |

                  ^

              TestResults

where TestResults is a join table between PatientsTests and TestParameters.

[bcolburn]

And TestResults is a second TO of TestParameters?

[bcolburn]

Thank you for showing me that diagram. I did in deed look at the video but didn't internalize it enough to arrive at that diagram independently. After looking at this, this is brilliant. However, I am still a little confused as to how this will all manifest. Can you possibly send me a sample document? I'm specifically interested in: 1.) how to lay this out on a patient record using portals (or portals within portals for findings?) and 2.) how to label the test parameter results on my layout if they are going to be constantly changing according to the test type. I usually learn a LOT from your example files, so if you have time to show me what this looks like in action, it would be a HUGE help.

[Søren Dyhr]

I should be able to do this at some time monday if you should have the required patience?

[bcolburn]

Absolutely! Thank you!

[Søren Dyhr]

Well it's not going to be today ... it's moved to my To Do list for tomorrow!

[bcolburn]

No worries. Thanks for making the time to help me out.

[Søren Dyhr]

Now this is how I would have structured this ... the next question is how the database is used beyond the actual logging?

--sd

cardiac.zip

[comment]

Søren,

I believe the RG should look more like this:

[Søren Dyhr]

Indeed it should, but it creates a resolve/tunneling issue with drop-downs first getting it's new correct value when the portalrow is committed ... which then would need a event-trigger ... which works by entering the following field, where a note of location should be made before committing the entire record ... for at safe return to the correct "cell".

I could be a little rusty on these matter, making me forget a more convenient way ... using menu's instead seems to me a bit ugly.

--sd

cardiac2.zip

[comment]

I was talking about structure, not UI: in your first file, all results of a test were related to the same parameter.

[Søren Dyhr]

You mean if I go to an arbitrarily chosen record in TestParameter have a won't i get all patients listed - and if I do would the results be a duplication of the first related records data. It seems I've fallen into a classical trap ... Doh what 4-6 month departure from a tool can do with you.

--sd

[comment]

No, I meant you had a fk_to_Paramter field in the PatientsTests table - thus creating a chain of:

TestParameters 2 -< PatientsTests -< TestResults

with the entire test (and all its results) belonging to a single parameter, instead of:

PatientsTests -< TestResults >- TestParameters 2

where each result belongs to a parameter of its own.

[bcolburn]

OK... so the graph I did on my own looked like Comment's. Is the file posted still a good example for how to structure my database in terms of UI?

Looks like maybe not. For the reason Comment already pointed out, I am still unsure how to make it so I only display the parameters assigned to the test in each portal row and am also not forced to display the max number of parameters when tests may only require a few for data entry. How can I design the UI to predict the parameter fields required based on testID?

[comment]

Designing a UI for this type of data structure is not trivial. See if you can adapt the approach suggested here:

http://www.fmforums.com/forum/showpost.php?post/149069/

[Søren Dyhr]

Ah yes Bruce at his best!!!!

--sd

[bcolburn]

So this is helpful, but I'm still a little unclear since, as you pointed out, this example is based off of only a single test and its parameters. I understand how to show the parameters for a particular test via portal using the testID field, but I want it to choose from several tests also. Since I am currently using a portal of patient records from a link table to constrain the second portal records (test results on that particular test), would it make sense to create a global test ID field and use a script so when I click on the first portal, it sets the global test ID field to the same value and then use this in combination with the patient ID to create a portal filter for the second portal that displays results?

In other words, use a transient third party field that allows the test ID from a portal record to constrain the records in a second portal. Thoughts?

Or if this strategy works, could I create a script that employs a similar strategy using a $tempvariable to accomplish the same thing?

[bcolburn]

In particular, how could I use a script to set $SelectedTestID to equal ONLY the TestID from the selected record? I would use on OnObjectEnter script trigger for all fields in the portal row, but is that enough to specify the specific field value I am looking for?