EP: Create code a Lookup and apply filter (in Visual C#) with two joined tables

EP: Create code a Lookup and apply filter (in Visual C#) with two joined tables

Here In this example, I want to display the records, from EcoResCategory Table for the fields Name and Code.

 

Here I am filtering the records which have, CategoryHierarchy 

EcoResCategoryHierarchyRole.CategoryHierarchy == EcoResCategoryHierarchy.RecId

 

 #region Code to ProductCategory lookup
    //ProductCategory Lookyp
    ///

    /// ProductCategory Lookup event handle method, this method will get Product from EcoResCatogory.
    ///

    ///
    ///
    protected void ProductCatLookup_Lookup(object sender, AxLookupEventArgs e)
    {
        AxLookup lookup = e.LookupControl;
        int EcoResCategoryId = TableMetadata.TableNum(this.AxSession, "EcoResCategory");
        //Create the lookup dataset � we will do a lookup in the EcoResCategory table
        using (Proxy.SysDataSetBuilder dataSetBuilder = Proxy.SysDataSetBuilder.constructLookupDataSet(AxaptaAdapter, TableMetadata.TableNum(EcoResCategoryLookupTableName)))
        {
            // Set the run time generated data set as the lookup data set
            lookup.LookupDataSet = new DataSet(AxSession, dataSetBuilder.toDataSet());
        }

        lookup.LookupDataSet.Init();
        using (Proxy.Query query = lookup.LookupDataSet.DataSetViews[0].MasterDataSource.query())
        {
            using (Proxy.QueryBuildDataSource dataSource = query.dataSourceName("EcoResCategory"))
            {
                TableMetadata EcoResCategoryMetadata = MetadataCache.GetTableMetadata(this.AxSession, EcoResCategoryId);
                TableDataFieldMetadata categoryHierarchyField =(TableDataFieldMetadata)EcoResCategoryMetadata.Fields.GetByName("CategoryHierarchy");
                //TableDataFieldMetadata nameField = (TableDataFieldMetadata)EcoResCategoryMetadata.Fields.GetByName("Name");
                

                using (Proxy.QueryBuildRange range = dataSource.addRange(categoryHierarchyField.FieldId))
                {
                    range.status = (int)Proxy.RangeStatus.Open;
                    range.value = Convert.ToString(this.GetEcoResCategoryHierarchyRoleByRole());                  
                }
            }
        }

        lookup.Fields.Add(AxBoundFieldFactory.Create(ProductCatLookup.LookupDataSetViewMetadata.ViewFields["Name"]));
        lookup.Fields.Add(AxBoundFieldFactory.Create(ProductCatLookup.LookupDataSetViewMetadata.ViewFields["Code"]));

        lookup.SelectField = "RecId";
        lookup.HideSelectField = true;

        RequisitionerRecId.Text = string.Empty;
        RequisitionerName.Text = string.Empty;
        this.EnabledDisableItemId(false);
        this.EnableDisableProductCat(true);
       
    }

 

///

    /// ProductCatogory on ok click event handel method, this mehod will assign RecID to ProductCategoryRecID text box.
    ///

    ///
    ///
    protected void ProductCatLookup_OnOkClicked(object sender, AxLookupEventArgs e)
    {
        string recIdValue = ProductCategoryRecID.Text;
        long recId = 0;

        if (recIdValue == null ||
            recIdValue.Length <= 0 ||
            recIdValue == "0" ||
            long.TryParse(recIdValue, out recId) == false)
        {
            ProductCategoryRecID.Text = string.Empty;
            ProductCategoryName.Text = string.Empty;
        }
        else
        {
            ProductCategoryName.Text = GetProductCategoryFromRecId(recId);
        }
    }
   
    private string GetProductCategoryFromRecId(long recId)
    {
        //string ProductNamel;
        using (AppProxy.EcoResCategory lEcoResCategory = AppProxy.EcoResCategory.find(recId))
        {
            ProductNamel = lEcoResCategory.Name;
        }
        return ProductNamel;
    }
    protected long GetEcoResCategoryHierarchyRoleByRole() // call Table method EcoResCategoryHierarchyRole getHierarchiesByRole
    {
        using (AppProxy.EcoResCategoryHierarchyRole lEcoResCategoryHierarchy = AppProxy.EcoResCategoryHierarchyRole.avaFindHierarchiesByRole(SalesCat))
        {
            ecoResCategoryHierarchyrecId = lEcoResCategoryHierarchy.CategoryHierarchy;
        }
        return ecoResCategoryHierarchyrecId;
    }