AnsweredAssumed Answered

Checklist architecture questions

Question asked by disabled_JustinClose on May 11, 2012
Latest reply on Aug 3, 2012 by philmodjunk

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


Outcomes