locked
List Column Connectivity-look up RRS feed

  • Question

  • Hi,

    I have 2 list training details and training tracking

    in training details- TrainingName and Modules i created as single line of text

    in training tracking TrainingAssigned and TotalModules i created as look up

    when i enter 2 times in training details

    1st entry-dot net(training name),modules(10)

    2nd entry-moss(training name),module(20)

    in 2nd list my entriesshud be like that when i choose dot net(training assigned) then for totalmodules column 10 shud come and when i choose moss then 20 shud come(as both column are look up in 2nd list) right now when i choose dot net ..or mos then both (10 20) entries are coming how can i do this logic

    For your more clarification-as i choose training assigned name(eg dot net) then in totalmodules only those  number shud present which someone enter in 1st list (training details) -module column with related training name .

    help:(

     i wrote this event handler is it relevant or will it work?

    using System;
    
    using System.Collections.Generic;
    
    using System.Linq;
    
    using System.Text;
    
    using Microsoft.SharePoint;
    
    using System.Web;
    
    using System.Net.Mail;
    
    using System.IO;
    
    using System.Data;
    
    using System.Xml;
    
    using Microsoft.SharePoint.Utilities;
    
    using System.Web.UI.WebControls;
    
    
    
    namespace NewTrainingTrackEventHandling 
    
    {
    
     public class TrainingTrack : SPItemEventReceiver
    
     {
    
      
    
      public HttpContext context;
    
      public TrainingTrack()
    
      {
    
       if (context == null)
    
       {
    
        context = HttpContext.Current;
    
       }
    
      }
    
      public override void ItemAdding(SPItemEventProperties properties)
    
      {
    
    
    
        
    
        SPSite siteurl = new SPSite("http://172.20.14.16/IS/");
    
        SPWeb mySite = siteurl.OpenWeb();
    
        SPList myList = mySite.Lists["Training Tracking"];
    
        SPField ListField = myList.Fields["Training Assigned"];
    
        string classname = "NewTrainingTrackEventHandling.TrainingTrack";
    
        string asmname = "NewTrainingTrackEventHandling,Version=1.0.0.0,culture=neutral, PublicKeyToken=d766c22041c04c60";
    
        myList.EventReceivers.Add(SPEventReceiverType.ItemAdding, asmname ,classname);
    
        String strViewName = "AllItems.aspx";
    
        SPView view = properties.OpenWeb().GetViewFromUrl("Lists/Training%20Tracking/" + strViewName);
    
        SPListItemCollection collListItems = myList.GetItems(view);
    
    
    
        if (collListItems.Count == 0 && collListItems.Count == 1)//Always true
    
        {
    
         ListField.ShowInNewForm = true; //show in new form (newform.aspx)//{this line or below line both for reaching at Newform.aspx}
    
    
    
         context.Response.Redirect("/IS/Lists/Training%20Tracking/NewForm.aspx");
    
         string trainingAssigned ="";
    
         string totalModules ="";
    
         SPList mylist = SPContext.Current.Web.Lists["Training Details"];
    
         SPQuery query = new SPQuery();
    
         //this query will pur value of Training Name Field into training Assigned variable
    
         query.Query = "<Where><Eq><FieldRef Name='Training Name'/><Value Type='Text'>" + trainingAssigned + "</Value></Eq></Where>";
    
         //SPListItem item = list.GetItems(query)[0];
    
         SPListItemCollection items = mylist.GetItems(query);
    
         SPListItem nitem = items[0];
    
         totalModules = Convert.ToString(nitem["Modules"]);
    
        }
    
       
    
        
    
       
    
      }
    
     }
    
    }
    
    
    
    

     

     


    sudhanshu sharma



    • Moved by Mike Walsh FIN Thursday, August 18, 2011 2:52 PM No mention of workflow but it's a very confusing post - is it even Admin? (From:SharePoint - Workflow (pre-SharePoint 2010))
    • Edited by sudhanshuSharma Thursday, August 25, 2011 9:37 AM
    Thursday, August 18, 2011 2:04 PM

All replies

  • Please help how can i achieve this relationship in columns ?

    :(:(


    sudhanshu sharma
    Friday, August 19, 2011 7:05 AM
  • I don't think there is an OOB way to do it. You might have to write simple code behind scenes..maybe a custom webpart where you can create a new item . In your code you can have a CAML query to extract the item(from list training details) where Training name = Training Assigned(from list Training Tracking) and pass the item["Modules"] to your new item ["Course"]. You can accomplish what you are looking for this way.
    Thanks, GeeVed
    Friday, August 19, 2011 10:02 PM
  • Hi,

    Create a formula for the column with IF statement and then pass the values based on the check.

    I hope this will help you out.

     


    Thanks, Rahul Rashu
    Saturday, August 20, 2011 9:05 AM
  • hi Rahul,

    I didnt get how can i create formula ...and where is need to write ....can u give a sample formula and tell me where i should write the same


    sudhanshu sharma
    Monday, August 22, 2011 9:39 AM
  • Hi SudhansshuS,

    the formulas are based on Excel system.

    You can enter it when you modify your column, it is the last section of the page.

    Hope this help,

    Mathieu


    Cdt, Mathieu D.
    Monday, August 22, 2011 3:17 PM
  • you are talking in moss 2007 formula is based on excel system

    when i do new item then i choose look up value and all ...where is last section of page....i didnt get  you sorry....


    sudhanshu sharma
    Tuesday, August 23, 2011 4:13 AM
  • hi Geeved,

    will i have to write code for newitem where CAML query will be required for look up columns.


    sudhanshu sharma
    Tuesday, August 23, 2011 4:18 AM
  • Hi Sudhanshu,

    I followed in the same way in my dev box as you have specified your requirement, however azt one place I found that it requires some hard coded values.

    I think this would not be the way you want to implement this scenario.

    Hence I would suggest you a simple customization that would serve your purpose.

    You can follow these steps:

    1. Create your list1 and List2.

    2. Create columns with values in List 1.

    3. Create a lookup column in list2 this will be mapped with Training Name column of List1. Create another column to store modules in list 2.

    4. Now implement an itemAdding event receiver in list two, this will check the value of lookup column you have selected in your list 2 and will compare this value by enumerating it through List 1 and will pick up the corresponding value.

    public override void ItemAdding(SPItemEventPropertiesproperties)

     {

    //The code logic to be added over here.

    }

    I hope this will help you out 

     


    Thanks, Rahul Rashu
    Tuesday, August 23, 2011 8:31 AM
  • Hi SudhanshuS,

    Yes, You will have to write code to manipulate those fields whenever a new item is added. If required you can create a custom 'Add new item' page(webpart/usercontrol) and deploy to your sharepoint site. Or add code to the ItemAdding event handler as suggested by Rahul.Your Logic should be this way

     

    - When a new item is added to you training tracking list, get the 'Training Assigned' value(e.g: .net) and pass to your CAML query.

    e.g :

    string trainingAssigned =".net";

    string  courseStr = string.Empty;

    SPList mylist = web.Lists["Training Details"];

    SPQuery query  = new SPQuery();

    Query.Query = "<Where><Eq><FieldRef Name='Training Name'/><Value Type='Text'>"+ trainingAssigned + "</Value></Eq></Where>";

    SPListItemCollection  items = mylist.GetItems(query);

    SPListItem nitem = items[0];

    courseStr =Convert.ToString(nitem["Modules"])

    - This should query your Training Details list to acquire the 'Modules' value for the item where Training Name =".net"

    - then update the new item's 'Course' field value with the value(courseStr) retrieved from the query.

     

     


    Thanks, GeeVed
    Tuesday, August 23, 2011 9:10 PM
  • hi GeeVed,

    thanks for your help.

    can you answer me few questions-

    if i create new item through my code it means i need to write code in designer or indirectly m creating newForm.aspx if i do so...how can i hide bydefault newForm.aspx it means whenever i do newitem from list my newForm.aspx shud work rite? m i getting right?

    regards,

    Sudhanshu


    sudhanshu sharma
    Wednesday, August 24, 2011 4:16 AM
  • Hi Sudhanshu,

    The code mentioned above needs to be written in form of a .net assembly and then needs to be deployed in GAC.

    No use of Sharepoint designer here.

     


    Thanks, Rahul Rashu
    Wednesday, August 24, 2011 4:19 AM
  • Rahul is right. You do not have to use SPD. You can create a usercontrol and deploy as a webpart with dll in GAC. The usercontrol can imitate your newform.aspx. You can also change the url for your New Item button on your list using SPD. Take a look at this thread.

    You can change it to the URL where you custom form exists.


    Thanks, GeeVed
    Wednesday, August 24, 2011 10:25 PM
  • Hi All,

    i wrote a event handle with class library when in portal someone open list Training Tracking and click newitem we reach at NewForm.aspx

    can i write event handling for this NewForm.aspx

    See My COde and suggest can we do like this-

    namespace NewTrainingTrackEventHandling 
    
    
    
    {
    
    
    
     public class TrainingTrack : SPItemEventReceiver
    
    
    
     {
    
    
    
     
    
    
    
     public HttpContext context;
    
    
    
     public TrainingTrack()
    
    
    
     {
    
    
    
      if (context == null)
    
    
    
      {
    
    
    
      context = HttpContext.Current;
    
    
    
      }
    
    
    
     }
    
    
    
     public override void ItemAdding(SPItemEventProperties properties)
    
    
    
     {
    
    
    
      SPSite collections = new SPSite("http://172.20.14.16/IS/");
    
    
    
    
    
    
    
      SPWeb mySite = collections.OpenWeb();
    
    
    
      SPList incidentList = mySite.Lists["Training Tracking"];
    
    
    
      string classname = "NewTrainingTrackEventHandling.TrainingTrack";
    
    
    
      string asmname = "NewTrainingTrackEventHandling,Version=1.0.0.0,culture=neutral, PublicKeyToken=d766c22041c04c60";
    
    
    
      incidentList.EventReceivers.Add(SPEventReceiverType.ItemAdding, asmname ,classname);
    
    
    
      String strViewName = "AllItems.aspx";
    
    
    
      SPView view = properties.OpenWeb().GetViewFromUrl("Lists/Training%20Tracking/" + strViewName);
    
    
    
      SPListItemCollection collListItems = incidentList.GetItems(view);
    
    
    
    
    
    
    
      if (collListItems.Count == 0 && collListItems.Count == 1)//Always true
    
    
    
      {
    
    
    
    
    
    
    
      context.Response.Redirect("/IS/Lists/Training%20Tracking/NewForm.aspx");
    
    
    
      string trainingAssigned = "";
    
    
    
      string totalModules = string.Empty;
    
    
    
      SPList mylist = SPContext.Current.Web.Lists["Training Details"];
    
    
    
      SPQuery query = new SPQuery();
    
    
    
      query.Query = "<Where><Eq><FieldRef Name='Training Name'/><Value Type='Text'>" + trainingAssigned + "</Value></Eq></Where>";
    
    
    
      //SPListItem item = list.GetItems(query)[0];
    
    
    
      SPListItemCollection items = mylist.GetItems(query);
    
    
    
      SPListItem nitem = items[0];
    
    
    
      totalModules = Convert.ToString(nitem["Modules"]);
    
    
    
    
    
    
    
    

     

     Please suggest ..its going to be very difficult:(

    how this event handler will be connect wid my portal list bydefault NewForm.aspx :(


    sudhanshu sharma



    Thursday, August 25, 2011 6:26 AM