Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / MainAccount Lookup Fie...
Finance | Project Operations, Human Resources, ...
Suggested answer

MainAccount Lookup Field Not Showing Data for Specific Users in Custom D365 F&O Screen

(1) ShareShare
ReportReport
Posted on by Mohammed Rafe 31

I have created a customized screen in Dynamics 365 F&O named 'Create Salaries GL.' Recently, I added two new lookup fields, 'MainAccount' and 'SchoolDim,' to the screen. While the 'SchoolDim' lookup works perfectly and displays data for all users, the 'MainAccount' lookup does not display any data for certain users, despite having valid data in the table.

I have ensured that the affected users have the appropriate roles and permissions for this screen, but the issue persists. Below is the code I used for the lookup logic for both fields:

MainAccount Lookup Code:
    [Control("String")]
    class MainAccountList
    {
        public void lookup()
        {
            MainAccountList.text("");
            SysTableLookup sysTableLookup = SysTableLookup::newParameters(tableNum(HcmWorker), this);
            Query query = new Query();
            QueryBuildDataSource qbds;
            QueryBuildRange qbr;

            qbds = query.addDataSource(tableNum(HcmWorker));
            qbds.addGroupByField(fieldNum(HcmWorker, MainAccountId));
            qbr = qbds.addRange(fieldNum(HcmWorker, MainAccountId));
            qbr.value(strFmt('!%1', queryValue('')));
            sysTableLookup.addLookupField(fieldNum(HcmWorker, MainAccountId));
            sysTableLookup.parmQuery(query);
            sysTableLookup.performFormLookup();
        }
    }

SchoolDim Lookup Code:
[Control("String")]
    class SchoolList
    {
        public void lookup()
        {
            SchoolList.text("");
            SysTableLookup sysTableLookup = SysTableLookup::newParameters(tableNum(SalaryManagement_EntryData_Sti), this);
            Query query = new Query();
            QueryBuildDataSource qbds;
            QueryBuildRange qbr;
    
            qbds = query.addDataSource(tableNum(SalaryManagement_EntryData_Sti));    
            qbds.addGroupByField(fieldNum(SalaryManagement_EntryData_Sti, School));
            query.queryType(QueryType::Join);
    
            qbr = qbds.addRange(fieldNum(SalaryManagement_EntryData_Sti, School));
            qbr.value(strFmt('!%1', queryValue('')));
            qbr = qbds.addRange(fieldNum(SalaryManagement_EntryData_Sti, DataAreaId));
            qbr.value(strFmt('%1', curExt()));
                
            sysTableLookup.addLookupField(fieldNum(SalaryManagement_EntryData_Sti, School));
    
            sysTableLookup.parmQuery(query);
            sysTableLookup.performFormLookup();
        }
    }

 

Note: The MainAccount field is based on the HcmWorker table, while the SchoolDim field is based on the SalaryManagement_EntryData_Sti table (a custom table).

Attached is a screenshot of the issue for further clarification.

Could anyone advise on why the MainAccount lookup might not be displaying data for some users and how to resolve this issue?

Thank you in advance for your assistance!"

 

Categories:
Dynamics 365 Finance
bb.png
  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 291,559 Super User 2024 Season 2 on at
    MainAccount Lookup Field Not Showing Data for Specific Users in Custom D365 F&O Screen
    Hi Mohammed,
     
    There can be a security policy active which is restricting the number of records for particular users. A security policy is an AOT object part of the Extensible Data Security (XDS) framework in Dynamics 365 F&O.
  • Martin Dráb Profile Picture
    Martin Dráb 230,579 Most Valuable Professional on at
    MainAccount Lookup Field Not Showing Data for Specific Users in Custom D365 F&O Screen
    Let me simplify your code a bit (and ignore the method that works correctly).
    Query query = new Query();

QueryBuildDataSource qbds = query.addDataSource(tableNum(HcmWorker));
qbds.addGroupByField(fieldNum(HcmWorker, MainAccountId));

QueryBuildRange qbr = qbds.addRange(fieldNum(HcmWorker, MainAccountId));
qbr.value(SysQuery::valueNotEmptyString());

SysTableLookup sysTableLookup = SysTableLookup::newParameters(tableNum(HcmWorker), this);
sysTableLookup.addLookupField(fieldNum(HcmWorker, MainAccountId));
sysTableLookup.parmQuery(query);
sysTableLookup.performFormLookup();
    Do the user see the data in the Workers form?

    What can you tell us about the MainAccountId field? Isn't it a surrogate key? If so, you'd need to handle the replacement fields too (such as grouping by them).
  • Suggested answer
    Waed Ayyad Profile Picture
    Waed Ayyad 6,623 Super User 2024 Season 2 on at
    MainAccount Lookup Field Not Showing Data for Specific Users in Custom D365 F&O Screen
     Hi,
     
    Did you try to trace the code to see the query? If not, then trace the code and check the query and try to use it in SQL, and check the result of it.

     Also try to use this qbr.value(SysQuery::valueNot(“”)); instead of  qbr.value(strFmt('!%1', queryValue('')));
     

    Thanks,

    Waed Ayyad

    If this helped, please mark it as "Verified" for others facing the same issue

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

Congratulations 2024 Spotlight Stars!

Kudos to all of our 2024 community stars!

Meet the Top 10 leaders for December!

Congratulations to our December super stars!

Get Started Blogging in the Community

Hosted or syndicated blogging is available! ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,559 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,579 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans