change field name in script

[swf]

Go to Layout [ “Labs” (patients) ]

Set Field [ [color:red]Labs::gAbbrev; "na" ]

Set Variable [ $c; Value:0 ]

Loop

Set Variable [ $c; Value:$c+1 ]

Go to Field [ Labs 2::DATE COLLECTED ]

Set Field [ [color:green]summary::nadate[$c]; Labs 2::DATE COLLECTED ]

Set Field [ [color:green]summary::navalue[$c]; Labs 2::RESULTS ]

Go to Portal Row

[ Select; Next ]

Exit Loop If [ $c>2 ]

End Loop

Go to Layout [ “Summary” (patients) ]

This script is working great so far. It sets gabbrev to "na" and performs the task. Now to make it very powerful I want it to change "na" to other values and perform the loop on each of them. the next values are "k","cl","co2". In all the are about 20 values.

2 questions:

1)

how can I get a variable to look up the next value

$a="k" then "cl" then "co2" etc

[color:red]eg: setfield( gabbrev;$a)

2) how can I make the fieldname dependent on the value. original is [color:green]nadate then kdate then cldate. Same applies to [color:green]navalue then kvalue then clvalue. It won't let me calculate a field name.

[aaa]

Hi, swf! Put all values na to one global text field by space, let say Global_text then anytfing like this:

Set Variable [ $i; Value:1 ]

Loop

