# Is it possible to find and compare different records in a calculation?

This topic is 2963 days old. Please don't post here. Open a new topic instead.

## Recommended Posts

Hi,

In my database I have different records of the same person in different years. As an example let´s say each record contains the amount of money the person has and the amount he wants to have next year. Is it possible to create a field with a calculation that will indicate whether the plan is achieved?

The calculation will have to find a record on both name and year, and compare the planned amount with the actual amount.

Edit: Just finding and retrieving a value from a different record should be sufficient actually, is this possible?

Edited by Vliet057
##### Share on other sites

27 minutes ago, Vliet057 said:

The calculation will have to find a record on both name and year, and compare the planned amount with the actual amount.

A calculation cannot find a record - but a relationship can. Define a self-join relationship matching on PersonID and Year (using a calculation of Year - 1 on one side) and get the data from the matching record. It would probably be best to have each record lookup the previous goal, so that you can work with stored data.

##### Share on other sites

Hi,

Thank you for the response. Your explanation is not completely understandable to me. I understand I make two occurrences of the table, and make a relationship between PersonID - PersonID and year - year, correct? (btw it won´t be able to find matching personID´s, although it concerns the same person they have different ID´s since its separate records).

And you lost me at the the calculation year - 1 ( I can´t fill in calculation in a relationship?), could you explain this in a way a newbie can understand?

##### Share on other sites

9 minutes ago, Vliet057 said:

Your explanation is not completely understandable to me.

Let me try and understand yours first.

By your description, you should have two tables: People (where each person has one record, with a unique PersonID) and a table of Years (for lack of better name), where each person could have any number of related records. In this table, you would have fields for PersonID (serving as the matchfield to the parent record in People), Year, Amount (actual) and NextYearAmount (planned).

If that's not what you have, then we're already in trouble.

##### Share on other sites

Yes that is not what I meant.

I have one table that hold the fields:

-Person

-Year

-amount of money

-planned amount of money.

What I actually want is to create another field in the table which will show the planned amount of money of the same person from the previous year.

##### Share on other sites

This is not a good arrangement to have, because (a) it needlessly duplicates information about a person from year to year, and (b) people's names can change (sometimes just by correcting a spelling error) - and when that happens, you will have nothing to link the person's records together (unless you painstakingly replicate the same change to all of them).

Have a look at the attached demo.

Edited by comment
##### Share on other sites

You are absolutely right but it just seemed to be the easiest way when I started working with filemaker. Now The whole database if filled so I cannot change anything about it. Your demo works great, I am going to play a bit with it and see if I get it implemented in my own project.

Thanks!

##### Share on other sites

5 hours ago, Vliet057 said:

The whole database if filled so I cannot change anything about it.

I don't see why not. It shouldn't take more than half an hour to migrate your data to a properly structured solution..

• 1
##### Share on other sites

This topic is 2963 days old. Please don't post here. Open a new topic instead.

## Create an account

Register a new account