Return a filtered value of USERID() in CalcFormula.

mysamzamysamza Member Posts: 66
I am creating a RoleCenter that will pull the total attendance of currently logged in user.

There is a table, let us call it MyTable that has records with the following columns.
Employee Code, Absent (this is a checkbox).

The Cue Table I have created for the RoleCentre I am creating, I created a field "Total Attendance" and want to write the following formula on its CalcFormula.
Filter through MyTable records with Employee Code that you find from USERID() then filter records with Absent unchecked.

This is how my CalcFormula looks as of now and I don't know how to properly user USERID() to get the Employee Code and then pass this value in the CalcFormula.

table 50043 "Cue Attendance"
{
DataClassification = CustomerContent;

fields
{
field(1; "Primary Key"; Integer)
{
DataClassification = CustomerContent;

}
field(2;"Total Attendance"; Integer)
{
FieldClass = FlowField;
CalcFormula = count("MyTable" where("Employee Code" = filter(USERID)));
}
}

keys
{
key(PK; "Primary Key")
{
Clustered = true;
}
}

Answers

  • ShaiHuludShaiHulud Member Posts: 228
    edited 2019-09-02
    There's basically two ways you could do it:
    1. Add FlowFilter type field "User ID Filter" to your "Cue Attendance" table. This will make your CalcFormula something like
    count("My Table" where("Employee Code" = field("User ID Filter")));
    
    And then on your Role Center's Activity page's OnOpenPage trigger add
    SETRANGE("User ID Filter", USERID);
    

    2. You could do the entire calculation on the OnOpenPage trigger. Something along the lines of
    OnOpenPage()
    MyTable.SETRANGE("Employee Code", USERID);
    TotalAttendance := MyTable.COUNT;
    
    And then you have a field in the Cue Group with SourceExpr of "TotalAttendance"
    To make it work just like FlowField, you'd also need to add some code on the drilldown code of that field:
    TotalAttendance - OnDrillDown()
    PAGE.RUNMODAL(0, MyTable); //assuming MyTable is global and doesn't get cleared at any point
    
Sign In or Register to comment.