Set Variable [ $na; MiddleWords(Global,text,Value:$i,1 ]

Exit Loop If(isempty($na))

....Here put you cycle, where instead "na" text

....use $na varible............................

Set Variable [ $i; Value:$i+1 ]

EndLoop

I think this must work.

how can I make the fieldname dependent on the value. original is nadate then kdate then cldate. Same applies to navalue then kvalue then clvalue. It won't let me calculate a field name.

Here must help GetField() function.Read about this function. If you have problem after i will make example for you.

Edited November 10, 2005 by Guest

[Søren Dyhr]

Set Field [ summary::nadate[$c]; Labs 2::DATE COLLECTED ] 

Set Field [ summary::navalue[$c]; Labs 2::RESULTS ]

Why bother scripting these things when Summary fields can be tunneled - actually when it comes to it, is remedying a inadequate datastructure with repeaters and excessive scripting a journey by which you discover that you need to plan your solution, instead of acting on instinct. Statistics and repeating fields is likely to cause frustrations, if not now then very very soon. David Kachel puts it very similar in his whitepaper:

These are very useful, and a huge bear trap for the FMP newcomer. The rule is simple: DON’T USE THEM! Why not? Repeating fields become a serious problem when the data in them has to be altered, manipulated, retrieved, included in calculations, reported on, exported or imported. You will create severely difficult programming conundrums for yourself

From: http://www.codemastersworkshop.com/Downloads/WhitePaperForFMPNovices.pdf Back to your scripting!! It seems like you would like only the latest two measurements put in a set of fields each derived from filtering a portal makes changing the primary key. Let me suggest a slightly different approach requirering a summary field in the related table that counts the number of each foreing key: Put all primarykeys in a the global field returndelimited and make that primary key for a new relation. This makes it posible to perform a GTRR(SO) that produces a found set in the related tables records ...but we do only wish to keep the last two of each individual foreing key:

...actually isn't the variabel required but it makes the script easier to understand.

We have now strained the found set for every earlier record than the last two of each foreign key. Now a proper designed database uses recordID's all over the place, this means that we now are ready to collect the chosen few records and make them appeare in a cut up portal after gathering them this way:

http://www.filemakermagazine.com/modules.php?op=modload&name=News&file=article&sid=550

What is the benfit of this approach?? No data is moved to another point requirering syncronization - only keys are manipulated!!!

--sd

Sort Records [ Specified Sort Order: Untitled::ForeignKey; ascending ] 

Go to Record/Request/Page [ First ] 

Loop 

      Set Variable [ $where; Value:GetSummary ( Untitled::TheCounting ; Untitled::ForeignKey ) ] 

      Omit Multiple Records [ $where-2 ] [ No dialog ] 

      Exit Loop If [ Get ( RecordNumber ) = Get ( FoundCount )-1 ]

      Go to Record/Request/Page [ 3 ] [ No dialog ] 

End Loop

[swf]

Your expertise is well appreciated. This problem started because I need to print lab values and have read in many places not to print from portal. I need to isolate the last two values from each test from 40,000 records for each patient. I attached a sample prototype file using your recommendation. If anyone can troubleshoot it I would appreciated it. The script is not currently working.

Thanks

test2.fp7.zip

Edited November 11, 2005 by Guest

[comment]

See if the attached helps.

LastNofEachType.fp7.zip

[Søren Dyhr]

Alright I've made an algorithmic mistake in my previous suggestion, but you tend to approach matters differently when you begin to process real data. So here's a corrected version dealing with single occurrences as well.

read in many places not to print from portal

Thats true, but it has more to do with the scrollbar... which I removed!!!

--sd

test2debugged.zip

[swf]

We are almost there. I really need to isolate the 2 last dates not the maximum id numbers. I made some changes to fix that. However there are still endless loops occurring. Try running it on patient 2 and 4 first with just na k in the test_abbreviation global and then with na k cl in the test_abbreviation global. sometimes you place a lab test in the global and there is no test for that patient.

Also I may want to retrieve the last 3 or 4 values. Is there an easy way to adjust that. I tried adjusting the $where-2 to $where-3 but there is a bug.

Thanks.

test2.fp7.zip

Edited November 12, 2005 by Guest

[Søren Dyhr]

Ok where it goes wrong is that you apparently have the registrations of dates that not follows the ID'ing of the records.

You have made a attempt to organizing twice, when you sort is there no need to make a concatanated key as well. Since the autoenter works independent of which patients portal it show up in....

Also I may want to retrieve the last 3 or 4 values. Is there an easy way to adjust that. I tried adjusting the $where-2 to $where-3 but there is a bug.

Indeed it is due to this:

Get ( RecordNumber )+Sign($where-1)+1

...since the calc only can give two results either the next record or the following. It isn't particular flexible since method only can be changed slightly say Sign($where-2)+2 gives {1,2,3} but you talk about at least four. This could be done in a different way, by locking yourself a tad closer to the original Fastsummary algorithm of Edoshins:

http://www.onegasoft.com/tools/fastsummaries/index.shtml

But we can't use the $where variabel twice, since we meanwhile have omitted records, must the GetSummary( be executed again.

Alright I've solved your problem, but is it worth it when Comments solution provides a almost unscripted way to get the right data?

--sd

test2Debug2.zip

[swf]

Bravo! An excellent fix to the solution. This accomplishes exactly what I wanted. As you note my trouble is that I am looking for most recent dates and not serial id numbers. The most recent date is not always the highest id. (the data is 40,000 exported lab values from another database).

You also mention that comment provides an excellent solution without scripting with very clever relationships. My concern is that the calculation field used in the self-join is a number calculation and I am looking for recent date. Perhaps there is way to modify it but at my skill level I was unable to accomplish this. My thanks again. Very clever database structure.

[Søren Dyhr]

My concern is that the calculation field used in the self-join is a number calculation and I am looking for recent date

Well, all dates are stored as integers behind the scenes, it's just a tiny algorithm that makes them display accoding to OS setup.

So wouldn't have the least of worries, to use a date instead of the autoID.

--sd

[comment]

The most recent date is not always the highest id.

It's easy to fix - in the definition of the NextChildren relationship, replace:

Child::ChildID < NextChildren::ChildID

with:

Child::Date < NextChildren::Date

[swf]

Thank you! 2 excellent solutions to the same problem. Thank you both for your expertise. I have learned a great deal.

[swf]

One file adjustment is needed once I started testing this. This works great if the number of records equals or exceeds the script parameter(eg. 5 records with 2 omitted leaves 3.) However if there are only 2 records it will return the most recent 2. Same for 1 or 0. The problem is when displaying these in a cut up portal. Having only 2 values shifts all of the other records into the wrong section of the cut up portal. example portal shows 1-3, 4-6,7-9. Records line up as 1-2,3-5,6-8 etc. Ideal would be to have the script duplicate or add placeholder record to equal script parameter. If there are 2 records and script parameter is 3, it would add 1. I tried this and got it to duplicate some records but continued to end up in an endless loop. Any thoughts?