5 Replies Latest reply on May 25, 2014 2:19 AM by zebdaag

    If calculation..



      If calculation..



           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?

        • 1. Re: If calculation..

                    these notes are connected to John by his ID and to the Project by Projectname.

               Would appear to be a likely issue. Exactly how did you set up your relationships linking notes to both projects and contacts? (there's more than one way you might do that.)

          • 2. Re: If calculation..

                 Both notes and projects is linked to contact by ID.  Notes and Projects are not linked.

                 in project you enter a project for example "website"

                 then when entering a note you can choose to wich project it belongs and you can choose from a list of project with related contact ID

            • 3. Re: If calculation..

                   Then your notes are not linked to Projects by Project Name.

                   If you are trying to set up a list of projects as a list or table view layout based on Projects, you have no direct link to Notes for that project. You have a link to contacts which then links to all notes for that contact and Last returns the last note made for that contact, not the last note for that project.

                   I suggest this set of relationships:


                   Contacts::__pkContactID = Projects::_fkContactID
                   Projects::__pkProjectID = Notes::_fkProjectID

                   And __PkProjectID is NOT the name of the project, but an ID generated as an auto-entered serial number or Get (UUID). With each note linked directly to Projects your Last function will return the most recently recorded note for each project. (And an easy way to record notes is in a portal to notes placed on the Projects layout.)

                   PS, you can have it both ways as long as you make sure that the correct values get entered into the match fields in the Notes table, you can set up one Tutorial: What are Table Occurrences? of Notes to link to contacts and a different one to link directly to projects.

              • 4. Re: If calculation..

                     Okay I understand that. But by auto-entering Notes::_fkProjectID I can't manage which project belongs to my note?
                I added and image for you to get a better view on what I want.


                • 5. Re: If calculation..

                       Okay, i got it, don't know what went wrong the first time but your option works perfect now.