How can I auto-populate list columns based on the selected values of another column?

Question

I have the following 2 lists:

SCG and RPM

SCG has 2 columns:

• SOR Number(Single line of text)
• PS Number(Single line of text)

When I create a new item I create entries like this:

SOR   | PS
------+--------
1000  | 293030
2000  | 293456
3000  | 12345

And so on.

In second list RPM I have 2 fields:

• RSOR Number(look up from 1st list SOR Number column)
• PS Number(single line of text)

I want to be able to create a new item in RPM list by selecting a value from RSOR (where values come as drop down because of the look up):

For example, if I select 2000 then the PS Number column should auto-populate with 293456.

However Dropdown cascading is not possible because for that we should have 3 lists and here data will be dynamic in first list we can fill new items every time and cascading is for static like in city –> state -> country example which is present in many sites in internet.

Can anyone offer advice on how I should link these lists up?

Answer 1

You could add Event Receiver (on ItemAdded), that after you add item to RPM list would update item with value from SCG list.

Automatic populating of field in Edit form is not possible, AFAIK, but in receivers you can do everything with it.

Answer 2

I had a similar requirement recently. Where a lookup field was selected by a user and another field needed to have a default value set dynamically using the value from a third list based on the user's selection. I did this using a combination of the Client Object Model and JQuery.

This came up in another question and I documented my code here: SharePoint 2010 Lookup Column - Autofill with the same Item . The list in question was for creating a new "procedure". There could be 3 types. A new procedure, a revision of an existing procedure, or a request to delete an existing procedure (maybe it is obsolete). If the request was for a new procedure the script did nothing. If the script was for a revision or delete, it looked up the previous "title" from another list and set the new item's title to "rev - previous title" or "delete - previous title". This sort of thing was not a good fit for an event receiver because we did not want the new item to be given a title by the user if it was a "rev" or "delete" and we still needed the user to know what the default title was.

Answer 3

I dont see it mentioned but is this for SharePoint 2010? In 2010 you could just bring over the additional information from the other list automatically based on your selection. If that doesnt specifically meet your needs you could actually modify your edit form to bring in the appropriate fields if you converted it to InfoPath and added a data connection to the SCG list. It would take some work but its possible.