I would do this work in a script. After you mark a task as "Yes", a script would run that would count the number of tasks related to this key step and count the number of Yes values. If they match, set the key step Completed value to "Yes". Otherwise "No".
A couple of options:
1. You can run the script by pressing a button on the layout
2. Run the script as a trigger on the task completed fields.
To make this work, I created a field called YesNo_c inside my tasks table that is a calculated field: if (Tasks::Completed = "Yes" ; 1 )
This will set this field to 1 only when you mark it as completed.
My script then counts the number of related task records to the keystep i'm on and puts it in a variable called $Total. Then it Sums the value of the YesNo_c field.
Then the branching logic of what to do with the KeyStep Completed field.
I prefer a script because I have control over when it happens. And this allows me to manually change the KeyStep completed to "Yes" even if not all the tasks are marked as complete. That might be a possibility.
See the demo file. Its pretty simple. I've got one goal with 5 steps in a portal. Click on any step to see the tasks for that step. Step 1 has all tasks completed, so the Step::Completed field is set to "Yes"
EDIT: A better version of the File
Goals.fmp12.zip 72.5 K
I got it setup. My next hurdle is that I have Key Step to change to "Yes" when Tasks are all completed ("Yes"). And then I set it up for Key Steps --> Goals. That said, I cannot seem to figure out the write trigger to activate the Key Steps --> Goals script. I have it on OnObjectModify, but that only triggers when the Key Step is clicked "Yes" itself, but not when it is automatically changed when all the sub-Tasks are completed. Any recommendation of how to automatically activate the script?
So yes, the OnObjectModify will not work when the field is updated from another source. So you're going to have to extend the script a bit to do the same thing at the goals → key steps level.
I would do the same thing pretty much in the KeyStep table we did for the Tasks table: set that calc field to set value to 1 if the Yes/No field is "yes".
You'll need to go up to the related goal record, so use GoToRelatedRecords, then look at all the key steps from that context or view. Do the same kind of logic we did when looking at all related tasks records.
Does that make sense?
I'll let you work through that a bit, but feel free to ping me through here or an email if you get stuck.
1 of 1 people found this helpful
Actually there's a better, more efficient way. Apologies for not thinking about this earlier:
Start by creating that calc field in the KeySteps that sets its value to 1 if KeySteps::Completed = "Yes"
1. Create a self-join relationship for the KeySteps table. Set up the relationship on the ID_Goal field.
this allows you to see all the key steps that are part of the same goal. If you're on Goal 1, key step 3, you are seeing all of Goal 1's key steps through this relationship.
I named my new TO: Goals_keySteps_KeySteps
2. Use that relationship to find the total records and the count of 1s (when the completed field is "Yes").
Use similar calcs we used from KeySteps to Tasks in this relationship. I set mine up like this:
- Total key steps for this goal:
Set variable [$Total ; Count ( Goals_KeySteps_KeySteps::ID ) ]
- Sum of key steps completed:
Set variable [$Count ; Sum ( Goals_KeySteps_KeySteps::YesNo_c)]
3. Use these two variables to do the same logic we did previously.
If I wasn't clear, this is part of the same script that you wrote to check tasks. You always want to check for key steps to be completed after the tasks have been marked as Completed. Put a Commit Records step in between so you have the latest information of the KeyStep you're on.
Let me know if I should send you my updated demo☺
- Total key steps for this goal:
Yes, the demo would be hugely helpful, I'm new to self-join relationships and it still is hard for me to fully wrap my head around.
Also, what does the acronym "TO" refer to? In a self-job do you rename a field in the 2nd occurrence table?
TO stands for table occurrence, or those little boxes in the relationship graph.
Self-join is simply joining two TOs together that are from the same underlying table. People use these for many reasons.
I took the demo I provided and grabbed some screen shots:
This first one shows the new TO. It is based on the KeySteps table (as you can see by the last word in the TO name). And it is relating all keySteps with the same goal ID to each other.
This is the new script steps. I simply used the same pattern as above. Notice line 16 and 18 are the same variable as above. That's ok. I'm done with them in the scope above. I'm reusing them. Since I know they'll always be reset by the relationship's values, I can rest assured that either variable won't hold previous data in it.
In line 22 and 24, I'm simply reaching back to the Goals table and setting that field.
Give this a try. Set this up in your file and then go to any KeyStep and mark each task as Yes. Do that for all key steps of a goal and you should see the Goal update to "Yes" in its completed field.
Goals.fmp12.zip 73.1 K
Jeremy, thank you. (I actually conversed with Derek and Steve this morning re a CWP concept.)
I am running into an odd glitch. I have a layout setup that has the Goal, KeySteps [portal], and Tasks [portal] all in one layout. When I move one of my later Tasks to "No" the first Key Step (not associated with this Task) changes to "No". I can then change the correct KeyStep to "No" manually, and then it won't go back to "Yes" until the Task is checked "Yes". HOWEVER, even then, when I move that Task back to "Yes", the first KeyStep in the portal, not associated with that Task, is the one that changes to "Yes", the correct KeyStep does not change in the portal.
In the individual KeyStep layout, it all works properly. Not sure of my glitch.
Also, just wondering, is there a way to delineate a separation of the Tasks in the portal so they are grouped together by KeyStep (instead all just listed clumped vertically)?
Hi Evan. You spoke with Steve and Derek. Cool. Steve is a great guy. I enjoyed going into his office everyday and talking about something. He taught me all about Recursive Functions.
To your glitch.
Without fully seeing your data structure, I do see an issue with the way I presented it to you. The tasks have to be directly related to a key step, otherwise you can't mark all tasks as YES to get the key step to be Yes. I guess I'd make that tasks portal related to only one keystep at a time.
You can set it up to click in a key step row, set a field called Goals::ID_Keystep and use this field to relate to the tasks. When you click on step 1, you'll see its tasks, step 2, and see its tasks and so on.
The current setup, the current way I suggested won't work because you have all tasks relating.
I'll put together another quick demo based on what I see.
And if you'd like to talk in person, I'd be happy to spend some time with you to get this correct. PM me and I can arrange a time.
Jeremy -- I created a series of portals that create a hierarchy (re: popover in a portal that houses another portal (workaround) ).
I can now show all Key Steps for an individual Goal, and all the Tasks for a selected individual Key Step. But when I toggle the task "Yes/No" radio button, it seems to only/always change the Key Step that is listed in the first row of the Key Step portal, regardless of which one is actually selected and has its Tasks shown in the Tasks portal.
Now, ideally, once completed, people are not going to "uncomplete" a task, but in the name of ironing out glitches, let me know if you have any thoughts. I'll continue to test though.
I built my goals file to incorporate the cascading portals and found the same issue that you found.
The issue is because your script is (probably) updating the Yes/No for keysteps through the portal relationship you've got set up. Updating thru that relationship will only look at the 1st record of the portal.
You'll need to set up another relationship to the KeySteps that relates the global KeyStep field to the ID of the keystep. Then you can use that relationship to update the record.
See my (updated) file. Be on the zSystem layout.
Goals_Cascading.fmp12.zip 76.5 K
Finally got it up and running! Had a weird glitch on my project but ended up figuring it out. I now no longer can create new Goal, Key Step, and Task records in the portal (I moved the layered button from below the text to an icon arrow so the text would be clickable). I'm assuming it could be done by allowing record creation in a relationship, but now with the global linked occurrences and the original tables, I'm not sure how to recreate that connection. I have it in my original, but since the hierarchy portals are in the occurrences, but not linked to each other, I'm not sure the proper approach.
I almost never create records via the portal. I think it looks outdated, and there are more modern ways to do it.
You can create a new record in any of the tables. I typically use popovers that contain a global field or two. In my scripting, a new record is created in the relevant table and the value of the global is put into the newly-created record.
For goals: all you need to do is have one field that allows the user to write the goal name. Then you go to a goals layout (preferrably a blank one so user doesn't see other records), create a new record, put the global value into the goal name field.
To finish this, clear out the global, go back to the original layout, and close the popover. If you want that new goal to be active, simply set the ID_Goal_g field (or what you call it) to the value of the ID of the goal you just created.
For KeySteps, have two options: a popover that contains new globals that allows the user to pick the goal and write the keystep, or you can just create a keystep for the active goal (the goal with the ID that's populated in the ID_goal_g field. Do the same thing as above: allow the user to type in the step into a Step_g field, go to a KeyStep-table layout, new record. This time set the KeyStep::ID_Goal to the value in that global field as well as the name of the keystep.
The concept is that you're creating a new record in those tables by:
1. Allowing user to type in the name of the goal or step or task into some global field
2. Going to the specific layout in which you want to create a record.
3. Create new record. Populate relevant fields with appropriate foreign keys.
4. Returning to the layout.
5. Clearing out the global fields in which the user typed.
6. Refreshing the relevant portal (use the Refresh Portal script step).
It is not too difficult. Once you have the process, it will be easy to replicate anywhere.
You could see if this works for you:
It is a good concept to study. But again, it uses the create-records-via-relationship, and I don't prefer that.
There is a minor glitch that if a Key Step does NOT have a Task below it, it is impossible to mark the Key Step completed. If tried in the portal, there is no change; if tried in a window on the Key Step table itself, it automatically reverts back to "No" after the "Yes" is selected. Any thoughts?
Howdy. Can you post a screenshot of how you have your portals set up? In my demo file I turned off data entry in browse mode for those fields.
Once I remembered to turn that on, I was able to change a step without tasks from Yes to No. The Yes/No field is simply a text field, so it is possible to override that.