select multiple records to one field on a report by fetch method

Finance and Operations/AX Developer Forum
1

hello world

I’m doing a report that has to display userId, username, group and user group name.

userId, username and group are solved.

The trick is with the two: Group and UsergroupName

Group: retrieve only the users that are managers {solved}

UserGroupName: retrieve all other groups that the manager has permission to e.g Admin, DocHand etc {not solved}; the problem with this field is that it retrieves only one group for all the managers, instead of displaying all groups that each manager has permission to, whether it’s 5 groups or more or even less.

Here is my fetch method:

public boolean fetch()
{
boolean ret;
QueryRun qr;
QueryBuildDataSource qbs;
UserGroupList userGroupList;
UserGroupInfo userGroupInfo;
UserInfo userInfo;
qr = new QueryRun(element.query());

// qbs = qr.query().dataSourceTable(tablenum(UserGroupList));
ret = super();
while (qr.next())
{
userGroupList = qr.get(tablenum(UserGroupList));
name = (select name from userInfo where userInfo.id == userGroupList.userId).name;
element.send(userGroupList);
}
// return ret;
return true;
}

What am I doing wrong? Please help…

Thanks

Sinoyolo

2

Could you show us your query? I would expect something like this:

select id, name from UserInfo

    exists join UserGroupList as IsManager
        where IsManager.userId == UserInfo.id
           && IsManager.groupId == 'Managers'

    join groupId from UserGroupList
        where UserGroupList.userId == UserInfo.id

        join name from UserGroupInfo
            where UserGroupInfo.groupId == UserGroupList.id

I wouldn’t even override fetch() for this requirement.