Calculation/script using data across records

[cbum]

I have a flat file of hematological hospital data, imported into FM11.

Each lab result is a separate record, and there are multiple types of result, eg total white cell count, % lymphocyte count, grams of hemoglobin etc, and fields for patient ID and collection dates.

I need to calculate the total cell type count (eg, lymphocyte) for this dataset, which is basically the total cell count times the % of that cell type, but obviously has to combine data from 2 related records, having the patient ID and the Date in common.

I can use a selfjoin to create a portal showing all values of a patient on the same day, but I can't figure out how to get access to "related" records to calculate this derived value, presumably using a loop creating new records for each calculation.

Any suggestions?

[cbum]

OK, I'm part way there:

the script is

GTR[first]

Loop

Duplicate record

GTRR[from TOC_1; using current layout]

replace field contents [TOC_1::Result; TOC_1::Result * TOC_2::Result / 100

GTR[next; exit after last]

end loop

Fortuitously, the selfjoin relation puts the matching record with the required labvalue from the selfjoin TOC_2 on top, so by grabbing the value in the current record and the top value in the joined record, I can calculate the derived value (total specific cell count).

Next stupid problem: it does so in both the original selected record as well as the duplicated record, overwriting the original value in the original record, a BAD thing...

why is it not just acting on the duplicated record, and how do I get it to do that?

thanks...

[Fitch]

Could you just create a summary report by Date and Patient ID?

[cbum]

I don't really need reports, I need these data for the database (most cases have the required result, but a subset [of these 5 million ! records] does not), and I'm trying to get these missing data.

Edited May 24, 2010 by Guest

[comment]

I am not really sure what you're trying to do here - but Replace Field Contents[] works on the entire found set. Use Set Field[] if you want to modify the current record only.

[Søren Dyhr]

I don't really need reports

I think you do, but you're perhaps not aware of this method:

http://edoshin.skeletonkey.com/2006/12/crosstab_report.html

...nothing else in this tool is build this measure/load of data! What I'm tying this statment to, is this:

which is basically the total cell count times the % of that cell type

--sd

[cbum]

Thanks Søren,

but that's not what I need these data for.

This is a research database, where queries are made against certain lab values in the context of specific population cohorts that are identified by complex searches of the database (i.e.,founds sets).

Your link is a very cool use of reports, but I'm not sure how reports would be useful in that context, since I don't see how they could be used in find mode.

[Søren Dyhr]

since I don't see how they could be used in find mode.

This is the first time "Find" Occur in this thread ... this is perhaps where we need to start instead?

Describe what kind of searches you wish to perform? ... I do actually think I see why you wish to use replace, but instead of selfjoining, should you still use summary fields, but then used as argument inside the Replace via the GetSummary( function.

--sd

[cbum]

The table of hematological data is but one piece of the research database. Investigators want to find subsets of patients, and then query for certain lab values etc.

I'm just trying to clean up the hematological data, which is missing some of the results needed, eg absolute counts of certain cell types. I have the record pairs with the 2 elements needed to calculate the missing absolute counts, which I want to place into a newly created third record (all for the same patient/date, which is what the self join is designed to select).

I have made some progress, or not ....

Ray Cologon suggested to use Set field instead of replace field, since that only acts on the current record, not the found set, and that did in fact work correctly.

Current problem is that the loop structure is not working, since the set field places the execution on the new record, and I loose the position on the original record, and the loop ends after one record is created....

[Søren Dyhr]

But the searches as such is not done in the raw data, but in data bundled/tied together ...such as give me all sets of 10% or less something - is this a correct assumption?

Ray Cologon suggested to use Set field instead of replace field, since that only acts on the current record, not the found set,

Well Comment did the very same some posts above in this thread. Since Ray is replying too, where is these answers given?

Is it somewhere here:

http://forum-en.filemaker.com/t5/Using-FileMaker-Pro/bd-p/FM-en-4

...or? You need to be sure how to handle it, if its doubleposting!

--sd

[cbum]

Sorry, I did not see comment's post before leaving last night (thanks comment), and I emailed Ray at Nightwing.

Being upside down, he was able to reply quite fast, I guess ;-), but not in this forum.

As to the searches, they are typically iterative, and repeated, and reports are actually rarely used except at the very end of a search process.

[cbum]

Thanks, Set Field[] did in fact work correctly, but I'm now facing a problem of how to get the loop to work (see my post to Soren below).

As to what I'm doing - I'm trying to clean up a dataset, where some of the results (each on its own record) are missing, but can theoretically be created using records with 2 complementary results from the same patient and the same day, ie the total cell count and the % of the cell type, which are on separate records. I'm trying to calculate the absolute count of that cell type, and put the result into the new record.

Hope that clear things up.

Once the dataset is cleaned up, I will be integrating it into the larger research database.

[Fitch]

Maybe you could do something like this:

GTR[first]

Loop

Set Variable[ $id ; Get(RecordNumber) ]

Duplicate record

GTRR[ [color:red]New Window ]

Set field [your calc]

Close Window

Omit Record // omits the dupe you just made

GTR[ $id ] // your original record

GTR[next; exit after last]

end loop

[cbum]

Thanks Fitch,

that sounds very promising - I'll give it a try and report back.

[cbum]

Fitch,

I gave your script a try, but I got an error telling me it couldn't modify the record because it was already being modified in another window.

Anyway do deselect the other window?

thanks!

[Søren Dyhr]

but I got an error telling me it couldn't modify the record because it was already being modified in another window.

Anyway do deselect the other window?

This is pretty simple, just enter a Go To Field pointing nowhere before opening the next window.

But there is another thing with Fitch'es suggestion. It can be done simpler since by starting in the opposite direction would two lines of Omit Record get rid of both the original and the clone where the new stuff is put in ... and by it could both the Gtrr(NW) and the close window - even the use of variables could be avoided - since the issue here is a self join instead of a fully normalized solution where structural dependencies have been investigated more thoroughly.

There is an old Bob Weaver algorithm here Fitch corrupts by introducing $variables as well

--sd

[cbum]

Would you mind writing out your script suggestion - I have no confidence in myself to correctly interpret what you are saying ;-)

and by all means elaborate on corrupting older "classic" (?) solutions...

[Fitch]

Put a Commit Record script step at the top of the script. Done.

[Søren Dyhr]

by all means elaborate on corrupting older "classic" (?) solutions...

Eventhough the scenario not is totally equivalent is the gist in it all that you would know where the newly created record would land without any sorting involved. In you case where the portalrows are part of the same table as the main table from where the view is done ... would you know that if you start from the latest record and work towards the first, then could the double "Omit Record" be performed.

I have made a simple template showing a more commonly structured solution, where a ...say an invoice similar to a previous....

--sd

Portaldupe.zip

[Fitch]

I think "corrupt" was an unfortunate word choice, as is the implication that there's anything wrong with using variables. Quite the opposite.

[lisaniel]

Thanx for sharing

Job costing