Hi all, 

I want to join StockItems and StockItemSupplier table using Sage.ObjectStore.Join below.

StockItems sis = new StockItems();
try
{
    Sage.ObjectStore.Join join = new Sage.ObjectStore.Join();
    join.RightTable = "StockItemSupplier";
    join.RightTableAlias = "StockItemSupplier";
    join.LeftTable = "StockItem";
    join.LeftTableAlias = "StockItem";
    join.Type = Sage.Common.Data.JoinType.Inner;
    Sage.ObjectStore.JoinKey joinkey = new Sage.ObjectStore.JoinKey("ItemID", "ItemID");
    join.Keys.Add(joinkey);
    sis.Joins.Add(join);
    
    if (productgroup != null)
    {
        sis.Query.Filters.Add(new Sage.ObjectStore.Filter(StockItem.FIELD_PRODUCTGROUPDBKEY, productgroup.ProductGroup));
    }
    if (stockitem != null)
    {
        sis.Query.Filters.Add(new Sage.ObjectStore.Filter("ITEMID", stockitem.Item)); //problem here - if use ITEMID, ambigous error. if use StockItem.ITEMID, invalid column name error.
    }
    if(analysisCodeValue != null)
    {
        sis.Query.Filters.Add(new Sage.ObjectStore.Filter(StockItem.FIELD_ANALYSISCODE1, analysisCodeValue.Value));
    }
    
    if (supplier != null)
    {
        join.Filters.Add(new Sage.ObjectStore.Filter("SupplierID", supplier.PLSupplierAccount));
    }
    sis.Find();
    
    return sis;
}
catch(Exception ex)
{
    Logger.WriteLog("Error:" + ex.ToString());
    throw ex;
}

Left Table = StockItem, alias StockItem

Right table = StockItemSupplier, alias StockItemSupplier.

The problem arise when I want to filter by ItemID, because both table have ItemID columns.

If I filter by ItemID, I will have ambiguous error. If I filter by alias table name + ItemID, I will have Invalid column name 'StockItem.ITEMID' error.

What is the correct way to do this?

Thank you