7 Replies Latest reply on Oct 15, 2015 5:42 PM by dyb

    Starter Solution "Projects" – Counting Tasks


      I'm trying to modify the starter solution "Projects" (FMP14) to my needs.



      I give my projects numbers "#2569" and my tasks numbers and initials of the person that's working on it: "01 MD".

      I'm trying to figure out what's going on in the tables and fields but i'm lost.


      When i create 2 tasks in a project i want the first one to be set to "01" and the second to "02".
      This value is then used to create folders through applescript and to set the task name.


      I also need to check for the task resources and get the initial of the resource.
      I know there is a field ready for that so i only need to check who is assigned to the task.


      I don't know if these elements are already implemented in this solution – if so, please tell me where to look.




        • 1. Re: Starter Solution "Projects" – Counting Tasks

          I've never played around with the starter solutions, but here's what I can tell from a cursory look at it:

          In the starter solution, each project is currently given a number (PROJECT ID MATCH FIELD) and each task is given a number (TASK ID MATCH FIELD). So there are your project and task numbers, and they are serial numbers so each new project or task will be given the next serial number. (Neither of these are currently set up to be unique, which is somewhat puzzling.) You can change the next serial number to whatever you want (2569) by managing the database, double-clicking on the field you want, and editing what the next serial number will be. The way it's set up, all tasks go into the same table, and all are given a serial number. Thus, task 1 might be assigned to project A and to person MD, task 2 to project B and to person JL, task 3 to project A and person JL, etc.


          So, with the current system, Project A would have tasks 1 and 3, Project B would have task 2. JL would have tasks 2 and 3, MD task 1.


          From your question, it sounds like you'd like each task for a given project to be serially numbered, given two digits, and have the person's initials appended. Right? One way to do this would be be to count all previous tasks for that project and add one; then format the number to have two or more digits, then add the person's initials.


          You want to add CONTACT ID MATCH FIELD to the tasks table, and create a relationship between that and the CONTACT ID MATCH FIELD in the Contacts Table. Let's call that TO ContactsFromTasks.


          You could then define a field TaskID as a calculation, result = text, with the following calculation:

          Let ( [

          ~N = ExecuteSQL ( "SELECT COUNT ( \"TASK ID MATCH FIELD\" )

                    FROM Tasks

                    WHERE \"PROJECT ID MATCH FIELD\" = ? AND \"TASK ID MATCH FIELD\" , ? " ;

                    "" ; "" ; Tasks::PROJECT ID MATCH FIELD ; Tasks::TASK ID MATCH FIELD )

                    + 1 /*Count previous serial #s, add 1*/;

          ~Nf = Case ( Length ( N ) > 1 ; N ; "0" & N ) /*Nformatted  has two or more characters*/ ;

          ~I = ContactsFromTasks::Initial ] ;

          ~Nf & " " & ~I )

          • 2. Re: Starter Solution "Projects" – Counting Tasks

            From your question, it sounds like you'd like each task for a given project to be serially numbered, given two digits, and have the person's initials appended. Right?


            Yes, exactly.


            Your calculation seems clear to me although i'm not sure how to set up the relations properly.


            I created "CONTACT ID MATCH FIELD" in "Tasks" – but what's its value?

            I created a relationship between "Tasks::CONTACT ID MATCH FIELD" and "ContactsFromTasks::CONTACT ID MATCH FIELD" which i created from "Contacts" table.

            I created a "Task_ID" field in "Tasks" table and set it to calculation you provided.


            The field is empty though...

            • 3. Re: Starter Solution "Projects" – Counting Tasks

              I actually do get a value: "0? "

              I had to create a new task to see this value.


              What did i do wrongthurmes ?

              • 4. Re: Starter Solution "Projects" – Counting Tasks

                I managed to get the Initials part working but it seems that the original "Contacts::Initials" field has a wrong calculation. It outputs only one letter instead of two.

                Upper     (


                        Case        (

                                not IsEmpty ( Last ) ;

                                Left ( Last ; 1 ) ;


                                not IsEmpty ( First ) ;

                                Left ( First ; 1 ) ;


                                not IsEmpty ( Company ) ;

                                Left ( Company ; 1 ) ;






                Is this a proper calculation? Is seems to only go through the first test. I tried to fill all the contacts field "company" included and it also outputs only the first test which is the first letter of "Last" field.

                • 5. Re: Starter Solution "Projects" – Counting Tasks

                  The CONTACT ID MATCH FIELD will be a unique number corresponding to the person who will be assigned the task. So, if MD's CONTACT ID MATCH FIELD is 1, and you're assigning a particular task to MD, you would enter 1 into that field (for every instance where MD has been assigned a task).  In fact, the number assigned to a new contact is a UUID, so it will look something like this: D2744A45-572E-174D-9C40-C8F9F281CD89. When you assign a person to a given task, you'll want to trigger a script that grabs the person's CONTACT ID MATCH FIELD and writes it to the field of the same name in Task.


                  I guess I should have looked more closely at the file. Much of the work was unnecessary, because there was already a field that counted the number of tasks assigned to a project (Projects::Task Number). The connection I had you make was essentially already made, but under the table name Resources | Contacts. So I played around with the file, and you can see the result. I changed the equation for the Initials field, so now you get both the first and last names' initials, added another two fields (Task Number for this Project; TaskID) to the Tasks layout; they look kludgy but that makes it easier for you to see; you can do what you want with them.

                  • 6. Re: Starter Solution "Projects" – Counting Tasks

                    That is the way Case() works—as soon as it finds a condition that delivers a True result, it ignores the rest. The best way to build a calc like this is to use the Let() function. See this example:


                    Let (


                       firstName = "Fred" // replace text with your field name

                      ; lastName = "Flintstone" // replace text with your field name

                      ; coName = "Rubble & Co" // replace text with your field name

                      ; firstInit = Left (firstName ; 1 )

                      ; lastInit = Left (lastName ; 1 )

                      ; coInit = Left (coName ; 1 )


                      ; result =

                       Upper (

                      Case (

                      not IsEmpty ( lastInit ) and not IsEmpty ( firstInit ) ; firstInit & lastInit ; // result of there is BOTH first and last name

                      not IsEmpty ( lastInit ) and IsEmpty ( firstInit ) and not IsEmpty (coInit ) ; lastInit & coInit ; // resuilt if the is no first name but there is company name

                      // you can add any other variations you need in here

                      "xx" // result of none of the above conditions match



                      ] ;



                    • 7. Re: Starter Solution "Projects" – Counting Tasks

                      As far as i understand, i don't really need the new CONTACT ID MATCH FIELD in "Tasks" as this relationship is already set up with "Resources | Contacts" and "Tasks Per Contact"? I think i get the concept of this whole matching thing but it's so much going on in one moment i guess i'll have to write it down bit by bit


                      As for the rest, i actually managed to do the same thing by myself except i created another field "Initials" which calculates 2 letters and left the original "Inital" for the sake of sorting the resources list. Also in your file your "TaskID" calculation only gets one set of initials – it doesn't list all the resources added to the task so i guess wrapping in a List function and proper formatting corrects the problem. I simply copied "Verbose Resources | Multiple" and fiddled with the calculation so it outputs nicely formated initials i already calculated in the "Initials" field.


                      I will post my finished solution later.


                      Thank you for your help! I learned much!