Hey GUys/Gals

I created an enrolled_patients database and a subject_visits database. The two are joined by the subject_id (single-join relationship). The enrolled_patients database is pulling in data from the subjects_visits database. I created a calculation in the enrolled_patients database that says If the enrolled_patientssubject_id = subjects_visits::subject_id and subjects_visits::visit='SCR' then add an 'X'. I've created the same calc for visit='RND', but I can't see both.

The problem is, depending on how the subjects_visits::visit is sorted, I won't see an 'X' on my calculation. I need to see all related visits by visit in the enrolled_patients database.

BTW, I need to see it in table view.

Help please?

patients.zip

Edited April 27, 200619 yr by Guest

The problem of course is that you are in the ONE side (enrolled_patients) viewing the many (subjects_visits). If you will note that, on SubjectID 2, there are one of EACH (SCR and RND). But FM, looking at the subjects_visits file, can only see the first record (SCR) unless you sort the relationship. It is fine to create TWO table occurrences and sort one relationship ascending and the other descending. This would solve it but ... if you add a visit type, you'll again be in trouble. I can't help but wonder about your perspective and structure. Something is 'off' here. A comparison would be a customer table needing to know if they have ever purchased a specific product and flagging the customer if they have. It seems that you might always be adding table occurrences and fields (to enrolled_patients) whenever you add a different VISID. Of course you could use ONE global in enrolled_patients and toggle which visits you want to see ... but you want them flagged in a table view as separate fields.

enrolled_patients should NOT contain the SubjectID; rather subjects_visits should contain the PTID. But even still, something is amiss. Because the enrolled_patients doesn't appear to be a Patients table (one each for every patient). It MAY be ... but there is no patient name - only PTINIT (for patient initials?). I've attached your files back.

Option 1 will give you what you wish. And there are other approaches as well (including modification of the subject_visits table) but no matter what you do it will be clunky because your structure and perspective is off and you will always need to add fields/table occurrences for each VISID type.

Option 2 allows for more flexibility and meets your specific requirement of "I need to see all related visits by visit in the enrolled_patients database." It uses a value list to turn ALL your related visits (one of each) into a concatenated line for table viewing in enrolled_patients (see cDisplayVisits). I am unsure whether this method is acceptable to you but it certainly is much more efficient. The power of option 2 is that adding different visit types will NOT require additional table occurrences, join fields or calculations. I would use Option2.

And welcome to FM Forums also!!

LaRetta :wink2:

Patients.zip

I see that LaRetta beat me to it, but since I had this already written, I post it anyway:

First of all, your calculation doesn't need to check for SUBJECT_ID=subject_visits::SUBJECT_ID, since that is a given when you set up your relationship on these two fields.

Now I'm assuming that if the subject_visits file contains a record anywhere with SUBJECT_ID = 1 and VISID = "SCR" then you want the corresponding record in enrolled_patients to have an X in the SCR field. The problem is that when you have a one to many relationship as you do in this case, only one record will be selected by the relationship, not all of them. And the record that is selected will be the first one according to the relationship's sort order (if the relationship has no sort order, then it's just the first record). That is, unless you use an aggregate function to summarize the related records. One way is to use a related valuelist to build a list of all the different VISIDs for each subject, and then use the position function to see if the desired VISID is in the list. This is a fairly advanced technique and isn't the easiest technique to understand, but it is effective, and it is flexible enough that you can easily expand it to add other similar columns in your patients file without much extra work. There is also a simpler technique, but it requires adding extra fields in your subject_visits file.

The attached files illustrate both methods.

Two_methods.zip

Thanks,

You've both been extremely helpful.