Filtered lookup column with 'WHERE' clause

Question

I have 2 lists: List 1 and List 2.

List 1 has 2 columns 'supplier' and 'broker'.

List 2 has 2 columns 'nature' and 'supplier name'.

My requirement is 'supplier' is a lookup from 'supplier name' column of 'List 2' where 'nature="supplier"' and 'broker' is a lookup from 'supplier name' of 'List 2 where 'nature="broker"'.

I am working on SharePoint foundation 2010 .

Things I have tried :

Using sharepoint designer 2010 : I created new form fields but getting error that says "Unable to display this web part" .

C# code for event handling mainly on item added event : the query doesnt return any result . Below is my code .

private void SetCalculatedTitle(SPItemEventProperties properties)
{
    SPList _supplierMaster ;
    if (properties.ListTitle == "PurchaseMaster")
    {
        if (properties.EventType == SPEventReceiverType.ItemAdded ||
        properties.EventType == SPEventReceiverType.ItemUpdated)
        {

          SPListItem _listItem = properties.ListItem;



            using (SPSite site = new SPSite("http://****/"))
            {

                using (SPWeb web = site.OpenWeb())
                {
                     _supplierMaster = web.Lists["PARTY"];

                }

            }

           // SPWeb web = new SPWeb("http://****/");// SPContext.Current.Web;



            SPQuery queryEmployee = new SPQuery();
            queryEmployee.Query = "<Where><Eq><FieldRef Name='nature' LookupId='True' /><Value Type='Lookup'>supplier</Value></Eq></Where>";
            SPListItemCollection result = _supplierMaster.GetItems(queryEmployee);


            _listItem["SupplierName"] = result;



                                this.EventFiringEnabled = false;
                                _listItem.SystemUpdate();
                                this.EventFiringEnabled = true;


     }

Using this code i was just trying to find that result should retrieve the desired value while debugging but couldn't get it.

I have no experience in C# neither in SharePoint .

Please give me a detailed solution if possible.

score 4 · Accepted Answer · answered Mar 12 '14 at 19:46

4

Your question seem to me like you need something like this Codeplex field. It's custom field like standard lookup but it allows you to specify query when adding column to the list. I use it and it works fine for list. There's problem in document libraries with Office information panel (relevant only for Office documents).

answered Mar 12 '14 at 19:46

Jan Vanek

407
2
2

This is a very nice addon. I have a question too about a caml query in case it helps someone: http://sharepoint.stackexchange.com/questions/163537/caml-query-for-filtered-lookup-column – bgmCoder Nov 27 '15 at 20:46

Filtered lookup column with 'WHERE' clause

1 Answers1