comparing records in same table

[CWH]

I have a database that I set up (naively) flat. Each record represents a quarterly report from an HIV clinic in Africa. There is an id field for each record, so you can find all the reports for each clinic. Each report is numbered in another field. I need to compare the results from one quarter to another, finding any that have been operating for more than 1 year that have had a decrease in a field that tracks the percentage of women getting treated.

I think I need some kind of script loop, and I started by self joining the id field so records for the same clinic are linked, but after that I'm not sure where to go. An If calc probably, but I'm ignorant about how they work.

Any suggestions much appreciated.

[Zero Tolerence]

Easy way to view both of them on the same screen would be to create two global fields, named whatever.

Create 2 extra instances of your table relate one of your new fields to the id in one of em, and the other field to the id in the 2nd new table instance.

Punch the ID's of the records you wish to compare in your two newly created fields and bam, you now have one record that is related to two records.

Create two portals, (one for each table instance) with the data you wish to compare and thats it.

Now if your wanting to do more than just look and print that might not work for you. but theres a quick and easy way to compare two records in a flat file.

I'd suggest creating a new table for these purposes, and then creating your two global fields in there. But thats more of a preference and need for organization on my part, plus thought of future reports than any real issue i see now for you.

[CWH]

Thanks, Zero. I think I didn't give enough background. I'm trying to find any set(s) of records for clinics where a value in field X (actually a percentage) has decreased over time. So my Find is something like Find any record where Report number is greater than 4 (4 quarter reports = operating over 1 year)and X in previous reports is greater. Since I'm trying to find clinics that have an overall declining trend, the script need to look at (and retrieve) a series of records.

[Zero Tolerence]

But still the same basic thing would apply. just make your relationship based off of that number "4" (That you entered into a global field.) with a greater than join to the Report Number field. Then a portal will list everyone record that has a "Report Number" of greater than 4.

[comment]

First, if you want to find clinics, you should have a separate table of clinics - where each clinic has one unique record. This would be related to the reports table by matching the clinic ID, so each report record has a parent clinic record.

This will be only a first step towards solving your issue, but a necessary one. It will be easy to find clinics with more than 4 reports, since each parent can count its children in a calculation field.

The next step is more difficult, because "trend" is such a flimsy concept. Numbers can go up and down in turn, but a human will still determine "an overall decreasing trend". These things are difficult to model, especially in a database. One possible easy way out would be to compare the first and the last value in the series, and call that a trend.

[CWH]

thanks comment. That's what I was afraid of. A lesson in the importance of designing well before you start.

Well, my amateur effort did the job for the last 7 years but now the whole thing is migrating to a giant sql system with none of the usability of FMP. I was just trying to milk some last info out of what I built, but doubt I'll have time to set this up.

[comment]

I don't think it should take more than say half an hour to fix this. You would simply import your records to a new Clinics table, while de-duping them - see here, for example:

http://www.fmforums.com/forum/showtopic.php?tid/188466

the whole thing is migrating to a giant sql system with none of the usability of FMP

Sounds like the new version 9 should be right up your alley.