How to get the value of a field in a portal based on another field on the same row on the same portal?

[emtau]

So I have a Table "Daily Leave" related to "User Profile" table. I created a "Daily Leave" portal on the User Profile layout and started adding other fields to this portal; Year, Type of Leave, Leave Entitlement, start leave, resume work etc... please check attached picture.

So the Leave Type field is a popup menu which let you choose the type of Leave you want to take e.g. Annual Leave, Sick Leave, Compassionate Leave, Leave without pay or Maternity Leave. So based on the this field "Leave Type" I want to show their last "Balance" like shown in the picture. I am able to show the Annual last balance which is 16 but its only because it is the last in the list so if I add a Sick leave after that it will change and not stay at 16 since Sick Leave has a different remaining Balance than Annual leave.

So how can I do something like that? Show only the last values of the "Balance" field based on the "Leave Type" field?

Thank you.

[comment]

The convenient method to produce such display would be a report in list view, where you can sort the records by type and use a summary field to show a running total.

If you want to show the remaining balance in a portal, you will need to add a self-join relationship to all previous leaves of the same employee of the same type in the same year. 

 

[emtau]

I do have a list view report where a user can use the "Leave" button to filter Print/View just a single type of leave for their perusal.Check picture below;

But out of convenience I would like to make it easy for users to just view their remaining balances where "Remaining Balance" is as I mentioned.. instead of clicking few buttons to get to see the whole summary report when they could just see it beforehand if possible.

Can I get those last leave balances on the User Profile layout (Where "Remaining Balance" is)? If not then I will just leave it like that.

Thank you again.

1 hour ago, comment said:

If you want to show the remaining balance in a portal, you will need to add a self-join relationship to all previous leaves of the same employee of the same type in the same year. 

I don't want to show it in the portal... the portal shows it btw but rather mixed up with other leave types and previous leave records etc... I want to show it in the User Profile table just right above the portal check first picture.

[comment]

1 hour ago, emtau said:

I would like to make it easy for users to just view their remaining balances where "Remaining Balance" is

I am not sure how that would work, without the user selecting the year to view. I am assuming an employee can have different allowances for each type in every year. 

With a report, you can sort the records by year and by type and show only the sub-summary parts - so they could easily get an overview with a single click.

 

1 hour ago, emtau said:

the portal shows it btw

How exactly?

 

[emtau]

Okay thanks I've decided to just leave it as it is and get rid of the "Remaining balance" summary on top. Thanks a lot.