Issue with Dropdown Lookup –

(4) Share

Report

Posted on by Ayushaman

271

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:

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.
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

Categories:

Dynamics 365 Finance

Other

I have the same question (0)

All responses (2)

Answers (0)

Martin Dráb 237,965 Most Valuable Professional on at

Like (0)

Report
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"?

Was this reply helpful? Yes No
Ayushaman 271 on at

Like (0)

Report

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

Was this reply helpful? Yes No

Join the conversation

Community site session details