This may be easy or difficult - regardless its 500 points.
Here's what I am working with -
1. MS SQL Server:
I have many database schemas within ONE server.
Server Name = "ONESERVER", and the schemas within this server are client schemas, and are named -
SCH_A,
SCH_B,
SCH_C,
SCH_D,
SCH_E...and so on
2. Each Client Schema has two tables (for this task however). These tables however contain data pertaining to that client. The two tables are -
"VENDORS" (Cols: USER_ACCOUNT, LAST_NAME, FIRST_NAME, VENDOR_STATUS)
"REVIEW_ASSIGNMENTS" (Cols: RA_ID, USER_ACCOUNT, RA_STATUS)
I load review assignments for vendors to review on a weekly basis,which means that I insert into REVIEW_ASSIGNMENTS table for that schema, the USER_ACCOUNT (Ex: JDOE), and set RA_STATUS to "ASSIGNED". The RA_ID is Autonumbered as Unique ID.
So when JDOE from SCH_A logs into my website, he sees that he has reviews to do
(because they are in "ASSIGNED" Status in the REVIEW_ASSIGNMENTS table in SCH_A schema on the back end).
--This is what I want to do--
I want to create another ASP page that gives JDOE the option to REASSIGN that particular review (remember, unique RA_ID) to another vendor in his group (so from the VENDORS Table in SCH_A schema that he exists in).
This page would only show JDOE a select list of USER_ACCOUNT from the VENDORS Table SCH_A schema whose VENDOR_STATUS = 'ACTIVE' (because that's where he's from)
Basically, the SELECT SQL query would be:
SELECT USER_ACCOUNT
FROM VENDORS
WHERE VENDOR_STATUS = 'ACTIVE'
Then, when JDOE selects the user from the generated form above and clicks a Submit button, it should update as follows:
Update REVIEW_ASSIGNMENTS
SET USER_ACCOUNT = ? (the USER_ACCOUNT JDOE selects from the form)
WHERE RA_ID = ? (the RA_ID of that specific review)
AND RA_STATUS = 'ASSIGNED'
So once the USER_ACCOUNT field has been updated from JDOE to whomever he selects from that list, it will assign that specific review to that vendor, and then redirect to "assignment_list.asp"
Each schema's Login/password is the same. (So for SCH_A, the Username is SCH_A, password SCH_A; For SCH_B, its SCH_B/SCH_B, and so on...)
---
A short scenario recap:
JDOE from SCH_A has 25 Assignments is his review list. He realizes that one of the assignments is assigned to him accidentally. He clicks on a link called "Reassign" (which I will hotlink), that takes him to the page (request above). On that page he sees a drop down list with a list of ACTIVE Vendors from his group in SCH_A. He picks a user_account, say 'HCLINTON' and clicks Submit, then gets redirected back to his review list (assignments_list.asp)
On the back end, the USER_ACCOUNT field in the REVIEW_ASSIGNMENTS table is updated from JDOE to HCLINTON. Now HCLINTON has that specific assignment on her list to review.
---
I tried to be as detailed as I could be, but if you need any info, pls feel free to ask. I am hoping to get this done by end of day tomorrow. I would really need an ASP page written out because I am not a programmer (but willing to learn :))
THANKS A LOT!
Start Free Trial
