• State Not Answered
  • Replies 13 replies
  • Subscribers 4 subscribers
  • Views 4266 views
  • Users 0 members are here
Related Discussions
Home » Discussions » Reports

Can anyone provide inputs on how can we achieve sorting around firstname/lastname instead of userid.

phanibabuk

Hi all,
We have a column in the db which stores userid.

We have a view that queries all other columns in addition to the userid.

While displaying the data on the grid, we are passing the userid to get the user's firstname and lastname.

Everything works fine, but when we sort on the this column, it sorts on the internal userid rather than the firstname/lastname that we had populated.

Can anyone provide inputs on how can we achieve sorting around firstname/lastname instead of userid.

Eg: Userid in db: "A123"

We are passing "A123" to a function to get its firstname/lastname which is "AAA 777".

Now, when we are sorting, it sorts on "A123" instead of "AAA 777".

One idea might be storing firstname/lastname in the db along with userid. If I use this approach, then I will have to modify my CDT to hold these values. I need to implement this change around several grids.

But, wanted to understand if there is any other way.

Any...

OriginalPostID-187277

  Discussion posts and replies are publicly visible

  • 0
    Hi panibabuk,
    try the below code in query filter:

    a!queryFilter(
    field: "userid",
    operator: "IN",
    value: {if(isnull(local!searchText),getdistinctusers(cons!ALL_USERS_GROUP),
                                  index(getdistinctusers(cons!ALL_USERS_GROUP),where(if({apply(search(local!searchText,_),
                                  apply(rule!getUserFullNameFromUserName(_),getdistinctusers(cons!ALL_USERS_GROUP)))}=0,false(),true()))))}
    )

    Before using that rule create one rule to get user fullname(rule!getUserFullNameFromUserName()) and create one constant for all user group.
  • 0
    Hi Phanibabuk,
    It looks like data value mismatch in the grid code which you have written.
    Could you please paste your grid code, so that We can analyse the code
  • 0
    @phanibabuk To the best of my knowledge, it's better to add the name (firstName+lasName) column to database object or update the cdt by adding a additional column and populate it with desired values before hand. Apart from this, any of the approach you take, such as writing expression rules etc complicates the simple functionality and further makes the processing time high for a simple sort operation. And just to let you know, whatever behavior you are currently seeing has no problems and the behavior is expected.
  • 0
    Apart from writing to the db, is there any way we can make this working. We are using a custom function to retrieve user firstname/lastname in the datagrid column using apply function.

    This retrieves the firstname/lastname as expected but sorting does not work.

  • 0
    Certified Senior Developer
    There can be one way. How about creating a dummy display cdt in a separate local variable to show the data on the grid. The display cdt will have all the data from the original cdt (which has been used as of now) and the function to change the user id to name.

    Now, when a user tries to sort, the data subset behind is sorting on the name of the user.
  • 0
    @phanibabuk Yes, the behavior is expected. Sorting only works on underlying data.

    As said by me earlier, an other way (apart from writing to DB) is to prepare a formatted cdt, add a new column in it and populate it with the function which you are currently using in the grid to retrieve firstname/lastname.

    I am not sure of any other way as of now, and even if one such exists, it complicates the simple sort functionality which is not intended as per my knowledge.
  • 0
    Could you please help with a piece of code of how to add firstname/lastname to the dummy cdt?
  • 0
    Certified Senior Developer
    =load(

    /* Assumption:'The employeeId column of myCDT is text'. If it is integer, then one has to create a duplicate cdt with employee id as text type (this cdt should only be used for display purpose). - see more comments below*/

    local!getData:/*"A query entity to fetch the required data*/

    /*The output is cdt in the format of
    [id:123 (int), employeeId: "A123" (text), designation:"Developer"(text)],
    [id:123 (int), employeeId: "A124" (text), designation: "Tester"]
    Now create a new local variable*/

    local!modifiedData:/*Here we will create a dummy cdt*/

    /*type!myCDT{
    id:index(local!getData,"id",null),
    employeeId:apply(rule!getFullname,index(local!getData,"employeeId",null)),
    designation:index(local!getData,"designation", null)
    }*/

    with(
    local!datasubset:todatasubset(local!modifiedData,local!paginInfo),
    /*create grid now*/
    )
    )
  • 0
    Certified Senior Developer
    to create a display cdt, we follow the below cdt..

    type!ABC_DisplayCDT{
    textColumn1,
    textColumn2,
    textColumn3,
    integer1,
    integer2,
    dateTimeColumn1,
    dateTimeColumn2
    }
© 2024 Appian. All rights reserved.
We want to hear from you! Submit a review on Gartner or G2.