Assign case to diffreddnt user and track (1 Viewer)

Sweetu · Apr 4, 2018

Dears need a bit hint or assistance ,

What would be the structure for assigning case to another user & revert back to same and so on while keeping the track ,
such as jeorge created a case which saved in case table & case table have one FK with case to identify the user assigned,
but how we will arrange the form to keep track the current case with whom & the case with whom?

Mark_ · Apr 4, 2018

Use a linking table to link Users to Cases. For each instance, you create a record with BOTH User ID and Case ID. I'd also put in a date field to track when it was assigned. This way you look for the latest entry to show who currently is assigned to a case.

Sweetu · Apr 4, 2018

Mark_ said:
Use a linking table to link Users to Cases. For each instance, you create a record with BOTH User ID and Case ID. I'd also put in a date field to track when it was assigned. This way you look for the latest entry to show who currently is assigned to a case.

in a separate table ?

Mark_ · Apr 4, 2018

TblCase would hold Case information
TblUser would hold User information
TblCaseUser would link the two. You are looking at a many to many relationship.

One user can be assigned to many cases over time.
One case can be assigned to many users over time.

How you wish to display this to end users and how you want end users to create and update these links depends on how your program needs to work though.

Pat Hartman · Apr 5, 2018

Technically, this would be a history table. You would store the AssignedTo and StartDate in the main table. When either of those changed, you would add a row to the history table:
HistoryID
CaseID
AssignedTo
StartDate
EndDate

To handle this process, I would store the two variables when one of them is changed. Then in the AfterUpdate event of the form, check the variables. If they have a value indicating that the case was reassigned, I would use DAO and the .AddNew method to add a row to the history table.

You would only use the history table if you wanted to see who was handling a case during a certain time period. It wouldn't be part of the day to day activities.

Mark_ · Apr 5, 2018

@Pat,

Any real advantage to keeping the assigned to and start date in both the case file AND the history table? For myself, it seems as though it would be easier to simply drive it off of one table without having to move data from one table to the next. Avoids the whole "Forgot to add a history record on change" issue if someone else works on the database.

The_Doc_Man · Apr 5, 2018

Avoids the whole "Forgot to add a history record on change" issue if someone else works on the database.

If this can happen, you have either a design flaw or an implementation flaw. Your users should always go through forms. If that is set up correctly, then when you perform a case reassignment, the FORM would ALWAYS remember to make the history entry transparently to alleviate the user from having to remember to do anything.

As to keeping the assign-to and assign-date (or start-date) in two places, that is at least a case of double-entry bookkeeping, which makes extra work, and is also very likely a case of improper normalization. Normalized tables are designed to prevent either of these things from happening.

Mark_ · Apr 6, 2018

@ Doc,

Wasn't concerned with end users missing it, more if someone else works on the database.

The_Doc_Man · Apr 6, 2018

@Mark_,

Wasn't concerned with end users either. More concerned about a design that left something to chance for a new maintainer to not realize that reassignment needed work.

Pat Hartman · Apr 6, 2018

Any real advantage to keeping the assigned to and start date in both the case file AND the history table

The CURRENT value is in the active record. The OLD values are in the history table. This avoids having to include the history table in every query that needs the assigned to and start date plus joining to the history table will always require a sub query because you need to use the most recent record from the history table.

Technically, this isn't a violation. The rows of the history table reflect data at a point in the past. The history table is never updated. You simply add a record to it when the current record changes. Think of this as a change log. You wouldn't include the change log in your day to day process. The change log is history and is used only if you need to find out something that happened in the past.

This is something that might be done with a data macro (ACE) or trigger (SQL Server) if you want to create one. That would ensure that the history record always gets created when it should.

Assign case to diffreddnt user and track (1 Viewer)

Sweetu

Registered User.

Mark_

Longboard on the internet

Sweetu

Registered User.

Mark_

Longboard on the internet

Pat Hartman

Super Moderator

Mark_

Longboard on the internet

The_Doc_Man

Immoderate Moderator

Mark_

Longboard on the internet

The_Doc_Man

Immoderate Moderator

Pat Hartman

Super Moderator

Similar threads

Users who are viewing this thread