Auto populating columns within a list?

Question

First time user & poster here. A friend recommended me to this site for a question I have in relation to populating columns within a list. Hopefully I can explain it properly and someone could point me in the right direction :)

Environment: MOSS 2007

I have done some extensive reading on scripts for cascading drop downs but I am not sure this is the right solution for my situation noted below

Effectively I have 2 lists at the moment. One master list with all the related employer information and a secondary list that will display the data from the main list. The master employer list has almost 5000 entries spread across 4 columns

• empcode • empname • accmgr • emptype

What I would like to do is based off the above list, create a lookup in my secondary list via the empcode when a new item is created. The lookup in theory is fine, but this is the part im stumped at... is it possible to auto populate the 3 remaining fields in the form after employer code has been chosen?

So to re-iterate if its not very clear:

empcode = S32 empname = test accmgr = mrtest emptype = test123

Once I select S32 from the drop down in my secondary list, the following 3 columns are automatically populated on load from the master list.

Would cascading drop downs be sufficient for this example? The reason why I am reluctant to use it I guess is because each employer code is unique, so each employer code (which is close to 5000) has one unique employer name

I thought there may be an easier way to use some form of script to grab the information from the master list to display that information

I hope I have explained this as clear as I can and really could appreciate some feedback if it can be done

Many thanks

Sanchez

Answer 1

No, cascading drop downs are unlikely to do this for you. You don't need cascading - that normally means having 2 lookups, with one filtering the options in the other.

You're saying 'When I choose a lookup value, I want multiple columns to be populated'

With SharePoint 2010, Lookups can do this out of the box. I note that you're on 2007 though. Given that, I'd suggest an event handler to copy the data from the master list to the secondary list. You could have some problems if the data in the master list changes - that won't be updated in the secondary list. You could also write a timer job (SPJobDefinition) to periodically synchronise that data from the master list again, if necessary.

Both the event handler and timer job would require coding. However, they're fairly achievable.

Edit: You may want to make the other 3 fields hidden in the edit/new forms of the secondary list - this can be achieved via CAML or code. See the 'ShowInNewForm' and 'ShowInEditForm' properties.

Answer 2

You could also use workflows to update the data, you can ask the user to fill in one item, like empcode. Then once it's saved, use a workflow to get the rest of the data and update it.

Answer 3

You could also use SPServices (http://spservices.codeplex.com) to pull the data from your list to populate the three fields with jQuery.

I'm more concerned with the overall architecture and UX. A Lookup with 5000 values is going to be unwieldy at best. Also, storing the three columns won't preserve the relationship, as alluded to by others.