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

[Vliet057]

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 January 21, 2016 by Vliet057

[comment]

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.

[Vliet057]

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?

[comment]

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.

 

[Vliet057]

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.

[comment]

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.

LookupPrev.fp7

 

 

Edited January 21, 2016 by comment

[Vliet057]

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!

[comment]

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..