Help?? I am creating multiple recurring chec...

[Dana_Casey]

Help??

I am creating multiple recurring checklists - with multiple Checklist Items AND multiple (record specific) fields associated with each Checklist Item.

I have four related tables for this:

Contacts - Contains ID (and misc contact specific info)

Checklist Name - Contains ID and Checklist Name

Checklist Items - Contains ID, Checklist Name Ref, Checklist Item Order and Checklist Items

Checklist Scheduled - Contains ID, Contact Ref, Checklist Name Ref, Checklist Item Ref, Date Due, Date Completed

My initial goal is to create reusable checklists of recurring steps within business processes to be attached to “Contacts” by selecting a “Checklist Name” from a list of checklists contained within the “Checklist Name” table. Then populate the “Checklist Scheduled” table with “Checklist Items” associated with the selected “Checklist Name”, then store the “Date Due” and “Date Completed” data for each “Item” as “Checklist Scheduled” records within the “Checklist Scheduled” table.

I am struggling to tie this all together.

[pravse]

Hi @Dana_Casey, how many items are in each checklist (just approximate) and for the entire lifetime of a Contact, how many different things get checked? Is it about 10 or 100 or 1000? If it is a smallish number, one simple solution is to have columns for each checklist item and just show/hide them depending on the stage.

[Bellave_Jayaram]

I have an app where there are variable size checklists (upto 24 items) and it works. It sure would be nice if there was a better way to build this.

[Dana_Casey]

Hello Praveen,

It is a larger number and the simple solution is not going to work - I have looked at that, wish it could be that simple, but there really is no way to associate the date values with columns that are created for use as simple toggle without doing something more complex, like I am working at in the above example - or is there?

Initially, there will be multiple checklists (with additional checklists added in the future for various business processes).

The initial number of checklists will be 3-5 with many more - potentially as many as 30+ additional checklists (that will likely be accessed through a cascading combo box style interface - as business processes grow and evolve).

Each checklist will have between 10 and 100 items.

Rather than checking off items, I will be using a completion date and conditional formatting to identify when the checklist items have been completed.

Many items will be auto-scheduled as well, by a combination of creating due dates and adding X

(+/’-) # of days to the scheduled date, or generating a due date by adding X (+/’-) # of days to a completion date of a previously completed item.

It will be necessary to view previously completed items, so in addition to the conditional color format based on completion status, there will be another condition to identify various days until due, as well as a sort on due date of items with a null completion date value, showing incomplete items first.

With the exception of the (+/’-) X # of days scheduling automation piece, I believe I have the basic data structure correct, but am more familiar with MS-Access building and will need guidance to get the functionality that I am needing out of these tables.

I would hope to select a “Checklist Name” and auto-populate the “Checklist Scheduled” table with the corresponding list of “items” tied to the “Checklist Name” from the “Checklist Items” table, (then work on the (+/’-) days piece to auto-schedule dates) to be completed on behalf of, and accessible through the record of a Contact.