11 Replies Latest reply on Aug 3, 2012 10:40 AM by philmodjunk

    Checklist architecture questions

      Title

      Checklist architecture questions

      Post

      Hey all,
          I had some questions about how to set up a series of checklists for a given item.  Each item goes through a process and we want to track various steps for each process.  We currently have two checklists implemented and we have 4 or 5 more we want to add.  Now seems like a good time to possibly redesign these lists (if needed).  The current lists are just checkbox sets on a layout, set to a field in the main Item table, using a value list generated from a Constants table field, which is just a return separated list of text.  So I was thinking of separating the checklists from the main Item table.


      I was thinking of possibly two new tables:  one for checklists, one for checklist line items (the values to display on each type of checklist).


      Checklists::
          pk_ListID
          fk_ItemID
          List_Type
          List_Values


      ChkLst_Values:
          pk_LineItemID
          List_Type
          Line_Text


      Relationships:
      Items::ItemID  = Checklists::fk_ItemID
      Checklists::List_Type = ChkLst_Values::List_Type


      (You could build the value list dynamically from ChkLst_Lines using the List_Type field.  Hmmm…maybe a third table for individual list line item responses, instead of a combination field "List_Values" in the "Checklists" table.)

      One problem I see with doing it this way is maintenance.  To create the supporting checklists a script would have to fire, either at the creation of a new Item record, or perhaps run it as a script trigger on "RecordLoad" when using the layout to view the checklist (checks if it exists, creates it if not).

      It hasn't been requested yet, but I can foresee that this is something that we might want to let certain end-users administer (the values on the checklist).  Putting them on a separate table would easily allow that...but just letting them edit a Value List would work, too.  One other consideration is that we may want to create a sum or calculation to display (on a different layout) how much of a checklist has been done.  Is that possible to do on a value list?  Or would the separate line item table be the best way to do that?

      Another feature that I would like to provide is a button to check all line items; experienced users may not sit there and tick them off one by one, but want to record them all at once.  How do you parse through a combined value list?  (If there was a third table of just responses iterating through those and setting the values with a script would be pretty straightforward.)


      What are the benefits of separating the checklists?  I know that it would slightly unclutter the main Item table (it already has 600+ fields), but is that really a problem?  The flipside is that it would require creating new tables and T.Os, cluttering the relationship graph.  :)

      Any discussion or suggestions about this would be appreciated.

      Thanks,
      J


        • 1. Re: Checklist architecture questions
          philmodjunk

          Will the same check list be used over and over again?

          If you want to set up reports that count how many times a specific item was selected, you really don't want a group of check box values for a single field. You can set up a related table where you display single value checkboxes in a portal in order to generate a table of check list responses where you can tabulate the results. The single value checkboxes might even be replaced by layout text set up with conditional formats and button setups that perform a script to select the value.

          • 2. Re: Checklist architecture questions

            Yes, I think...

            Each Item will have it's own checklists (4 or 5 of them, each different), but the checklists between ItemA and ItemB are the same.  (It is process control; make sure A, B, C are done before moving on.)

             

            I don't necessarily want to count how many times something is selected, I want to count how many entries in a given list are checked.  I don't really care about comparing ItemA and ItemB, I just want to know how many checkboxes in a single checklist have been marked.  Then that could be used as a progress gauge of sorts, to see how far along an Item might be at a given stage.

             

            Thanks,

            J

            • 3. Re: Checklist architecture questions

              I have been messing around with implementing some of this.  I was testing out how to display the various checklists.  I created a portal to show the records for Items, and put a number of buttons in each portal row, the idea being that each button would open a given checklist.  I was trying to open the checklists in a tabbed-portal, separate from the list-of-items portal.  It kind of works.

              The problem is that while my selector changes (each button sets a selector field), the field shown in the other portal doesn't change.  The particular tab being shown changed correctly, and I see the different lists, but no matter which record I click on the left the record being shown on the right doesn't change.  (Well, almost never.  Once in a long while the recordID in the right portal will update, but I haven't been able to figure out why yet.  It certainly isn't in response to my clicking the buttons on the left.  I have better luck if I click a checkbox in the right hand record.  But that still isn't 100%.)

              I configured the right hand portal to filter based on the Selector = ItemID.

               

              Thanks,

              J

              • 4. Re: Checklist architecture questions
                philmodjunk

                Here's what I mean by using the same check list more than once: Everytime a pilot gets ready to take off in their plane, they run the same check list of items. Thus, they have to record that the same items are ok over and over again.

                See if this set of relationships work:

                Checklist----<CheckListItems-----<CheckListResponses

                CheckList::CheckListID = CheckListItems::CheckListID

                CheckListItems::ItemID = CheckListResponses::ItemID

                On your CheckList layout, you can add a portal to CheckListItems with a button in the portal row to create or delete a record in CheckListResponses when it is clicked to record (or clear) the "checking off" of a specific item. You'd need to include at least one additional detail here to keep the responses for each time that the Check List is "run" separate from each other and that added detail depends on how you need the check list to work. This could be the date one which the checklist responses were recorded or a location or equipmentID, or ....

                That adds one more pair of match fields that can be included in the above relationships or a portal filter as well as used in the script that records and/or clears the response recorded for an item in your check list.

                • 5. Re: Checklist architecture questions

                  No, I don't think our checklists are used in quite that manner.  Each item will have 5 checklists, each being different.  A checklist is used only once for each item.  But each item has the same checklists that it needs to go through.  It is a quality check or preparedness process, asking questions about the item to make sure it is done and ready for the next stage.  E.g if it were a die, perhaps the lists would be: Is it square?  Is it the right size?  Is it blue? Does it have 6 sides?  Are the spots correct?  Each die would be inspected and checked against the list, with responses recorded.  But once it was done it wouldn't ever come back to that point and get rechecked.  To stick w/ the airplane analogy, it is more the manufacturing of the plane that is being checked, than the operation of the plane on a daily basis.

                  As you were suggesting, I wondered if a third table might be needed in this scenario, the one for holding the responses.  But then wouldn't I want a script to run just once upon creation of the checklist, and populate the Responses table with all of the text labels for that checklist?  I mention this in reference to your mention of running a script for deleting/adding a record in the responses table.  Perhaps it would look like this:

                  Items::_ID  --->  Checklist_Responses::Item_ID & ::Type
                  Checklist_Responses::Item_ID & ::Type   <------  Checklist_Line_Items

                  In short, the Checklist_Line_Items table is only used to populate a series of records in the Responses table when a checklist is needed/created.  The TYPE would be set based on the button or layout used to access the list. 

                  Should the table for list items (the display text, or question) be one record for each checklist, where the individual values would be return-separated lines in one field?  Or would it be better to have each line item in it's own record?  For the Responses, the return separated lines would have to be parsed out into individual records to track the response.  Am I correct in thinking that it is difficult (impossible?) to 'count' checked-off responses if the checklist line items are in a single field?

                  Thanks,
                  J

                  • 6. Re: Checklist architecture questions
                    philmodjunk

                    But each item has the same checklists that it needs to go through.

                    That would appear to indicate that you are using the same check list over and over again, but each time for a different "item" much like my imaginary pilot has to do it each time he flies....

                    Short check lists (half dozen or so items) might be implented with check box groups, but much depends on whether you need to analyze/tabulate individual selections on the check list or just need to see that the entire check list was correctly checked off each time.

                    What you describe would work provided you populate the responses table with a script each time you need to run the check list. What I'm suggesting uses a button in the portal to log each time an item is checked off by creating a record in the related table. If the user clicks it a second time, the script finds and deletes that record to 'clear' the selection. (Say they clicked the button by mistake...)

                    Given the additional details from your last post, I'd set it up like this:

                    CheckListItemsByType>-----Items----<CheckListResponses>----CheckListItemsByCheckListID

                    Items::Type = CheckListItemsByType::Type

                    Items::__ID = CheckListRepsonses::Item_ID

                    CheckListItemsByCheckListID::CheckListID = CheckListReponses::CheckListID

                    CheckListItemsByType and CheckListItemsByCheckListID are occurrences of the same table.

                    You can place a portal to CheckListItemsByType to list the items that make up the check list. But you can use Layout Text formatted with conditional formatting with this expression to show which items have been checked off:

                    IsEmpty ( FilterValues ( List ( CheckListItemsByCheckListID::CheckListID) ; CheckListItemsByType::CheckListID) )

                    And specify that the text size be changed to 500 pts so that if this expression is true, the text is not visible.

                    You then set up the layout text and fields in the portal row as a single button that performs a script that checks to see if there's a related record in the responses table. If there is, it finds and deletes it. If there is not, it creates one.

                     

                    • 7. Re: Checklist architecture questions

                      Working on implementing a multi-table approach...might be af few days before I get it finished.  Will write more later.

                      • 8. Re: Checklist architecture questions

                        Wow, what a prophetic statement:  "might be a few days..." 

                        I am finally getting back to this.  I have some tables set up much as was described above:  two 'template' tables for checklists and line items, and then one central 'Responses' table where all the responses to the checklists are kept.  (The records in Responses are created by scripts, copying information from the two template tables.)

                        I am now trying to figure out how to get the right summarization numbers for a given checklist.  I want to provide the end user with a percentage done number, for each checklist.  I am recording the total number of line items in the Responses table when creating the records.  I was then trying to figure out how to get a calculation field to get populated with the number of items marked as 'done' (i.e. checked off the checklist).  I can get it to work in the Data Viewer, but when I put that into the field definition calc window, I only get a question mark on my layout. 

                        Here's the code that works in Data Viewer:

                        Let ( [
                           _CrsFld = GFN(Checklist_Responses::aParentID_fk);
                           _ListFld = GFN (Checklist_Responses::aListID_fk);
                           _FromTable = GTN (Checklist_Responses::aaResponseID_pk);
                           _RespFld = GFN (Checklist_Responses::LineItem_Value)
                        ];

                        ExecuteSQL(
                            "Select count(*) FROM " & _FromTable &
                            " WHERE " & _CrsFld & " = " & _CrsFld & " AND " & _ListFld & " = ? " & "AND " & _RespFld & " = ?"
                        ; "" ; "" ; Checklist_Responses::aListID_fk;  "1" )    //END SQL

                        )   //END Let

                        And here's the code that doesn't seem to work in the field definition calc:

                        Let ( [
                           _ParentFld = GFN(Checklist_Responses::aParentID_fk);
                           _ListFld = GFN (Checklist_Responses::aListID_fk);
                           _FromTable = GTN (Checklist_Responses::aaResponseID_pk);
                           _RespFld = GFN (Checklist_Responses::LineItem_Value)
                        ];

                        ExecuteSQL(
                            "Select count(*) FROM " & _FromTable &
                            " WHERE " & _ParentFld & " = " & _ParentFld &
                            " AND " & _ListFld & " = ? " &
                            " AND " & _RespFld & " = ?"
                        ; "" ; "" ; aListID_fk ;  "1" )    //END SQL

                        )   //END Let

                         

                        I have also attached an image that shows a few screen captures of the table schema (field definitions), the calc field window, and the dataviewer window with result.  (End result for this particular list should be 4 checked, out of 49 total.)

                        A description of what I was thinking of doing in the SQL for the calc field:

                           Essentially each record would end up with calculated value for the percentage done in it.  Not the greatest way to do it, but my Responses are all in one monolithic table; I thought about creating a CheckList_Summary kind of table as an intermediary. 

                           The SQL is intended to count the records in the Responses table that match the current record's ParentID, the current record's ListID, and has a Response Value = 1.  So, from the context of the record, it should only see responses that are from that same list, and thus be able to narrow it down to the marked off ones.

                        I hope I provided enough detail about the structure for someone else to follow along.  It is all pretty clear to me.  :)

                        Thanks,

                        J

                         

                         

                         

                         

                        • 9. Re: Checklist architecture questions
                          philmodjunk

                          The passage of time has dimmed my recollection of the details. Frown

                          I don't see the need for Execute SQL here as you can use summary fields with count or sum to add up our count records. The aggregate functions Sum and count can also be used.

                          Your execute SQL expression uses functions not part of the standard function set for FileMaker. Are GTN and GFN custom functions? What do they do?

                          assuming that this is your relationship structure:

                          CheckListItemsByType>-----Items----<CheckListResponses>----CheckListItemsByCheckListID

                          Then if marking an item Done in checkListRepsonses enters a 1 in a field and 0 or empty marks an item not completed (if you used other values, calculation fields can be used to return 1 or 0 based on the values enered), then you can define a fraction of total summary field in CheckListRepsonses, then you can put this field on an Items layout and you'll see the percent complete for that item.

                          • 10. Re: Checklist architecture questions

                            Ah, the custom functions:  those are just there to provide robustness to the code.  They are:  Get Table Name (GTN) and Get Field Name (GFN).  They just return, surprise, the Table Name or Field Name.

                            ExecuteSQL was something of a an exercise of a new feature.  Also, it keeps the relationship graph simpler.  :)  So, I don't have any connections except to enable some portals.  Responses table is isolated.  ExecuteSQL allows me to (try at least) look back at itself, and even compare the record to itself.  At least in some cases, apparently; my current problem is that this doesn't quite seem to work in the field definition calc box.

                            Yes, marking an item as Done sets Responses::List_Value = 1.  It becomes blank when cleared.

                            Oh, in my screen captures the field definition calc box uses a slightly different reference to the ListID_fk field:  it is just the field name.  I tried it that way and as fully qualified, i.e. Responses::aListID_fk, and got the same results.  It is fully qualified in the Data Viewer box.

                            I attached a shot showing some sample data from Responses.  Yeah, repeating some data.  That's why I was partly thinking of creating a ResponsesSummary table where it would contain these calc fields, but also List level info (ID, # of items, etc).

                            • 11. Re: Checklist architecture questions
                              philmodjunk

                              How do GFN and GTN work? The fields used as parameters look like they simply pass a number to the custom function. I don't see how a custom function can use a value like 123 and return the name of a table (table occurrence actually) or the name of a field.

                              I get the idea behind using Execute SQL to avoid defining relationships in the relationships graph, but don't think that it will ultimately be a practical approach for a FileMaker Solution in current versions of this application. The results of such a function are just too limited and thus you are likely to find that you need to set up those relationships anyway as the alternatives are much more complex to set up and manipulate.

                              The basic relationships needed to produce the counts that you want with convential uses of the summary field or an aggregate function are not all that complex.

                              FWIW, having worked with other systems besides FileMaker, I've come to appreciate the discipline imposed in FileMaker that requires me to set up relationships in the graph. I've spent meny hours tracing relationships in layered SQL queries where not one relationship in the entire database existed anywhere but in the SQL join expressions. That was a real nightmare and had the original designer documented at least the basic ER 'backbone' it would have taken me far less time just to figure out the basic structure well enough to start modifying it.