Task List

Phase 1: Design and Implement an Information System to Support the new IBGP.

1. Sheetal, in consultation with Sandy, will implement the database in Access including all the tables and their relationships. (Done, 8/14)

2. Sandy will test the tables and their relationships. (Done 8/16)

3. Sheetal will create a prototype Switchboard and input forms in Access. (Done, 8/17)

4. Sandy and Sheetal will discuss the prototype with Allan and Christine. (Done, 8/18)

5. Make modifications and additions based on this meeting. (Done, 8/22)

6. Sandy will test the input forms. (Done, 8/22)

7. Make modifications and additions based on this testing. (Done, 8/23)

8. Make modifications based on newly discovered problems. (Done, 8/24)

9. Mary Zando, a CIS graduate student and Access expert, will test the forms, including all input functions. (Done, 8/25)

10. Make modifications based on this testing. (Done, 8/28)

11. Upload the data exported from the Groupwise application into the faculty subsystem. Delete records for which Organization /= IBGP. (Done, 8/29)

12. Install the database on Christine's computer and she will begin to input data. (Scheduled for Wednesday, 8/30, 3p)

Phase 2: Consider the Redesign of the IGBP IS to Accommodate Training Grant Tables and Upload Existing Data.

1. Sandy and Sheetal met with Allan, Christine and Angie on Tuesday, 9/27. Christine had a list of requests for modifications to the database. Angie had a training grant application. Allan's new goal for the IS is to be able to generate from the database all the tables in the grant. We will need a copy of the grant to determine database and upload issues to accomplish this.

2. Make the modifications as per Christine's request. (Done, 9/30)

3. Redesign the database to accommodate the grant tables. (Preliminary analysis done, 10/3)

4. Create queries/reports that will generate the tables from the database. (Delayed until after new COMPH db is done.)

5. On October 28 Allan authorized the work required to transform the IGBP IS into the COMPH IS.

Phase 3: Transform the IGBP IS to the COMPH IS

1. Sheetal will reconsider the analysis of the training grant tables; Sandy and Sheetal will meet to discuss the COMPH IS design; then Sheetal and Sandy will meet with Allan, and others he deems appropriate, to resolve issues relating to unknown or ambiguous data. (Done.)

2. Sandy will document the COMPH db design. Kabardhi will implement the COMPH database in Access. (Done.)

3. Kabardhi will implement the new Switchboard (input forms) for the COMPH database. (Done, 12/12/2000)

4. Sandy tested the Switchboard. (Done 12/12/2000.)

5. Kabardhi made all the modifications. (Done 12/13/2000)

6. Sandy tested again. (Done 12/13/2000)

7. Kabardhi made modifications. (Done 12/14/2000)

8. Sandy tested again: Student->Alum-PostDoc: PostDocID still is a writable field; it should not be; the Queries, Reports and Exit buttons should behave as follows--depressing Queries or Reports has no affect; depressing Exit should either exit access or the button name should be changed to Close; I think we want the exit function. (Done 12/14/2000)

9. Kabardhi made modifications. (Done 12/15/2000)

10. Sukhdeep tested. (Done 12/19/2000)

11. Kabardhi made modifications.

12. Kabardhi will upload the Word training grant data into the new COMPH db, in so far as this process can be automated. Further upload may have to be done manually by staff Allan will hire. In January, 2001 Allan had asked us to wait to do the upload because the data were being updated to be more complete and correct. Kabardhi did however, devise an automated scheme for doing the upload (Done, March, 2001) so that we will be able to do the upload quickly when the data are ready.

13. Kabardhi and Sukhdeep are writing the queries that generate the training-grant tables (reports). We decided to do this in two steps. First we generated all the queries that would be used in the set of reports. Kabardhi and Sukhdeep completed this task April 24, 2001. Then, using these queries in Excel, we are proceeding to generate the reports, sometimes a multi-step process.

14. We will have several of the reports complete by the end of this week and we invited Allan and all other interested parties to come over for a demo so that we could get their input before we complete the report generation phase. They plan to visit us during the week of April 30 to conduct this review.

15. The software architecture for automatic table generation is as shown below:

    

16. In the meantime, Kabardhi has updated the Switchboard so that the input forms contain all the new data fields that have been added. Sandy tested the new Switchboard on April 24 and Kabardhi made all the corrections on April 25, 2001, so that it is now complete.

17. On May 4, Allan, Christine, Darlene Johns, Angi Gilliland and Kristen Urmson reviewed the NIH table generation architecture and online/printed copies of several of the tables. They approved the generation process and were pleased with the formatted results.

