Preamble: I'm a FM novice and my database has been pieced together over the years with pouring through posts in this forum (really appreciate this community). I have a few tables with relationships and portals, but I'm definitely a newbie.
One component of our company is project management. In our database, my boss would like a simple checkbox placed in a tab where, for a client, he can put a check mark as the project progresses.
Here's a simplified example of the system:
Checkbox Value List:
- Project Started
- Design Completed
- Budget Approved
- Project Completed
My boss wants two components to the checkbox system:
1. Track the date
A date field associated to each checkbox that is auto-dated when a value in the checkbox is checked. I can achieve this by creating a calculation field for each value in the Checkbox value list that logs the date when a value in the checkbox list is selected
Something like this:
If ( FilterValues ( Milestones ; "Project Started" ) = "Project Started¶" ; Get(CurrentDate) ; "")
2. Show the most current progress.
He would like a separate field that shows the most recent milestone value of the project. A sort of "at-a-glance" field (I'm thinking a merge field) that shows where we're currently at with a given project. This is where I'm having difficulty figuring it out.
Because of the nature of checkbox lists, especially that the boxes can be checked, unchecked, rechecked, etc. the order of the values listed in the ProjectMilestones field can change. This is also problematic for the auto-date enter. Meaning that the field that date-calculating field will update to today's date if ever a checkbox is inadvertently unchecked and then rechecked.
Maybe there's a completely different way to accomplish this? One of keys for me is that it needs to be simple and intuitive for the end user (i.e. my boss!).
Appreciate any input.