Multi Approvals Task Management (Dashboard Control Form) (1 Viewer)

Alhakeem1977

Registered User.
Local time
Today, 07:19
Joined
Jun 24, 2017
Messages
308
Hi All,

I am working on a Multi Approvals Task Management database and trying to set up a Dashboard Control Form which will allow me to predefined each task (Combobox) and it's concerned department.

How can I achieve this form of control?

As an example:

1. Task 1 should be assigned to four departments: Dept 1, Dept 3, Dept 4, Dept 2 >> End.
2. Task 2 should be assigned to two departments: Dept 1, Dept 2 >> End.
3. Task 1 should be assigned to three departments: Dept 1, Dept 2, Dept 4 >> End.

Thanks in advance!
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 12:19
Joined
May 7, 2009
Messages
16,393
you need to setup a Login form (with matching userTable with username, password, dept).
login form can be as simple as having a combobox with usernames and dept.
have a button (close button), but instead of closing the form make it's Visible property to False (hidden).
on the dashboard you can validate (allow/disallow) as per dept (from login form combo).
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 00:19
Joined
Feb 19, 2002
Messages
36,284
I am confused by the question.
1. Dashboard forms are typically for display rather than user entry.
2. I don't know what ">> End" means

Are you asking how to assign more than one value to a field? If so, the answer is DON'T DO IT. Create a second table that will hold one row for each department assigned to the task. I would use a subform to assign departments to a task. Use a combo with a RowSource that lists department and just choose one. To prevent duplicates, I would create a unique index on the TaskDept table comprised of TaksID (the foreign key to the parent table) and DeptID (the foreign key to the Dept table.
 

Alhakeem1977

Registered User.
Local time
Today, 07:19
Joined
Jun 24, 2017
Messages
308
I am so sorry for the delay, I am still struggling to overcome this issue, actually to clarify my need is that I want to create a control form that contains the FormName (pdf fillable forms will be uploaded to the database as text path) and it's flow through the departments it can be vary from one form to one another.

How can I structure the control form?
Sorry for my limited knowledge in Access.

Sent from my HUAWEI NXT-L29 using Tapatalk
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 00:19
Joined
Feb 19, 2002
Messages
36,284
Are the documents categorized into types?
Is the path consistent for each type?

If the answers are both yes, you can create a type table to define categories of documents and you can create a path table with a row for each department in the path including a sequence number so that you can control the sequence.
 

Alhakeem1977

Registered User.
Local time
Today, 07:19
Joined
Jun 24, 2017
Messages
308
Are the documents categorized into types?
Is the path consistent for each type?

If the answers are both yes, you can create a type table to define categories of documents and you can create a path table with a row for each department in the path including a sequence number so that you can control the sequence.
Thanks for your response.
Actually I was waiting for your response, but I missed the notification.

Yes, the documents are categorized and the path is consistent for each document too.

Still I am confused to structure the transactions table [Cases] that it has a columns for each department first one the Customer service department who create the entry that they chose the document type (predefined from the control form) upload the document to the system then it will be assigned (workflow) to the concern departments and it can be vary to four, three or two departments depends on the document type.

That's my aim, as you stated for sure it will automate the process but it's a bit difficult for me to implement it, do you prefer to upload my database or you can give me more clarifications to structure my tables?

Thanks a lot in advance!





Sent from my HUAWEI NXT-L29 using Tapatalk
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 23:19
Joined
Feb 28, 2001
Messages
22,780
You are looking for a linear list of approvals. IF the list MUST be approved in a specific order, then the simplest way is an ordered junction table.

You have two main tables: your documents and your departments. If both tables have a usable prime key, then your solution is a third table that we call a junction. You will also need a users table that lets you track who did what to whom at any given time, a persons table perhaps. In that table, you would need a Department ID field showing which department the person represents, and perhaps 0 if this person can use the DB but doesn't have approval authority, only read-only / viewing authority.

DocDepJunction:

DocNum: Long, foreign key to Docs table
DepNum: Long, foreign key to Deps table
OrdNum: Long, shows the order in which the the approvals must occur: 1 must be first, 2 must be 2nd etc.
AppDate: Date, the date/time of the approval
AppPers: Long, foreign key to Persons table (representing who approved it)

You have one record in this table for each combination of document and department, so for a given document, you have N+1 records where N is the number of departments and the +1 is the "END department" entry that says you are done.

Now build a query that only shows the record with the LOWEST ordinal number because that record represents the department that must approve the document next.

Then you can build joins between the junction table and the other two tables. When a person logs in, you can query that list of "lowest ordinals" that have the person's department ID. That person can then review and approve as needed. Obviously, you will have a special value representing the "END" department, which doesn't need approval and would use that END department to make lists of documents that are fully approved.

For statuses, you can query this same junction for documents where the lowest available entry is the "END department" entry - and thus get a list of closed documents. Or you can make a query against the same table for cases where the lowest entry is NOT the "END department" thus seeing what is still pending approval (and implicitly, which department needs to approve it.)

This is just an idea, worth pursuing only if it makes sense to you. So I'll stop here for new and let you think about it.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 00:19
Joined
Feb 19, 2002
Messages
36,284
When working in a relational database environment think DOWN rather than ACROSS. Data repeats as rows. Each department is on a separate row. You would never repeat columns as you do with Excel.

This basic difference in point of view is what enables you do do something ONCE in Access that you would have to do multiple times (across) in Excel and which causes NO code or query changes in Access but which causes function and code changes in Excel. Every time you need to add a new department column to your spreadsheet, you need to modify ALL functions to include the new column. When you add a new department row to a table, nothing has to change.
 

Alhakeem1977

Registered User.
Local time
Today, 07:19
Joined
Jun 24, 2017
Messages
308
When working in a relational database environment think DOWN rather than ACROSS. Data repeats as rows. Each department is on a separate row. You would never repeat columns as you do with Excel.

This basic difference in point of view is what enables you do do something ONCE in Access that you would have to do multiple times (across) in Excel and which causes NO code or query changes in Access but which causes function and code changes in Excel. Every time you need to add a new department column to your spreadsheet, you need to modify ALL functions to include the new column. When you add a new department row to a table, nothing has to change.
Thanks for your clarifications, that's exactly what I want, unfortunately, I am out of my country now, once I reach I will let you know the progress of my application.

Sent from my HUAWEI NXT-L29 using Tapatalk
 

Users who are viewing this thread

Top Bottom