Hello,

I've created a custom column in my table and I want to use calculate to get the sum of the custom column and display it at the bottom of the table. I can't figure out how to reference the custom column since it doesn't have a column name, and I can't reference it by "label". Is there a way to name custom columns?

My custom column:

        array(
            "label"=>"Test",
            "value"=>function($row) {
                $result = $row["TTL_MINUTES_WRKD"] / 60;

                $result=round($result, 2, PHP_ROUND_HALF_UP);

                return $result;
            },
        ),

Where I'm using calculate on other columns:

"calculate"=>array(
               
                "{sumTotal}"=>array("sum","TOTAL"),
                
                "{sumPTO}"=>array("sum","HOURS_OFF"),
                
                "{sumWrkdHrs}"=>array("sum","WRKD_HRS")
            ),
            "top"=>"<b>{USRNAME}</b>",
            "bottom"=>"<b>{USRNAME}: Total Hrs: {sumTotal} | Total PTO Hrs: {sumPTO} | Total Worked Hrs: {sumWrkdHrs}</b>"    
        ),
        ),

Thanks for the response, however, I'm struggling to use Calculated Column in my pages and I'm not sure if that's the right solution. Let me provide some more context.

Here is my table code on my .view page: `

Table::create(array(
    "dataStore"=>$this->dataStore("paybyp"),

    "grouping"=>array(
    "STR_ID","USRNAME"=>array(
        "calculate"=>array(
            "{sumTotal}"=>array("sum","TOTAL"),

            "{sumPTO}"=>array("sum","HOURS_OFF"),

            "{sumWrkdHrs}"=>array("sum","WRKD_HRS")
        ),

        "top"=>"<b>{USRNAME}</b>",

        "bottom"=>"<b>{USRNAME}: Total Hrs: {sumTotal} | Total PTO Hrs: {sumPTO} | Total Worked Hrs: {sumWrkdHrs}</b>"    
    ),
    ),
    
    "sorting"=>array(
        "PERIOD_DT"=>"asc"
    ),

    "columns"=>array(
    
    "STR_ID"=>array(
        "label"=>"Store",
        "type"=>"string",
        "prefix"=>""
    ),
    "USRNAME"=>array(
        "label"=>"Name",
        "type"=>"string",
        "prefix"=>""
    ),
    "PERIOD_DT"=>array(
        "label"=>"Punch Date",
        "type"=>"datetime",
        "format"=>"Y-m-d",
        "displayFormat"=>"Y-m-d"
    ),
    "HOURS_OFF"=>array(
        "label"=>"PTO",
    ),
    "PD_HRS"=>array(
        "label"=>"Paid Hrs",
    ),
    "PD_MINUTES"=>array(
        "label"=>"Mins",
        "type"=>"float"
    ),
    "PD_TIM"=>array(
        "label"=>"Actual Time",
        "type"=>"time",
        "format"=>"H:i:s"
    ),
    array(
        "label"=>"Test",
        "value"=>function($row) {

            //some calculations

            return $result;
        },
    ),
    "TOTAL"=>array(
        "label"=>"Total Hrs",
    ),
        
    ),
));

Near the top of my table code I'm using "calculate" and referencing columns like "TOTAL", I'm able to take the sum of the column, save it to {sumTotal} and display it using "bottom".

Toward the bottom of my table code I've created a custom column labeled "Test" where I would like to perform some calculations and return the result. I cannot for the life of me figure out how to reference that column at the top so I can take the sum and display it like I am with the other columns.

It might be important to mention that the data for my columns is coming from a SQL query on my setup page, here's the code from that page.

   $this->src('payroll')
    ->query("SELECT A.USR_ID
    ,A.STR_ID
    ,A.PAY_FREQ
    ,A.PERIOD_START_DATE
    ,A.PERIOD_END_DATE
    ,CONVERT(DATETIME,A.[Date])PERIOD_DT
    ,B.PD_HRS
    ,B.PD_MINUTES
    ,B.TTL_MINUTES_WRKD
    ,COALESCE(B.ACT_TIM,CAST('00:00:00' AS TIME(0)))ACT_TIM
,COALESCE(B.PD_TIM,CAST('00:00:00' AS TIME(0)))PD_TIM
    ,COALESCE(B.WRKD_HRS, 0) + COALESCE(D.HOURS_OFF, 0) as TOTAL
,C.NAM AS USRNAME
    ,D.MINUTES_OFF
    ,D.HOURS_OFF
    FROM [TABLE] A
    LEFT JOIN [TABLE] B
    ON A.[Date] = B.TIMCRD_DAT
    AND A.USR_ID = B.USR_ID
    AND A.STR_ID = B.STR_ID
    INNER JOIN [TABLE] C
    ON A.USR_ID = C.USR_ID
LEFT JOIN [TABLE] D 
ON A.USR_ID = D.USR_ID 
AND A.STR_ID = D.STR_ID 
AND A.[Date] = D.PTO_DT 
WHERE 1 = 1 "
    .($query_params[":min"]  ? " AND A.[Date] >= :min" : "")
    .($query_params[":max"]  ? " AND A.[Date] <= :max" : "")
    .($query_params[":user"] ? " AND A.[USR_ID] IN (:user)" : "")
    )
    ->params($query_params)
  
    ->pipe($this->dataStore('paybyp'));

} } ` If the solution is to use Calculated Column, can you provide an example showing how to use it with the way I'm setting up my table?

Thanks for the help

That worked, thanks!