2 Replies Latest reply on Apr 28, 2010 1:20 AM by Kristoffernolgren

    Dashboard-like thing for filemaker.



      Dashboard-like thing for filemaker.


      Okay, so I'm pretty new to filemaker and have this problem. I don't even know what question to ask, so if there is already a solution, out there somewhere, i'm sorry for bugging you. I tried to search but couldn't find anything.


      Here is the scenario:


      I'm designing a database for a sertain workflow. Every time a customer calls there are a number of predefined tasks that ought to be done. Every time a new customer calls a new dataline is created in a table called "Assignments", every assignment get's an assignment ID there is also an other table called "tasks". Every task has an ID and a name. For every assignment, every task can get checked of from a checklist. When a task is checked of, the taskID and assignment ID get's added to a new row in a jointable. 


      I want to create a new layout where all non completed (not all tasks are checked of) are displayed, so you can see ongoing assignments.


      The condition i'd like to create is "if the number of datarows in the jointable with a specific assignmentID are less than the total number of rows in the task table" Display this assignmentID in a list.

        • 1. Re: Dashboard-like thing for filemaker.

          You have a join table; it has all the Tasks and their Assignments, as well as a field for Completed. I would also create a calculation field for  "not completed" (tested vs. the Completed field). It could just produce a 1 (or it could produce one of the IDs, if needed).


          Your "dashboard" table (could be most any table) could have a "constant" calculation, number result, = 1


          If you point that to the Assignment | Tasks join table, to the Not_Completed calculation field (I'd name it "z_cCompleted_NOT", but that's just me :-), it would hit all records not completed.


          If you created another little relationship, from that join table, to another instance of the Assignments table (which has a unique record for each assignment), using this, based only on Assignment ID, it would target only Assignment records which had at least one Task not completed in the join table.* This relationship works like a funnel; an Assignment ID might have many "not completed" hits in the join table, but the relationship to Assignments would funnel it down to 1.


          You could show this in a portal on a Dashboard layout. There are other ways to do this I imagine, but this seems fairly direct. 


          * Be sure to name this new relationship in a way that shows what its path is, or function is (I use path, ie., structural naming, alphabetical when possible).

          • 2. Re: Dashboard-like thing for filemaker.

            Awesome thanks a bunch, i'm gonna describe the model (just so I get it straight)


            In the jointable, there are three columns:

            AssignmentID TaskID Completed


            Completed is Boolean (1/0)


            Everytime a new assignment is created (when a customer calls) a line is created in the jointable with the assignments ID and each task ID, every completed-task is 0.


            Whenever a task is checked-of the completed task gets set to 1.