Dynamics Community Forum Thread Details

How to use range on datasource in a view to filter records

Hi,

I am using Event Handler to customize a lookup field OrderAccount while creating a new Purchase Order (PurchCreateOrder form). I need to filter this lookup to get vendor records only which exist in PdsApprovedVendorList table irrespective of ItemId. Also it is possible that PdsApprovedVendorList table can have multiple records for a given vendor under Release product Item. (Release Product -> setup). Also, need to fetch name, nameAlias & address of vendor in the lookup. So, To achieve this, I created a view & add all 4 dataSources as needed with innerJoin on all child dataSources & using this view as SystableLookup in lookup Event Handler.

Also, I am filtering records based on SiteId of vendor. Now, I have select a SiteId -1 & then those records are coming in lookup which has siteId -1. but getting two records in lookup for an Account no- 1001, bcz two records exist in PdsApprovedVendorList table for this vendor- 1001. Now, what's could be the possible way to get only single record for this vendor in lookup irrespective of how many records exist in PdsApprovedVendorList table for vendor- 1001.

[FormControlEventHandler(formControlStr(PurchCreateOrder, PurchTable_OrderAccount), FormControlEventType::Lookup)]
    public static void PurchTable_OrderAccount_OnLookup(FormControl sender, FormControlEventArgs e)
    {
        FormStringControl               formCtrlSiteId;
        Query                           query = new Query();
        QueryBuildDataSource            qbdsVendTable;

        formCtrlSiteId = sender.formRun().design().controlName(formControlStr(PurchCreateOrder,HSHTSiteId));

        SysTableLookup sysTableLookup = SysTableLookup::newParameters(tableNum(HSHTVendTableRecordsView),sender);
        qbdsVendTable = query.addDataSource(tableNum(HSHTVendTableRecordsView));

        sysTableLookup.addLookupfield(fieldNum(HSHTVendTableRecordsView,AccountNum));
        sysTableLookup.addLookupfield(fieldNum(HSHTVendTableRecordsView,Name));
        sysTableLookup.addLookupfield(fieldNum(HSHTVendTableRecordsView,NameAlias));
        sysTableLookup.addLookupfield(fieldNum(HSHTVendTableRecordsView,InventSiteId));
        sysTableLookup.addLookupfield(fieldNum(HSHTVendTableRecordsView,City));
        sysTableLookup.addLookupfield(fieldNum(HSHTVendTableRecordsView,State));
        sysTableLookup.addLookupfield(fieldNum(HSHTVendTableRecordsView,CountryRegionId));
        sysTableLookup.addLookupfield(fieldNum(HSHTVendTableRecordsView,ZipCode));
        sysTableLookup.addLookupfield(fieldNum(HSHTVendTableRecordsView,PrimaryContactPhone));
        sysTableLookup.addLookupfield(fieldNum(HSHTVendTableRecordsView,PrimaryContactEmail));
        sysTableLookup.addLookupfield(fieldNum(HSHTVendTableRecordsView,HSSiteId));

        qbdsVendTable.addRange(fieldNum(HSHTVendTableRecordsView,InventSiteId)).value(queryValue(formCtrlSiteId.valueStr()));

        // Run the query
        sysTableLookup.parmQuery(query);


        // Run the lookup
        sysTableLookup.performFormLookup();

        //cancel the call to super() to prevent the system from trying to show
        //the lookup form twice and cause an error.
        FormControlCancelableSuperEventArgs cancelableSuperEventArgs = e as FormControlCancelableSuperEventArgs;
        cancelableSuperEventArgs.CancelSuperCall();

    }

Thanks,

Harshit

Quick Links