Custom lookup on UserInfo - want Id for form filter but display Name

(0) Share

Report

Posted on by _MGP

506

I have a custom form, part of which is displaying the Security roles of users.

I want to be able to filter the roles based on a selected User. To do this I have created a StringEdit control which is not bound to any datasource. I have overridden the lookup() and modified() methods.

The lookup() has this code:

public void lookup()
{
    //super();

    Query                   query;
    QueryBuildDataSource    qbds;
    SysTableLookup          lookup;

    query = new Query();
    qbds = query.addDataSource(tableNum(UserInfo));

    lookup = SysTableLookup::newParameters(tableNum(UserInfo), this);
    lookup.parmQuery(query);
    lookup.addLookupfield(fieldNum(UserInfo, Name));
    lookup.addLookupfield(fieldNum(UserInfo, Id), true);

    lookup.performFormLookup();

}

This is returning the Id value because I want to filter on this.

The modified triggers the executeQuery() method of the main datasource:

public boolean modified()
{
    boolean ret;

    ret = super();

    TmpSecurityRole_DS.executeQuery();

    return ret;
}

The executeQuery() method filters the form based on all of the roles (using a custom query here):

public void executeQuery()
{
    Query                   query;
    QueryRun                queryRun;
    QueryBuildDataSource    formDataSource;
    SecurityRole            role;

    this.query().dataSourceTable(tableNum(TmpSecurityRole)).clearDynalinks();
    this.query().dataSourceTable(tableNum(TmpSecurityRole)).clearLinks();
    
    this.query().dataSourceTable(tableNum(TmpSecurityRole)).clearRanges();

    if(UserFilter.valueStr())
    {
        query = new Query(queryStr(AllSecurityRolesByUser));
        query.dataSourceTable(tableNum(SecurityUserRole)).addRange(fieldNum(SecurityUserRole, User)).value(UserFilter.valueStr());
        queryRun = new QueryRun(query);

        while(queryRun.next())
        {
            role = queryRun.get(tableNum(SecurityRole), 1);

            this.query().dataSourceTable(tableNum(TmpSecurityRole)).addRange(fieldNum(TmpSecurityRole, SecurityRoleAotName)).value(queryValue(role.AotName));
        }
    }
    
    super();
}

I would like the lookup to display the user name instead of the id which is more presentable.

I looked at Reference Groups but the UserInfo table is a system table so cannot be modified.

Is there a better way?

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (0)

Sohaib Cheema 49,438 User Group Leader on at

Like (0)

Report

A lookup do not stop you for joining tables. So you may join the UserInfo, with any other table that container username.

Was this reply helpful? Yes No
_MGP 506 on at

Like (0)

Report

I may be missing something here but (from the UserInfo table) I want to display the User name in the lookup but I want to use the Id to do the filtering.

Is this possible?

Was this reply helpful? Yes No
Sohaib Cheema 49,438 User Group Leader on at

Like (0)

Report

yes this is possible.

already I can see that you are using .addLookupField two times one for id and 2nd for Name. That makes sense. Your selected value wil always be UserId as you have passed true there.

Was this reply helpful? Yes No
_MGP 506 on at

Like (0)

Report

I should have been more clear.

After the user is selected then the lookup disappears. What is left in the StringEdit control is the UserId. I would like to know how to make this the User name.

I want to do 2 things: have access to the UserId for filtering and displaying the User name for better presentation.

Does this make sense?

Was this reply helpful? Yes No
Suggested answer

Chaitanya Golla 17,225 on at

Like (0)

Report
Hi,

My understanding from your requirement is that you want to have userId lookup and on selecting the userId, userName should be displayed in the stringEdit and at the sametime executeQuery of the formdatasource should operate on selected userId to get the relevant user roles. If we are inline, then perform below steps:

Declare userInfo table in the classDeclaration of the form like UserInfo userInfoLocal

1. Instead of stringEdit(namely UserFilter), make it a bound field on your temptable that extends EDT Name.

2. In the modified method of field UserFilter, as and when you select the userId fetch username to display it.

