Stump the Panel: SharePoint Q&A

Hi there not 100% sure if this is the best place for this post but I'm sure you guys will correct me if I'm wrong.

I have a number of custom lists that look like this
List x
Title, c1,c2,c3,c4,c5,c6

List 2
Title, ac1,ac2,ac3,ac4,ac5,ac6

I use a view on list x to average each colum but what I need to do is to automatically copy/update List 2 with theses averages.

The environment I am working in is pretty locked down ie I don't have access to the server central admin functions. It's WSS 3.0
I have full Site collection Admin and I have SPD

My questions are
a) is this possible in the environment
b) is it SPD or something else

I am pretty familiar with SP and how to configure the front end and I use CEWP's a lot to do things. I am good with workflow but to date have not really had to use SPD for anything else as I have managed to find other ways to do it.

Happy if there is another way to do it

Thanks in anticipation

Kenny

The first thing that comes to mind is to use SPD and create a workflow that when an item is changed in list 1 to update list 2. Also have you looked at spServices from Marc Anderson. You could use getlistItem and UpdateListItem. It may be overkill but I am not sure.

I have tried the workflow and I can get it to sort of work. The problem is down to the fact that the first list has many entries and I need to re-average every time somon adds an item.

With SPD workflow I can get it to work but then the users added some extra complexity to thier process and the workflow went out the window.

I will look at Marcs solution, you got to just love a bit of overkill..

Thanks

Kenny

I still think it would work with workflow, even though I would probably use SPServices. I typically do *not* use workflow anymore.

With workflow, you would just have 3 columns in your second list to do the averages. One column for your total (gets updated every time an item is created or updated). One column that is a running tally of items (gets updated every time an item is created or deleted). Finally, one column that is a calculated column of the average of these two columns. Something simple like:

You'll definitely want to expand on that formula to handle division of zero and things like that... But it's definitely doable. You just need to have solid relationships between your lists. I typically use ID whenever possible to create these relationships.

That was my original thinking but it got very complex as there are 18 colums in the first list and depending on how the user categorised their answer using the first 2 fields of the lists then the averages are calculated based on those groupings

example

Field1 Field2 C1 c2 c3 c4.....C18
A B1 1 2 1 1
A C1 1 2 4 4
B C1 2 3 4 3
B B1 3 4 2 3

I want to calculate Average based on Field1=A and B
I also want Average of Field 2 based on B1 and C1

The workflow logic and the 3 fields per C column was just making it too complex for my timy brain to cope with.

I could do something in MS Access in about 5 mins but we don't have that here.

Are you saying, you want the Field 1 and Field 2 to be dynamic? Or do you want Field 1 to always calculate A & B, as well as Field 2 to always calculate B1 & C1? The grouping comment has thrown me off a little bit. Can you elaborate?

ok here goes...

list 1 - name job, department, c1-c18 (potentially 50+ entries)
list 2 job c1-c18 (potentially 20 job titles)
list 3 department c1-c18 (poptentially 10 departments)

People enter data into list 1, they select thier job and department (these are lookups from list 2 and 3)

Now the fun starts.
I need to calculate the average of c1-c18 for each job and then update record in list 2
ditto department

kenny

So, there's a special column that only appears when you have a list with lookups as columns. I'm talking about your List3 from your example. Since that list's column "Department" is being used as a lookup, if you create a new column in List3, called NumOfDeptsUsed for grins, as a lookup into the List 1. You'll get an option to select Lookup (Count Related). What this will do is count how many times within List 1 that particular Department has been used.

My instructions are probably bad, so let me iterate. Think of Parent/Child relationships. Your two lists( List 2, List 3 ) are being used to control input in your List 1. If you create a lookup to List 1 in either List 2 or List 3, the Lookup (Count Related) option appears. That really only gets you 1/3 of the way there. You only have your total usage of the Departments/Jobs. You need to find out how many rows are being used on List 1. Depending on what you really need, you could create a report using a DVWP that counted the rows within the list. OOTB, it's usually dvt_RowCount and then do some math against the numbers in the other list(s) on the fly. Then you could create a form action button that writes these values to the appropriate list. That could get a little hairy, so your only other option is to use SPServices.

Once you get your total Dept/Job usage columns in place, you then will have to use the GetList operation on your List1. That will return the ItemCount. Then you'll want to grab all of your Totals for each Job/Departments from List 2/3 using GetListItems and calculate the average. Finally after calculating the averages, you'll need to use UpdateListItems into List2/3 and write your totals accordingly.

If it were up to me, I'd use SPServices all the way. It'll definitely be a challenge, but you aren't doing anything that's considered OOTB...

Thanks Matt for taking the time...spservices here I come..

Kenny

Good luck! Don't be shy about posting your sticking points. We can help with those too. :P