Complex find problem, script needed?

[cbum]

I need to find all instances of records with a common ID, which also have a Date that is more than 12 months apart.

Finding records with common ID is easy with the duplicate (!) symbol in the ID field, but how do I limit this to only cases where the Date spread is over 12 months?

Thanks for any suggestions.

[LaRetta]

Hi cbum,

My question is

[cbum]

LaRetta,

I need to find all records for which additional (follow-up) records exist that span a period of over x months (typically over a year). All records from the same patient have a common ID, but I don't know how to find only those that have long (>1 year) periods of follow-up.

How they are displayed is less important, I can sort by ID so all records of the same patient are together.

[ESpringer]

cbum (and welcome back LaRetta ),

It seems you need to create a self-join relationship that joins each record with every other record for the same patient ID.

Are you aware of how to create a self-join relationship?

Then, you can find out what the difference is between the Max(VisitDate) and Min(VisitDate) is among that set of related records. The follow calculation field will flag all records that belong to patients with visits spread out over more than a year:

[color:"blue"]If(Max(SamePatientJoin::VisitDate)-Min(SamePatientJoin::VisitDate)>365,1,0)

If you want "all records for which additional (followups) exist... over x months" then you want to retrieve only the FIRST of each such set? Then, assuming patients don't visit more than once in a day, you can get the set of EARLIEST records with:

cFirst in series flag=

[color:"blue"]If(Max(SamePatientJoin:: VisitDate)-Min(SamePatientJoin: VisitDate)>365 and Min(SamePatientJoin::VisitDate)= VisitDate,1,0)

Is this the kind of thing you're aiming at? If you want to track specific ISSUES -- to see whether someone is following up over more than a year on the same COMPLAINT (say), then you need a concatenated calc field that shows patient ID &"_"& complaint code (or whatever)...

[Ugo DI LUCA]

For sure ESpringer catched it.

However, the calc given above is unstored, as referencing a relationship, so searching its result may take some time.

As you go into your field and relationship definition, add to your file a d_FirstVisitDate_lk field, that will be auto-filled by a lookup using the SelfJoin mentionned previously (Patient_ID::Patient_ID) and the Visit Date field.

If you keep the sort as it is, the first visit date will always be filled in that field.

This way, you could have an indexed calculation of :

c_checkPeriod => VisitDate-d_FirstVisitDate_lk>365, which result will lead to a 1 for those patients you're tracking.

You could perform a search on that field, or involve another relationship, from a constant field at left side (could be a global number g_constant), that you'd populate with a "1", and on the right side, the c_checkPeriod field.

This will allow to go directly to your result list, either through a portal or a script "GoToRelated Records".

Finally, I assume having a list without duplicate would also be a better choice.

With some little work on the relationship and some new field, you could sort out any duplicate too, but this may be another story...

[cbum]

Thanks to both, I'll give it a try, and ask back if I have problems.

I will actually have to combine this search with additional parameters to find cohorts matching certain criteria, but I suspect I can do that as refind or maybe as script.

Note the file is over 40000 records, so performance may be an issue.

[ESpringer]

cbum,

I didn't mention before, but it seems logical that your database solution would have a separate file for *patients*, no? Wouldn't this be necessary for storing contact info, etc.? If you're looking for patients to participate in a study (say), rather than info about specific visits, it seems reasonable that your calculation would be taking place within the patients file, looking at *which* patients match certain criteria, including having a certain profile of visits over time.

[Ugo DI LUCA]

Not sure about this.

Could be that the Visit Line Item File has that pertinent information, and that cross finds on this file ends up being more accurate.

Having all synthetic information stored in the Patient File requires a lot of scripting, in order to keep it updated.

Now, by a GTRR back to the Patient File at end of search, this final list could still be viewed in the Patient's file.

[cbum]

Ugo,

re: "If you keep the sort as it is, the first visit date will always be filled in that field."

Can you elaborate on that? Keep the sort as what?

When I tried it, the file performed a lookup, but all records remained empty for this field. What am i doing wrong?

Also, If a lookup is done on a self referencing/joining field, you can't trigger relookups, since you can't select the filed in the layout, right?

[cbum]

(I'm asking because ESPringer's calc works, but it is indeed very slow for the 40k records....)

Would changing this field to a number field, and then entering the same calc as an calc entry option allow for indexing?

[Ugo DI LUCA]

Ok,

Say you're working on a Line Item, where each visit is a record...

You've made a self relationship, the Patient_ID field matching the Patient_ID field on both sides in that same file (not with the Patient File).

Now, when you're referencing a relationship, and more than one value exists for this relationship, FM will by default grab the first related record, which is, in that case, the first record for the Patient_ID.

Then, if you set a d_FirstVisitDate to lookup its value from a relationship SelfJoinOnPatient_ID, and DON'T touch the relationship Sort options, any time you'd be creating a record, the first visit date should fill up that d_FirstVisitDate.

About the relookup, you still can select the Patient_ID field somewhere, but you should need to do this only once...

See attached a quick sampler.

FirstVisit.fp5.zip

[cbum]

Ugo,

perhaps my problem is that it isn't doing the lookup since the records already exist?

In any case, the field remains blank, and I don't know how to force a relookup...

[Ugo DI LUCA]

Oh yes, sure....

It will work this way when you'll be in the process of adding records.

In the meanwhile, you can just create a new layout, blank, and add drag and drop the Patient_ID field there.

Then, show all records and select this field. Manually perform a relookup.

That's it.

Back up your file first...

[cbum]

thanks.

I actually brute forced it by adding a new field, and replacing with the content of the calc (unindexable) field.... not very elegant, but it worked.

I find the different conditions that determine if a calcs/lookups/updates are reflected in records, particularly existing ones, quite frustrating...

[Ugo DI LUCA]

"not very elegant, but it worked...."

You'll get smarter. Just need to start somewhere he !

[cbum]

Ugo,

you mentioned the following: "Finally, I assume having a list without duplicate would also be a better choice. With some little work on the relationship and some new field, you could sort out any duplicate too".

I do need to create lists without duplicates (multiple records from the same patient), if only to get the number of cases matching certain criteria.

Could you elaborate on your comment? It seems this would be an important feature for many uses...