Sounds like an interesting challenge.
I made you a quick little demo file, really basic.
I don't know if this is the best way to do this or not, but I think something like this might work.
I haven't gotten to the 'Percentage done' part yet, but I'll try to work on it a little when I have some time.
I do however still seem to have a problem creating new records (New Departments and new Tasks) from within a portal.
I have been searching for an App to this as well. I've tried OmniFocus, Things and so many other project management apps but none will do this. I was told FileMaker would be my best option Do you have any suggestions? Thanks for the demo file. It's really nice
This is very doable in FileMaker, treat it like a survey. The stores are the survey takers (table 1), the departments (table 2) are the question categories and tasks (table 3) are the questions. The answers (table 4) to the tasks are a "must create new" every time, by script or relationship, and each answer record will contain the date, the store ID, the department ID, the question ID and the task ID. Make the response field in answers a boolean. Do the math on the answers.
You have a many to many relationship between tasks and stores. YOu have many stores to link to your tasks and many tasks to perform at each store.
You might take a look at the "check boxes" layout in this demo file on many to many relationships: http://www.4shared.com/file/dZ0bjclw/ManyToManywDemoWExtras.html
I think this situation even goes a little further.
Like Jeff said, you have multiple stores. Every store has several departments.
But the other stores have the same departments.
So there you already have a many to many relationship.
But then you haven't even gotten to the tasks yet.
Every department has several tasks, and the other departments have the same tasks.
So there is another many to many.
In my demo file I posted a little earlier I tried to deal with this situation by creating a layout based on the table Stores.
And then I creted two portals, One for the Departments, and one for the tasks.
If you select a department in the first portal you see it's tasks in the second potal.
This has 2 join tables. One between Stores and Departments, and one for the tasks.
The thing I was having a problem with is creating records straight onto these portals, but that doesn't seem possible. Probably because of the join table not being the basis for the layout.
The relationship between departments and tasks would seem to be very similar. The fact that "departments" records can be grouped and related to different stores doesn't seem to be a major part of the problem.
I'm suggesting that DaSaint take a look at the checkboxes layout in my demo file. It has buttons in the portal that could be used to check off tasks as they are completed. A script is used to modify data in the join table so it looks like you are editing records directly in the portal when you are actually modifying data in the join table. It's not a perfect match, but may give you some ideas.