Skip to main content

Notifications

Announcements

No record found.

Community site session details

Community site session details

Session Id :
FastTrack for Dynamics 365 forum
Unanswered

Issue with Dropdown Lookup –

(4) ShareShare
ReportReport
Posted on by 235
Hi Everyone ,
 

Description:
I am implementing a lookup to populate a dropdown based on specific criteria, but it is not working as expected.

Criteria for Populating the Dropdown:

  1. The dropdown should display only those SchemeCode values where:
    • The status is "Released."
    • The SchemeCategory matches the value selected by the user in another dropdown.
  2. Scheme visibility rules:
    • Global Schemes → If a scheme exists in PrmSchemeTable but has no entry in PrmRegionScheme, it should be shown (✅ Allowed, but not duplicated).
    • Region-Specific Schemes → If a scheme exists in PrmRegionScheme, it should be shown only if assigned to the user’s region (✅ Show).
    • Multi-Region Schemes → If a scheme is assigned to multiple regions, it should be visible for its assigned regions only (✅ Allowed, but not duplicated).
    • Exclude Other Region Schemes → If a scheme exists only for other regions and not for the user’s region, it should NOT be shown (❌ Exclude).
    • A scheme assigned exclusively to another region (e.g., if S4 is assigned only to 'DEL', it should not be visible in 'KAR').

Issue:

While most of the logic is working correctly, the problem arises when a scheme is assigned to multiple regions—it does not appear in the dropdown at all.

