none
Get and set Sharepoint online Lookup column in to a dropdown RRS feed

  • Question

  • Hi all,

    I m trying to bind a dropdown list with Sharepoint lookup column. 

    List- Leave Requesthave a Department column thatlookup column get values fromdepartmnet</g> table. 

    I m using following but it is not working the department column value is Null when I try to write the new leave request back to the leave request list .</g> 

    Using MVC C#

    classes 

                  

     public class Department
        {
            public string Name { get; set; }
            public int DepartmentID { get; set; }

            public List<Department> listdep { get; set; }

        }  public class Leave
        {
            public string Description { get; set; }
            public string Reason { get; set; }
            public DateTime StartDate { get; set; }
            public DateTime EndDate { get; set; }
            public string Manager { get; set; }
            public string Status { get; set; }

            public string Department { get; set; }
        } public class CreateNewLeave
        {
            public List<Leave> Leaves { get; set; }
            public Leave Leave { get; set; }
            public List<Department> Departments { get; set; }
            public Department Department { get; set; }


        }

    Home Controller 

      [HttpGet]
            public ActionResult Create()
            {
                var spContext = LocalLeaveAddinWeb.SharePointContextProvider.Current.GetSharePointContext(HttpContext);

                //Get Leave Reason type choice field values
                List<SelectListItem> leaveTypes = SharePointService.GetLeaveTypes(spContext);
                ViewBag.VacationTypes = leaveTypes;

                //Get the department details from department SP list
                CamlQuery queryDepartment = new CamlQuery();
                queryDepartment.ViewXml = @"<View><ViewFields><FieldRef Name='Title'/><FieldRef Name='ID'/></ViewFields></View>";
                List<Models.Department> Departments = (SharePointService.GetDepartment(spContext, queryDepartment)).ToList();
                List<string> listOfDept = new List<string>();
                foreach (var item in Departments)
                {
                    listOfDept.Add(item.Name);
                }
                ViewBag.AllDepartments = listOfDept;

                return View();
            }

            //Below Action gets called when "Create" button is clicked on the create.cshtml view. Here we write code to store data in to a datasource (like SQL server) in this case SharePoint List.
            [HttpPost]
            public ActionResult Create(LeaveAddinWeb.Models.CreateNewLeave leaves)
            {
                var spContext = LocalLeaveAddinWeb.SharePointContextProvider.Current.GetSharePointContext(HttpContext);
                VacationDataAccessLayer vdal = new VacationDataAccessLayer();
                vdal.AddLeaveRequest(leaves, spContext);
                return RedirectToAction("Index", new { SPHostUrl = LocalLeaveAddinWeb.SharePointContext.GetSPHostUrl(HttpContext.Request).AbsoluteUri });
            }

    Views

     <!--Get the Departments as a drop down-->
                <div class="form-group">
                    @Html.LabelFor(model => model.Department.Name, new { @class = "control-label col-md-2" })
                    <div class="col-md-10">
                        @*Binding the choice field to dropdown list*@

                        @Html.DropDownListFor(model => model.Department.Name, new SelectList(ViewBag.AllDepartments, "Department"), "Select the Department")
                        @*@Html.DropDownList("Department", (IEnumerable<SelectListItem>)@ViewBag.AllDepartments, "Select the Department")*@


                        @Html.ValidationMessageFor(model => model.Department.Name)

                    </div>
                </div>

    Following will get the data from SP and bind with model

    //Gets <g class="gr_ gr_365 gr-alert gr_spell gr_disable_anim_appear ContextualSpelling ins-del multiReplace" data-gr-id="365" id="365">deprtment</g> from SP list

            public static List<Models.Department> GetDepartment(LocalLeaveAddinWeb.SharePointContext spContext, CamlQuery camlQuery)
            {

                departments = new List<Models.Department>();
                using (var clientContext = spContext.CreateUserClientContextForSPHost())
                {
                    if (clientContext != null)
                    {
                        List lstDepartments = clientContext.Web.Lists.GetByTitle("Department");
                        ListItemCollection lstDepartmentItems = lstDepartments.GetItems(camlQuery);
                        clientContext.Load(lstDepartmentItems);
                        clientContext.ExecuteQuery();

                       

                        if (lstDepartmentItems != null)
                        {
                            foreach (var lstDepartmentItem in lstDepartmentItems)
                            {
                                departments.Add(
                                new Models.Department
                                {
                                    Name = lstDepartmentItem["Title"].ToString(),
                                    DepartmentID = Convert.ToInt32(lstDepartmentItem["ID"]),


                                });
                            }
                        }
                    }


                }
                return departments;
            }

     then 

    public class VacationDataAccessLayer
        {

            //Adds the user filled vacation request form values to Leave Request list
            public void AddLeaveRequest(Models.CreateNewLeave leaves, LocalLeaveAddinWeb.SharePointContext spContext)
            {
                
                using (var clientContext = spContext.CreateUserClientContextForSPHost())
                {
                    if (clientContext != null)
                    {

                        
                        List lstLeaves = clientContext.Web.Lists.GetByTitle("LeaveRequests");
                        ListItemCreationInformation itemCreateInfo = new ListItemCreationInformation();
                        ListItem leaveListItem = lstLeaves.AddItem(itemCreateInfo);
                       
                        leaveListItem["Status"] = "Pending With Manager";
                        FieldLookupValue Dep = new FieldLookupValue();
                     
                        Dep.LookupId = leaves.Department.DepartmentID;
                       leaveListItem["Department"] = Dep;
                       
                        leaveListItem.Update();
                        clientContext.ExecuteQuery();

                    }
                }
            }


    d.n weerasinghe





    • Edited by newnw Wednesday, March 15, 2017 6:03 AM
    Wednesday, March 15, 2017 6:00 AM

Answers

  • Hi,

    Please change the two lines of code like this:

    Dep.LookupId = 1;

    leaveListItem["Department"] = Dep;

    Best regards,

    Patrick

    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    • Marked as answer by newnw Friday, March 31, 2017 10:04 PM
    Wednesday, March 22, 2017 12:59 PM
    Moderator

All replies

  • Hi,

    Does it mean that, when trying to add a new item to the “LeaveRequests” list, the “Department” column is blank?

    If this is the case, about this line:

    Dep.LookupId = leaves.Department.DepartmentID;

    Can you please log out the value of “leaves.Department.DepartmentID” to see if it is a number and valid item id in the source list(Department)?

    Please set it with a fixed item id like this and do another test:

    Dep.LookupId = <a valid item id get from the source list>;

    Such as:

    Dep.LookupId = 1;

    Best regards,

    Patrick

    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Friday, March 17, 2017 8:17 AM
    Moderator
  • Hi Patrick,

    Thank you for your mail, I have changed the code as follows and then I get an error 

    leaveListItem["Department"] = 1;

    An exception of type 'Microsoft.SharePoint.Client.ServerException' occurred in Microsoft.SharePoint.Client.Runtime.dll but was not handled in user code
    Additional information: Operation is not valid due to the current state of the object.

    Then I log out the value of “leaves.Department.DepartmentID” and it is null ,</g></g> I think this happens because of following code in my Controller-  not adding the item.DepartmentID

    CamlQuery queryDepartment = new CamlQuery();
                queryDepartment.ViewXml = @"<View><ViewFields><FieldRef Name='Title'/><FieldRef Name='ID'/></ViewFields></View>";
                List<Models.Department> Departments = (SharePointService.GetDepartment(spContext, queryDepartment)).ToList();
                List<string> listOfDept = new List<string>();
                foreach (var item in Departments)
                {
                    listOfDept.Add(item.Name);
                }
                ViewBag.AllDepartments = listOfDept;

                return View();

    .


    d.n weerasinghe



    • Edited by newnw Monday, March 20, 2017 5:13 AM
    Monday, March 20, 2017 5:11 AM
  • Hi,

    Please change the two lines of code like this:

    Dep.LookupId = 1;

    leaveListItem["Department"] = Dep;

    Best regards,

    Patrick

    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    • Marked as answer by newnw Friday, March 31, 2017 10:04 PM
    Wednesday, March 22, 2017 12:59 PM
    Moderator
  • Hi, 

    Any progress now?

    Best regards,

    Patrick


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Friday, March 31, 2017 12:01 PM
    Moderator
  • Hi Patrick, Thank you very much for your help, yes all working now. Now I m doing the validation and use Jquery date picker and other functions. I ll be posting the entir code when I finish, hope it will beneficial for someone else. Thanks.

    d.n weerasinghe

    Friday, March 31, 2017 10:09 PM
  • Hi Patrick,

    As I told you I m, doing the data annotation validation now as follows

    My View is

      <div class="form-group">
                    @Html.LabelFor(model => model.Leave.EmployeeName, new { @class = "control-label col-md-2" })
                    <div class="col-md-10">
                        @Html.EditorFor(model => model.Leave.EmployeeName)
                        @Html.ValidationMessageFor(model => model.Leave.EmployeeName)
                    </div>
                </div>
                <div class="form-group">
                    @Html.LabelFor(model => model.Leave.Reason, new { @class = "control-label col-md-2" })
                    <div class="col-md-10">
                        @*Binding the choice field to dropdown list*@                   
                        @Html.DropDownListFor(model => model.Leave.Reason, ViewBag.VacationTypes as IEnumerable<SelectListItem>)                                              
                             @Html.ValidationMessageFor(model => model.Leave.Reason)
                    </div>
                </div>

                <!--Get the Departments as a drop down-->
                <div class="form-group">
                    @Html.LabelFor(model => model.Department.Name, new { @class = "control-label col-md-2" })
                    <div class="col-md-10">
                        @*Binding the choice field to dropdown list*@
                        @Html.DropDownListFor(model => model.Department.Name, new SelectList(ViewBag.AllDepartments, "Department"), "Select the Department")
                        @Html.ValidationMessageFor(model => model.Department.Name)

                    </div>
                </div>

    My Controller

                                                                                                      

     public ActionResult Index()
            {
                var spContext = LocalLeaveAddinWeb.SharePointContextProvider.Current.GetSharePointContext(HttpContext);
                ViewBag.Username = SharePointService.GetUserName(spContext);
                //Read submitted leave requests and display in the view
                CamlQuery queryLeaves = new CamlQuery();
                queryLeaves.ViewXml = @"<View><ViewFields><FieldRef Name='Title'/><FieldRef Name='StartDate'/><FieldRef Name='EndDate'/><FieldRef Name='Reason'/>
                                        <FieldRef Name='Manager'/><FieldRef Name='Status'/><FieldRef Name='Department_x003a_Title'/></ViewFields></View>";
                List<Leave> leaves = SharePointService.GetLeaves(spContext, queryLeaves);
                return View(leaves);
            }

            //Below Action gets called When we navigate to the HomeController/Create url
            [HttpGet]
            public ActionResult Create()
            {
                var spContext = LocalLeaveAddinWeb.SharePointContextProvider.Current.GetSharePointContext(HttpContext);

                //Get the department details from department SP list
                CamlQuery queryDepartment = new CamlQuery();
                queryDepartment.ViewXml = @"<View><ViewFields><FieldRef Name='Title'/><FieldRef Name='ID'/></ViewFields></View>";
                List<Models.Department> Departments = (SharePointService.GetDepartment(spContext, queryDepartment)).ToList();
                List<string> listOfDept = new List<string>();
                foreach (var item in Departments)
                {

                    listOfDept.Add(item.Name);
                }
                ViewBag.AllDepartments = listOfDept;

                //Get Leave Reason type choice field values
                List<SelectListItem> leaveTypes = SharePointService.GetLeaveTypes(spContext);


                ViewBag.VacationTypes = leaveTypes;

                return View();
            }


            //Get the selected Depsrtment Id 
            [HttpGet]
            public string GetSelectedDeprtmentId(string SlectDep)
            {
                var spContext = LocalLeaveAddinWeb.SharePointContextProvider.Current.GetSharePointContext(HttpContext);


                //Get the department details from department SP list

                CamlQuery querySelectedDepartment = new CamlQuery();


                querySelectedDepartment.ViewXml = @"<View><Query><Where><Eq><FieldRef Name='Title'/><Value Type='Text'>"+SlectDep+ "</Value></Eq></Where></Query></View>";

                             string SelectedDepartmentId = SharePointService.GetSelectedDep(spContext, querySelectedDepartment);



                return SelectedDepartmentId;
            }

            //Below Action gets called when "Create" button is clicked on the create.cshtml view.
            [HttpPost]
            public ActionResult Create(Models.CreateNewLeave leaves)
            {

    if (ModelState.IsValid)
                {
                    string SelectedID = GetSelectedDeprtmentId(leaves.Department.Name.ToString()).ToString();
                leaves.Department.SelectDep = Convert.ToInt32(SelectedID);
                var spContext = LocalLeaveAddinWeb.SharePointContextProvider.Current.GetSharePointContext(HttpContext);
                VacationDataAccessLayer vdal = new VacationDataAccessLayer();

                    vdal.AddLeaveRequest(leaves, spContext);
                    return RedirectToAction("Index", new { SPHostUrl = LocalLeaveAddinWeb.SharePointContext.GetSPHostUrl(HttpContext.Request).AbsoluteUri });
                }
                else return View();

            }

    Model

                  

    public class Leave
        {
            [Required (ErrorMessage="EmployeeName required")]
            public string EmployeeName { get; set; }
            public string Reason { get; set; }
            public DateTime  StartDate { get; set; }
            public DateTime  EndDate { get; set; }
            public string Manager { get; set; }
            public string Status { get; set; }

            public string Department { get; set; }

        } public class CreateNewLeave
        {
            public List<Leave> Leaves { get; set; }
            public Leave Leave { get; set; }
            public List<Department> Departments { get; set; }
            public Department Department { get; set; }


        }public class Department
        {
            public string Name { get; set; }
            public int DepartmentID { get; set; }

            public List<Department> listdep { get; set; }
            public int SelectDep { get; set; }
        }

    When I do this I m getting the following error 

    Additional information: There is no ViewData item of type 'IEnumerable<SelectListItem>' that has the key 'Leave.Reason'.

    ON ->>> @Html.DropDownListFor(model => model.Leave.Reason, ViewBag.VacationTypes as IEnumerable<SelectListItem>) 

    Could you please help me to fix this . 

    Here how I Processing the data 

      public class SharePointService
        {
            public static List<Models.Department> departments;
            public static string GetUserName(LocalLeaveAddinWeb.SharePointContext spContext)
            {
                string strUserName = null;
                User spUser = null;
                using (var clientContext = spContext.CreateUserClientContextForSPHost())
                {
                    if (clientContext != null)
                    {
                        spUser = clientContext.Web.CurrentUser;
                        clientContext.Load(spUser, user => user.Title);
                        clientContext.ExecuteQuery();
                        strUserName = spUser.Title;
                    }
                }
                return strUserName;
            }

            //Reads submitted leaves and displays in the UI
            public static List<Models.Leave> GetLeaves(LocalLeaveAddinWeb.SharePointContext spContext, CamlQuery camlQuery)
            {

                List<Models.Leave> leaves = new List<Models.Leave>();
                using (var clientContext = spContext.CreateUserClientContextForSPHost())
                {
                    if (clientContext != null)
                    {
                        List lstLeaves = clientContext.Web.Lists.GetByTitle("LeaveRequests");
                        ListItemCollection lstLeaveItems = lstLeaves.GetItems(camlQuery);
                        clientContext.Load(lstLeaveItems);
                        clientContext.ExecuteQuery();
                        if (lstLeaveItems != null)
                        {
                            foreach (var lstLeaveItem in lstLeaveItems)
                            {
                               leaves.Add(
                                new Models.Leave
                                {
                                    EmployeeName = lstLeaveItem["Title"].ToString(),
                                    StartDate = (DateTime)lstLeaveItem["StartDate"],
                                    EndDate = (DateTime)lstLeaveItem["EndDate"],
                                    Reason = lstLeaveItem["Reason"].ToString(),
                                    Manager = lstLeaveItem["Manager"].ToString(),
                                    Status = lstLeaveItem["Status"].ToString(),
                                  //  Department = lstLeaveItem["Department"].ToString()
                                    Department = (lstLeaveItem.FieldValues["Department_x003a_Title"] as FieldLookupValue).LookupValue.ToString()
                                });
                            }
                        }
                    }
                }
                return leaves;
            }

            //Gets Leave Reason type choice field values from list
            public static List<SelectListItem> GetLeaveTypes(LocalLeaveAddinWeb.SharePointContext spContext)
            {
                List<SelectListItem> reasonTypes = new List<SelectListItem>();
                using (var clientContext = spContext.CreateUserClientContextForSPHost())
                {
                    if (clientContext != null)
                    {
                        List lstLeaves = clientContext.Web.Lists.GetByTitle("LeaveRequests");
                        Field choiceReason = lstLeaves.Fields.GetByInternalNameOrTitle("Reason");
                        FieldChoice fldReason = clientContext.CastTo<FieldChoice>(choiceReason);
                        clientContext.Load(fldReason, f => f.Choices);
                        clientContext.ExecuteQuery();
                        foreach (string vacationType in fldReason.Choices)
                        {

                            reasonTypes.Add(new SelectListItem() { Text = vacationType.ToString(), Value = vacationType.ToString() });
                        }
                    }

                }//End Using
                return reasonTypes;
            }

            //Gets <g class="gr_ gr_121 gr-alert gr_spell gr_run_anim ContextualSpelling ins-del multiReplace" data-gr-id="121" id="121">deprtment</g> from SP list

            public static List<Models.Department> GetDepartment(LocalLeaveAddinWeb.SharePointContext spContext, CamlQuery camlQuery)
            {

                departments = new List<Models.Department>();
                using (var clientContext = spContext.CreateUserClientContextForSPHost())
                {
                    if (clientContext != null)
                    {
                        List lstDepartments = clientContext.Web.Lists.GetByTitle("Department");
                        ListItemCollection lstDepartmentItems = lstDepartments.GetItems(camlQuery);
                        clientContext.Load(lstDepartmentItems);
                        clientContext.ExecuteQuery();

                       

                        if (lstDepartmentItems != null)
                        {
                            foreach (var lstDepartmentItem in lstDepartmentItems)
                            {
                                departments.Add(
                                new Models.Department
                                {
                                    Name = lstDepartmentItem["Title"].ToString(),
                                    DepartmentID = Convert.ToInt32(lstDepartmentItem["ID"]),


                                });
                            }
                        }
                    }


                }
                return departments;
            }

            
            public static string  GetSelectedDep(LocalLeaveAddinWeb.SharePointContext spContext, CamlQuery camlQuery)
            {
                string SelectedDepartment="";

                using (var clientContext = spContext.CreateUserClientContextForSPHost())
                {
                    if (clientContext != null)
                    {
                        List lstDepartments = clientContext.Web.Lists.GetByTitle("Department");
                        ListItemCollection lstDepartmentItems = lstDepartments.GetItems(camlQuery);
                        clientContext.Load(lstDepartmentItems);
                        clientContext.ExecuteQuery();

                        int x = lstDepartmentItems.Count;

                        if (lstDepartmentItems != null)
                        {
                            
                            foreach (ListItem lstDepartmentItem in lstDepartmentItems)
                            {

                                SelectedDepartment = lstDepartmentItem["ID"].ToString();


                               
                            }
                        }
                    }


                }
                return SelectedDepartment;
            }


        }


    d.n <g class="gr_ gr_62 gr-alert gr_spell gr_run_anim ContextualSpelling ins-del multiReplace" data-gr-id="62" id="62">weerasinghe</g>



    • Edited by newnw Tuesday, April 4, 2017 5:49 AM
    Tuesday, April 4, 2017 5:20 AM
  • Hi,

    Thanks for your sharing.

    Best regards,

    Patrick 


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Tuesday, April 4, 2017 10:23 AM
    Moderator
  • Hi Patrick,

    Sorry for troubling you, I m getting an error on that code when I try to do the validation. 

    Could you please have a look and help me.

    Thanks. 


    d.n weerasinghe

    Wednesday, April 5, 2017 12:00 AM