public boolean modified() { boolean ret; ret = super(); if (ret) { select userInfoLocal where userInfoLocal.id == UserFilter.valueStr(); SampleTmpTable.UserFilter = (userInfoLocal.name); // Replace with your temptable } SampleTmpTable_DS.executeQuery(); return ret; }

3. In the executequery method of the formdatasource, apply filter based on userInfoLocal.UserId

public void executeQuery() { Query query; QueryRun queryRun; QueryBuildDataSource formDataSource; SecurityRole role; this.query().dataSourceTable(tableNum(TmpSecurityRole)).clearDynalinks(); this.query().dataSourceTable(tableNum(TmpSecurityRole)).clearLinks(); this.query().dataSourceTable(tableNum(TmpSecurityRole)).clearRanges(); if(UserFilter.valueStr()) { query = new Query(queryStr(AllSecurityRolesByUser)); query.dataSourceTable(tableNum(SecurityUserRole)).addRange(fieldNum(SecurityUserRole, User)).value(queryValue(userInfoLocal.id));//.value(UserFilter.valueStr()); // Use filter from UserInfo table queryRun = new QueryRun(query); while(queryRun.next()) { role = queryRun.get(tableNum(SecurityRole), 1); this.query().dataSourceTable(tableNum(TmpSecurityRole)).addRange(fieldNum(TmpSecurityRole, SecurityRoleAotName)).value(queryValue(role.AotName)); } } super(); }

Was this reply helpful? Yes No
_MGP 506 on at

Like (0)

Report
Thanks.

I have tried your solution. In your code sample (3) I did change both instances of "UserFilter.valueStr()" to "userInfoLocal.Id".

Starting from a blank lookup I select User A. The role data is correctly displayed but the lookup ends up blank.

Then I select User B. User B's role data is displayed but User A is displayed in the lookup field.

If you close the form after the first selection and then re-open it then I see User A in the lookup. But now the role data is displayed without filter.

2 other perhaps minor points:-

1. I did not know how to remove the stored user settings from the lookup. The stored value persists but then the displayed data retrieved from executeQuery() is not correct. I tried to find a solution here but no luck.

2. I note in your modified() method

SampleTmpTable.UserFilter = (userInfoLocal.name); // Replace with your temptable

that we are only setting the current record in the datasource. If a user selects a row after the first in the displayed security role data then the lookup field will disappear because we have moved records. I looped over the datasource to set every record like below (maybe this is not best solution?)

public boolean modified() { TmpSecurityRole roleLocal; boolean ret; ret = super(); if(ret) { select userInfoLocal where userInfoLocal.id == UserFilter.valueStr(); //TmpSecurityRole.UserFilter = userInfoLocal.name; roleLocal = TmpSecurityRole_DS.getFirst() as TmpSecurityRole; while(roleLocal) { roleLocal.UserFilter = userInfoLocal.name; roleLocal = TmpSecurityRole_DS.getNext() as TmpSecurityRole; } } TmpSecurityRole_DS.executeQuery(); return ret; }

It would be good to try and make this solution work because it seems it is not too far away.

Thanks for your help so far.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi,

As per the description you have given, you have lookup displaying UserID & Username. Once you click on a value in the lookup & after the lookup disappears, you need the Username value to be displayed in the control. If the above statement is correct, you just need to modify your lookup() code to be able to achieve it:

public void lookup()

{

    //super();

Query                   query;

    QueryBuildDataSource    qbds;

    SysTableLookup          lookup;

query = new Query();

    qbds = query.addDataSource(tableNum(UserInfo));

lookup = SysTableLookup::newParameters(tableNum(UserInfo), this);

    lookup.parmQuery(query);

    lookup.addLookupfield(fieldNum(UserInfo, Name),true);//Add it here

    lookup.addLookupfield(fieldNum(UserInfo, Id));//Remove True parameter from ID field & add it to Name field

lookup.performFormLookup();

}

Please verify the answer if it helped you!!!

Was this reply helpful? Yes No