Checklist with a twist

[AdamFeerst]

I have a set of checklists using the standard vertical format: tblList, tblData, tblMaster (see thread http://www.access-programmers.co.uk/forums/showthread.php?t=283525)

The twist is that not all of the tasks are the same type of field (in this case, I'm using a date field; click and it enters today's date, indicating that it has been done). Some fields will be a choice, either a check box for a Y-N field, or a toggle button for a number field. The user's selection will determine which of the next few tasks they have to do. A few tasks will be fill in, short text.

What changes do I need to make to the tables?

How do I design the forms so that a task shows up as either a text box for dates, text, or a check box as needed?

 

[plog]

Browsed the other thread, not convinced you have the proper table structure. That's the first step, not designing forms. Next step is making sure you can get the data out like you want via Reports/Queries. Last step is building forms.

Can you post a screen shot of your relationship view?

 

[AdamFeerst]

See attached

 

[plog]

Without knowing more about what each field holds, I only see 1 error. You shouldn't have multiple paths between tables. I see 2 ways to get from tblChecklistData to tblChecklistType. They shouldn't be connect by both tblCheckListMaster and tblChecklistTasks. One or the other, perhaps neither.

The key to fixing that is identifying where the field ListTypeID should be. It shouldn't be in both tblCheckListMaster and tblCheckListTask. Explain what ChecklistType is.

It might also help to explain the briefly what each of your tables holds.

 

[AdamFeerst]

Without revealing too much, we manage remote payments for different companies. There are a number of different products. The payments can come via different sources, with each source being a different type of product. Different industries can have different products. Within each product, there are different processes; e.g., adding a new client, adding a new product to an existing client, changing the banking info for a client, deleting one product for a biller. Each combination of product and process requires a unique set of tasks, checklists leading to a live implementation.

What I have, essentially, is several distinct sets of single checklists, that I want to be able to manage in one place.

How to do that was the gist of the other thread. I'm pretty comfortable with how I have it set up. Briefly, ChecklistType (ListTypeID) identifies which distinct checklist is being used for a project (ChecklistID). When starting a new project, the ChecklistType is selected, in addition adding the customer info, proj dates, etc. Then, only the tasks with the selected ListTypeID from tblChecklistTasks are added to tblChecklistData.

I'm pretty comfortable with this set up. It's working well, so far, in testing. While I'm open to suggestions, that's not what I'm asking here.

Regardless of the structure, how do I handle different types of responses (date, Y-N, text) on a checklist, and when the response at one step dictates the following steps?

 

[AdamFeerst]

P.S. I don't normally pre-define table relationships. I let that happen through the form/subform and query design.

 

[plog]

Short answer: I don't know.

Rambling stab in the dark: You might have to make a subform or input for each possibility. You would hide all but the first step and then unhide the correct subform/input once step 1 has been filled in.

Suppose Step 1 is a drop down if they select 1 then Step 2 is a Date, if 2 Step 2 is a text input, if 3 then Step 2 is a Yes/No. For Step 2 you would build all 3 inputs and set them to Visible=False. Then when Step 1 is chosen, you make the correct input Visible. Once that is filled in you proceed to Step 3, etc, etc.

Now here's the rub, this is why doing it via this method and not trying to solve this with a better design is why I am skeptical. User comes in and selects 3 for Step 1, Step 2 unhides the Yes/No checkbox, but the user screwed up and really meant to select 1. How do you allow them to fix that? Worse than that, suppose they work through Step 5 and then a week later it turns out they answered Step 2 incorrectly?

If you had perfect users and data never changed, this is going to take a lot of coding. Once you factor in imperfect users and changing data, this thing is going to take a massive amount of coding.

 

[AdamFeerst]

I'm open to better design ideas. Surprised this hasn't come up before. Please just focus on this aspect of it and ignore the multiple checklist part.

For the different input types, my idea is to add a field to tblChecklistTasks, let's call it InputType, long integer; e.g., 1=date, 2=Y-N, 3=text). On tblChecklistData, change DateDone to Done, short text. On the subform, I might be able to get away with a single textbox for Done, but change the behavior (on click) depending on the InputType: 1-enters today's date as text; 2-toggles between Yes and No; 3-allows text entry.

As far as next steps, rather than hiding/revealing tasks, one option might to enable/gray out tasks. That might require adding two additional fields to tblChecklistTasks, next steps if option 1 or Y chosen, next steps if option 2 or N chosen. This only happens on a few of the checklists, and never >2 options.

Another idea is wait to add those conditional next step tasks to tblChecklistData until the choice is made. You'll notice I have a SortOrder field in tblChecklistTasks, so they will go in the right place. If the imperfect user later changes the choice, I would delete one set of tasks and add in the other. As I'm writing, this option seems like it might be the better way to go. The code to add and remove tasks is straightforward, which would include first checking to see if they've already been added.