7 Replies Latest reply on Jan 25, 2011 1:56 PM by Sorbsbuster

    Displaying Linked Possible Values



      Displaying Linked Possible Values


      Hi, I'm relatively new to this forum and Filemaker as a whole.  Thanks in advance for your sympathy.

      I'll explain my setup first.  My database manages research data on MRI scans.  Patient's have unique medical record numbers (MRN), of which they have only one.  Patient's are also given Subject Codes, which is the main method of identifying subjects in our study.  When a patient undergoes a scan, they are given a subject code.  This particular study involves the patient undergoing more than one scan, so each particular scan is also given a timepoint (i.e. the number of the scan - 1, 2, 3...).  For instance, a patient will have one MRN 1111, and will also be referenced in our system by their subject code n001 with this patient's scans being referred to by 1 and 2.  So each of these values (MRN, subject code, and timepoint) are 3 different fields.  The top table is the "patient" table, which includes basic patient data, which is linked via MRN to the "scan" table where a new record is given for each timepoint (i.e. one record for subject code n001 timepoint 1, one record for subject code n001 timepoint 2).

      Here is where my question comes in:

      Under the "scan" table is a generic data collection table called "spectroscopy".  In spectroscopy I want to include numbers that relate to one scan, so this table is linked to the scan table via subject code and timepoint.  I don't have any issues with this, but I'd like to simplify things a bit.  Ideally I'd like to enter at the top of the layout for spectroscopy the subject code, then tab to the timepoint field and have the previously entered values display.  I can get the timepoint field to display previously entered values for ALL of the scans (i.e. throughout all records in the scan table via a value list), but I want it to show JUST what values have been entered for that particular subject code.  The reason is because these timepoints are randomized, so the second scan may have a timepoint of 7 and the first a timepoint of 9.  Because it can sometimes get confusing about which timepoints actually exist for a patient (i.e. they aren't always 1 or 2), having only the options to enter values that have already existed for this patient is a sort of quality control check that would make data entry easier.  Any ideas on how to do this?

      Also, maybe this might make things easier.  It would help me out also if a record for a particular subject code and timepoint (i.e. one MRI scan) in the spectroscopy table was created automatically when I created a record in the scan table.  I have no idea how to do this right now haha.  Doing this automatically woudln't suffice though, as not every MRI scan has spectroscopy portions.  I would ideally like a button or a check box that when selected while creating a record in the scan table, a record with the same subject code and timepoint was created in the spectroscopy table.  If someone could help me with that I'd be very appreciative.  Basically I'd like a button on the scan layout that I could use to say "this scan had spectroscopy, create a corresponding record in the spectroscopy table".

      Thanks in advance!

        • 1. Re: Displaying Linked Possible Values

          As tempting as it is to use the MRN and other fields to uniquely identify your records and to link to various tables of records, give careful thought to defining an auto-entered serial number field for each record as a unique identifier. You can keep the other fields, you'll need them after all, but don't use them in your main relationships as a data entry error (entering the wrong MRN, for example) will create significant issues for you if you then create related records before you catch and attempt to correct this error.

          You haven't indicated what tables and relationships you may have defined here. This is a key part of the process and it can profoundly affect how well your database will work. If the right relationships have been defined, you can set up a conditional value list of just the time codes relevant to a given scan. Here's a pair of links on conditional value lists:

          Custom Value List?

          Finally, instead of automatically creating a related record (That can be done BTW), you might consider using a portal to the spectroscopy records with the "Allow creation of records via this relationship" option enabled for that portal's relationship. Then you can create the related record in the portal as needed just by typing data into the bottom blank row of the portal.

          • 2. Re: Displaying Linked Possible Values

            The thing about not using MRN or subject codes to identify subjects is that there is little chance of error based on when and how I enter these values into the database.  For instance, the MRN is entered during one instance for a patient, and is so long anyway that it's always copied and pasted from other areas of our data.  I understand the usefulness of serial codes, but then the issue becomes referring to records on these meaningless numbers that have no relation to any of the rest of the numbers in our data, such as the subject code.  When I'm working on a particular patient's data, the subject code is so identifiable and ingrained in everything that it's much easier to use as a reference than having to switch back and forth between the different layouts to ensure the serial code I'm looking at is for the subject I think it is.  There might be a way to make it clearer, but that effort probably isn't worth the effort, because there is a near 0% chance that there will be an error in entering the MRN, subject code, etc.  Thanks for the tip though, and if I have time I'll consider implementing something like that.

            The tables that are involved in this are: patient, scan, and spectroscopy.  Patient and scan are related by matching MRNs, scan and spectroscopy are related by matching subject codes and timepoints.

            The issue isn't having an easy way to enter data, via a portal system.  Spectroscopy is just one type of specific data that results from a scan.  The patient table describes the patient information, the scan table describes the different parameters of the scan itself (i.e. the date and time), so the scan table links to the numerical data for the scan via the subject code and timepoint.  There can be any number of these numerical data tables, based on the type of data acquired during the particular scan, which can change.  For instance, we can choose to acquire spectroscopy during a scan but not always, and similarly for acquisition type a, b, c, d etc.  So it isn't going to be tidy to have portals to these numerical data tables on the scan layout, and isn't necessary.  Often I'll enter the details for a scan into the database and then add records for the numerical data tables as they have been processed.  It can take many many hours to get the data for all of a patient, so all of the data certainly isn't available at one time upon creation of a record of a patient & scan in the database.  This is why it would be much easier to simply enter that a type of data was collected during a scan when I'm entering the brief summary about it in the scan table.  Ideally I would have a list of types of data and just check each of the appropriate ones off, then when the data was actually processed, I can go to the layout for those tables and just search for subject code and timepoint for the data I'm working with (these values are all over the place with the data we work with, so it's very convenient to reference them) and then start to enter data without having to even touch the scan table.

            Could you help with how to do that?  Thanks!

            • 3. Re: Displaying Linked Possible Values

              Using a serial number field does not prevent you from using an MRN or any other such externally supplied ID to find and work with records. It can function hidden from view to link your records. Considering that these are patient medical records, I'd want to take every possible precaution to keep user errors from breaking the links between records or, even worse, linking them to the wrong records--even if it seems very unlikely that this type of user error could occur. (All you need for a wrong MRN to be used here is for your keyboard to get a bit sticky, then you might accidentally paste the previously copied MRN instead of the one you intended to copy...)

              What I described with a portal does not "touch" the scan table in any way. It's just a very quick, accurrate way to create a new record (Spectroscopy) and link it to a record from another table (Scan). It does not even require a portal if the relationship is "one to one" (Only one spectroscopy record for any given scan record).

              If you want to use a button to create a related record, a script patterned after this outline will do the job:

              #Run from a layout based on your Scan table where the current record is the one for which you want a linked spectroscopy record
              Freeze Window
              Set Variable [$ScanID ; Scan::ScanID]
              Go To Layout [Spectroscopy]
              New Record/Request
              Set Field [Spectroscopy::ScanID ; $ScanID]
              Go To Layout [original layout]

              • 4. Re: Displaying Linked Possible Values

                You've got Phil on your case, so you couldn't ask for better.

                My tuppence worth? - your phrase " I understand the usefulness of serial codes, but then the issue becomes  referring to records on these meaningless numbers that have no relation  to any of the rest of the numbers in our data" is spoken like a, well: 'Human'.  The user of a database is usually totally protected from the idea that every record has a unique ID.  (Oh - and the database is usually protected from the Human's assertion that such a thing isn't useful.)

                It is usually easy to hide from the user the fact that they are using Unique IDs.  They see a portal record listed as 'John Smith', and when they click on it it takes them to - amazingly, amongst the umpteen John Smiths in your database - the very one you had in mind. You don't normally have to expect the user to know, understand, or -  heaven forfend - remember, meaningless numbers.  But my vote would still be with Phil's recommendation to assign a unique number to all your records, and then present the user with an easy way to unknowingly exploit them for accuracy.

                • 5. Re: Displaying Linked Possible Values

                  I guess the issue is that the codes I do use are unique numbers, so how is it better to use a serial number than them?  The way our data is set up on our servers is such that you could never have duplicate subject codes, so they are unique IDs.  The only advantage I could see to using serial numbers would be if when you created a record in one table it automatically created a record in a different table, linking the two by this serial number.  But why is this any more beneficial than linking by an equally unique, yet relevant, id?  It even seems limiting in some respects, unless you could prompt the creation of numerous records connected to one record in a different table, as the relationship often isn't one to one, and by doing so you'd have to ensure that on the newly created record you typed in the correct subject code or you would have an error.  If you're going to enter the subject code automatically, why not have it as the defining relationship?  It's still providing a link based on a unique number, yet the number is defined by me, is meaningful, and could be linked in such a way that the id is automatically entered into the other table to prevent using an incorrect one.

                  When it comes down to it though, say I wanted to enter data into spectroscopy for subject n002.  To do that I'd search for the spectroscopy record that had subject code n002, I wouldn't look for a serial number that matched with the correct scan in the scan table because that connection would allow the subject code in the spectroscopy record to be something besides n002.  If the subject code was the defining relationship, then the only chance for data entry error would be if I accidentally put subject n003's data in n002, but that couldn't be corrected by a program as it's human error.  If a serial number was the defining relationship, any number of fields could match improperly unless I have all of them filled automatically, which is more work than the other method.  It just seems extraneous in every sense.

                  • 6. Re: Displaying Linked Possible Values

                    It comes down to this: "You can keep the other fields, you'll need them after all, but don't use them in your main relationships as a data entry error (entering the wrong MRN, for example) will create significant issues for you if you then create related records before you catch and attempt to correct this error."

                    Say you enter or paste an incorrect MRN number and then proceed to log other data in other tables related by MRN to your main table. Then you discover that an error was made, so you pull up your main table record and correct the error. As a result, you have just disconnected all records in related tables as they still have the incorrect MRN number. They may even match to a record in your main table for a different patient. Now how do you, without any errors, find all those related records and update them with the corrected MRN without also linking in records that were not in error but happen to have this same MRN? Remember that you yourself have said that the MRN would be copy and pasted, so pasting the wrong person's MRN here is quite possible.

                    If, instead, you have an MRN field but don't use it as your Primary Key, and then link it to other tables, your correction is simply a matter of editing a single MRN field and you have no further corrections to make to recover from this error...

                    • 7. Re: Displaying Linked Possible Values

                      ...Or, you're looking at a record for 'John Smith', and they click on it to see all the scans for this 'John Smith' fellow.  And everything works hunky dory.  Then he corrects you -  his name is actually 'John Smyth'.  So you correct that mistake - and immediately lose all the downstream connected records, as none of them match the key field 'John Smith' any more.   So you have to find all these orphaned records (and how, exactly?) and fix the now wrong key field.  Buy hey - you manage that, because you can.  Can.. change the key field in a record.  So, if you can... so can someone else - who notices in one of the downstream records, as they are chatting to Mr Smith, or Smyth, about his results, that his name is spelled wrongly.  So of course they fix it, there and then.  Because they can.  And instantly lose all connection to any upstream or downstream records, which may only be noticed when , well - when the S hits the F, possibly.  Or even worse, his downstream records aren't lost from being linked at all! - happily, they're linked perfectly.  To a completely different person who happens to be called John Smyth.

                      As Phil says - keep the fields you have and like (nothing to stop you then having many, completely distinguishable, John Smiths) but use a generated unique number as the true key field used in all your relationships.

                      ()I know I used 'name' and you are thinking 'n002' but you are only masking the principle behind a cloud of complexity.  It really is as simple (for it to be messed up) as Phil suggests.)