18. A warning message for possible duplicate records, based only on first name, last name was requested. (Done 7/6/2001--we provide a drop down list of all students in the IS, with both first name and last name so that addition of a duplicate can be seen immediately)

19. For tracking student rotations: add to the LaboratoryRotations table a CourseNumber field with values 793 and 999 and a Grade field with values S and U. Then, prepare a query to be run at the end of each quarter that sends email to each faculty member who supervised a lab rotation that quarter asking for the grade. A second email may be sent when the course grade is entered into the db notifying the faculty they will 'receive credit' for the rotation (Mamrak suggests that we already have fields for these data in the LaboratoryRotations table in the student subsystem and that faculty can check it's values when they review their faculty reports so that all this email may be unnecessary and overly complicated. Allan agreed to this solution on 7/13/2001.)

20. We have uploaded the data that is available in Word documents as per list of Tables above. (Done 7/6/2001).

21. We can now generate, with signifcant amounts of data, the tables listed below from the 'Medical Scientist Training Grant'.
    • Table 2 -- Membership of Participating Departments . . .
    • Table 8 -- Participating Faculty Members
    • Table 9 -- Grants and Contract Support . . .
    • Table 11 -- Faculty Research Interests . . .
    • Table 18 -- Residency Match Results . . .

22. Delivery of COMPH IS to Allan (Done 7/6/2001).

23. A query for each faculty and student in the program, listing all their relevant data for their review and update. We reviewed a draft faculty report with Allan on 7/13. He liked all the data already in the report and asked that we add info about past student advisees. We will do that and work on the student report as well. (Done 7/31/2001)

24. Some data elements to help follow the students timetable in the program: Allan explained the requirements for this new IS subsystem to Sandy on 7/13. Sandy designed and implemented the database components (Done: 7/13/2001) and Kabardhi will implement the input (Switchboard addition done 7/31/2001). The report component is not yet done.

25. In a phone conversation on 7/9/2001 Allan asked that we meet with his input personnel and devise a plan for the data input needed to generate all the NIH training-grant tables (Sandy has generated a set of instructions for this input and will meet with input personnel in a meeting to be arranged by Chris, likely on 7/18/2001. The meeting was postponed until August 1.

26. We have completed the automatic report-generation process (Done 7/6/2001). We have the code in place to generate all the other tables than those listed above, but have not been able to thoroughly test it due to a lack of data values in the IS. When these data are entered we will work with the input personnel to ensure that all tables are generated correctly and to their satisfaction.

27. On August 1 we met with Chris, Cindy Fox and others for a training session on data input for the COMPH IS.
    • We asked the data input personnel to begin the data input process and to keep a list of problems, issues and so on. We will meet with them likely next week (week of August 6) to discuss these issues.
    • We asked Chris to give us values for several lookup tables and she said she would email these to us. She will also generate for us a list of international students that are to be deleted from the IS.
    • We got the lookup list for core courses. Kabardhi added these as well as made sure that the current TG title appears in drop down lists for StudentTG, FacultyTG, DirectorTG and PostDocTG tables. Other changes that Kabardhi made include omitting the duplicate B.S. entry in the degrees lookup table, adding the values MD and MD/PhD to the AdmissionsType lookup table, changing the academic advisor relationship from one-to-one to one-to-many (to allow keeping records for past advisors), removing the Masters subsystem from the Student input form, and removing the entry 2000-2001 from the AcademicYears lookup table
    • Darlene Johns showed Sandy a db in which she has grant info (TG and other) for all or a good deal of faculty members in the department. We will investigate the feasibility of uploading this data into the COMPH IS. After initial upload dual input into her system and the COMPH will still be required. The upload appears to be fairly straight-forward if we drop the lookup-table restriction for funding agency (which seems reasonable because we don't do any searched based on that).

28. Sandy met with Cindy and Allan to help Cindy get started with COMPH input for faculty. During the course of the meeting we discovered several more changes/additions that will be required. The first change is required because when the TG tables are generated there is a need to distinguish among different kinds of students in the medical school. The second change is required to prevent duplicate input of publication data when multiple authors are involved.

    The work on the publications subsystem is a top priority and should be completed before Kabardhi leaves for India so that Cindy can do the input during his absence. The other work can be done during Autumn Quarter.

    • For the lookup table for Status, we did add MD and PhD. We need to add 'IBGP' and 'MD/PhD and IBGP'.
    • For all TG tables (13, 15, 19, 20 and 22, but not 10) that gather data about students, we need a GUI interface to the query that asks the user whether students should have status MD/PhD, IBGP, and/or MD/PhD and IBGP. We have to parameterize the queries to accept this input and rewrite the query to reflect it.
    • Pull the publications subsystems from the faculty and student subsystems and create a new publications button on the switchboard. The publications system should be changed to accommodate multiple authors where the author can be either chosen from combined faculty/student table values or an 'Other' free-format input field. We will assume that the order of the creation of the author records indicates the order of authors as listed in the publication.
    • Change all queries that select publications to accommodate this new format. These are queries for Tables ...
    • In Table 10, the field 'Training Period' should be interpreted to mean 'Years in Program.' Change the query to reflect this.

29. Kabardhi visited the COMPH program on Friday, 8/10, to install the latest changes to the software and help Cindy with some problems she is having. Two requests were made: 1) allow for multiple input on the Research Keywords field and 2) for funding agencies, continue to provide a lookup table, but also add a field for text input for agencies not in the lookup list. We will support both requests.

    Cindy made a request that we document the semantics of every field in the database. We believe that 95% of the fields are self-explanatory and asked that, for now, they keep a list of fields they don't understand and we will document only those.

30. During the break between summer and autumn quarters Cindy input a lot of data. Two requests she made are 1) make the research keywords fields plain text (we'll do this and also change the format of the current data in the db) and 2) make the title field in the publications table longer. She also is keeping a list of issues/problems that she encounters and Kabardhi will discuss these with her after the autumn quarter starts.

31. On September 23 Allan asked Sandy to further extend the information system to accommodate tracking of MSP (?) students. This would require the addition of several tables, changes to the switchboard for input and the addition of one or more queries. When all the other work to which we are committed is complete Sandy will talk to Allan in detail about these new requirements.

32. On September 28 Kabardhi met with Cindy to discuss changes she wanted as a result of her input experience. Karbardhi lengthened the title field in the journals table and cleaned up some faculty records (deleting erroneous and duplicate records). Cindy also asked to alphabetize the lookup values for Degrees and Degree Granting Units. Kabardhi will do this as well as address the research keywords problem and then meet with Cindy to install the fixes and to start generating the various training grant tables.

33. Sandy and Kabardhi met with Cindy on 10/10/2001 to go through the processes required to run the tables in a training grant. The following tasks were identified for Kabardhi and all have been completed as of 11/1/2001.
    • Alphabetize the look-up values in all the drop-down list boxes (e.g., TrainingGrants -> Faculty Names, Director TG ->Role).
    • Display both the first and last names of faculty in the drop-down list box for Students->LabRotations form.
    • In the TrainingGrants form, change the end date to 4 digits for the year and change the funding agency field to free text box.
    • Remove the AmountDirect, AmountIndirect from the DirectorTG, FacultyTG sub-forms.
    • Remove the AmountDirect, AmountIndirect, StartDate, EndDate from the StudentTG sub-form.
    • CurrentTGParameters -> Change the TGId list box to indicate the Training Grant titles instead of grant IDs; create Drop-down list box for DGUnitName; add the student category (IBGP, COMPH, Both) list box.
    • Update the Faculty Recruits form to allow data entry based on the Faculty Rank.
    • Check the inconsistent interactive behavior of the Student Button on the main switchboard.
    • Debug the Tables 2, 8, 10, 11, 15.

34. Sandy and Kabardhi have been meeting with Cindy every Wednesday since October 10 to continue to test and debug all the training grant tables. As of November 1, she has tested 5 of the fifteen tables with data that she input. All the other tables are now debugged except for small problems remaining in tables 15 and 21. We will continue to meet with Cindy through November and early December to complete this process.

35. By next Wednesday, 11/8, we plan to have these tables fully debugged and also the grant data uploaded so that we can finalize the testing on tables 3 and 9.

36. We continue to find ambiguities in the interpretation of the data in the tables and have made changes to accommodate these as appropriate. However, because we are on a deadline to complete the project by the end of Autumn quarter, we will not be able to handle every possible problem. In particular, we currently don't provide the ability to enter a speaker in the MD/PhD seminar series who is from outside OSU.

37. In a meeting with Allan and others on 1/22/2002, we reviewed reports that are needed for tracking IBGP and MD/PhD students and discovered that several new fields are required in the database, in addition to generating a new report. Sandy generated a task list. She will meet with Kabardhi on 1/24 to discuss its implmentation.