Ik hope I can explain what I want. Here I go. I'm working on a contact management system.
1. So I have a contact lets call him John. He is in the Contact table.
2. I'm working on two project for John, "project 1" and "project 2", They are in the Project table connect to John by Johns IDnr.
3. Then I have the possibilities to add notes. They can be found in the "Notes" table. And I'm able to make notes for "project 1" and notes for "project 2" these notes are connected to John by his ID and to the Project by Projectname.
Now I want to show a list of all projects including the last note I made.
What I did is I made a new field in the Project tabel called "LastNote" I want to fill this field by using a calculation. What I use now is:
If (Projectname = Notes::Projectname; Last(Notes::Note))
The problem now is that in the list. At project 1 it shows the last note I made no matter what Projectname I add to it. At project 2 it doesn't show any note. What am I doing wrong?