Calculation to compare a current record to a previous record

[LochNessMonster]

Hi Guys,

 

I've been working with FileMaker for about a month now and am busy hacking away at my first project.  Up to this point I've managed to find solutions to all of my problems on these forums and others but I've now come up against a problem that has me stumped.

 

What I would like to do is to setup a calculation field that compares the current values of a field to the previous record value for that field in a sorted portal.

 

I have a portal that is showing related records.  My current portal has 4 related records in it sorted by date and looks like the following:

 

Primary Key:  Review Date:    Value:    Changed? (Has value changed since previous review - Calculated):

2                    25/11/2013       5            No

1                    24/11/2013       5            Yes

4                    23/11/2013       4            No

3                    22/11/2013       4            N/A - Inital Review

 

The "Changed?" field is what I want to automatically calculate.  I thought about using Get(RecordNumber) to get the previous record but the problem is the record numbers that Get(RecordNumber) uses might not be in order if a user enters data with a date that's earlier than an existing record.

 

I'm really not sure how I can achieve this.  I'm thinking I might have to setup some kind of related table occurance but unfortunately my understanding of filemaker is not quite there yet.  Any suggestions you guys can give me would be immensely appreciated.

 

Kind regards,

LochNess

 

 

[LochNessMonster]

Hi Guys,

 

It's funny how writing things out can help to make things clearer.  It's only been about 30 minutes since I posted my question and I think I've now found the solution using a second table occurance as I expected.

 

I setup a second table occurance of the "Review" table called "Review_Previous" with the following relationship:

 

Review | Review Previous

 

Primary Key = Primary Key

AND Review Date > Review Date

 

I also set the second table occurance to sort based on the review date order the same as I had in the portal.

 

Finally I created my quick and dirty script to calculate automatically compare the current record to the previous record and return a YES or NO answer as follows:

 

If(Value  ≠ Review_Previous::Value; "Yes"; "No")

 

That's it.  It appears to be working.

 

If you guys have any comments or suggestions on my solution please let me know.

 

Cheers,

LochNess

[comment]

Yes, that is correct (I was writing all of that up while you posted...).

 

 

A minor point: I would make the calculation return a result of type Number (0 or 1) and format is as Boolean on the layout.

[LochNessMonster]

Hi Comment,

 

Thanks for the feedback.  It's much appreciated.

 

I think I understand what you mean.  I must admit that I've just been storing all of my boolean fields with Yes or No text via radio button fields.

 

What are the benefits of storing it as a number instead of text and then how do I go about getting the field to display yes or no on my layout instead of 1 or 0?

 

Kind regards,

LochNess

[comment]

Number fields can be summarized. Another benefit is that you can easily change the displayed label, e.g. "Changed" instead of "Yes", without modifying your existing data or value list. Also, a calculation going =

 

Case ( Flagged ; ... )

 

is easier to read and modify than

 

Case ( Flagged = "Yes"  ; ... )

 

 

 

how do I go about getting the field to display yes or no on my layout instead of 1 or 0?

 

As i said, format it as Boolean (in the Inspector).