How to know the rownumber in an aggregate

How to know the rownumber in an aggregate

  

For a school project I've been working on a ranking system. I'm sorting a table of people on the amount of points, so, as it goes with a ranking, the more points, the higher on the ranking, person with the most points is at the top.

I am able to display the top 3, with just sorting the table on points and having a line count of 3 on the TableRecords. In another Table Records I want to show the personal rank of the person that's logged in. 

http://prntscr.com/lo6qp2 Screenshot for a better picture of what I mean. 

So on the screenshot it should 2. before Jelle on the ranking. My thought is that I need to know the row number that Jelle is in, in the aggregate. So like, how can I get the row number that Jelle is in? 

On the aggregate that I'm using for the personal ranking table I'm using the following filter statement:  

Medewerker.Email = Session.Username

As the email is the same as an username in this project. Any thoughts on how I can get the row number?


Stan ten Napel wrote:

For a school project I've been working on a ranking system. I'm sorting a table of people on the amount of points, so, as it goes with a ranking, the more points, the higher on the ranking, person with the most points is at the top.

I am able to display the top 3, with just sorting the table on points and having a line count of 3 on the TableRecords. In another Table Records I want to show the personal rank of the person that's logged in. 

http://prntscr.com/lo6qp2 Screenshot for a better picture of what I mean. 

So on the screenshot it should 2. before Jelle on the ranking. My thought is that I need to know the row number that Jelle is in, in the aggregate. So like, how can I get the row number that Jelle is in? 

On the aggregate that I'm using for the personal ranking table I'm using the following filter statement:  

Medewerker.Email = Session.Username

As the email is the same as an username in this project. Any thoughts on how I can get the row number?



Hi,

You just want to display the details of the logged in user?

Am i right?

I want to display the rank the current user has. So in order to do that, I guess I need to know what rownumber the current user has with the sorting I'm using (aka sorting on points)

Hello Stan,

I would say that would simplify (a lot) your work if you just store the ranking in the entity as well as the points, and from time to time you recalculate the ranking (could be a timer, set to run every dary, for example).

This way you don't need to fetch everybody up to the user to know the rank of the logged user and this would have a positive impact in your application performance.

Cheers.

Eduardo Jauch wrote:

Hello Stan,

I would say that would simplify (a lot) your work if you just store the ranking in the entity as well as the points, and from time to time you recalculate the ranking (could be a timer, set to run every dary, for example).

This way you don't need to fetch everybody up to the user to know the rank of the logged user and this would have a positive impact in your application performance.

Cheers.

How would I store the ranking in the entity? Like, with the sorting on points (descending) I have my ranking, I just need to add numbers from 1 to ... Can't find a way to do that though. 

When I'm googling I'm finding more general SQL queries about ROW_NUMBER() and RANK(), but I can't seem to get these to work, neither are these available in the Outsystems aggregate thing it looks like.


Hi Stan ten Napel,

Lists have a property CurrentRowNumber you can use for this. If you are displaying your records in a Table Records (YourTableRecords) you can use <YourTableRecords>.List.CurrentRowNumber for that... but be aware that as soon as you start using filters, sorting by other columns etc... that row number will loose it's "ranking" meaning.

What Eduardo is suggesting is that you create a timer that regularly performs the query (YourAggregate) sorted by points, and then for each record assigns to the (new) Ranking Entity Attribute its <YourAggregate>.List.CurrentRowNumber. This means that you can display this ranking anywhere and it will not depend on you getting all records and sorting by points.

Hi Stan ten Napel,

 what  scenario are you following for two persons having same points. If this is not in your scenario then you can just add an system's server action called "ListIndexOf" after the aggregate which is giving you result as per the image you shared and then assign Rank local variable with 

SyntaxEditor Code Snippet

ListIndexOf.Position + 1




Hi Stan ten Napel,

 what  scenario are you following for two persons having same points. If this is not in your scenario then you can just add an system's server action called "ListIndexOf" after the aggregate which is giving you result as per the image you shared and then assign Rank local variable with 

SyntaxEditor Code Snippet

ListIndexOf.Position + 1



Or you are having records with same points then you need two aggregates 

1. First aggregate is to get logged-in person's details(Points).

2. Second aggregate is to get number of records having points greater-than points in first aggregate. and in second aggregate the points attribute should be set to group-by.

3. Then you can assign Rank variable to second aggregate list length +1.


Solution

Hi Stan ,

As per my understanding , your aggregate is already sorted with respect to the points . If you are using using as list Record or table record ,then my suggestion is to use expression control and add the following value there.

Consider the aggregate name is 'GetAccountOwners' then the expression should have the following value

GetAccountOwners.CurrentRowNumber + 1 [The index starts from 0 so add 1].

Now if you want to show the logged in user rank then in that case use your current aggregate and create a loop and then check for the condition if the aggregate username is same as that of the session username.

Some thing like this.

[GetAccountOwners.List.Current.AccountOwner.UserName =Session.Username]

if its equal then store the [GetAccountOwners.CurrentRowNumber + 1 ] in a variable . This will be the current user rank. [ alsoI would suggest to end the loop if you found any match in order to increase the performance of your logic].

This is the simplest thing that can be done and should your purpose I believe.


Solution