Compare Field in Previous Record

[clz]

I would like to compare the fields in a record to the fields in the previous record and change the color of the text if the field is different. The comparison will be done in a found set of records so the comparison will need to be dynamic.

For instance, the weight in the first found record is 100, the weight in the second record is 200 and the weight in the third is 200. I want the 200 in the second record to display in Red and the other two records will display in black.

Any suggestions?

[Ender]

In FM8 or later, this would be pretty straight forward. In FM6, it's not as simple. The solution depends on what you mean by "previous record".

If you mean the previously created record, there are a couple methods, the best probably being a self-join on the RecordID-1 to the RecordID. Then a calc can determine if the value is different for the current record, and display the value. The calc field on the layout would then be set to diplay in red and overlaid on the original field.

If you mean the previous record in the found set, you'll need a script to loop through the found set, setting a global with the current value, and with each iteration, comparing the global with the current records value, then set a regular text or number field for those that are different. That field would then be displayed in red and overlaid on the original field.

[clz]

Thanks for your reply. I forgot to update my profile to show that I am using FMP 8 Advanced. I want to compare records in a found set not necessarily in the order they were created. How would I accomplish the field comparisons using this version?

Cindy

Edited September 24, 2006 by Guest