"Forwarding" a record(s) to another user (1 Viewer)

[CuriousGeo]

How would you design a database that tracks "issues" that can be forwarded to another user in the database. Users are in a table. A user enters an issue (date, issue type, issue summary, etc. in another table) How could the original person forward that issue to another user in the user table (for example a supervisor). Then, say that supervisor decides they should forward that case to someone else. How would I be able to capture each user that has touched that record. Until the issue has been marked as complete/resolved. Thank you for any ideas.

 

[theDBguy]

Sounds a normal one-to-many or parent/child relationship.

 

[CuriousGeo]

So, make the original person/issue a parent table/record, then have a child table that captures other people that the record gets "sent to"?

 

[CuriousGeo]

I found a related post where a replier Doc Man explains it perfectly:

Sending records from one user to another user

Hello experts, I have some questions if something is possible. It is possible to make in MS Access a form ( formX) that will be used for sending records from one user to another user? User1 make some new records and user2 must sign that records. I have 4 forms and user1 can produce records...

www.access-programmers.co.uk

You are essentially talking about approval sequencing, or what the U.S.Navy called a "chop chain" - where everyone takes a chop at something in a given order until everyone has had a turn.

You do this by putting a field in the record that says "who gets this next" - which implies a couple of possible solutions immediately, like a pre-stored list of people with a numeric key in the order that the corresponding person would see it. OR there are alternatives for things that have more than one possible route, such as pick-lists (via combo box) or a complex list of step number and department number.

In EITHER case, this extra field would be the basis of a QUERY and your form would then use the query as its recordsource rather than using a table directly. The query can have every field that the table would have, but would include a WHERE clause to select only those records with the specified "next person in order" so that person would see those records and nothing else. If the department is significant, the WHERE clause could include that option.

This also implies that you have users with separate usernames or IDs or something so that you can drive the process by knowing who your user is and thus implement filtration at all. This sort of HAS to be there since otherwise anyone could sign anything in any order (if no one has IDs)."

 

[theDBguy]

I found a related post where a replier Doc Man explains it perfectly:

Sending records from one user to another user

Hello experts, I have some questions if something is possible. It is possible to make in MS Access a form ( formX) that will be used for sending records from one user to another user? User1 make some new records and user2 must sign that records. I have 4 forms and user1 can produce records...

www.access-programmers.co.uk

You are essentially talking about approval sequencing, or what the U.S.Navy called a "chop chain" - where everyone takes a chop at something in a given order until everyone has had a turn.

You do this by putting a field in the record that says "who gets this next" - which implies a couple of possible solutions immediately, like a pre-stored list of people with a numeric key in the order that the corresponding person would see it. OR there are alternatives for things that have more than one possible route, such as pick-lists (via combo box) or a complex list of step number and department number.

In EITHER case, this extra field would be the basis of a QUERY and your form would then use the query as its recordsource rather than using a table directly. The query can have every field that the table would have, but would include a WHERE clause to select only those records with the specified "next person in order" so that person would see those records and nothing else. If the department is significant, the WHERE clause could include that option.

This also implies that you have users with separate usernames or IDs or something so that you can drive the process by knowing who your user is and thus implement filtration at all. This sort of HAS to be there since otherwise anyone could sign anything in any order (if no one has IDs)."

Glad to hear you found a solution. Good luck with your project.

 

[The_Doc_Man]

A wrinkle is introduced by this question:

How would I be able to capture each user that has touched that record. Until the issue has been marked as complete/resolved.

This SOUNDS like you want to be able to track the item from beginning to end through each resolver that touched it. You can have a history by making what is called a "JUNCTION TABLE." In essence, this table connects the list of issues with the list of resolvers. The junction table might be:

IssueHistory: IssueID (LONG, foreign key to Issue table), ResolverID (LONG, foreign key to Resolver table), DateTransferred (DATE, shows when THIS Resolver got THIS Issue), IssueStatus (Code of the issue at the time of transfer OR of resolution), IssueCurrent (YES/NO, TRUE if this entry describes the current state of the issue.) NOTE: IssueCurrent is a SLIGHT denormalization to make it easier to find who has the issue quickly. You set it TRUE to pass it to someone for action and set it FALSE if you are passing your own entry to someone else. (But you set their entry with TRUE.)

You then have a table of resolvers - people who take issues and do something constructive with them. You might have a sequencing list that shows the order in which a given issue will be passed along. At this point, your "distribution" method becomes very much like a HELP DESK scenario. You COULD (as a thought) have a code in your list that says "Next resolver is any help-desk tier II responder." Or something like that. This is the tricky part. The way OUR system worked was not only did we know who was Tier I, Tier II, or Tier III, but we knew who had issues in their queue and could pick the person with the lowest count of active issues. (P.S. - I was Tier III.)

 

[CuriousGeo]

A wrinkle is introduced by this question:



This SOUNDS like you want to be able to track the item from beginning to end through each resolver that touched it. You can have a history by making what is called a "JUNCTION TABLE." In essence, this table connects the list of issues with the list of resolvers. The junction table might be:

IssueHistory: IssueID (LONG, foreign key to Issue table), ResolverID (LONG, foreign key to Resolver table), DateTransferred (DATE, shows when THIS Resolver got THIS Issue), IssueStatus (Code of the issue at the time of transfer OR of resolution), IssueCurrent (YES/NO, TRUE if this entry describes the current state of the issue.) NOTE: IssueCurrent is a SLIGHT denormalization to make it easier to find who has the issue quickly. You set it TRUE to pass it to someone for action and set it FALSE if you are passing it to someone else. (But you set their entry with TRUE.)

You then have a table of resolvers - people who take issues and do something constructive with them. You might have a sequencing list that shows the order in which a given issue will be passed along. At this point, your "distribution" method becomes very much like a HELP DESK scenario. You COULD (as a thought) have a code in your list that says "Next resolver is any help-desk tier II responder." Or something like that. This is the tricky part. The way OUR system worked was not only did we know who was Tier I, Tier II, or Tier III, but we knew who had issues in their queue and could pick the person with the lowest count of active issues. (P.S. - I was Tier III.)

Thank you Doc Man for replying to this post, since it was you who replied on the other post. Your prior explanation about a "chop chain" is what I was trying to accomplish. Your new reply has me thinking... My chain of resolvers is not as complicated as your differing tiers. It's more basic, one person sends an issue to another person who will most likely resolve the issue, or at most forward it to the actual person who can resolve it. Basically 2 tiers: lead persons and supervisors.
Thank you for your further explanation and idea of how to set this up!

 

[Isaac]

How would you design a database that tracks "issues" that can be forwarded to another user in the database. Users are in a table. A user enters an issue (date, issue type, issue summary, etc. in another table) How could the original person forward that issue to another user in the user table (for example a supervisor). Then, say that supervisor decides they should forward that case to someone else. How would I be able to capture each user that has touched that record. Until the issue has been marked as complete/resolved. Thank you for any ideas.

Use an Assignments concept, with queues.

 

[The_Doc_Man]

@CuriousGeo - you are quite welcome.