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\" )
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 )
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?
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...
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.
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.
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.
Projects.fmp12.zip 567.4 K
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:
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 =
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
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!