What I Did:

  • Implemented a lookup function to filter schemes based on the above criteria.
  • Ensured schemes assigned to a specific region are visible only within that region.
  • Considered creating a separate table to store region-scheme mappings for filtering.
  •      public void SchemeLookup(FormStringControl _ctrl)
      {
          PRMUserSetup prmUserSetup;
          PRMRegion_Branch prmRegionBranch;
          PRmRegion prmRegion;
          Query query = new Query();
          QueryBuildDataSource qbdsScheme, qbdsRegion,qbdsExclude;
          QueryBuildRange qbrRegion;
          SysTableLookup sysTableLookup;
    
          str userSite, userRegionName, userRegionCode;
    
    
          select firstonly InventSiteId from prmUserSetup
          where prmUserSetup.UserId == curUserId();
    
          if (!prmUserSetup)
          return;
    
          userSite = prmUserSetup.InventSiteId;
    
    
          select firstonly Name from prmRegionBranch
          where prmRegionBranch.Code == userSite;
    
          if (prmRegionBranch)
          {
              userRegionName = prmRegionBranch.Name;
    
    
              select firstonly RegionCode from prmRegion
              where prmRegion.REGIONNAME == userRegionName;
    
              if (prmRegion)
              userRegionCode = prmRegion.RegionCode;
          }
    
    
          qbdsScheme = query.addDataSource(tableNum(PRMSchemeTable));
    
    
          qbdsRegion = qbdsScheme.addDataSource(tableNum(PrmRegionScheme)); 
          qbdsRegion.joinMode(JoinMode::OuterJoin);
    
          qbdsRegion.relations(false);
          qbdsRegion.addLink(fieldNum(PrmRegionScheme, SchemeCode), fieldNum(PRMSchemeTable, SchemeCode));
    
          qbdsScheme.addRange(fieldNum(PRMSchemeTable, Scheme_Category)).value(queryValue(PrmCustomerSchemeEntry.Scheme_Category));
          qbdsScheme.addRange(fieldNum(PRMSchemeTable, SchemeStatus)).value(queryValue(PrmSchemeStatus::Release));
    
    
          if (userRegionCode)
          {
    
              qbrRegion = qbdsRegion.addRange(fieldNum(PrmRegionScheme, RegionCode));
              qbrRegion.value(SysQuery::value(userRegionCode) + " || " + SysQuery::valueEmptyString());
          }
          else
          {
    
              qbdsRegion.addRange(fieldNum(PrmRegionScheme, RegionCode)).value(SysQuery::valueEmptyString());
          }
          qbdsExclude = qbdsScheme.addDataSource(tableNum(PrmRegionScheme));
          qbdsExclude.joinMode(JoinMode::NoExistsJoin);
          qbdsExclude.relations(false);
          qbdsExclude.addLink(fieldNum(PrmRegionScheme, SchemeCode), fieldNum(PRMSchemeTable, SchemeCode));
          qbdsExclude.addRange(fieldNum(PrmRegionScheme, RegionCode))
      .value(SysQuery::valueNot(userRegionCode) + " && " + SysQuery::valueNot(SysQuery::valueEmptyString()));
         
          sysTableLookup = SysTableLookup::newParameters(tableNum(PRMSchemeTable), _ctrl);
          sysTableLookup.addLookupField(fieldNum(PRMSchemeTable, SchemeCode));
          sysTableLookup.addLookupField(fieldNum(PRMSchemeTable, Scheme_Category));
          sysTableLookup.addLookupField(fieldNum(PrmRegionScheme, RegionCode));
          sysTableLookup.parmQuery(query);
          sysTableLookup.performFormLookup();
      }
    How can I modify my approach to ensure that schemes assigned to multiple regions appear in the dropdown correctly while still meeting all the specified criteria? If there is a better way to achieve this functionality, I am open to suggestions.

    Thanks,
    Ayushaman
  • Ayushaman Profile Picture
    235 on at
    Issue with Dropdown Lookup – Multi-Region Schemes Not Visible
    Hi Martin,
     
    Hope you are doing well!
     
    The issue arises when selecting a scheme in the dropdown. If a scheme is assigned to a specific region or multiple regions, it should only be visible to users from the designated region(s). Users from other regions should not see the scheme, as it is restricted to certain regions. 
    Note: 
    **prmCustomerSchemeEntry.Scheme_category is a enum "prmSchemeList" not string 

    Thanks,
    Ayushaman
  • Martin Dráb Profile Picture
    234,542 Most Valuable Professional on at
    Issue with Dropdown Lookup – Multi-Region Schemes Not Visible
    First of all, let me refactor your code a bit, to make it easier to understand.
    public void schemeLookup(FormStringControl _ctrl)
    {
        Query query = this.buildQuery(prmCustomerSchemeEntry.Scheme_Category, this.getUserRegionCode());
    
        SysTableLookup sysTableLookup = SysTableLookup::newParameters(tableNum(PRMSchemeTable), _ctrl);
        sysTableLookup.addLookupField(fieldNum(PRMSchemeTable, SchemeCode));
        sysTableLookup.addLookupField(fieldNum(PRMSchemeTable, Scheme_Category));
        sysTableLookup.addLookupField(fieldNum(PrmRegionScheme, RegionCode));
        sysTableLookup.parmQuery(query);
        sysTableLookup.performFormLookup();
    }
    
    private str getUserRegionCode()
    {
        PRmRegion prmRegion;
        PRMRegion_Branch prmRegionBranch;
        PRMUserSetup prmUserSetup;
    
        select firstonly RegionCode from prmRegion
            exists join prmRegionBranch
                where prmRegionBranch.Name == prmRegion.RegionName
                exists join prmUserSetup
                    where prmUserSetup.InventSiteId == prmRegionBranch.Code
                       && prmUserSetup.UserId == curUserId();
                   
       return prmRegion.RegionCode;
    }
    
    private Query buildQuery(str _schemeCategory, str _userRegionCode)
    {
        Query query = new Query();
        QueryBuildDataSource qbdsScheme = query.addDataSource(tableNum(PRMSchemeTable));
    
        QueryBuildDataSource qbdsRegion = qbdsScheme.addDataSource(tableNum(PrmRegionScheme)); 
        qbdsRegion.joinMode(JoinMode::OuterJoin);
    
        qbdsRegion.relations(false);
        qbdsRegion.addLink(fieldNum(PrmRegionScheme, SchemeCode), fieldNum(PRMSchemeTable, SchemeCode));
    
        qbdsScheme.addRange(fieldNum(PRMSchemeTable, Scheme_Category)).value(queryValue(_schemeCategory));
        qbdsScheme.addRange(fieldNum(PRMSchemeTable, SchemeStatus)).value(queryValue(PrmSchemeStatus::Release));
    
        qbdsRegion.addRange(fieldNum(PrmRegionScheme, RegionCode)).value(SysQuery::valueEmptyString());
        if (_userRegionCode)
        {
            qbdsRegion.addRange(fieldNum(PrmRegionScheme, RegionCode)).value(queryValue(_userRegionCode));
        }
    
        QueryBuildDataSource qbdsExclude = qbdsScheme.addDataSource(tableNum(PrmRegionScheme));
        qbdsExclude.joinMode(JoinMode::NoExistsJoin);
        qbdsExclude.relations(false);
        qbdsExclude.addLink(fieldNum(PrmRegionScheme, SchemeCode), fieldNum(PRMSchemeTable, SchemeCode));
        qbdsExclude.addRange(fieldNum(PrmRegionScheme, RegionCode))
            .value(SysQuery::valueNot(_userRegionCode) + " && " + SysQuery::valueNot(SysQuery::valueEmptyString()));
    }
    Do I understand correctly that you have a problem with the point "Multi-Region Schemes → If a scheme is assigned to multiple regions, it should be visible for its assigned regions only"?

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Ramesh Kumar – Community Spotlight

We are honored to recognize Ramesh Kumar as our July 2025 Community…

Congratulations to the June Top 10 Community Leaders!

These are the community rock stars!

Announcing the Engage with the Community forum!

This forum is your space to connect, share, and grow!

Leaderboard > FastTrack for Dynamics 365

#1
Adis Profile Picture

Adis 26 Super User 2025 Season 1

#2
Navneeth Nagrajan Profile Picture

Navneeth Nagrajan 20 Super User 2025 Season 1

Featured topics

Product updates

Dynamics 365 release plans