SharePoint Approach for a system - Task Management

Question

Hi I would like to get some process flow or logic for the following problem,

1. I have a company which works in three states, lets say states A,B,C
2. In the company I also have team of employees work for each states, That is for state A, we have a group of employees, For B we have another group and same in the case of state B.
3. For each states I have 5 different activities.

4. Now I have an admin for each state, who will upload the request into the system using a SharePoint portal in the form of an Excel sheet.

   a. The admin will select in the site, which state, which activity and a flag whether the activity is priority or not.

   b. All the requests will go to the document library for that state, and into the folder for that particular activity.

5. Now the real problem is, that excel sheets can contain 10 lines (each line is one activity - same kind) or 100 lines.

6. Now how can I distribute the requests (1 request is one line in the excel sheet) to the group for that country, on the basis of work load.

Please suggest me one possible flow to achieve this ?

Answer 1

There is an article, which suggests how to read programmatically from excel sheets in SharePoint - http://www.sharepointwithattitude.com/archives/61

I would request to

1. Create a custom timer job, which would programmatically read these excel sheets from the list one by one.

2. Read line by line in the excel sheet and perform actions required for distributing these requests.

3. Once finished, update the status column of the list to completed for the particular excel sheet.

Answer 2

Got a good approach,

Decided to use SSIS (SQL Server Integration Service) which will take the requests to SQL server tables and add a flag with whether the requests are processed. Will then use a windows service to pull the data from the table to send to all users in a table.

Thanks!!